Générer une dimension date à l’aide d’une CTE récursive (MAJ)

Un besoin récurrent lors de la création d’un DataWareHouse est la génération de la dimension date.

Les approches pour arriver à notre fin sont nombreuses:

• Génération depuis l’assistant SSAS
• Utiliser des générateurs de temps
• Utiliser SSIS
• Ou encore utiliser un script SQL (la solution que nous allons voir ici)

Vous trouverez le détail des approches ci-dessus ici => http://thomasricquebourg.wordpress.com/2011/01/17/creer-une-time-dimension-avec-ssis/

Toutefois, j’ai un point de discorde avec les méthodes ci-dessus, l’utilisation d’un idTemps auto-incrémenté ! Plus de détails chez David Joubert

Pourquoi utiliser un idTemps auto-incrémenté alors que la date nous fournit un ID entier et unique fonctionnellement utilisable ? Utilisez le format YYYYMMJJ pour faire votre ID cela n’a que des avantages !

Voici donc un exemple de script de génération de dimension temps utilisant une CTE Recursive au sein d’une fonction table. (Merci Florian Eiden et David Joubert, Mis à jour avec Romuald)

-- =============================================
-- Author:        Romuald Coutaud
-- Create date: 2012-10-03
-- Description:    Requête de génération d'un table de calendrier pour la dimension Date
-- =============================================
ALTER FUNCTION [dbo].[getDimDate]
(    
    @DateStart    DATE = '2010-01-01',
    @DateEnd    DATE = NULL
)
RETURNS TABLE 
AS
RETURN 
    (
    WITH DateCTE
    AS (
        SELECT cast(@DateStart AS DATETIME) Datevalue
        UNION ALL
        SELECT datevalue + 1
        FROM DateCTE
        WHERE datevalue + 1 < = @DateEnd
        )
    SELECT
        [DateId] = CAST(CONVERT(CHAR(8), Datevalue, 112) AS INT)
        ,[Date] = CAST(Datevalue AS DATE)
        ,[DayOfYear] = CAST(DATENAME(dayofyear, Datevalue) AS INT)
        ,[DayMonth] = CAST(DATENAME(d, datevalue) AS INT)
        ,[DayName] = CAST(DATENAME(DW, Datevalue) AS NVARCHAR(10))
        ,[DayOfWeekId] = DATEPART(DW, datevalue)
        ,[WeekName] = CAST(DATENAME(WEEK, Datevalue) AS INT)    
        ,[Year] = CAST(DATENAME(year, DateValue) AS INT)
        ,[QuarterId] = DATENAME(year, DateValue)+ CAST(DATENAME(QUARTER,DateValue)AS VARCHAR(1))
        ,[QuarterOfYearId] = DATEPART(QUARTER,DateValue)
        ,[QuarterName] = 'Q'+CAST(DATENAME(QUARTER,DateValue)AS VARCHAR(1)) + ' ' + CAST(Year(DateValue) AS CHAR(4))
        ,[QuarterOfYearName] = 'Q'+CAST(DATENAME(QUARTER,DateValue)AS VARCHAR(1))
        ,[MonthId] = CAST(CONVERT(VARCHAR(6), Datevalue, 112) AS INT)
        ,[MonthOfYearId] = CAST(MONTH(DateValue) AS INT)
        ,[MonthName] = UPPER(LEFT(DATENAME(Month, DateValue),1)) + SUBSTRING(DATENAME(Month, DateValue),2,LEN(DATENAME(Month, DateValue))-1) + ' ' + CAST(Year(DateValue) AS CHAR(4))
        ,[MonthOfYearName] = UPPER(LEFT(DATENAME(Month, DateValue),1)) + SUBSTRING(DATENAME(Month, DateValue),2,LEN(DATENAME(Month, DateValue))-1)
    FROM DateCTE D
    )

SELECT * FROM dbo.getDimDate('20120101','20121231')
OPTION (MAXRECURSION 0)

Quelques explications:

1. SELECT datevalue + 1
    FROM DateCTE
   WHERE datevalue + 1 < = @DateEnd

   Nous allons générer des dates jusqu’à la @DateEnd spécifiée en entrée.

2. OPTION (MAXRECURSION 0)

   N’oublions pas de préciser l’option MaxRecursion 0 car par défaut le nombre de recursions maxium est limité à 100, autant dire un peu juste pour une dimension date.

En conclusion, cette méthode permet de générer en un minimum de code une dimension date assez flexible.

Et surtout n’utilisez pas d’auto-incrément pour votre id de table, privilégiez plutôt un YYYYMMJJ qui s’obtient facilement avec CAST(CONVERT(VARCHAR(8), Datevalue, 112) AS INT) et qui permet entre autre chose de pouvoir partitionner les tables de fait assez simplement en gardant le sens fonctionnel de notre identifiant.

Vous pouvez télécharger le script SQL de la fonction getDimDate.

[Edit du 15/08/2015] Si vous avez besoin d’une dimension temps