This is the first in a series of dating tips, so if you need you’ve come to the right place.

Well ok tips about how to use Dates in a Data Warehouse, Power Pivot, and Analysis Services both Tabular and Multidimensional.

So here in part 1 we are going to start at the basics and cover the date table. The date table is where all the magic happens and without it our dates are pretty meaningless.

Why is the table so important?

1. Without a date table almost all Time Intelligence functions will not work
   1. Year to Date
   2. Rolling Quarters
   3. Parallel Period
   4. Etc…….
2. Can’t build Date hierarchies such as (Year, Quarter, Month, Date)
3. Will not be able to support Fiscal Calendars

Here is a script that will generate a Date Table for you. I am populating the table using a recursive CTE which does have a limit of loading 32767 dates.

There are also 3 Variables that you need to set the values for

@StartDate default is 1/1/1999

@EndDate default is 1/1/2050

@FYStartDate default is 4/1/1999, The year of this must be the same fiscal year as the StartDate