PowerPivot Time intelligent functions golden rules

UPDATE: make sure you visit this blog post to see what is updated in SQL 2012 PowerPivot, this will make time intelligence functions much easier.

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:

  1. Never use the datetime column from the fact table in time functions.
  2. Always create a separate Time table.
  3. Make sure your datetime table has a continuous date range
  4. Create relationships between fact tables and the Time table.
  5. Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).
  6. 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

and MSDN blog post: Time Intelligence Functions in DAX