One dimension you can be positive will always make it’s way into your data warehouse is the Date dimension. Over the past couple of years I have taken a look at a lot of Date dimension scripts and found quite a few good ones. Well recently I needed to add fiscal date to the dimension and the script I had didn’t let me do that properly. Rarely does the fiscal year start and end on the same dates every year. Most companies adopt some kind of 4-4-5 or 4-5-4 model for their year.
The script below will allow you to, if you choose, add fiscal date to the dimension. I just did some modifications on this script (since I combined a bunch that I have found and rewrote/added things I needed over time) so please let me know if you find any issues with it and I will happily update it as soon as possible! If you see some of your code in here please leave a comment as I can’t remember all the places where I have pulled from, but there are many great resources that went into this script collaboration, I can’t take credit for it all.
Bookmark this page and/or save a copy of the script, chances are you will need it again sometime in the near future. I do have to add the portion back in where I add the indexes on, so I apologize for that missing right now.
This file contains:
- DimDate.sql – The new combined SQL script with both calendar and fiscal. This also includes several fields that are NOT in the other files
- Date Dimension.sql – A version of the Date Dimension I have used for a while, but does not have fiscal date in it and does not have the added fields from the first script.
- Date Dimension Add Fiscal Date.sql – Adds fiscal dates to the second script. This logic has been folded into script number 1 with the same columns that are included here.
Download the Zip file here: DimDate
Nice script – thanks for sharing!
Thanks Bradley for sharing these scripts!
But I’m getting an issue while executing the ‘Date Dimension Add Fiscal Dates.sql’ script.
In my company the Fiscal year is calculated from July 1st to June 30th.
In the DimDate.sql script , I’ve declared the @StartDate DATE = ’01/01/2012′ and @EndDate DATE = ’01/01/2016′
And in the Fiscal date script I’ve set the values like-
SET @dtFiscalYearStart = ‘July 01, 1995’
SET @FiscalYear = 1995
SET @LastYear = 2025
SET @FirstLeapYearInPeriod = 1996
Ideally the output should come as –
From dates 01/01/2012 to 06/30/2012, Fiscal year should be 2012
From dates 07/01/2012 to 06/30/2013, Fiscal year should be 2013
From dates 07/01/2013 to 06/30/2014, Fiscal year should be 2014 and so on….
But somehow after executing the script its showing a change in the Fiscal year starting from 07/05.
So for fiscal year 2013, its starting from 07/05/2012, instead of 07/01/2012
Fiscal year 2014 is starting from 07/04/2013, FY 2015 starts from 07/03/2014….
Could you please help me regarding this issue?
Was this issue ever resolved? I have the same question. Thank you!
Was this issue ever resolved? I have the same question. Thank you!
Yeah, the variables you set will not correctly fill out the rest of the script in terms of the fiscal days, months and quarters. It only half works correctly. For example, if you set the start of the fiscal to February 2nd, 2014, the end of the fiscal month is actually on week 5 rather than week 4. Thus, it breaks the whole structure of 4-5-4.
I need a fiscal year from 2015-10-01 to 2016-09-30 ? can u help me this question ASAP.
Hi I love all your webnars on PW.Is there any chance I can get the demo script for the webnar ‘Overcoming Common Tabular and Power Pivot Feature Limitations’ you did on 1/22/2015.
Thx for all you do for the IT industry.