SSAS - Defining the Data Dimention

In as much as the date dimension is to all intents a generic dimension that can be reused across virtually all cube projects it is worth taking the time to ensure it captures a complete set of information the end users will require.

And whilst SQL Server 2008 can generate the date dimention for you I favour using Excel to generate the source for the flexibility it offers.

There are 2 basic date dimensions that need to be created:

Period Dimension (Offering granularity to a month level); and
Date Dimension (Offering granularity to a day level)

I will deal with each of these in turn.

Period Dimention - Granularity Month

Before going in to the process it helps to consider the grouping stucture you are looking to achieve PeriodID - MonthID - QtrID - HalfID - YearID PeriodID - FIS_MonthID - FIS_QtrID - FIS_HalfID - FIS_YearID PeriodID - CAL_MonthID - CAL_QtrID - CAL_HalfID - CAL_YearID This provides for both Fiscal and Calendar hierarchies It's important to note that a month key is used aswell as the PK of PeriodID. The reason for this is that most accounting packages make use of a period 13 and sometimes even a Period 14. These periods usually are designed to be grouped into period 12 and for reporting the Month will group these but if users need to see it split they can group on period

When putting the date dimension together I use PeriodID as the table PK. The field is of type int and is generated by multiplying the Financial Year by 100 and adding the period.

I construct the spreadsheed by population the PeriodID column say starting at July 2008 period 1, period Id wold be 200801 I would fill down incrementing to 200813 assuming we are using 13 periods. The nex cell would then be the top cell + 1000. then fill down to create all period you need. All other columns can be populated from formulas on the key column

The source table sould offer up all fields to report on Fiscal Year as well as Calendar Year. In the following I have just included the Fiscal Year fields, which tend to be the ones used.

The following SQL will create the table


CREATE TABLE [dbo].[Period](
	[PeriodID] [int] NULL,
	[FIS_Period_Int] [int] NULL,
	[FIS_Period_MMM] [nvarchar](3) NULL,
	[FIS_Period_MMMYY] [nvarchar](6) NULL,
	[FIS_Period_MMMM] [nvarchar](9) NULL,
	[FIS_Period_MMMMYYYY] [nvarchar](14) NULL,
	[FIS_MonthID] [int] NULL,
	[FIS_Month_Int] [int] NULL,
	[FIS_Month_MMM] [nvarchar](3) NULL,
	[FIS_Month_MMMYY] [nvarchar](6) NULL,
	[FIS_Month_MMMM] [nvarchar](9) NULL,
	[FIS_Month_MMMMYYYY] [nvarchar](14) NULL,
	[FIS_Month_ForTheMonthEndedDDMMMMYYYY] [nvarchar](37) NULL,
	[FIS_Month_AsAtDDMMMMYYYY] [nvarchar](23) NULL,
	[FIS_QtrID] [int] NULL,
	[FIS_Qtr_Int] [int] NULL,
	[FIS_Qtr_Q0] [nvarchar](2) NULL,
	[FIS_Qtr_1stQtr] [nvarchar](7) NULL,
	[FIS_Qtr_FirstQuarter] [nvarchar](14) NULL,
	[FIS_Qtr_MMMQtr] [nvarchar](7) NULL,
	[FIS_Qtr_MMMMQuarter] [nvarchar](17) NULL,
	[FIS_Qtr_MMMMYYYYQuarter] [nvarchar](22) NULL,
	[FIS_Qtr_ForTheQuarterEndedDDMMMMYYYY] [nvarchar](39) NULL,
	[FIS_Qtr_AsAtDDMMMMYYYY] [nvarchar](25) NULL,
	[FIS_HalfID] [int] NULL,
	[FIS_Half_Int] [int] NULL,
	[FIS_Half_H0] [nvarchar](2) NULL,
	[FIS_Half_6MonthsToMMMM] [nvarchar](20) NULL,
	[FIS_Half_6MonthsToMMMMYYYY] [nvarchar](25) NULL,
	[FIS_Half_ForTheHalfEndedDDMMMMYYYY] [nvarchar](35) NULL,
	[FIS_Half_AsAtDDMMMMYYYY] [nvarchar](22) NULL,
	[FIS_YearID] [int] NULL,
	[FIS_Year_Int] [int] NULL,
	[FIS_Year_FY00] [nvarchar](4) NULL,
	[FIS_Year_ForTheYearEndedDDMMMMYYYY] [nvarchar](31) NULL,
	[FIS_Year_AsAtDDMMMMYYYY] [nvarchar](18) NULL,
	[CAL_Year_Int] [int] NULL
) ON [PRIMARY]

GO

Contact MDW

What can we do for you?

MDW is a Perth based consulting company with an experienced and dedicated team of business, information and technology specialists whose skills in the design, development and deployment of the SQL Server product range is second to none.

From the database itself through to the BI technology stack including Integration Services (SSIS), Reporting Services (SSRS) and Analysis Services (SSAS). we can offer specialist skills in all aspects, including installation and configuration, development and support of the SQL Server suite.

We are available for small and large scale projects.

Call us to discuss your business needs and we can arrange for a consultant to conduct an initial assessment of your business at our cost.

Contact our sales manager on 0407 192 124 during business hours or click here to send us an email.

Contact MDW