PowerPivot is bulk loaded with Time Intelligence functions. Those functions make it very easy to do data calculations like Y-1, YTD, MTD, .... In V1 however, you needed to follow the golden rules as discussed in this blogpost by Kasper de Jonge. If you didn't, you ended with wrong results in DAX calculations.
In a typical business scenario for PowerPivot we combine data coming from a relational datawarehouse with other data sources. One of the best practices when building relational datawarehouses is the use of surrogate integer keys. The primary key of our date dimension will be a integer column as well as the foreign keys in the fact table).
This breaks of course rule 5. Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).
In V2 we no longer need to workaround this issue. We can mark a table as a Date Table. Once we do this time intelligence functions will work out fine, even if the relationship between the fact table and time dimensions is based on an integer colum.
In the screenshot below you can see how a simple YTD calculation is wrong simply because the relationship between the two tables is based on an integer column.
After marking DimTime as Date Table the calculations run just fine.
In the mean time Kasper has blogged new Golden rules for PowerPivot V2.
In a typical business scenario for PowerPivot we combine data coming from a relational datawarehouse with other data sources. One of the best practices when building relational datawarehouses is the use of surrogate integer keys. The primary key of our date dimension will be a integer column as well as the foreign keys in the fact table).
This breaks of course rule 5. Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).
In V2 we no longer need to workaround this issue. We can mark a table as a Date Table. Once we do this time intelligence functions will work out fine, even if the relationship between the fact table and time dimensions is based on an integer colum.
In the screenshot below you can see how a simple YTD calculation is wrong simply because the relationship between the two tables is based on an integer column.
After marking DimTime as Date Table the calculations run just fine.
In the mean time Kasper has blogged new Golden rules for PowerPivot V2.
- 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.
No comments:
Post a Comment