Expression du besoin :

Un besoin récurrent est de définir une table de dimension temps et de l'alimenter.

Mise en place :

Le script ci-dessous crée une structure simplifiée de la table « DimDate » issue de la base d'exemple AdventureWorksDW.

En effet, par simplicité, seule les informations de langue anglaise sont enregistrées dans cette table.

L'alimentation de la table est réalisé à l'aide d'une procédure stockée qui prend en paramètre les deux bornes des dates.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))

DROP TABLE [dbo].[DimDate]

GO

CREATE TABLE [dbo].[DimDate](

    [DateKey] [int] identity NOT NULL,

    [FullDateAlternateKey] [date] NOT NULL,

    [DayNumberOfWeek] [tinyint] NOT NULL,

    [EnglishDayNameOfWeek] [nvarchar](10) NOT NULL,

    [DayNumberOfMonth] [tinyint] NOT NULL,

    [DayNumberOfYear] [smallint] NOT NULL,

    [WeekNumberOfYear] [tinyint] NOT NULL,

    [EnglishMonthName] [nvarchar](10) NOT NULL,

    [MonthNumberOfYear] [tinyint] NOT NULL,

    [CalendarQuarter] [tinyint] NOT NULL,

    [CalendarYear] [smallint] NOT NULL,

    [CalendarSemester] [tinyint] NOT NULL,

CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED

(

    [DateKey] ASC

))

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FillDimDate]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[FillDimDate]

GO

Create Procedure FillDimDate

    @starting_dt datetime

    ,@ending_dt datetime

as

set datefirst 1

declare @cntr_day datetime,@diff int,@cntr int;

SET @cntr = 0

select @diff = datediff(dd,@starting_dt,@ending_dt)

while @cntr <= @diff

begin

    select @cntr_day = dateadd(dd,@cntr,@starting_dt)

    insert into [DimDate]

    (FullDateAlternateKey,

    DayNumberOfWeek ,

    EnglishDayNameOfWeek,

    DayNumberOfMonth,

    DayNumberOfYear,

    WeekNumberOfYear,

    EnglishMonthName,

    MonthNumberOfYear,

    CalendarQuarter,

    CalendarYear,

    CalendarSemester)

    select

    @cntr_day

    ,datepart(dw,@cntr_day)

    ,case datepart(dw,@cntr_day)

    when 1 then 'Monday'

    when 2 then 'Tuesday'

    when 3 then 'Wednesday'

    when 4 then 'Thursday'

    when 5 then 'Friday'

    when 6 then 'Saturday'

    when 7 then 'Sunday'

    end

    ,datepart(day,@cntr_day)

    ,datepart(dy,@cntr_day)

    ,datepart(wk,@cntr_day)

    ,case datepart(mm,@cntr_day)

    when 1 then 'January'

    when 2 then 'February'

    when 3 then 'March'

    when 4 then 'April'

    when 5 then 'May'

    when 6 then 'June'

    when 7 then 'July'

    when 8 then 'August'

    when 9 then 'September'

    when 10 then 'October'

    when 11 then 'November'

    when 12 then 'December'

    end

    ,datepart(mm,@cntr_day)

    ,datepart(qq,@cntr_day)

    ,datepart(yy,@cntr_day)

    ,case (datepart(mm,getdate()))

    when 1 then 1

    when 2 then 1

    when 3 then 1

    when 4 then 1

    when 5 then 1

    when 6 then 1

    else 2

    end

    set @cntr = @cntr + 1

end

GO

EXECUTE FillDimDate @starting_dt ='2006-01-01',@ending_dt = '2009-12-31'

GO