Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

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

with 2 comments

When dimensions and their attribute hierarchies are built, there is a choice to design KeyColumn property as a Composite Key (consisting of several key columns) or a Simple Key (consisting of only one key column). To ensure proper data rollup in the user-designed hierarchies, sometimes (and especially in the Date/Time dimension) it is necessary to use Composite Key. For example, Quarter, Month and Week levels need to use Year key along with the original (Quarter, Month or Week) key to be placed properly under their parents. Usually, Composite Keys take more space (by duplicating the same data) in the Analysis Services data storage structures and generally less optimized than Simple Keys. Even additional internal data structures can appear in the situation when String type is used as a Key Column (very bad practice), thus making key structures far from optimal for data loading and navigation. The last statement relates to the Simple Keys as well. 

There are two ways to provide Simple Key instead Composite one: use DSV and generate Named Calculation for each level of proposed user hierarchy or pre-generate them on the relational side and leave DSV less cluttered. In the case of the DSV enhancement it is required to provide a number of data casting such as:

cast(cast(CalendarYearKey as char(4)) + right(‘0’ + cast(CalendarMonthOfYearNum as varchar(2)), 2) as int)

, which might not be convenient in the small dialog box of the New Named Calculation. Besides this approach is less portable and requires to repeat the same DSV manipulations in the case of creating another SSAS project (I am not talking about confirmed dimensions here – usually, in real life, clients have many projects flying around, each having its own Date Dimension).

I prefer the second way of generating Simple Keys for each hierarchical level – SQL.

Here is the table structure of our future DimDate dimension :

create table [dbo].[DimDate](

      [DateKey] [int] NULL,

      [Date] [datetime] NULL,

      [Date1] [varchar](10) NULL,

      [Date2] [varchar](11) NULL,

      [Date3] [varchar](20) NULL,

      [DateLong] [varchar](30) NULL,

      [CalendarYearKey] [smallint] NULL,

      [CalendarYearLong] [varchar](7) NULL,

      [DayNumberOfYear] [smallint] NULL,

      [CalendarSemesterKey] [int] NULL,

      [CalendarSemesterOfYearNum] [tinyint] NULL,

      [CalendarSemesterOfYear1] [varchar](16) NULL,

      [CalendarSemesterOfYear2] [varchar](5) NULL,

      [CalendarSemesterLong] [varchar](11) NULL,

      [CalendarQuarterKey] [int] NULL,

      [CalendarQuarterOfYearNum] [tinyint] NULL,

      [CalendarQuarterOfYear1] [varchar](15) NULL,

      [CalendarQuarterOfYear2] [varchar](5) NULL,

      [CalendarQuarterLong] [varchar](11) NULL,

      [CalendarMonthKey] [int] NULL,

      [CalendarMonthOfYearNum] [tinyint] NULL,

      [MonthNameLong] [varchar](10) NULL,

      [MonthNameShort] [varchar](3) NULL,

      [CalendarMonthOfYear2] [varchar](30) NULL,

      [CalendarMonthOfYear3] [varchar](10) NULL,

      [DayNumberOfMonth] [tinyint] NULL,

      [CalendarWeekKey] [int] NULL,

      [CalendarWeekOfYearNum] [tinyint] NULL,

      [CalendarWeekOfYear1] [varchar](13) NULL,

      [CalendarWeekOfYear2] [varchar](10) NULL,

      [CalendarWeekLong] [varchar](16) NULL,

      [DayNameOfWeekLong] [varchar](10) NULL,

      [DayNameOfWeekShort] [varchar](3) NULL,

      [DayNumberOfWeek] [tinyint] NULL,

      [WorkDay] [tinyint] NULL,

      [FiscalYearKey] [smallint] NULL,

      [FiscalYearLong] [varchar](7) NULL,

      [FiscalSemesterKey] [int] NULL,

      [FiscalSemesterOfYearNum] [tinyint] NULL,

      [FiscalSemesterOfYear1] [varchar](5) NULL,

      [FiscalSemesterLong] [varchar](11) NULL,

      [FiscalQuarterKey] [int] NULL,

      [FiscalQuarterOfYearNum] [tinyint] NULL,

      [FiscalQuarterOfYear1] [varchar](5) NULL,

      [FiscalQuarterLong] [varchar](11) NULL,

      [FiscalMonthKey] [int] NULL,

      [FiscalMonthOfYearNum] [tinyint] NULL,

      [FiscalMonthOfYear1] [varchar](7) NULL,

      [FiscalMonthLong] [varchar](12) NULL,

      [FiscalWeekKey] [int] NULL,

      [FiscalWeekOfYearNum] [tinyint] NULL,

      [FiscalWeekOfYear1] [varchar](7) NULL,

      [FiscalWeekLong] [varchar](12) NULL,

      [Holiday] [tinyint] NULL,

      [HolidayName] [varchar](15) NULL,

      [RecordCreateDate] [datetime] NULL,

      [RecordUpdateDate] [datetime] NULL

)

Blog entry is text limited, so I have to split text and stored procedure into 5 parts.

Advertisements

Written by Konstantin Gorakine

July 15, 2009 at 12:44 pm

Posted in SSAS

2 Responses

Subscribe to comments with RSS.

  1. Nice Script! This is a good way to populate date dimension. In my current SSIS packages, I use the Excel file from the Kimball\’s Datawarehousse Toolkit. I modified the column a bit to suit our needs. The Excel file contains similar columns as your table.

    George

    July 15, 2009 at 7:41 pm

  2. Here are this and some other articles on SSAS Date Dimension

    http://ssas-wiki.com/w/Articles#Date_Dimension

    Sam Kane

    March 22, 2011 at 12:03 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: