Powerpivot CTP3: What is new for Time Intelligence functions

Time Intelligence functions are not new in SQL Server Denali. But in Denali we have made working with these functions a little easier and flexible.  To use these one of these functions in 2008 R2 DAX you used to apply some rules: https://www.kasperonbi.com/powerpivot-time-intelligent-functions-golden-rules.

The rules in SQL 2008 R2 were the following:

  1. Never use the datetime column from the fact table in time functions.
  2. Always create a separate Date table.
  3. Make sure your date table has a continues date range
  4. Create relationships between fact tables and the Date 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 Date table should be at day granularity (without fractions of a day).

With the release of Denali we have changed these to the following:

  1. Never use the datetime column from the fact table in time functions.
  2. Always create a separate Date table.
  3. Make sure your date table has a continues date range
  4. Create relationships between fact tables and the Date table.
  5. The datetime column in the Date table should be at day granularity (without fractions of a day).
  6. Mark the Date table as a Date Table in PowerPivot and set the Date column.

So what is changed here? In Denali your date table can have a column that is related to the fact table that is not of the type Date.  This is a situation most datawarehouses will have set up. Most data warehouses will have a fact table that is joinedwith a date table based on a column that is an integer value. In 2008 R2 PowerPivot getting this to work was very hard.

In Denali all we have to do in PowerPivot / BIDS is make sure that our model knows which table is a date table and what column is a date column. The reason we need to supply DAX with a date column is that DAX needs to know how to apply its time intelligence functions, if you have an integer column like 20031010 how does DAX then know how to subtract a year?

Open the PowerPivot window, select the date table, go to design and click Mark as Date Table:

This will prompt you a dialog. In this dialog you can select the column that contains your date values:

This will result in two things:

  • Time intelligence function will now work using this table
  • Excel is aware that that columns from this table are Date columns and the specific Excel time related filters are now enabled:

As you can see this small operation has big effects 🙂