Do You Need a Date Dimension?

2017-01-13_12h49_19

I’ve hear this question many times; “Do you think we need a date dimension?”.  some years ago, date dimension has been used more, but nowadays less! The main reason is that there are some tools that are using their own built-in date hierarchy. As an example Power BI has a built-in date hierarchy that is enabled by default on top of all date fields which gives you a simple hierarchy of year, quarter, month, and day. Having this hierarchy brings the question most of the time that do I need to have a date dimension still? of I can simply use this date hierarchy? In this post I’ll answer this question and explain it with reasons. Majority of this post is conceptual and can be used across all BI tools, however part of it is Power BI focused. If you like to learn more about Power BI, read Power BI online book from Rookie to Rock Star.

What is Date Dimension?

Date Dimension is a table that has one record per each day, no more, no less! Depends on the period used in the business you can define start and end of the date dimension. For example your date dimension can start from 1st of Jan 1980 to 31st of December of 2030. For every year normally you will have 365 records (one record per year), except leap years with 366 records. here is an example screenshot of a date dimension records;

Date Dimension is not a big dimension as it would be only ~3650 records for 10 years, or even ~36500 rows for 100 years (and you might never want to go beyond that). Columns will be normally all descriptive information about date, such as Date itself, year, month, quarter, half year, day of month, day of year….

Date Dimension will be normally loaded once, and used many times after it. So it shouldn’t be part of your every night ETL or data load process.

Why Date Dimension?

Date Dimension is useful for scenarios mentioned below;

  1. Ability to slice and dice by many date attributes such as week number, half year, day of year, and etc.
  2. Consistency in Analysis
  3. Ability to do analysis based on public holidays (Easter Monday, Good Friday, and etc)
  4. Some BI tools extended functions need to work with a Date Dimension

Let’s look at reasons one by one;

Powerful Slice and Dice

If you have worked with Power BI you know that there is a date hierarchy built-in which gives you fields such as Year, Quarter, Month, and Day. This hierarchy normally adds automatically to date fields. here is an example of how it looks like;

2017-01-13_12h35_06

This built-in hierarchy is very good help, but what about times that you want to slice and dice date fields based on something other than these 4 fields? for example questions that you cannot answer with this hierarchy are;

  1. Weekday analysis of revenue (by Monday, Tuesday, …., Sunday)
  2. Which week in the year generates least revenue or most revenue?
  3. What about Fiscal? What is the revenue for Fiscal year?
  4. and many other questions.

The fact is; when you use the simple hierarchy of Year, Quarter, Month, and Day, you are limited to only slice and dice by these fields. If you want to step beyond that, then you have to create additional fields to do it. That’s when a date dimension with all of these fields is handy. Here are some fields that you can slice and dice based on that in Date Dimension (and this is just a very short list, a real date dimension might have twice, three times, or 4 times more fields than this! Yes more fields means more power in slicing and dicing)

2017-01-13_12h45_34

You can answer questions above without a date dimension of course, for example; to answer the first question above, you will need to add Weekday Name, because you would need to sort it based on Weekday number, so  you have to bring that as well. To answer second question you need to bring Week number of year. For third question you need to bring fiscal columns, and after a while you will have heaps of date related columns in your fact table!

And worst part is that this is only this fact table (let’s say Sales fact table). Next month you’ll bring Inventory fact table, and the story begins with date fields in Inventory fact table. This simply leads us to the second reason for date dimension; Consistency.

Consistency in Analysis

You don’t want to be able to slice and dice your Sales data by week number of year, but not having this feature in Inventory data! If you repeat these columns in all fact tables you will have big fact tables which are not consistent though, if you add a new column somewhere you have to add it in all other tables. What about a change in calculation? Believe me you never want to do it.

Date Dimension on the other hand is a dimension that is shared between all fact tables. You add all fields and calculations here, and fact tables are only related to this. This is a consistent approach. Here is a simple view of a data warehouse schema with a date dimension shared between two fact tables.

2017-01-13_12h49_19

Public Holidays Insight

In most of businesses nowadays public holidays or special event’s insight is an important aspect of reporting and analysis. some of analysis are like;

  • Was the sales in Easter this year better than last year? (you don’t want to calculate Easter in Power BI of course)
  • How is the revenue is public holidays compared with weekends?
  • How was the sales for store in opening day? (special event)
  • and many other questions….

One of the best use cases of date dimension is public holidays and special events. I have shown previously in another post how to fetch public holidays live in Power BI. Calculating some of public holidays are time consuming and not easy. Easter for example changes every year. You can also have some fields in date dimension that mention what the special day is (opening, closing, massive sale….). Your date dimension can be enhanced a lot with insight from these fields. Here is an example of public holidays information in a date dimension;

2016-12-06_18h41_25

Extended Functions for Time Intelligence

BI Tools in the market has some extended functions that gives you insight related to date, named as time intelligence functions. For examples, talking about Power BI, you can leverage heaps of DAX functions, such as TotalYTD to calculate year to date, ParallelPeriod to find the period parallel to the current period in previous year or quarter, and many other functions. These functions sometimes need to work with a date dimension, otherwise they won’t return correct result!

Yes, you’ve read it correctly, DAX time intelligence functions won’t work properly if you don’t have a proper date dimension. proper date dimension is a table that has a full date column in one of the fields, and has no gaps in dates. This means that if you are using Sales transaction table which has an OrderDate field as an input to DAX time intelligence functions, and if there is no sales transaction for 1st of January, then the result of time intelligence functions won’t be correct! It won’t give you an error, but it won’t show you the correct result as well, this is the worst type of error might happen. So having a Date Dimension can be helpful in this kind of scenarios as well.

2016-04-05_00h03_34

In this post I’ve explained how to use some of DAX time intelligence functions in Power BI.

Do I Need a Date Dimension?

Now is the time to answer the question; “Do I need a date dimension”? the answer is: Yes, IMHO. Let me answer that in this way: Obviously I can do a BI solution without date dimension, but would you build a table without hammer?! Would you build  a BI solution that has some date columns in each fact table, and they are not consistent with each other? would you overlook having special event or public holidays dates insight in your solution? would you only stick to year, quarter, month, and date slicing and dicing? would you like to write all your time intelligence functions yourself? Obviously you don’t. Everyone has the same answer when I explain it all.

I have been working with countless of BI solutions in my work experience, and there is always requirement for a Date Dimension. Sooner or later you get to a point that you need to add it. In my opinion add it at the very beginning so you don’t get into the hassle of some rework.

How to Build a Date Dimension?

Now that you want to build a date dimension, there are many ways to build it. Date Dimension is a dimension table that you normally load once and use it always. because 1st of Jan 2017 is always 1st of Jan 2017! data won’t change, except bringing new holiday or special event dates. I mention some of ways to build a date dimension as below (there are many more ways to do it if you do a bit of search in Google);

Date Dimension T-SQL Script

You can use a written T-SQL Script and load date dimension into a relational database such as SQL Server, Oracle, MySQL, and etc. Here is a T_SQL example I have written few years ago.

Power Query Script

I have written a series of blog posts explaining how you can do the whole date dimension in Power Query. the blog post is a bit old, I will write a new one in near future and will show an easier way of doing this in Power BI.

Fetch Public Holidays

I have written another blog post that mentioned how to fetch public holidays from internet and attach it to a date dimension in Power BI.

DAX Calendar Functions

You can use Calendar() function in DAX to generate a date dimension very quickly as well.

Using Date Dimension Multiple Times?

Using a dimension multiple times in a data warehouse called Role Playing dimension. I have explained how to do role playing dimension for DimDate in this blog post.

Save

Save

Save

Save

Save

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

21 thoughts on “Do You Need a Date Dimension?

  • Very useful topic <3, there is one suggestion that instead of using sql script for sql server, we could use date dimension wizard in SSDT of ssas project to create date dimension

    • Hi Tri,
      Thanks for your feedback. Yes, Date dimension wizard in SSDT is another way of doing that, it won’t have public holidays though, but an easy way overall.
      Cheers
      Reza

  • Hi Reza,
    thx for this great intro and excellent advice! One tip that I would like to add is:
    After you’ve connected your fact-tables with your date-dimension, hide the date field from your fact table from the Report View. As your reports might return basically wrong results in some cases if you use the date-field from your fact-tables.

    • Thanks Imke for your kind words and great advise.
      Absolutely right, there should be only one place to do date analysis from it, so extra date fields should be marked as hide in report view.
      Cheers
      Reza

  • Hi Reza,
    Great post as a usual. Question, has anyone ever just created a PBI template with a complete date table built into it for us less savvy end users to have and use?
    Dave

    • Hi Dave,
      Thanks for your kind feedback. The link I have shared has a M script for creating a full date dimension. you can copy and paste the code in Power BI or Excel and you would be good to go. I will write soon another post with a new version of it.
      Cheers
      Reza

  • Hi Reza,

    What if, fact data are not at day level, but agregated by week or by month? How could I have a calendatr table ? Does it means that in this case I cannot benefit on PBI time intelligence functions ? How could I manage this case ?

    • Hi Laurent,
      You can create a date value in every week for the fact table. Let’s say only Mondays or Fridays or another day. Then you can join it to the date table normally.
      Cheers
      Reza

  • Hi Reza,

    I might just not have read it. But I started to bring back a date Dimension into Power BI when I did find that Power BI is creating a temporary date table in the background for each date column in the model.
    This might cause some performance issues. So I did disable the setting for creating the background date tables for each date column in the model.

    Or is this not really needed?

  • Hi Reza,
    I am working on tabular cube, one date dimension DimDate, 2 fact tables, both 2 fact tables have reportDate column,which have relationship with dimDate by FullDate column, when I deploy cube to server and test on powerbi,
    only one fact table can be sliced by DimDate, another fact table doesn’t work with DimDate.

  • Hey Reza,
    Love your work!
    We have infrastructure monitoring data which has date/time columns down to the second. I want to add a date dimension so we can slice data across the whole data set and perform analysis against time of day and day of week.
    I started by creating a date dimension with day level granularity, then started added a custom column in the data tables to calculate the date from the date/time column. This works for slicing data by the date, but I can’t see how I can analyse data by the hour or by the day of week without adding columns to each data table to calculate the hour and day of week.
    Should I instead create a date dimension down with second level granularity? Is this a good idea?
    Thanks for your help!
    Jason

    • Hi Jason
      Don’t combine Time attributes (hours, minutes, second etc) into the Date table, instead create a Time dimension separately. connect them both to your fact table. You do need to separate the date and time fields in your fact table for that purpose. Here I explained that with an example of a Time dimension.
      Cheers
      Reza

  • Hi Reza, thanks for this info. it is my experience that using the Date dimension dates in my PowerPivot output in thin workbooks drains the resources on larger pivot tables and limits the number og years of data that i can show. However using a date from one of the data tables does not seem to have the same limitations. Should using Dimdate filed effect my pivottable usability like this or do you think there may be some underlying issue with the dimension date table set up / linking to the other tables?

    • Hi Gary
      Having a custom date table shouldn’t reduce your performance. In many cases, it should even increase it. I guess the problem is somewhere else in the model.
      Cheers
      Reza

  • What purpose do the summary fields (e.g. CalendarQuarter, CalendarYear) in the date-dimension serve?

    • If you want to slice and dice your data in other tables by Quarter, Year, Half-year, week, etc. then you need them to be columns in your Date table.
      Cheers
      Reza

  • Hi Reza, I have the date dimension without issue except when I have to look up related attributes from the date dimension. Let’s say I have two dates in the sales table and I want to substract the fiscal years of such dates. Could you plese suggest how to write the dax to lookup into the date dimension?
    Thanks

Leave a Reply