SSAS - Defining the Data Dimention

Period Dimention - Granularity Month

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.

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.

Field NameSample
PeriodID198001
FIS_Period_Int1
FIS_Period_MMMJul
FIS_Period_MMMYYJul-79
FIS_Period_MMMMJuly
FIS_Period_MMMMYYYYJuly 1979
FIS_MonthID198001
FIS_Month_Int1
FIS_Month_MMMJul
FIS_Month_MMMYYJul-79
FIS_Month_MMMMJuly
FIS_Month_MMMMYYYYJuly 1979
FIS_Month_ForTheMonthEndedDDMMMMYYYYFor the Month Ended 31 July 1980
FIS_Month_AsAtDDMMMMYYYYAs at 31 July 1980
FIS_QtrID19801
FIS_Qtr_Int1
FIS_Qtr_Q0Q1
FIS_Qtr_1stQtr1st Qtr
FIS_Qtr_FirstQuarterFirst Quarter
FIS_Qtr_MMMQtrSep Qtr
FIS_Qtr_MMMMQuarterSeptember Quarter
FIS_Qtr_MMMMYYYYQuarterSeptember 1979 Quarter
FIS_Qtr_ForTheQuarterEndedDDMMMMYYYYFor the Quarter Ended 30 September 1979
FIS_Qtr_AsAtDDMMMMYYYYAs at 30 September 1979
FIS_HalfID19801
FIS_Half_Int1
FIS_Half_H0H1
FIS_Half_6MonthsToMMMM6 Months to December
FIS_Half_6MonthsToMMMMYYYY6 Months to December 1979
FIS_Half_ForTheHalfEndedDDMMMMYYYYFor the Half Ended 31 December 1979
FIS_Half_AsAtDDMMMMYYYYAs at 31 December 1979
FIS_YearID1980
FIS_Year_Int1980
FIS_Year_FY00FY80
FIS_Year_ForTheYearEndedDDMMMMYYYYFor the Year Ended 30 June 1980
FIS_Year_AsAtDDMMMMYYYYAs at 30 June 1980
CAL_Year_Int1979

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