PowerPivot Time intelligent functions golden rules
I was helping a client this week with a quickstart on PowerPivot. She was working with datetime columns from fact table, relating this column to a values in a date table. When i used the relationship in the workbook just a few rows had actual relationships with the date column. It appeared the datetime in the fact table had times as well and that was why we the relationship couldn’t be made. Another much heard issue is that people need to use the ALL() function to work with time intelligent functions.
That night i remembered receiving a mail from Marius Dumitru from MSFT with a few rules on working with Time intelligent function in RTM PowerPivot.
When you apply these rules to your time intelligent functions are good to go:
- Never use the datetime column from the fact table in time functions.
- Always create a separate Time table.
- Make sure your datetime table has a continues date range
- Create relationships between fact tables and the Time table.
- Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).
- The datetime column in the Time table should be at day granularity (without fractions of a day).
Applying item #5 above is what ensures that ALL() isn’t required at the end of calls to time functions.
Make sure you check out my other blog posts on Time intelligent functions as well:
PowerPivot time intelligent functions revisited: why use ALL() and how to work around it
Create a separate related time table for time intelligent functions in PowerPivot
Screencast: Introduction to time intelligent functions in PowerPivot
Hi Kasper,
Thanks a lot for this. In our dataset (it is a datawarehouse on SAP R/3 data) in de DWH area we work with artificial keys. Also for the time table. So far I did not encounter problems with these, but I’m relative new to DAX. What kind of problems, or functions not properly working can I expect?
Groet, Frits
Building datawarehouses on SAP without SAP BW
Hi Frits,
For a multiple reason, check out this blog post for an example:
http://www.powerpivotblog.nl/ok-why-should-i-want-to-load-an-entire-time-table-in-powerpivot
and
http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-revisited-why-use-all-and-how-to-work-around-it
Kasper
Hi Kasper,
Clear. Indeed these are golden rules. After creating the join between the time table and the fact table on a date field, functions with YTD and filters on date are doing what you expected without building workarounds. Also the processing time takes much, much shorter.
Thanks again,
Frits
http://www.newfrontiers.com
Hi Kasper,
Indeed, after some practice I found out that date and time function work much better joining them on a date field. Processing time is also much shorter.
Thanks again, Frits
Building datawarehouses on SAP without SAP BW
Kasper, could you elaborate on rule #5? I do not understand fully why I would have to use the All function when my time table in the datawarehouse is linked to the fact table on a surrogate key, like in Adventure Works DW.
Thanks,
Hans.
Hi @Hans Geurtsen ,
The reason is that DAX needs the values of an actual date column to work its magic. How will it be able to go back one year if it has a int value? If you don’t use the ALL in your example the row context that is being created by the relationship will only return a single row from the date table. And it needs all rows from the date column to be able to use the time intel function (hence the ALL).
Hope that answers your question.
Kasper