Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

Date Dimension script optimized for Analysis Services KeyColumn property (Part 2)

leave a comment »


Here is the beginning of the stored procedure that generates DimDate data:

create procedure [sp_PopulateDateDimension]


                 @StartDate smalldatetime,

                 @EndDate smalldatetime





    set nocount on


    declare @CurrentDate smalldatetime

    set @CurrentDate = @StartDate


    — Working table for intermediate results

    declare @DimDate table (

    DateKey int,

    Date smalldatetime,

    Date1 char(10),

    Date2 char(11),

    Date3 varchar(20),

    DateLong varchar(30),                                                                        


    –year data                          

    CalendarYearKey smallint,

    CalendarYearLong char(7), 

    DayNumberOfYear smallint,


    –semester data

    CalendarSemesterKey int,

    CalendarSemesterOfYearNum tinyint,

    CalendarSemesterOfYear1 char(16),

    CalendarSemesterOfYear2 char(5),

    CalendarSemesterLong char(11),


    –quarter data

    CalendarQuarterKey int,

    CalendarQuarterOfYearNum  tinyint,

    CalendarQuarterOfYear1 char(15),

    CalendarQuarterOfYear2 char(5),

    CalendarQuarterLong char(11),


    –month data

    CalendarMonthKey int,

    CalendarMonthOfYearNum tinyint,                                    

    MonthNameLong varchar(10),

    MonthNameShort varchar(3),

    CalendarMonthOfYear2 varchar(30),

    CalendarMonthOfYear3 varchar(10),

    DayNumberOfMonth tinyint, 


    –week data

    CalendarWeekKey integer,

    CalendarWeekOfYearNum tinyint,               

    CalendarWeekOfYear1 varchar(13),

    CalendarWeekOfYear2 varchar(10),

    CalendarWeekLong varchar(16),

    DayNameOfWeekLong varchar(10), 

    DayNameOfWeekShort varchar(3),

    DayNumberOfWeek tinyint,

    WorkDay tinyint,


    –fiscal year data

    FiscalYearKey smallint,

    FiscalYearLong char(7),


    –fiscal semester data

    FiscalSemesterKey int,

    FiscalSemesterOfYearNum tinyint,

    FiscalSemesterOfYear1 char(5),

    FiscalSemesterLong char(11),


    –fiscal quarter data

    FiscalQuarterKey int,

    FiscalQuarterOfYearNum tinyint,

    FiscalQuarterOfYear1 char(5),

    FiscalQuarterLong char(11),


    –fiscal month data

    FiscalMonthKey int,

    FiscalMonthOfYearNum tinyint,                            

    FiscalMonthOfYear1 varchar(7),

    FiscalMonthLong varchar(12),


    –fiscal week data

    FiscalWeekKey int,

    FiscalWeekOfYearNum tinyint,                     

    FiscalWeekOfYear1 varchar(7),   

    FiscalWeekLong  varchar(12),                      


    –holiday data

    Holiday tinyint,

    HolidayName varchar(15),


    RecordCreateDate smalldatetime,

    RecordUpdateDate smalldatetime



Written by Konstantin Gorakine

July 15, 2009 at 12:47 pm

Posted in SSAS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: