Script to generate a time dimension table to use in Power Pivot.

In SQL Server 2012 Power Pivot a new feature is introduced. You can Mark as Date Table. This will enable you to leverage date filtering in Excel. For instance, you can see the revenue totals grouped by different date groupings. For instance per week number, per month, week day number etc. To use this feature you need to have a Time Dimension table.

• Create a time dimension table.  (See later in this blog how to do)
• Mark this table as Date table.
• Link from your Revenue table the column invoicedate to the key of the Time Dimension table.

Now you are ready to use this feature.

To create a Time dimension table you can a script (GlobeBI_DimTime.SQL) which can be downloaded from here. At the end of the script you can specify the start date and end date of the Time dimension entries. 


INSERT INTO Dimtime
SELECT * FROM dbo.F_TABLE_DATE ('20000101','20201231')

In the script Date entries are created from 1 Januari 2000 up to 31 December 2020.