PowerPivot v2 for SQL 2012 was released in March. Some of the limitations we had in v1 of the product have been solved in this v2 release which makes modelling easier and enables the analysis of large items in a better way.
In this post we’ll look at two of the main improvements.
1. Role Playing tables
Let’s begin with Role Playing tables. In a data model, this type of table contains columns that need to play different factual roles.
With PowerPivot one of the best practices is to use a separate Date table to perform time based analysis. Many of the time intelligence functions within PowerPivot rely on a separate data table being present.
The Date table is a common role playing table. If we consider the InternetSales fact table in the AdventureWorks sample database we have OrderDate, ShipDate, DueDate. The Month attribute from the Date table will give different results depending on the Date type we want.
Prior to this release of Power Pivot we could only create 1 relationship between tables. In other words, we could only analyse the facts by one of the Date types. There are work-around’s for this problem but it can make the model messy.
In PowerPivot v2 we can create multiple relationships between tables! However, there can be only 1 relationship active for a given calculation.
Notice in the screen shot below the 3 relationships between the FactInternetSales table and the Date table. The solid line refers to the active relationship.
Here we can see the active relationship is between the OrderDateKey and the DateKey. This means that when we use the attributes in the Date table we will be seeing the results based on the Order Date.
We can override this relationship in the Pivot table by creating a new measure. The function used to do this is called USERELATIONSHIP. Two arguments are used to define the relationshp we want to use in the calculation, these being the start and end points of the relationship we want to make active.
In the example below I’m using the CALCULATE function with USERELATIONSHIP to create a measure to define Sales Amount based on the Ship Date.
In the Pivot Table below you can clearly see the different numbers being calculated along 2 different relationships.
There we have it.
Next, we’ll look at the Hierarchy functionality that is new to Power Pivot v2.
Hierarchies enable you to navigate a large list of items by grouping detail into summary levels that can be drilled. Pivot Tables provide this functionality by default but the advantage of creating them in advance means the production of the Pivot Table is easier. This is particularly true for hierarchies that contain many levels.
Let’s consider the Product table. The low level product is too detailed for reporting. We can create the higher level product groups by creating a hierarchy. We create the hierarchies from the Network Diagram view by right clicking over the column we wish to use as the first level of the hierarchy and then selecting Create Hierarchy.
We can then rename the new hierarchy and add new levels simply by dragging the appropriate columns to the foot of the hierarchy.
That’s all there is to it. Proceed to create the Pivot Table and you’ll see the new Hierarchy with the built-in drill capability. This is a nice feature that enables you to build models that are easy to navigate.
You can download the sample workbook I used in this example from here.
As always, feel free to comment.