This blog is part of a series in which I will share my experiences in the move from PowerPivot in SQL Server 2008 R2 to SQL Server ‘Denali’. As always, your comments are welcome! In this segment, I will explore the upgrade itself, and some new functionality in PowerPivot – the creation of KPIs.
The Upgrade
The upgrade from SQL Server 2008 R2 to PowerPivot ‘Denali’ couldn’t be easier. The upgrade was simply a matter of taking a copy of my Excel file with PowerPivot, and opening it in PowerPivot Denali. When the *.xlsx file is opened in Denali, the following prompt appears:
To upgrade, click ‘Ok’ This was straightforward; there was a little bar at the bottom right hand side of the screen. The only tiny criticism, I’d say, is that the progress of the upgrade wasn’t immediately clear to me, and I wasn’t sure if it had correctly upgraded or not until I saw all of the PowerPivot buttons fully appear in the ribbon. If I could change things at this point, it would be to provide ‘in your face’ feedback that the upgrade was in progress, and successful.
Once the upgrade is completed, PowerPivot fans are in for a real treat! The interface looks crisp and there is new functionality to be explored. Next, we will look at the creation of KPIs, which is very simple.
KPIs in PowerPivot Denali
This section will focus on a very simple creation of a KPI using PowerPivot Denali. The KPI will take the value of Order Margin Percent. The data source is the AdventureWorks Denali Data Warehouse, which can be downloaded from
Codeplex here.
Essentially the KPI takes the Order Margin, and calculates its percentage of the whole Sales Amount. Here is a closer look at the actual measure here:
To create a KPI is very simple in PowerPivot Denali; there are two ways:
a. click on the Measure and select ‘Create KPI’ in the ribbon
b. right-click on the Measure and select ‘Create KPI’ in the pop up menu.
Here, we will create a KPI business rule quite simply says:
If the Percentage is less than 41%, then it is a ‘red’ KPI, meaning that the status is critical: (red)
If the Percentage is equal to or greater than 41% but less than 86%, then the status is warning: (yellow)
If the Percentage is equal to or greater than 86%, then the status is successful: (green)
This is implemented in the graphic below:
Note that the ‘Absolute Value’ is set to 1, not 100; and the percentages are specified in the decimals rather than as percentage values. Hopefully users won’t get confused, since if the percentages are specified rather than the decimal values, then they might wonder why their KPI value is not working.
If we choose the red-yellow-green ‘traffic symbols’ then our report appears as follows. If it is hard to read, please do click on the image to go to my flickr blog.
Creating KPIs in PowerPivot is extremely easy to do, and I achieved some impactful results in just a few steps. It didn’t require any typing so if you are most comfortable with directly interacting with the interface to produce the KPI, then this is the tool for you.
The other side of the coin is that, as readers of the blog will know, I’m not a fan of red-yellow-green since colour blind people have issues in seeing these colours. It is also possible that people with strong shortsighted prescriptions in their glasses can have a ‘rainbow’ like prism effect if they look at an image off-axis. This is known as chromatic aberration, and is a result of a prismatic separation of colours, which appears as a prism of strongly-contrasting colours. As the individual moves their head, the prismatic effect of the colours can change, which can distort the image.
This is the basis of the Duochrome test, which uses chromatic aberration to identify short-sightedness. Most people are familiar with this: here is an example:
X F J S U O
X F J S U O
Here are some generalisations – there will always be specific cases that break the generalities! Generally speaking, very short sighted people will see the red image more clearly, and if the eye is corrected properly, then both lines appear equally sharp. In short sighted people, the axial length can be longer, which means that the light does not focus on the retina; thus short sighted people can be more impacted by focal length of the blue light.
Hence the red-green debate has some basis in the ways in which our eyes work. I understand that PowerPivot KPIs are still in ‘early visibility’ stage to everyone, but I have my fingers crossed that the KPIs will be able to be amended. Here is another version that I could do with the existing functionality. If it is difficult to see, please click on the image to go to my flickr account:
In this example, I have tried to go with the ‘longer length equals higher value’ approach, and not used any colour to distinguish the KPI statuses. Ideally, I would like to make these icons go ‘left to right’ in order to facilitate comparison between the Years or Row Labels. I would also be able to choose red-blue colours to distinguish between statuses properly. Let’s see what happens!
In my next post, I will be covering more new PowerPivot features in Denali. In the meantime, I look forward to your comments.
Nice post. The different types of KPI icons looks great (like OrdermarginPercent), not only standard MOSS indicators 🙂