La 1ere semaine de Janvier 2010 dans OLAP ne correspond pas au calendrier ISO

Le calendrier utilisé par Project server pour construire la dimension [Time] du cube OLAP se trouve dans la table Reporting.dbo.MSP_TimeByDay.

La dimension [Time] utilise les champs suivants:

·         [TimeByDay]

·         [TimeDayOfTheWeek]

·         [TimeMonthOfTheYear]

·         [TimeYear]

·         [TimeDayOfTheMonth]

·         [TimeWeekOfTheYear]

·         [TimeQuarter]

La numérotation de la semaine est basée sur le champ [TimeWeekOfTheYear] qui commence le 1er janvier, conformément à la norme de calcul américaine:

Mais il existe plusieurs autres normes pour le calcul de la numérotation des semaines. La norme ISO 8601 est l’une d’entre elles.

ISO 8601 inclut le système semaine-date ISO, un système de numérotation pour les semaines. Chaque semaine est associée à l'année dans laquelle jeudi se produit. Par exemple, la semaine 1 de 2004 (2004W01) a eu lieu du lundi 29 décembre 2003 au dimanche 4 janvier 2004. Le numéro de semaine le plus élevé dans une année peut être 52 ou 53:

• Si la semaine commence le dimanche:

• Si la semaine commence le lundi:

Ce style de numérotation est utilisé en général dans les pays européens, plus rarement ailleurs.

Le système de numérotation des différents pays peut ne pas se conformer à la norme ISO. Il existe au moins six possibilités comme illustré dans le tableau suivant:

Il existe 2 solutions pour contourner ce problème :

1. Utiliser les champs dédiés aux périodes fiscales de la table Reporting.dbo.MSP_TimeByDay (implique qu’aucune période fiscale n’ait été définie)
2. Créer ses propres dimensions dans le cube OLAP (non traitée ici)

IMPORTANT : la solution consistant à modifier la numérotation des semaines et trimestres dans les champs [TimeWeekOfTheYear] et [TimeQuarter] n’est pas supportée.

Pour implémenter la solution n°1, il faut:

1. Créer une fonction SQL personnalisée qui calcule et renvoie le 1er jour de l’année

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

• Si le trimestre commence le 1er janvier:

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

• Si le trimestre commence le 1er jour de l’année (2010, 2011 et 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

3. Construire un cube OLAP

4. Créer une vue basée sur les champs de période fiscale:

Une fois toutes ces étapes réalisées, dans l’Analyseur de Données, vous obtenez les informations suivantes :

Nous n’avons pas encore de retour d’expérience sur l’utilisation de cette solution, donc n’hésitez pas à nous laisser des commentaires et des suggestions afin d’améliorer et de compléter cet article si nécessaire.

Bonne lecture.

Marc Biarnès