Building a Date Dimension Table

A date dimension is needed by almost all data models and particularly those that require date intelligence calculations (e.g. Year-to-Date, Year-over-Year) and company-specific calendars. Building a robust date table in the source system provides significant long-term value across BI projects and tools. A complete date table accounts for all the required grains or hierarchy levels of both the standard (Gregorian) calendar and any fiscal calendar specific to the organization. Additionally, surrogate key columns aligned to each grain are included to drive the sort order of report attributes and to enable date intelligence expressions.

This recipe includes a design phase to identify the required date dimension columns and a process for adding a date intelligence surrogate key column to a dimension.

Getting ready

Full date tables are available from various sources:

• The Kimball Group downloadable Excel file: http://bit.ly/2rOchxt
...