Vous êtes nombreux à avoir remarqué que cette année, le premier jour de l’année ne correspondait pas à celui qui est calculé en suivant la norme ISO.

Le but de cet article est de vous expliquer les raisons de cette différence et de vous proposer une solution de contournement.

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:

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

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:

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

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

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:

Premier jour de la semaine

La première semaine de l'année contient

Semaines assignées deux fois

Utilisé par/dans

Dimanche

1 janvier,

Premier samedi,

1 à 7 jours de l'année

Oui

États-Unis

Lundi

1 janvier,

Premier dimanche,

1 à 7 jours de l'année

Oui

Majorité de l'Europe et Royaume-Uni

Lundi

4 janvier,

Premier jeudi,

4 à 7 jours de l'année

Non

ISO 8601, Norvège et Suède

Lundi

7 janvier,

Premier lundi,

7 jours de l'année

Non

 

Mercredi

1 janvier,

Premier mardi,

1 à 7 jours de l'année

Oui

 

Samedi

1 janvier,

Premier vendredi,

1 à 7 jours de l'année

Oui

 

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

2. Mettre à jour les champs [FiscalYear], [FiscalQuarter] et [FiscalPeriodName] avec la nouvelle numérotation:

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

image

 

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

image

 

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