Alimentation d’une table de dimension temps

  • Article

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