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

53

4

2009-12-31

2009

53

4

2010-01-01

2010

1

1

2010-01-02

2010

1

1

2010-01-03

2010

2

1

2010-01-04

2010

2

1

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

TimeByDay

TimeYear

TimeWeek

TimeQuarter

2009-12-29

2009

53

4

2009-12-30

2009

53

4

2009-12-31

2009

53

4

2010-01-01

2010

53

4

2010-01-02

2010

53

4

2010-01-03

2010

1

1

2010-01-04

2010

1

1

Startet die Woche am Montag, verschiebt sich der erste Tag

TimeByDay

TimeYear

TimeWeek

TimeQuarter

2009-12-29

2009

53

4

2009-12-30

2009

53

4

2009-12-31

2009

53

4

2010-01-01

2010

53

4

2010-01-02

2010

53

4

2010-01-03

2010

53

4

2010-01-04

2010

1

1

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

Ja

United Kingdom und viele andere Länder in Europa

Montag

4 Januar,
Erster Donnerstag,
4–7 Tage der Woche

Nein

ISO 8601, Norwegen, Schweden, Deutschland

Montag

7 Januar,
Erster Montag,
7 Tage

Nein

 

Mittwoch

1 Januar,
Erster Donnertag,
1–7 Tage

Ja

 

Samstag

1 Januar,
Erster Freitag,
1–7 Tage

Ja

 

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

    (

    SELECT

        -- 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)

    FROM

        (

        SELECT

            --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):

BEGIN TRAN

UPDATE [MSP_TIMEBYDAY]

SET FISCALYEAR=TIMEYEAR,

FISCALQUARTER=

CASE

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,

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

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

clip_image001

Nachdem alle Schritte durchgeführt wurden, sollte das Ergebnis ähnlich aussehen wie folgt:

clip_image002