Créer une Time Dimension avec SSIS

Une Time Dimension contient toutes les données d’une période définie, c’est une dimensiion plutôt stable car elle ne connait aucun changement.
Un seul chargement de données est suffisant pour alimenter cette dimension.
Il est très important de fixer au départ la granularité (Année, Trimestre, Mois, Jour etc…). Et rien ne sert d’alimenter la dimension temps avec une dizaine d’années …il est préférable de chager 2 à 3 années et de rajouter au moment voulu les  années futures.
La dimension Temps est une dimension incontournable dans un data warehouse. On peut générer cette dimension de différentes manières:

–>Option 1 : Utilisation de l’assitant de création de dimension dans SSAS.

Cet assistant à l’avantage d’être simple, rapide  et il gère différents types de calendriers.
L’inconvénient majeure c’est qu’il n’est pas très pratique pour le choix du formatage et des langues.
Il est conseillé d’utiliser cet assitant dans le cadre d’une preuve de concept…

–>Option 2 : Créer un script SQL

CREATE TABLE Dim_Time_Generated 
( 
DateID       INT        NOT NULL    PRIMARY KEY 
,MonthName    VARCHAR(9) NOT NULL 
,MonthNumber  INT        NOT NULL 
,DayNumber   INT        NOT NULL 
,DayName      VARCHAR(9) NOT NULL 
,Quarter      INT        NOT NULL 
,QuarterName  CHAR(7)    NOT NULL 
,Year         INT        NOT NULL 
,FullDate DATETIME NOT NULL 
)
BEGIN 
SET DATEFIRST 1 
DECLARE @startDate  DATETIME 
DECLARE @endDate    DATETIME 
DECLARE @date       DATE 
DECLARE @DateId  INT 
DECLARE @cpt INT 
SET @startDate  =    '2009-01-01' 
SET @endDate    =    '2009-12-31'
SET @cpt        =    0 
SET @date       =    DATEADD(dd, @cpt, @startdate)
WHILE    @date <= @enddate 
BEGIN 
SET @dateId = CONVERT(VARCHAR(8), @date, 112)
INSERT INTO Dim_Time_Generated(DateID,MonthName,MonthNumber,DayNumber,DayName,Quarter,QuarterName,Year,FullDate)
VALUES ( @DateID 
,DATENAME(mm, @date)
,DATEPART(mm, @date)
,DATEPART(dd, @date)
,DATENAME(DW, @date)
,DATEPART(qq, @date) 
,'Q' + DATENAME(qq, @date)
,DATEPART(yy, @date)  
, @date )
SET  @cpt   =    @cpt + 1 
SET  @date  =    DATEADD(dd, @cpt, @startdate) 
END
END

Les scripts SQL permettent d’avoir plus de liberté et de contrôle sur les données.

–>Option 3 : Utiliser des générateurs de temps

Il existe des générateur de script SQL pour créer votre dimension des temps comme ce site : http://www.regnecentralen.dk/time_dimension_generator.html

–>Option 4 Utiliser SSIS

Il est possible d’utiliser SSIS pour créer sa propre Time Dimension.
Voici une dimension Temps très simple dans un data warehouse.

Dans un projet SSIS on créé les variables suivantes :

StartDate et EndDate : ces variables vont spécifier la période de temps que l’on souhaite avoir dans notre dimension.
MonthName et DayName sont des variables SSIS, elles vont être utilisées car SSIS ne possède pas de fonction pour récupérer directement le nom du mois et le nom du jour.

On créé un Dataflow, qui possèdera cette structure:

Le script C# est de type Source. Il nous permettra de récupérer toute les dates de la période spécifiée mais aussi le nom des mois et des jours.
Dans l’éditeur du composant script on ajoute les 4 variables SSIS dans la propriété:ReadOnlyVariables.


Puis on créé les sorties comme ceci:

On peut maitenant éditer le code C# du composant script, plus précisément la méthode CreateNewOutPutRows() :

 public override void CreateNewOutputRows()
 {
 /*
 Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
 For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
 */
 DateTime tmp = this.Variables.StartDate;
 do
 {
 TimeOutputBuffer.AddRow();
 TimeOutputBuffer.FullDate = tmp; //Récupération des dates au format MM/JJ/AAAA
 TimeOutputBuffer.MonthName = tmp.ToString("MMMM"); //Le nom du mois (Janvier,Février...)
 TimeOutputBuffer.DayName = tmp.ToString("dddd"); //Le nom du jour (Lundi, Mardi Mercredi ...)
 tmp = tmp.AddDays(1);

 } while (tmp <= this.Variables.EndDate);
//NOTE IMPORTANTE:
//Les fonctions utilisées retournent des valeurs dans langue utilisée par le système.

On utilise un composant Derived Column pour créer les autres champs nécessaires

Maitenant il ne reste plus qu’à mettre une destionation OLE DB vers votre dimension de votre Data warehouse.
Dans notre cas j’ai utilisé un Union All et un activer un Data Viewer pour vous montrez le résultat.

Une réflexion sur “Créer une Time Dimension avec SSIS

  1. Bonjour,
    Quelques petits soucis avec le script SQL de génération d’une dimension Temps. L’instruction qui générait la concaténation yyyymmdd était hors de la boucle, et avait donc toujours la même valeur.

    Une proposition complémentaire : http://pastebin.com/Z8T5N15V (j’ai remplacé l’id numérique autoincrémenté par la concaténation yyyymmdd en tant que clef primaire).

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s