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