Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

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

with one comment

This is the final part of our stored procedure


           –plain cursor works faster than CTE

           declare @CurrentYearKey smallint


           declare flaotHolidays cursor fast_forward

           for select distinct CalendarYearKey from @DimDate

           open flaotHolidays

           fetch next from flaotHolidays into @CurrentYearKey


           while @@fetch_status = 0



               –Memorial Day

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

                where DateKey = (select max(DateKey)

                 from @DimDate where CalendarMonthOfYearNum = 5

                  and DayNameOfWeekLong = ‘Monday’

                  and CalendarYearKey = @CurrentYearKey)


                –Labor Day

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

                 where DateKey = (select min(DateKey)

                  from @DimDate where CalendarMonthOfYearNum = 9

                   and DayNameOfWeekLong = ‘Monday’

                   and CalendarYearKey = @CurrentYearKey)



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

                 where DateKey = (select (min(DateKey) + 21)

                  from @DimDate where CalendarMonthOfYearNum = 11

                   and DayNameOfWeekLong = ‘Thursday’

                   and CalendarYearKey = @CurrentYearKey)


           fetch next from flaotHolidays into @CurrentYearKey



           close flaotHolidays deallocate flaotHolidays


           update @DimDate set RecordCreateDate = @CurrentDate,

                               RecordUpdateDate = @CurrentDate   


           –return the completed table as a result set.

           select * from @DimDate



Once procedure is compiled on the server, a SSIS package can be build with one Data Flow task having OLE DB Source with SQL Command text as following:

exec [sp_PopulateDateDimension]  ’01/01/2009′, ’12/31/2013′

Once package completes, table DimDate is populated with data with each level having it’s own key column that will be used while adding those levels as attribute hierarchies.

Therefore, instead of using Composite Keys as shown on the picture


, a Simple Key will be used providing more optimized key solution. Just don’t forget to order Month attribute hierarchy by the Key, not Name (default)


Written by Konstantin Gorakine

July 15, 2009 at 12:51 pm

Posted in SSAS

One Response

Subscribe to comments with RSS.

  1. Awesome Script…..thanks

    Abdul Khan

    June 28, 2012 at 2:28 pm

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: