How do I make a date dimension?

Date dimensions are very useful. With a date dimension / date table you can look up for any date which week it falls in, which year, which month, whether it is a public holiday, etc. This article shows what you can do with a date dimension. You can also download a script to create a date (table) / dimension yourself.

A date dimension is nothing more or less than a date table. Every date in the year is a separate row. Next to it are a number of columns that show which week that day falls in, the month, the quarter and a lot more. There is also a column that shows whether a date is a public holiday. That way you can quickly count public holidays. A date dimension works well in a data warehouse, but of course you can also simply create it in any database, such as Exact or Microsoft Dynamics.

Note: this is an example dimension. You can of course improve it yourself with fields that matter to you!

If we now create the date dimension in the AdventureWorks database (so not the AdventureWorks dwh), you can see its power. We join the date dimension to the OrderDate. Then we can easily find several properties for this date!

1
SELECT TOP 100
2
SOH.SalesOrderID,
3
SOH.OrderDate,
4
DD.DatumId,
5
DD.WeekNummer,
6
DD.MaandNummerVanJaar,
7
DD.Kwartaal445Code,
8
DD.Kwartaal445Nummer
9
FROM Sales.SalesOrderHeader AS SOH
10
-- Hier zetten we de OrderDate om naar een integer en joinen hem met de Datum-dimensie DimDatum
11
LEFT OUTER JOIN DimDatum AS DD ON YEAR(SOH.OrderDate) * 10000 + MONTH(SOH.OrderDate) * 100 + DAY(SOH.OrderDate) = DD.DatumId

Also useful for Excel PowerPivot: load the date table as a tab. Then you can also use it in Excel PowerPivot.

Another example, give me all public holidays in SQL Server for 2013:

If you have company-specific holidays and you know them in advance, you can also mark them in this table. This is useful when you want to run certain calculations and take public holidays into account.

Attached to this article is a script to create the date dimension table. After that comes an MS SQL Server stored procedure to fill the table.

If you like, I can also write a script that creates the table and inserts the data right away

Download