Date dimension to support multiple fiscal year

If you have requirement to present your data based on the fiscal year user is using, you can provide user with option to either select fiscal year he wants or can make it dynamic in the report such that it dynamically select the current user location and render the report.

This is one way to model your time dimension to support multiple fiscal year. There are obviously other ways to support various fiscal year like by adding more columns to time dimensions, storing into separate fact for each single fiscal year you may need to support, building separate hierarchies for each fiscal year etc. I insist the approach because you don’t have to add columns in the database time dimension table to support and of course it is not considered good to add columns to support piece of business. We should always try and see if it can be done using same table columns.

We will create 3 tables. Find script below to create these tables. We will create a calendar dimension table with all the attributes, a Date dimension table with key and date and a bridge table to connect these two dimension tables (Calendar and Date).

Here is the script to create 3 tables -

//Calendar dimension table CREATE TABLE [DBO].[TTB_DIM_CALENDAR]( [CALENDAR_KEY] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY, [CALENDAR_DATE_ID] [VARCHAR](100) NOT NULL, [CALENDAR_NAME] [VARCHAR](50) NOT NULL, [CALENDAR_ORDER] [TINYINT] NOT NULL, [ACTUAL_DATE] [DATE] NOT NULL, [DATE_NAME] [VARCHAR](50) NOT NULL, [YEAR] [DATE] NOT NULL, [YEAR_NAME] [CHAR](4) NOT NULL, [QUARTER] [DATE] NOT NULL, [QUARTER_NAME] [VARCHAR](9) NOT NULL, [MONTH] [DATE] NOT NULL, [MONTH_NAME] [VARCHAR](14) NOT NULL, [WEEK] [DATE] NOT NULL, [WEEK_NAME] [VARCHAR](12) NOT NULL, [DAY_OF_YEAR] [SMALLINT] NOT NULL, [DAY_OF_YEAR_NAME] [VARCHAR](7) NOT NULL, [DAY_OF_QUARTER] [TINYINT] NOT NULL, [DAY_OF_QUARTER_NAME] [VARCHAR](6) NOT NULL, [DAY_OF_WEEK] [TINYINT] NOT NULL, [DAY_OF_WEEK_NAME] [VARCHAR](5) NOT NULL, [WEEK_OF_YEAR] [TINYINT] NOT NULL, [WEEK_OF_YEAR_NAME] [VARCHAR](7) NOT NULL, [MONTH_OF_YEAR] [TINYINT] NOT NULL, [MONTH_OF_YEAR_NAME] [VARCHAR](36) NOT NULL, [MONTH_OF_QUARTER] [TINYINT] NOT NULL, [MONTH_OF_QUARTER_NAME] [VARCHAR](8) NOT NULL, [QUARTER_OF_YEAR] [TINYINT] NOT NULL, [QUARTER_OF_YEAR_NAME] [VARCHAR](9) NOT NULL, )//Date dimension table CREATE TABLE [DBO].[TTB_DIM_DATE]( [DATE_KEY] [INT] NOT NULL PRIMARY KEY, [ACTUAL_DATE] [DATETIME] NOT NULL, )//Bridge fact table CREATE TABLE [DBO].[TTB_DIM_DATE_CALENDAR]( [DATE_KEY] [INT] NOT NULL REFERENCES [DBO].[TTB_DIM_DATE]([DATE_KEY]), [CALENDAR_KEY] [INT] NOT NULL REFERENCES [DBO].[TTB_DIM_CALENDAR]([CALENDAR_KEY]) ) GO

Let’s setup data source view in the OLAP. Add above 3 tables in the DSV, you should get this -

Next populate above tables using SQL script. Make sure you have entries for each single day in this table. You need to have entries per Gregorian calendar and same number of entries for each month of a year starting February (and of course for each year) . Reason, we are not creating entries for January because if fiscal starts from January it will be same as calendar year.

Here is the sample script to populate above tables.

DECLARE @STARTDATE DATETIME = '2008-01-01' DECLARE @ENDDATE DATETIME = '2010-12-31' DECLARE @COUNTER INT SET @COUNTER = 1 ;WITH DATES( D ) AS ( SELECT @STARTDATE AS D UNION ALL SELECT DATEADD(DAY, 1, D) FROM DATES WHERE D < @ENDDATE ) ,MONTHS(M) AS ( SELECT @COUNTER AS M UNION ALL SELECT M + 1 FROM MONTHS WHERE M <= 11 ) INSERT INTO TTB_DIM_CALENDAR ( CALENDAR_DATE_ID, CALENDAR_NAME, CALENDAR_ORDER, ACTUAL_DATE, DATE_NAME, YEAR, YEAR_NAME, QUARTER, QUARTER_NAME, MONTH, MONTH_NAME, WEEK, WEEK_NAME, DAY_OF_YEAR, DAY_OF_YEAR_NAME, DAY_OF_QUARTER, DAY_OF_QUARTER_NAME, DAY_OF_WEEK, DAY_OF_WEEK_NAME, WEEK_OF_YEAR, WEEK_OF_YEAR_NAME, MONTH_OF_YEAR, MONTH_OF_YEAR_NAME, MONTH_OF_QUARTER, MONTH_OF_QUARTER_NAME, QUARTER_OF_YEAR, QUARTER_OF_YEAR_NAME) SELECT CASE WHEN LEN(FISCALSTARTMONTH) = 1 THEN '0' ELSE '' END + CONVERT(VARCHAR(8), FISCALSTARTMONTH) + '_' + CALENDARKEY AS CALENDARDATEID ,CALENDARNAME ,FISCALSTARTMONTH ,ACTUALDATE ,[DATENAME] ,[YEAR] ,CONVERT(CHAR(4), DATEPART(YYYY, [YEAR])) AS YEARNAME ,DATEADD(Q, CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) - 1, [YEAR]) AS [QUARTER] ,CONVERT(CHAR(4), DATEPART(YYYY, [YEAR])) + ' Q' + CONVERT (VARCHAR,CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0)) AS QUARTERNAME ,DATEADD(MM, DATEDIFF(MM, 0, ACTUALDATE), 0) AS [MONTH] ,CONVERT(VARCHAR, DATENAME(MM, ACTUALDATE)) + ' ' + CONVERT (CHAR(4), DATEPART(YYYY, ACTUALDATE)) AS [MONTHNAME] --,(DATEDIFF(MM, [YEAR], ACTUALDATE) + 1) AS [MONTHNUMBER] ,DATEADD(WK, DATEDIFF(WK, 0, ACTUALDATE), 0) AS [WEEK] ,'WEEK ' + CONVERT(VARCHAR,(DATEDIFF(WK, [YEAR], ACTUALDATE) + 1)) + ' ' + CONVERT(CHAR(4), DATEPART(YYYY, [YEAR])) AS [WEEKNAME] ,DATEDIFF(DY, [YEAR], ACTUALDATE) + 1 AS [DAYOFYEAR] ,'DAY ' + CONVERT(VARCHAR, DATEDIFF(DY, [YEAR], ACTUALDATE) + 1) AS DAYOFYEARNAME ,DATEDIFF(DY, DATEADD(Q, CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) - 1, [YEAR]), ACTUALDATE) + 1 AS DAYOFQUARTER ,'DAY ' + CONVERT(VARCHAR, DATEDIFF(DY, DATEADD(Q, CEILING ((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) - 1, [YEAR]), ACTUALDATE) + 1) AS DAYOFQUARTERNAME ,DATEPART(DW, ACTUALDATE) AS [DAYOFWEEK] ,'DAY ' + CONVERT(VARCHAR, DATEPART(DW, ACTUALDATE)) AS DAYOFWEEKNAME ,DATEDIFF(WK, [YEAR], ACTUALDATE) + 1 AS WEEKOFYEAR ,'WEEK ' + CONVERT(VARCHAR, DATEDIFF(WK, [YEAR], ACTUALDATE) + 1) AS WEEKOFYEARNAME ,DATEDIFF(MM, [YEAR], ACTUALDATE) + 1 AS MONTHOFYEAR ,'MONTH ' + CONVERT(VARCHAR, DATEDIFF(MM, [YEAR], ACTUALDATE) + 1) AS MONTHOFYEARNAME ,DATEDIFF(MM, DATEADD(Q, CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) - 1, [YEAR]), DATEADD(MM, DATEDIFF(MM, 0, ACTUALDATE), 0)) + 1 AS MONTHOFQUARTER ,'MONTH ' + CONVERT(VARCHAR, DATEDIFF(MM, DATEADD(Q, CEILING ((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) - 1, [YEAR]), DATEADD(MM, DATEDIFF(MM, 0, ACTUALDATE), 0)) + 1) AS MONTHOFQUARTERNAME ,CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) AS QUARTEROFYEAR ,'QUARTER ' + CONVERT(VARCHAR, CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0)) AS QUARTEROFYEARNAME FROM ( SELECT CONVERT(CHAR(8), D, 112) AS CALENDARKEY ,M AS FISCALSTARTMONTH ,CASE WHEN M = 1 THEN 'GREGORIAN' ELSE CONVERT(VARCHAR, DATENAME(MONTH, DATEADD (MONTH, (M-1), 0))) + ' BASED FISCAL' END AS CALENDARNAME ,D AS ACTUALDATE ,DATENAME(DW, D)+ ', ' + DATENAME(MM, D) + ' ' + RIGHT ('00' + CONVERT(VARCHAR(2), DATEPART(D, D)), 2) + ' ' + CONVERT (CHAR(4), DATEPART(YYYY, D)) AS [DATENAME] ,CASE WHEN DATEPART(MONTH, D) >= M THEN DATEADD(MONTH, (M-1), DATEADD(YEAR, DATEDIFF(YEAR, 0, D), 0)) WHEN DATEPART(MONTH, D) < M THEN DATEADD(MONTH, (M- 1)-12, DATEADD(YEAR, DATEDIFF(YEAR, 0, D), 0)) END AS [YEAR] FROM DATES CROSS JOIN MONTHS ) DATES OPTION (MAXRECURSION 10000) ;WITH DATES( D ) AS ( SELECT @STARTDATE AS D UNION ALL SELECT DATEADD(DAY, 1, D) FROM DATES WHERE D < @ENDDATE ) INSERT INTO TTB_DIM_DATE (DATE_KEY, ACTUAL_DATE) SELECT CONVERT(INT, CONVERT(CHAR(8), D, 112)) AS DATEKEY, D AS ACTUALDATE FROM DATES OPTION (MAXRECURSION 10000) INSERT INTO [TTB_DIM_DATE_CALENDAR] (DATE_KEY, CALENDAR_KEY) SELECT D.DATE_KEY, C.CALENDAR_KEY FROM TTB_DIM_DATE D INNER JOIN TTB_DIM_CALENDAR C ON D.DATE_KEY = CONVERT(INT, RIGHT(C.CALENDAR_DATE_ID, 8))

At this point if I assume you have just populated one year entries into the calendar dimension this is how number of entries should be in the calendar dimension table -

Add calendar and set dimension properties as under -

Set additional calendar attribute properties as under -

Next build hierarchy and set attribute relationship as under -

Add Date dimension and leave everything to default value. I however changed the key column to Date from Date_Key. Here is the snap of the Date dimension -

Now we will add a fact table (actual fact) for sales. Fact table will be linked to calendar and date dimensions.

CREATE TABLE [DBO].[TTB_FACT_SALES]( [DATE_KEY] [INT] NOT NULL REFERENCES [DBO].[TTB_DIM_DATE]([DATE_KEY]), [CALENDAR_KEY] [INT] NOT NULL REFERENCES [DBO].[TTB_DIM_CALENDAR]([CALENDAR_KEY]), [SALES_AMOUNT] DECIMAL(10,2) )

Add above fact table to data source view and it should look like this at this point - 

Create a measure from the bridge table. This will act as intermediate fact table when we join with sales fact through calendar dimension (using many to many relationship and will be discussed later). It is nothing but the fact less fact table and we will not be using this for anything. Let’s add count measure on Date_key column. Here is what I mean -

Populate sales fact table as under. I just created for all the possible date and calendar key combination for us to be able to test with some data.

INSERT INTO [DBO].[TTB_FACT_SALES]([DATE_KEY], [CALENDAR_KEY], [SALES_AMOUNT]) SELECT DATE_KEY, CALENDAR_KEY, RIGHT(DATE_KEY, 2) FROM [DBO].[TTB_DIM_DATE_CALENDAR]

Next add sales measure to the cube and leave everything else to default. This is how cube structure should look like.

Set the dimension usage tab as under. Link DATE CALENDAR measure group with Calendar and Date dimension using Regular relationship type. This measure group we will be using when linking with actual fact which is sales in this case.

Link sales fact with Date dimension using Regular relationship type and with calendar dimension using many to many relationship type through bridge table measure group (above one). Find below dimension usage tab and many to many relationship snap for your to connect well.

 

Process the cube and analyze what we got in here. Drag sales in the measure area and calendar hierarchy. This is what we get, all the values are same for the Calendar name because we had set IsAggregatable = False.

What we gonna see next is interesting. In Gregorian calendar March falls in Q1 and in March based fiscal again it will fall in Q1 but in April based fiscal April will fall in Q1. Similarly in Gregorian calendar May falls in Q2 but in April based fiscal it will fall in Q1 etc. So what I am driving here is, sales value will be accrued to different quarter in a year based on what is the starting month of a fiscal.

Rest you browse the cube and check the sales value distribution. Hope this will help you design time dimension when you have to support different calendar, fiscal etc. Thank you and bye for now!