dim_timeThe reason the time dimension can be complex and time consuming is that end-users often want to break out time in many different ways. Some examples include: by day, week, month, year quarter. This could also be by fiscal month, quarter or year. These times can vary for each different project based on the unique fiscal year of an organization.

To develop a robust time dimension, you will need one that adjusts to a company fiscal year that may be different from a calendar year - while providing the ability to report by EITHER fiscal or calendar year. You will then need to ensure that you can measure all of the appropriate attributes for your time dimension over a long period of time (my personal preference is to have 200 years built into the time dimension - with the times being +/- 100 years of the current date). Here are my recommended list of attributes to add to your time dimension:


[Actual_Date],
[Actual_Date_Label],
[Day_Of_Week],
[Day_Of_Week_Label],
[Weekday_Or_Weekend_Label],
[Holiday_Flag],
[Days_In_Calendar_Year],
[Calendar_Year],
[Calendar_Half_Year_Label],
[Calendar_Quarter],
[Calendar_Quarter_Label],
[Calendar_Month],
[Calendar_Month_Label],
[Calendar_Week],
[Calendar_Week_Label],
[Calendar_Week_Start],
[Calendar_Week_End],
[Calendar_Day_Of_Year],
[Calendar_Day_Of_Month],
[Calendar_Last_Day_Of_Month],
[Fiscal_Year],
[Fiscal_Half_Year_Label],
[Fiscal_Quarter],
[Fiscal_Quarter_Label],
[Fiscal_Month],
[Fiscal_Month_Label],
[Fiscal_Week],
[Fiscal_Week_Label],
[Fiscal_Week_Start],
[Fiscal_Week_End],
[Fiscal_Day_Of_Year],
[Fiscal_Day_Of_Month],
[Fiscal_Last_Day_Of_Month],
[Fiscal_Year_Begin_Date],
[Fiscal_Year_End_Date]

If you're in a large enterprise that may have multiple BI projects, you should strongly consider automating your time dimension. For example, because we work on a large number of BI projects, we have developed a dynamic TIME DIMENSION that could be implemented for any BI project and company’s fiscal year and for the calendar year. The code can be installed on any SQL Server 2005 to SQL Server 2008 R2 and it takes less than 5 minutes to populate the time dimension for a span of 200 years (current + or – 100 Years) it has 2 variables that need to be updated based on a company’s fiscal year “Start Date” and “End Date”. This script also takes into consideration leap years, week days, week end, and much more. This script saves us many hours when adding the time dimension to our projects; if your enterprise is serious about putting reporting tools into the hands of every information worker and executive, then developing a rapidly-deployable time dimension script is a must.