One of the most common dimension tables in any data warehouse or data mart is the date dimension.

It typically stores, for each date relevant to the organisation, a few fields which describe that date, such as: year, month, month name, work day(yes/no), financial year, quarter, day name, and the list goes on, depending on what you need.

When connecting the date dimension with your fact tables, it makes it easy to answer questions like “what’s the most profitable day of the week”, “what are the business results of the 3rd quarter” etc.

Here’s a glimpse:

Modern BI visualisation products (namely where I’ve seen this: Tableau and Power BI) often have a built-in functionality that serves as a date dimension. They identify that a field is of a date type, and they offer additional attributes for that field, such as year, month and quarter.

I often recommend not to rely on these built-in capabilities, and still build a date dimension. Here are some good reasons:

1. If you want to be able to answer ad-hoc data requests by running queries directly on your data mart, without using any BI product, you would benefit from having the date table available for your queries.

2. When you have several fact tables contributing to the data presented in a report, and you want your users to be able to filter by date, you’ll be able to just present them with the date filter from the date dimension, instead of forcing them to work with several date filters.

3. You can customise and add your own attributes, and not rely on the several built-in options, which are normally just year, quarter, month and week. One company shows weekly data by “First Day of Week”, another does so by “Last Day of Week” etc.. When you have your own tables, you have your own control.

Another note, specific to Power BI users: I recommend you don’t get tempted to create a date table in DAX, because you’ll need to do so in each and every dataset, instead of reusing a database table.

Finally, here are some links to date dimension creation scripts:

SQL server – option one

SQL server – option 2

PostgreSQL

Oracle – my own version

Hopefully that helps, please comment if you have any questions or suggestions.