Parce que toutes vos analyses business sont « Time » dépendantes, il est crucial de bien modéliser cette dimension. Malheureusement, force est de constater qu’elle est en général montée à la va-vite.
Je vous invite à respecter les bonnes pratiques suivantes :
- Le plus petit grain de cette dimension ne doit pas excéder le jour. Si vous avez besoin d’analyse par tranche horaire, créer une dimension spécifique permettant de les lister
- Utiliser une clé technique numérique de type entier en formatant la date selon le masque suivant YYYYMMDD. Cela permet d’optimiser le stockage, réduire les I/O et faciliter la maintenance (1 entier est codé sur 4 octets un datetime sur 8 soit pour une table de 100 millions d’enregistrements un gain 400 Mo)
- Associer à chaque attribut un code numérique et un libellé, cela permet de gérer les valeurs « Indéfini » et « Inconnu »
- Définir avec le métier le premier jour de la semaine, et l’intervalle de date sur lequel doit être généré votre dimension temps.
Voyons désormais comment mettre en place une dimension Temps basique, permettant de définir :
- un calendrier mensuel (de type Année -> Semestre -> Trimestre -> Mois -> Jour)
- et un calendrier hebdomadaire (du type Année Iso -> Semaine Iso -> Jour)
Il convient dans un premier temps de définir la structure de notre table :
CREATE TABLE [Common].[DimCalendrier]( [IdCalendrier] [int] NOT NULL, --Date au format iso YYYYMMDD [CodeDate] [datetime] NOT NULL, --Date [Date] [nvarchar](10) NOT NULL, --Date, valeur formatée à afficher (DD/MM/YYYY) [CodeAnnee] [int] NOT NULL, --Clé Année [Annee] [nvarchar](10) NOT NULL, --Libellé Année [CodeSemestre] [int] NOT NULL, --Clé Semestre [Semestre] [nvarchar](10) NOT NULL, --Libellé Semestre [CodeTrimestre] [int] NOT NULL, --Clé Trimestre [Trimestre] [nvarchar](10) NOT NULL, --Libellé Trimestre [CodeMoisAnnee] [int] NOT NULL, --Clé Mois [MoisAnnee] [nvarchar](20) NOT NULL, --Libellé Mois [CodeAnneeSemaineIso] [int] NOT NULL, --Clé Année de la semaine Iso [AnneeSemaineIso] [nvarchar](10) NOT NULL, --Libellé Année de la semaine Iso [CodeSemaineIso] [int] NOT NULL, --Clé Semaine Iso [SemaineIso] [nvarchar](10) NOT NULL, --Libellé Semaine Iso [JourAnnee] [nvarchar](10) NOT NULL, --Numéro du jour de l’année [JourMois] [nvarchar](10) NOT NULL, --Numéro du jour du mois (1 à 31) [JourSemaine] [nvarchar](10) NOT NULL, --Numéro du jour de la semaine (1 à 7) [Jour] [nvarchar](30) NOT NULL, --Nom du jour [CodeMois] [int] NOT NULL, --Clé du mois (1 à 12) [Mois] [nvarchar](30) NOT NULL, --Libellé du mois [DateAnneePrecedente] datetime NOT NULL, --Même jour de la semaine A-1 CONSTRAINT [PK_DimCalendrier] PRIMARY KEY CLUSTERED ([IdCalendrier] ASC) ) GO
et sa petite sœur anglaise :
CREATE TABLE [Common].[DimCalendar]( [CalendarID] [int] NOT NULL, --Date au format iso YYYYMMDD [DateCode] [datetime] NOT NULL, --Date [DateName] [nvarchar](10) NOT NULL, --Date, valeur formatée à afficher (DD/MM/YYYY) [YearCode] [int] NOT NULL, --Clé Année [YearName] [nvarchar](10) NOT NULL, --Libellé Année [SemesterCode] [int] NOT NULL, --Clé Semestre [SemesterName] [nvarchar](10) NOT NULL, --Libellé Semestre [TrimesterCode] [int] NOT NULL, --Clé Trimestre [TrimesterName] [nvarchar](10) NOT NULL, --Libellé Trimestre [YearMonthCode] [int] NOT NULL, --Clé Mois [YearMonthName] [nvarchar](20) NOT NULL, --Libellé Mois [IsoYearOfWeekCode] [int] NOT NULL, --Clé Année de la semaine Iso [IsoYearOfWeekName] [nvarchar](10) NOT NULL, --Libellé Année de la semaine Iso [IsoWeekCode] [int] NOT NULL, --Clé Semaine Iso [IsoWeekName] [nvarchar](10) NOT NULL, --Libellé Semaine Iso [DayNumberOfYear] [nvarchar](10) NOT NULL, --Numéro du jour de l’année [DayNumberOfMonth] [nvarchar](10) NOT NULL, --Numéro du jour du mois (1 à 31) [DayNumberOfWeek] [nvarchar](10) NOT NULL, --Numéro du jour de la semaine (1 à 7) [DayName] [nvarchar](30) NOT NULL, --Nom du jour [MonthCode] [int] NOT NULL, --Clé du mois (1 à 12) [MonthName] [nvarchar](30) NOT NULL, --Libellé du mois [SameDayLastYear] datetime NOT NULL, --Même jour de la semaine A-1 CONSTRAINT [PK_DimCalendar] PRIMARY KEY CLUSTERED ([CalendarID] ASC) ) GO
Passons désormais à l’alimentation de celle-ci :
SQL Server ne disposant pas de fonctions natives permettant de récupérer l’année et la semaine Iso associées à une date, il est nécessaire de définir une fonction scalaire spécifique. Cela nous permettra de créer le calendrier hebdomadaire. Ci-dessous le code de la dite fonction :
CREATE FUNCTION [Common].[ufn_ISOWeek] (@date DATETIME) RETURNS nvarchar(6) AS BEGIN DECLARE @ISOWeek INT, @year int, @rVal nvarchar(6); SET @ISOWeek = DATEPART(wk,@date) + 1 - DATEPART(wk,CAST(DATEPART(yyyy,@date) as char(4)) + N'0104'); SET @year = DATEPART(yyyy,@date); SET @rVal = CAST(@year as nvarchar(4)) + RIGHT(N'00' + CAST(@ISOWeek as nvarchar(2)),2); IF (@ISOWeek = 0) BEGIN SET @rVal = Common.ufn_ISOWeek(CAST(DATEPART(yyyy,@date) - 1 AS char(4)) + N'12' + CAST(24 + DATEPART(DAY,@date) as char(2))) + 1; END IF ( ( DATEPART(mm,@date) = 12 ) AND((DATEPART(dd,@date) - DATEPART(dw,@date)) >= 28) ) BEGIN SET @ISOWeek = 1; SET @year=DATEPART(yyyy,@DATE)+1; SET @rVal = CAST(DATEPART(yyyy,@date) + 1 as nvarchar(4)) + N'01' END RETURN @rVal; END GO
L’alimentation de la dimension se fait par l’intermédiaire d’une CTE, veillez à bien définir avec le métier les informations suivantes :
- le premier jour de la semaine
- le langage utilisé pour les libellés de vos membres temporels
- les bornes de votre calendrier
--Initialisation du premier jour de la semaine
SET DATEFIRST 1
GO
--Initialisation de la langue du calendrier
SET LANGUAGE FRENCH
GO
--Définition d'une table temporaire permettant de définir les membres Inconnu et Indéfini
select -2 as CodeDefaut,cast('Inconnu' as nvarchar(9)) LibDefaut,cast('Inconnu' as nvarchar(9)) as type
into #Defaut
union
select -1,'Indéfini','Indéfini';
-- Initialisation du calendrier
declare @startDate datetime = '20010101';
declare @endDate datetime = '21000101';with cte
as
(
select
cast(convert(nvarchar(8),@startDate,112) as int) as IdCalendrier,
@startDate as CodeDate
union all
select
cast(convert(nvarchar(8),cte.CodeDate + 1,112) as int) as IdCalendrier,
cte.CodeDate + 1 as CodeDate
from cte
where cte.CodeDate + 1 < @endDate
)
insert into [Common].[DimCalendrier]
(IdCalendrier, CodeDate, Date, CodeAnnee, Annee, CodeSemestre, Semestre, CodeTrimestre,
Trimestre, CodeMoisAnnee, MoisAnnee, CodeAnneeSemaineIso, AnneeSemaineIso, CodeSemaineIso,
SemaineIso, JourAnnee, JourMois, JourSemaine, Jour, CodeMois, Mois, DateAnneePrecedente)
select
cte.IdCalendrier,
cte.CodeDate,
convert(nvarchar(10),cte.CodeDate,103) as Date,
year(cte.CodeDate) as CodeAnnee,
cast(year(cte.CodeDate) as nvarchar(9)) as Annee,
cast(cast(year(cte.CodeDate) as nvarchar(4)) + cast(case when month(cte.CodeDate) < 7 Then 1 Else 2 end as nvarchar(1)) as int) as CodeSemestre,
cast(cast(year(cte.CodeDate) as nvarchar(4)) + N', S' + cast(case when month(cte.CodeDate) < 7 Then 1 Else 2 end as nvarchar(1)) as nvarchar(9)) as Semestre,
cast(cast(year(cte.CodeDate) as nvarchar(4)) + cast(datepart(q,cte.CodeDate) as nvarchar(1)) as int) as CodeTrimestre,
cast(cast(year(cte.CodeDate) as nvarchar(4)) + N', T' + cast(datepart(q,cte.CodeDate) as nvarchar(1)) as nvarchar(9)) as Trimestre,
cast(cast(year(cte.CodeDate) as nvarchar(4)) + RIGHT(N'0' + cast(month(cte.CodeDate) as nvarchar(2)),2) as int) as CodeMoisAnnee,
cast(cast(year(cte.CodeDate) as nvarchar(4)) + N', ' + UPPER(LEFT(datename(month,cte.CodeDate),1)) + LOWER(Right(datename(month,cte.CodeDate),LEN(datename(month,cte.CodeDate)) - 1)) as nvarchar(20)) as MoisAnnee,
cast(left(Common.ufn_ISOWeek(cte.CodeDate),4) as int) CodeAnneeSemaineIso,
cast(left(Common.ufn_ISOWeek(cte.CodeDate),4) as nvarchar(9)) AnneeSemaineIso,
cast(Common.ufn_ISOWeek(cte.CodeDate) as int) as CodeSemaineIso,
cast(left(Common.ufn_ISOWeek(cte.CodeDate),4) + N', S' + right(Common.ufn_ISOWeek(cte.CodeDate),2) as nvarchar(9)) as SemaineIso,
cast(datepart(dy,cte.CodeDate) as nvarchar(9)) as JourAnnee,
cast(datepart(d,cte.CodeDate) as nvarchar(9)) as JourMois,
cast(datepart(dw,cte.CodeDate) as nvarchar(9)) as JourSemaine,
UPPER(LEFT(datename(dw ,cte.CodeDate),1)) + LOWER(RIGHT(datename(dw ,cte.CodeDate),LEN(datename(dw ,cte.CodeDate)) - 1)) as Jour,
month(cte.CodeDate) as CodeMois,
UPPER(LEFT(datename(month,cte.CodeDate),1)) + LOWER(Right(datename(month,cte.CodeDate),LEN(datename(month,cte.CodeDate)) - 1)) as Mois,
DATEADD(week, ROUND(52.1775 * -1, 0),cte.CodeDate) as DateAnneePrecedente
from cte
union
select CodeDefaut,N'1900-01-01', LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, LibDefaut, LibDefaut, LibDefaut, LibDefaut, CodeDefaut, LibDefaut, N'1900-01-01'
from #Defaut
where type = 'Inconnu'
union
select CodeDefaut, N'1900-01-02', LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, CodeDefaut, LibDefaut, LibDefaut, LibDefaut, LibDefaut, LibDefaut, CodeDefaut, LibDefaut, N'1900-01-02'
from #Defaut
where type = 'Indéfini'
option (maxrecursion 0);
GO
Il conviendra bien sûr de modifier la liste des champs pour alimenter la table dans sa version anglaise.
Et voilà, vous avez une dimension temps toute propre, il ne vous reste plus qu’à ajouter certaines spécificités métiers (calendrier fiscal par exemple, jour ouvré, ouvrable …) mais le socle est là.
MS BI Addict 
Really informative article post.Thanks Again. Awesome. fddeeeadfebd