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: http://www.kasperonbi.com/powerpivot-time-intelligent-functions-golden-rules.
The rules in SQL 2008 R2 were the following:
- Never use the datetime column from the fact table in time functions.
- Always create a separate Date table.
- Make sure your date table has a continues date range
- Create relationships between fact tables and the Date table.
- Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).
- 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:
- Never use the datetime column from the fact table in time functions.
- Always create a separate Date table.
- Make sure your date table has a continues date range
- Create relationships between fact tables and the Date table.
- The datetime column in the Date table should be at day granularity (without fractions of a day).
- 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 🙂
This is great news Kasper. Thanks for sharing this and keep up the great job. Kudos to you and the rest of the SSAS team:)
Thanks a lot,
It solved an issue regarding the YTD function in DAX.
Love the Blog, Kasper! Can you give an example of when your fact table would be related to your datetime table through a column that is not of type date?
@Jeff
Many systems use an integer of the form yyyymmdd (20120117 for January 17th, 2012) to represent the date instead of using a date type. So your relationship could be on a column called Date_Int and another column on the Date table would have the date type.
This is very useful functionality to make it easier to create date calculations.
My question is about Tabular SSAS deployments.
Is there any way for a front-end tool to find out which tables/columns have been marked as date tables?
I have deployed a simple database with a date table/column marked in it.
I took a look in the $SYSTEM.dbschema_columns and the CSDL metadata, but couldn’t find anything promising in there.
Is it enough to know (from CSDL) that the table key is a PropertyRef to a DateTime column?
Hi @Colin ,
You can see it in two ways:
In CSDL it’s returned in Contents.
In XMLA it’s DimensionAttributeTypeEnumType.
The type has been set to Date.
Kasper
@Kasper de Jonge
Thanks don’t know how I missed it, just didn’t read past the columns I guess.
The element is present and correct, thanks!
I have created the time calculations using the technique mentioned above. My Current Year calculations works fine. However, my last year (Prior Year – Same Period, Prior YTD etc) calculations do not work and doesn’t show up on the Pivot Table.
Can you think of any reason why?
When marking as a date table, is there a way to set the year end date to 06-30? Thank you.