Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

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

leave a comment »

      —-load initial keys for fiscal calendar (example of fiscal

            calendar: starts on July 1st of the year calendar                      

           FiscalYearKey = case

                             when CalendarMonthOfYearNum <= 7

                               then CalendarYearKey

                              else CalendarYearKey + 1

                             end,

           FiscalSemesterOfYearNum = case

                                       when CalendarSemesterOfYearNum = 1

                                         then CalendarSemesterOfYearNum + 1

                                       else CalendarSemesterOfYearNum 1  

                                     end,

           FiscalQuarterOfYearNum = case

                                       when CalendarQuarterOfYearNum < 3

                                         then CalendarQuarterOfYearNum + 2

                                        else CalendarQuarterOfYearNum 2

                                    end,

           FiscalMonthOfYearNum =  case

                                     when CalendarMonthOfYearNum < 7

                                       then CalendarMonthOfYearNum + 6

                                     else CalendarMonthOfYearNum 6

                                   end,

           FiscalWeekOfYearNum = case

                                   when CalendarWeekOfYearNum > 27

                                    or CalendarMonthOfYearNum >= 7

                                     then CalendarWeekOfYearNum 26

                                    else CalendarWeekOfYearNum + 27

                                 end

    –load rest of data for fiscal calendar

    update @DimDate

           set

           FiscalWeekKey = cast(FiscalYearKey as char(4)) + right(‘0’ +

                           cast(FiscalWeekOfYearNum as varchar(2)), 2),

           FiscalMonthKey = cast(FiscalYearKey as char(4)) + right(‘0’ +

                            cast(FiscalMonthOfYearNum as varchar(2)), 2),

           FiscalQuarterKey = cast(FiscalYearKey as char(4)) + right(‘0’ +

                              cast(FiscalQuarterOfYearNum as varchar(2)), 2),

           FiscalSemesterKey = cast(FiscalYearKey as char(4)) + right(‘0’ +

                             cast(FiscalSemesterOfYearNum as varchar(2)), 2),

 

   

           FiscalYearLong = ‘FY ‘+ convert(char (4), FiscalYearKey),

           FiscalSemesterOfYear1 = ‘FY H’ +

             cast(FiscalSemesterOfYearNum as char(1)),

           FiscalSemesterLong = ‘FY H’ +

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

             cast(FiscalYearKey as char(4)),

           FiscalQuarterOfYear1 = ‘FY Q’ +

             cast(FiscalQuarterOfYearNum as char(1)),

           FiscalQuarterLong = ‘FY Q’ +

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

             cast(FiscalYearKey as char(4)),

           FiscalMonthOfYear1 = ‘FY M’ +

             cast(FiscalMonthOfYearNum as varchar(2)),

           FiscalMonthLong = ‘FY M’ +

             cast(FiscalMonthOfYearNum as varchar(2)) + ‘, ‘ +

             cast(FiscalYearKey as char(4)),

 

           FiscalWeekOfYear1 = ‘FY W’ +

             cast(FiscalWeekOfYearNum as varchar(2)),

           FiscalWeekLong = ‘FY W’ +

             cast(FiscalWeekOfYearNum as varchar(2)) + ‘, ‘ +

             cast(FiscalYearKey as char(4))

                                 

           –holidays (fixed)

           –New Year                     

           update @DimDate set Holiday = 1, HolidayName=‘New Year’

               where CalendarMonthOfYearNum = 1

                 and DayNumberOfMonth = 1 and CalendarYearKey

                  in (select distinct CalendarYearKey from @DimDate)

              

           –4th of July

           update @DimDate set Holiday = 1, HolidayName=‘4th Of July’

               where CalendarMonthOfYearNum = 7

                 and DayNumberOfMonth = 4 and CalendarYearKey

                  in (select distinct CalendarYearKey from @DimDate)

                                                

            –Christmas

            update @DimDate set Holiday = 1, HolidayName=‘Christmas’

               where CalendarMonthOfYearNum = 12

                 and DayNumberOfMonth = 25 and CalendarYearKey

                  in (select distinct CalendarYearKey from @DimDate);                            

 

           –holidays (float) – CTE works slower than cursor and hence

           commented

           

             —-Memorial Day

           –with MemorialDays as

           –(select CalendarYearKey, max(DateKey) as DayKey from @DimDate

           — where CalendarMonthOfYearNum = 5

              and DayNameOfWeekLong = ‘Monday’

              and CalendarYearKey

                 in (select distinct CalendarYearKey from @DimDate)                

           — group by CalendarYearKey )                 

           — update @DimDate set Holiday = 1, HolidayName = ‘Memorial Day’

            where DateKey in (select DayKey from MemorialDays);

 

           —-Labor Day

           –with LaborDays as

           –(select CalendarYearKey, min(DateKey) as DayKey from @DimDate

           — where CalendarMonthOfYearNum = 9

              –and DayNameOfWeekLong = ‘Monday’ –and CalendarYearKey

               –in (select distinct CalendarYearKey from @DimDate)                

           — group by CalendarYearKey )                 

           — update @DimDate set Holiday = 1, HolidayName = ‘Labor Day’

             –where DateKey in (select DayKey from LaborDays);

 

             —-Thanksgiving

             –with Thanksgivings as

           –(select CalendarYearKey, min(DateKey) + 21 as DayKey

            –from @DimDate

           — where CalendarMonthOfYearNum = 11

              –and DayNameOfWeekLong = ‘Thursday’

              –and CalendarYearKey

               –in (select distinct CalendarYearKey from @DimDate)                

           — group by CalendarYearKey )                 

           — update @DimDate set Holiday = 1, HolidayName = ‘Thanksgiving’   

            –where DateKey in (select DayKey from Thanksgivings);

     

Advertisements

Written by Konstantin Gorakine

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