As a data modeler, you need to understand the “one or many date tables” dilemma. You don’t want having to switch from one option to another after a few weeks of development, as it’d be a painful change to go through. In this post, I’ll try to give you a clear overview of these two options so you can make the right decision right away.

Deep dive into the two options

Here’s an example of a single date table model.

Here’s the same model, with multiple date tables

Notice that on the single date table model, there is one active relationship between the fact table and the date table; the other two are inactive. It means that by default, whenever you’re using an amount from the fact table, and split it by the date table, it will take the DueDateKey relationship.

For example, here’s the result if you look at the SalesAmount by FiscalYear.

Using the single date table model, if you want to look at the SalesAmount by FiscalYear, but for the OrderDate, you’ll have to create the following measure:

SalesAmount by OrderDate =

    CALCULATE(

        SUM(FactInternetSales[SalesAmount]),

        USERELATIONSHIP(DimDate[DateKey],FactInternetSales[OrderDateKey])

    )

Here, we’re asking Power BI to use the inactive relationship between the date table and the column OrderDateKey on the fact table.

We have logically different results from the first chart.  

Notice that with the single date table model, you must create more DAX measures, and you have to name them using a naming convention if you want your users to know what they’re looking at.

With multiple date tables, you don’t need DAX to get these results. You just need to select the right FiscalYear field from the right date table.

However, with multiple date tables, users can compute the sales amount by any date table available to them. For example, you could have more date tables related to other fact tables. In my model, I have another fact table called FactResellerSales, which also have 3 date tables. If I wanted to model this properly with many date tables, I’d have to create 3 additional date tables. I’d also have to specify that it’s for the reseller sales, not the internet sales. I’d have the following date tables:

• Date – Reseller – Due Date
• Date – Reseller – Order Date
• Date – Reseller – Ship Date
• Date – Internet – Due Date
• Date – Internet – Order Date
• Date – Internet – Ship Date

Now if a user makes a mistake and asks for the SalesAmount from the InternetSales tables, by “Date – Reseller – Due Date” Fiscal Year, he’d now have the wrong numbers. He’d actually see the total sales amount of the whole fact table for each fiscal year. For this reason, I consider that the single date table is less error-prone for users, especially users new to Power BI and analytics.

The single date table needs more DAX measures, but less maintenance of date tables. Indeed, if you need to create a new date hierarchy for example, you’d have to do this as many times as the number of date tables you have. It can be time consuming.

There’s one visual you can do easily just with the multiple date table model, which is the following:

Here, I see a matrix of the sales amount, by due date fiscal year in rows and order date fiscal years in columns. Doing this with a single date table might be possible with some advanced DAX, but it certainly wouldn’t be as easy as just dragging 3 fields to the matrix visual. Additionally, if you want to ask for a measure, filtered by 2 distinct date tables, it’s something you can easily do with multiple date tables as we can see. For example, let’s say you want to see the SalesAmount for the DueDate in 2011 and the OrderDate in 2010. With the multiple date table, you can see it’s 43.421 USD.

However, notice that the names are very confusing. Both are named Fiscal Year, and you can’t really know for sure which one is which. You must hoover over the field to know for sure. If you consider having many date tables in your model, it might be a good idea to rename all columns to specify which date table it is. Here, I’d have to rename the FiscalYear columns to:

• Internet – DueDate FiscalYear
• Internet – OrderDate FiscalYear

It’s one of the main reason why I’d pick the single date table in most cases, especially if you’re not certain of what you’re doing. It’s less confusion.

Another aspect you wouldn’t see without having tested both options is when you use date filters on your reports. If you want to show a page which contain 2 fact tables, and you want to give the flexibility to filter on a specific FiscalYear; you’d have to add several FiscalYear column if you have many date tables. It’s not user friendly and it can be dangerous, as they could just forget to filter all fiscal year columns and get the wrong numbers. In this case, I’d suggest to put the filters as slicers, correctly named, on the page itself.

Pros and cons

Single date table:

• Pros
  • Simplify the data model, as it has less tables
  • You have control over what the users can do with your model, as you select the relationship in your measures
  • It’s less maintenance over your date table, and it gives you more flexibility
  • You can filter a whole report by a date filter, no matter the numbers of different date columns used
• Cons
  • Users have less flexibility over choosing which date they need. They must ask for a new DAX measure if they need a new measure.
  • You can’t easily build a matrix with one fact amount split by 2 distinct date tables

Many date table:

• Pros
  • If you need the same measure split by different dates, it requires less DAX measures.
  • You can ask for an Amount, filtered at the same time by different date tables.
• Cons
  • The data model becomes less readable, as you will need more tables
  • You have less control over what the users can do with your model. They can pick the wrong date table
  • It can cause issues when you want to filter a report by date if you have several fact tables on the same page
  • It requires more maintenance of the date tables. It requires a strong naming convention as well otherwise it’s confusing

Conclusion

In the end, once you understand the pros and cons of each options, it’s way easier to commit to one.

You just need to think about the model you’re building and ask yourself a few questions:

• Who will use your data model and how?
• How many fact tables you have?
• How many date tables you’ll need? Is this number if likely to change?
• …

For example, let’s say that you’re building the first enterprise data model for a company. After gathering some information, you notice that:

• Most users don’t know much about Power BI, or analytics in general
• Specifications given by various departments are vague
• You’ll need 10 fact tables and most of these have 2 to 10 date fields
• The date table available in the data warehouse you’ll be using isn’t robust

In this case, as you’re building a model that will answer most of the business questions people have, you know that model is going to be big. You could have up to 100 date tables potentially if you go with the many date tables options. You also know that you’ll have to build something simple as users are new to analytics. Also, you may have to update the date table quite often as it’s not robust yet.

For these reasons, I’d recommend going with the single date table option in a business case like this one. You’d have more adoption as the model is going to be easy to use, and it’s going to be a much better self-service data model, and less error-prone.