Date table(s) - why are they needed? and problems they bring with links/loops

[rbbi]

I've read some other discussions of this but I'm trying to get a more definitive answer ...

I completed the EDX course "Analyzing and Visualizing Data with Power BI" and the demos and exercises there had a date table linked to a date column in the fact table.

But in real life the fact table(s) may have more than one date column, and dimension tables may also have one or more date columns.

So my question is: Why have a date table at all?

To elaborate ... doesn't (or shouldn't) Power BI provide enough functionality on date columns to make a separate date table unnecessary?

It would be really nice to just have date columns and not need date table(s).

If a separate date table is really needed, I can't have just one date table, because if the fact table has more than one date column, I can only link one of these to the date table, So I need a separate date table for each date column in the fact table. And if dimension tables also have dates, I can't link these to any date table that's linked to the fact table, because that will create loops!

The conclusion is that if I do need a separate date table to get the date processing functions required, then I really need one date table for every date column in the model that I want to be able to process as a date .. seems very inefficient to me in time, storage and maintenance.

thanks

[rbbi · Accepted solution]

Thanks for your reply Matt. Some good insights there.

So it seems I'm right that in the current release I need date tables for all date columns that I wish to process as dates, one per column to avoid loops in the model. But I think this only applies if I want to have more than just the default hierarchy of dates that PBI already provides (like fiscal year). And I found some information (further below) that might even make that unnecessary!

I disagree a bit though on the contiguous nature of raw dates (i.e. without linked date columns). I have a simple test model that has sale dates and amounts in 2015, 2016 and 2018 (note: none in 2017!) and a couple of charts show up very nicely showing the proportional spread of these dates. The top one uses the date hierarchy automatically provided, the lower one is switched to just the date.

I also found some good info in a recent presentation here, which explains this and points to future developments ...

https://www.youtube.com/watch?v=RiHpkN0gfPM

At 38min30sec he starts talking about calculated calendar tables that automatically populate to the date ranges present in your data.

Then at 42min15sec he runs Power BI in a special mode that shows 'hidden' date template tables that are present all the time and are automatically linked to all date & datetime columns! Seems these are going to be exposed to us in future releases, at least the template structure(s) so that they can be customised with things like fiscal year definition and any other date processing we need. This will make using our own date tables unnecessary.

cheers, Rod

[MattAllington]

A date/calendar table is just another dimension table. Dimension tables are used to save space and make everything work more effectively (among other things). So instead of having date, day name, month name, month number, year, day number, quarter, etc etc in your fact table (which is inefficient), you abstract all these attributes into a dimension table. 

Also, inbuilt time intelligence requires a date table that meets certain rules - eg contiguous ranges. Fact tables don't have to meet these rules, so the calendar table is needed to provide consistent structure. 

You don't need a calendar table for each date column in your fact table. You only need it if you want to use time intelligence functions or want to access dimension columns. 

You may like to read my blog post on this topic here. http://exceleratorbi.com.au/power-pivot-calendar-tables/