As a date dimension and the date intelligence it supports is needed by almost all data models 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 required date dimension columns and a process for adding a date intelligence surrogate key column to a dimension. Reference date dimension templates and examples of related T-SQL date functions are included in the Getting ready and There's more......