KPIs are another Analysis Services concept coming to PowerPivot. Creating KPIs is pretty straight forward.
In the example below I have table with Sales and Costs. Let's say we want to have a 5% margin.
KPIs in PowerPivot are based on a meusure. So, first thing to do is create a measure to calculate the margin.
Step 2 is setting the KPI target values. Target values can again be based on a measure or as in this simple example on a fixed value.
Step 3 is setting the tresholds and finally we choose an icon style.
And that's it, our KPI is ready to use!
Friday, August 12, 2011
Thursday, August 11, 2011
PowerPivot Nuggets - Part 9 - Descriptions
After the long PowerPivot Nugget on Parent Child relations a very short nugget today.
PowerPivot v2 allows you to add descriptions to various objects. Explore the user interface and you will discover that you can add descriptions on Tables, Columns, Calculated measures, KPIs, ....
A nice little new feature that will make your models easier to understand for other users.
PowerPivot v2 allows you to add descriptions to various objects. Explore the user interface and you will discover that you can add descriptions on Tables, Columns, Calculated measures, KPIs, ....
A nice little new feature that will make your models easier to understand for other users.
Tuesday, August 09, 2011
PowerPivot Nuggets - Part 8 - Parent Child Relations
Parent Child hierarchies like organization hierarchies and account structures are very common in reporting environments. PowerPivot V2 ships with a set of new DAX functions which allows us to leverage this in PowerPivot.
Let's have a look at our dimEmployee table. This table has a parent-child relations (ParentEmployeeKey --> EmployeeKey). As you can see in the screenshot below; you cannot create a relationship between the two columns in PowerPivot. Just like with roll-playing dimensions PowerPivot uses a different approach than Analysis Services.
In PowerPivot Parent Child relatinships are managed through DAX. Let's focus on employee John Campbell and play around with some DAX functions.
First function is PATH. PATH returns a delimited text string with the EmployeeKey of all the parents of the current EmployeeKey, starting with the earliest and continuing until the current.
=PATH([EmployeeKey],[ParentEmployeeKey])
John Campbell's manager is Peter Krebs, and Peter Kreb's manager is Ken Sánchez. If we look at the corresponding Employekeys this results in 112|23|20.
Second function is PATHLENGTH(). PATHLENGTH(PATH([EmployeeKey],[ParentEmployeeKey])) will return the length of the path. In John Campbell's case this is 3.
With PATHCONTAINS() we can check if a particular key exists in the path.
=PATHCONTAINS(PATH([EmployeeKey],[ParentEmployeeKey]),23) will return TRUE if EmployeeKey 23 is found anyware in the levels above the current Employee. In the case of John Campbell this will return TRUE.
PATHITEM AND PATHITEMREVERSE allows us to pick out a particular level in the hierarchy.
PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the top. PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the bottom.
In the case of John Campbell this is two times 23.
The last function we need to peek at is LOOKUPVALUE which allows us to lookup a particular column value for a given search criteria. The following DAX statement will return the manager's name.
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2))
For John Campbell this is Peter Krebs.
If we now combine LOOKUPVALUE, PATH, and PATHITEM we can turn this parent-child dimensions into a natural hierarchy by creating a column for each level:
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),1))
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2))
This approach is very similar to the concept used by BIDSHelper's Parent-Child Dimension Naturalizer for Analysis Services.
Next step is to create a hierarchy in the diagram view and create a pivottable.
This nugget was written on CTP3. One thing that is missing here is an easy way to hide the "empty levels". E.g. Peter Krebs is on level 2, so level 3, 4, 5, .. are empty for Peter Krebs.
In Analysis Services we solve this by setting the HideMemberIf property. Alberto Ferrari blogged a workaround for this in his post on Parent Child relationships. Alberto also opened an item on Microsoft Connect. Let's vote for this feature request and hope it makes it in the RTM version.
The longest nugget so far on a very cool BI concept coming to PowerPivot.
Let's have a look at our dimEmployee table. This table has a parent-child relations (ParentEmployeeKey --> EmployeeKey). As you can see in the screenshot below; you cannot create a relationship between the two columns in PowerPivot. Just like with roll-playing dimensions PowerPivot uses a different approach than Analysis Services.
In PowerPivot Parent Child relatinships are managed through DAX. Let's focus on employee John Campbell and play around with some DAX functions.
First function is PATH. PATH returns a delimited text string with the EmployeeKey of all the parents of the current EmployeeKey, starting with the earliest and continuing until the current.
=PATH([EmployeeKey],[ParentEmployeeKey])
John Campbell's manager is Peter Krebs, and Peter Kreb's manager is Ken Sánchez. If we look at the corresponding Employekeys this results in 112|23|20.
Second function is PATHLENGTH(). PATHLENGTH(PATH([EmployeeKey],[ParentEmployeeKey])) will return the length of the path. In John Campbell's case this is 3.
With PATHCONTAINS() we can check if a particular key exists in the path.
=PATHCONTAINS(PATH([EmployeeKey],[ParentEmployeeKey]),23) will return TRUE if EmployeeKey 23 is found anyware in the levels above the current Employee. In the case of John Campbell this will return TRUE.
PATHITEM AND PATHITEMREVERSE allows us to pick out a particular level in the hierarchy.
PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the top. PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the bottom.
In the case of John Campbell this is two times 23.
The last function we need to peek at is LOOKUPVALUE which allows us to lookup a particular column value for a given search criteria. The following DAX statement will return the manager's name.
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2))
For John Campbell this is Peter Krebs.
If we now combine LOOKUPVALUE, PATH, and PATHITEM we can turn this parent-child dimensions into a natural hierarchy by creating a column for each level:
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),1))
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2))
This approach is very similar to the concept used by BIDSHelper's Parent-Child Dimension Naturalizer for Analysis Services.
Next step is to create a hierarchy in the diagram view and create a pivottable.
This nugget was written on CTP3. One thing that is missing here is an easy way to hide the "empty levels". E.g. Peter Krebs is on level 2, so level 3, 4, 5, .. are empty for Peter Krebs.
In Analysis Services we solve this by setting the HideMemberIf property. Alberto Ferrari blogged a workaround for this in his post on Parent Child relationships. Alberto also opened an item on Microsoft Connect. Let's vote for this feature request and hope it makes it in the RTM version.
The longest nugget so far on a very cool BI concept coming to PowerPivot.
Monday, August 08, 2011
PowerPivot Nuggets - Part 7 - Multiple Relationships
Let's dive a little deeper into relationships today. In PowerPivot V1 we could only create one relationship between two tables. Good news coming to V2. We can now create multiple relationships between tables.
Let's have a look at our factInternetSales table. The table has OrderDateKey, DueDateKey and ShipDateKey. All three link to the DimDate table. As you can see in the screenshot below we can create a relationship for each one of them. However, only 1 of them can be the "active relationship". And that is the key-concept to understand in V2.
When you create a pivot table, PowerPivot will use the active relationship (on OrderDateKey). If you want see the Sales per DueDate or ShipDate you must create a DAX calculation and use the new USERELATIONSHIP function. This function will force PowerPivot to use a "non-active" relationships.
CALCULATE(SUM(FactInternetSales[SalesAmount]),USERELATIONSHIP(FactInternetSales[ShipDateKey], DimTime[TimeKey]))
This support for multiple relationships is a very nice addition to PowerPivot. But, personally I think Analysis Services' concept of roll-playing dimensions is more "user-friendly" than PowerPivot's concept of active relationships and USERELATIONSHIP.
Let's have a look at our factInternetSales table. The table has OrderDateKey, DueDateKey and ShipDateKey. All three link to the DimDate table. As you can see in the screenshot below we can create a relationship for each one of them. However, only 1 of them can be the "active relationship". And that is the key-concept to understand in V2.
When you create a pivot table, PowerPivot will use the active relationship (on OrderDateKey). If you want see the Sales per DueDate or ShipDate you must create a DAX calculation and use the new USERELATIONSHIP function. This function will force PowerPivot to use a "non-active" relationships.
CALCULATE(SUM(FactInternetSales[SalesAmount]),USERELATIONSHIP(FactInternetSales[ShipDateKey], DimTime[TimeKey]))
This support for multiple relationships is a very nice addition to PowerPivot. But, personally I think Analysis Services' concept of roll-playing dimensions is more "user-friendly" than PowerPivot's concept of active relationships and USERELATIONSHIP.
Wednesday, August 03, 2011
PowerPivot Nuggets - Part 6 - Sort By Column
In PowerPivot V1 members in a slicer are sorted in alphabetic order by default. This is fine for product descriptions or customer names. But not for months or weekdays.
The workaround in v1 was to add a number before the weekday as you can see on the right hand slicer in the above screenshot. It works, but it is - let's say' - a little ugly.
In PowerPivot V2 we can specify a "Sort By Column".
And this is the result:
A small but nice enhancement.
The workaround in v1 was to add a number before the weekday as you can see on the right hand slicer in the above screenshot. It works, but it is - let's say' - a little ugly.
In PowerPivot V2 we can specify a "Sort By Column".
And this is the result:
A small but nice enhancement.
Tuesday, August 02, 2011
PowerPivot Nuggets - Part 5 - Mark As Date Table
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.
Monday, August 01, 2011
PowerPivot Nuggets - Part 4 - Perspectives
Perspectives, like drill-through, are another Analysis Services feature coming to PowerPivot.
Perspectives allow you to expose a subset of your model to the end users. Let's say you created a PowerPivot model on Sales, Purchase and Inventory with 15 dimensions, each with about 10 attributes and a total of +50 measures. This model can be a little bit overwhelming for e.g. the Sales Manger.
We can now create a perspectives that contains only the most important attributes and measures related to Sales and expose that to the Sales Manager.
Before you can create perspectives, you must switch to PowerPivot in advanced mode.
Next you choose Perspectives in the Advanced tab, and start creating your perspectives.
Another nice Analysis Services (Enterprise!!) feature coming to PowerPivot.
Subscribe to:
Posts (Atom)