Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

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

leave a comment »

 

    –specify Sunday as the 1st day of the week.

    set datefirst 7

 

    –generate dates only

    while @CurrentDate <= @EndDate

      begin

         insert @DimDate (Date) values (@CurrentDate)

 

         — move to the next day

         set @CurrentDate = dateadd(day, 1, @CurrentDate)

    end

 

    –current timestamp

    set @CurrentDate = getdate()   

 

    –load initial keys for year calendar

    update @DimDate

       set Date1 = convert(char(10), Date, 101),

           Date2 = replace(convert(varchar(11), Date, 106), ‘ ‘, ‘-‘),

           Date3 = datename(mm, Date) + ‘ ‘ +

                   datename(dd, Date) + ‘, ‘ +

                   datename(yy, Date),

           DateLong = datename(dw , Date) + ‘, ‘ +

                      datename(month , Date) + ‘ ‘ +

                      cast(datepart(day, Date) as varchar(2)) + ‘, ‘ +

                      cast(datepart(year, Date) as char(4)),

 

           CalendarYearKey  = cast(datepart(year, Date) as smallint),

           CalendarSemesterOfYearNum =

              case

                when cast(datepart(month, Date) as tinyint)

                     in (1, 2, 3, 4, 5, 6) then 1

                else 2

              end,

           CalendarQuarterOfYearNum   =

             cast(datepart(quarter, Date) as tinyint),

           CalendarMonthOfYearNum = cast(datepart(month, Date) as tinyint),

           CalendarWeekOfYearNum  = cast(datepart(week, Date) as tinyint),

 

           DayNumberOfYear = cast(datepart(dayofyear, Date) as smallint),

           DayNumberOfMonth = cast(datepart(day, Date) as tinyint),

           DayNumberOfWeek = cast(datepart(weekday, Date) as tinyint)

    –load rest of data for year calendar

    update @DimDate

       set DateKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarMonthOfYearNum as varchar(2)), 2) +

             right(‘0’ + cast(DayNumberOfMonth as varchar(2)), 2),

           CalendarWeekKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarWeekOfYearNum as varchar(2)), 2),

           CalendarMonthKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarMonthOfYearNum as varchar(2)), 2),     

           CalendarQuarterKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarQuarterOfYearNum as varchar(2)), 2),

           CalendarSemesterKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarSemesterOfYearNum as varchar(2)), 2),

 

           CalendarYearLong = ‘CY ‘+ convert(char (4), CalendarYearKey),

           CalendarSemesterOfYear1 = ‘Semester ‘ +

             cast(CalendarSemesterOfYearNum as char(1)) + ‘, ‘ +

             cast(CalendarYearKey as char(4)),

           CalendarSemesterOfYear2 = ‘CY H’ +

             cast(CalendarSemesterOfYearNum as char(1)),

           CalendarSemesterLong = ‘CY H’ +

             cast(CalendarSemesterOfYearNum as char(1)) + ‘, ‘ +

             cast(CalendarYearKey as char(4)),

 

           CalendarQuarterOfYear1 = ‘Quarter ‘ +

             cast(CalendarQuarterOfYearNum as char(1)) + ‘, ‘ +

             cast(CalendarYearKey as char(4)),

           CalendarQuarterOfYear2 = ‘CY Q’ +

             cast(CalendarQuarterOfYearNum as char(1)),

           CalendarQuarterLong = ‘CY Q’ +

             cast(CalendarQuarterOfYearNum as char(1)) + ‘, ‘ +

             cast(CalendarYearKey as char(4)),

                    

           MonthNameLong = datename(month , Date),

           MonthNameShort = left(datename(month , Date), 3),

           CalendarMonthOfYear2 = datename(month , Date) + ‘-‘ +

             cast(CalendarYearKey as char(4)),

           CalendarMonthOfYear3 =

             replace(right(convert(varchar(11), Date, 106), 8), ‘ ‘, ‘-‘),

          

           CalendarWeekOfYear1 = ‘Week ‘ +

             cast(datepart(week, Date) as varchar(2)) + ‘, ‘ +

             cast(CalendarYearKey as char(4)),

           CalendarWeekOfYear2 = ‘CY Week ‘ +

             cast(datepart(week, Date) as varchar(2)),

           CalendarWeekLong = ‘CY Week ‘ +

             cast(datepart(week, Date) as varchar(2)) + ‘, ‘ +

             cast(CalendarYearKey as char(4)),

           DayNameOfWeekLong = datename(dw , Date),

           DayNameOfWeekShort = left(datename(dw , Date), 3),

           WorkDay = case

                       when DayNumberOfWeek in (2, 3, 4, 5, 6) then 1

                       else 0

                     end,

          

     

Advertisements

Written by Konstantin Gorakine

July 15, 2009 at 12:49 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: