Diese Informationen basiert auf den Informationen im Blog von Brian Smith zu dem Thema.
Mit dem Beginn des Jahres 2010 trat in Europa das Problem auf, dass die erste Woche des Jahres anders berechnet wird als in US, was sich in den OLAP Cubes und den Ansichten der Datenanalyse zeigt.
Bezogen auf die ISO Norm 8601 beginnt die Nummerierung der ersten Woche des Jahres mit der ersten kompletten Woche, bezogen auf 2010 ab dem 4. Jan 2010. Die Tage davor werden noch zu der Woche 53 des Vorjahres gezählt.
Analysis Services und somit der OLAP Cube verwenden diese Zählung allerdings nicht und sie läßt sich auch nicht einstellen. Die erste Woche des Jahres beginnt am ersten Tag des Jahres, also am 1. Jan.2010
Der Kalender auf dem der OLAP Cube basiert, ist in der Reporting Datenbank in der Tabelle dbo.MSP_TimeByDay enthalten.
Die Zeitdimensionen werden in den folgenden Feldern abgebildet:
· [TimeByDay] · [TimeDayOfTheWeek] · [TimeMonthOfTheYear] · [TimeYear] · [TimeDayOfTheMonth] · [TimeWeekOfTheYear] · [TimeQuarter]
Die Wochennummerierung basiert auf dem Feld [TimeWeekOfTheYear] . Das Startdatum dort ist der 01.01.2010 bzw. 2010-01-01 und basiert auf dem US Standard:
TimeByDay
TimeYear
TimeWeek
TimeQuarter
2009-12-29
2009
53
4
2009-12-30
2009-12-31
2010-01-01
2010
1
2010-01-02
2010-01-03
2
2010-01-04
Aber es gibt noch weitere Bedingungen, die die Wochennummerierung beeinflussen.
So ändert sich die Wochennummer abhängig davon, ob die Woche am Sonntag oder am Montag startet.
Startet die Woche am Sonntag, dann ergibt sich folgende Zählung
Startet die Woche am Montag, verschiebt sich der erste Tag
Das Nummerierungssystem in den verschiedenen Länder Europas decken sich nicht immer mit den Vorgaben der ISO Norm. Letztendlich gibt es 6 Möglichkeiten wie in der Tabelle angezeigt wird:
Erster Tag der Woche
Erste Woche des Jahres enthält
Woche wird zwei mal zugeordnet
Verwendet in
Sonntag
1 Januar, Erster Samstag 1–7 Tage
Ja
USA
Montag
1 Januar, Erster Sonntag, 1–7 Tage
United Kingdom und viele andere Länder in Europa
4 Januar, Erster Donnerstag, 4–7 Tage der Woche
Nein
ISO 8601, Norwegen, Schweden, Deutschland
7 Januar, Erster Montag, 7 Tage
Mittwoch
1 Januar, Erster Donnertag, 1–7 Tage
Samstag
1 Januar, Erster Freitag, 1–7 Tage
Da wir die Berechnung an sich nicht ändern können, haben wir eine Workaround entwickelt, der in der Reporting Datenbank gegen die Tabelle MSP_TimeByDay ausgeführt wird.
Dieser Workaround beinhaltet zu einem eine benutzerdefinierte SQL Funktion F_ISO_WEEK_OF_YEAR und zum anderen eine Stored Procedure, um die abweichenden Wochennummern in die Tabelle zu speichern. Dazu werden die “Fiscal” Felder benutzt, die normalerweise durch die Finanzzeiträume gefüllt werden..
Hier nun die zweiteilige Lösung:
1. Erstellen der ISO Funktion, die die erste Woche des Jahres ermittelt
CREATE FUNCTION DBO.F_ISO_WEEK_OF_YEAR
(
@DATE DATETIME
)
RETURNS INT
AS
/*
FUNCTION F_ISO_WEEK_OF_YEAR RETURNS THE ISO 8601 WEEK OF THE YEAR FOR THE DATE PASSED.
*/
BEGIN
DECLARE @WEEKOFYEAR INT
SELECT
-- COMPUTE WEEK OF YEAR AS (DAYS SINCE START OF YEAR/7)+1
-- DIVISION BY 7 GIVES WHOLE WEEKS SINCE START OF YEAR.
-- ADDING 1 STARTS WEEK NUMBER AT 1, INSTEAD OF ZERO.
@WEEKOFYEAR =
(DATEDIFF(DD,
-- CASE FINDS START OF YEAR
CASE
WHEN NEXTYRSTART <= @DATE THEN NEXTYRSTART
WHEN CURRYRSTART <= @DATE THEN CURRYRSTART
ELSE PRIORYRSTART
END,@DATE)/7)+1
FROM
-- FIRST DAY OF FIRST WEEK OF PRIOR YEAR
PRIORYRSTART = DATEADD(DD,(DATEDIFF(DD,-53690,DATEADD(YY,-1,AA.JAN4))/7)*7,-53690),
-- FIRST DAY OF FIRST WEEK OF CURRENT YEAR
CURRYRSTART = DATEADD(DD,(DATEDIFF(DD,-53690,AA.JAN4)/7)*7,-53690),
-- FIRST DAY OF FIRST WEEK OF NEXT YEAR
NEXTYRSTART = DATEADD(DD,(DATEDIFF(DD,-53690,DATEADD(YY,1,AA.JAN4))/7)*7,-53690)
--FIND JAN 4 FOR THE YEAR OF THE INPUT DATE
JAN4 = DATEADD(DD,3,DATEADD(YY,DATEDIFF(YY,0,@DATE),0))
) AA
) A
RETURN @WEEKOFYEAR
END
GO
2. Aktualisieren der Felder [FiscalYear], [FiscalQuarter] und [FiscalPeriodName] mit den neuen Werten:
-- Zeitraum startet am 1. Jan 2010:
BEGIN TRAN
UPDATE [MSP_TIMEBYDAY]
SET FISCALYEAR=TIMEYEAR,
FISCALQUARTER=TIMEQUARTER,
FISCALPERIODNAME = 'Week' + CAST(DBO.F_ISO_WEEK_OF_YEAR(TIMEBYDAY) AS NVARCHAR(2))
WHERE TIMEBYDAY BETWEEN '2010-01-01 0:00:00' AND '2012-12-31 23:59:59'
COMMIT TRAN
-- Zeitraum startet mit dem ersten Montag (2010, 2011 and 2012):
FISCALQUARTER=
WHEN DATEPART(DAY,TIMEBYDAY)<DAY(DATEADD(DD,(DATEDIFF(DD,-53690,DATEADD(DD,3,DATEADD(YY,DATEDIFF(YY,0,TIMEBYDAY),0)))/7)*7,-53690)) AND (DATEPART(MONTH, TIMEBYDAY)-1)%3 = 0 THEN TIMEQUARTER-1
ELSE TIMEQUARTER
END,
Bitte beachten
Die Schreibweise des Datums kann abweichen, mit deutschen Einstellungen wird die Zeile folgendermaßen geändert
TIMEBYDAY BETWEEN '01.01.2010 0:00:00' AND '31.12.2012 23:59:59'
Sollten Sie den Cube über einen größeren Zeitraum erstellen, dann sollten Sie sicherstellen, dass dieser Zeitraum durch die Funktion erfasst wird, sonst sehen Sie Daten vor dem 1.1.2010 in den Ansichten nicht.
Sollten Sie eine andere Bezeichung als “Week” wünschen, dann ändern Sie diese in der Zeile FISCALPERIODNAME = 'Week' durch die gewünschte Bezeichnung.
3. Erstellen Sie einen neuen OLAP Cube
4. Erstellen einer Datenanlyse Ansicht basierend auf Fiscal Feldern:
In einer deutschen Umgebung werden die Fiscal-Felder unter den Bezeichnung “Geschäftuhrzeit” geführt
Nachdem alle Schritte durchgeführt wurden, sollte das Ergebnis ähnlich aussehen wie folgt: