[BI]an Smith
Things related to business intelligence
KPI’s in PowerPivot Denali (SQL Denali CTP3)
Posted by on July 14, 2011
I want my report not only to display the right information for me and my team but I also want it to light up like a christmas tree.. yeah baby!!!
In PowerPivot Denali it is possible to create your own KPI’s. Using the Adventureworks database (which you can download here) I made a report with two measures on it;
- Sales amount
- Sales amount previous year
Sales amount is a simple SUM on the fact table. Sales amount previous year is a measure I made, the formula is;
=CALCULATE(SUM(FactInternetSales[SalesAmount]), PARALLELPERIOD(DimDate[FullDateAlternateKey], -1, YEAR))
Easy as pie, isnt it? Next thing I did was create a KPI from within Excel:
Note that both of the measures used by this KPI are explicitly known as measures in PowerPivot:
And the result is… christmas lights!!!
Ok not very intimidating but imagine lots of lights and actual meaningfull information 
Pick your flavour
Your information can be analysed using the PowerPivot tabular interface (one on the left) or the Multi-dimensional interface. I personally prefer the multi-dimensional interface (one on the right):
Advertisement
Interesting approach just for power users… I still prefer all this work of creating KPIS’s made centraly by IT, and then business users use as self-service BI.
Just my 2 cents.
Pedro
Using a tool ike PowerPivot you enable business users to “sandbox design” their information needs in a familiar tool like Excel. This can then ben given to the IT department whom will facilitate further.
More over, the technical skill requirements for the IT department to master are much lower.
I understand you. I just prefer excel with OLAP behind to enable a real self service BI. All the work could be reused and data is centralized.
Pedro
MVP SQL BI
That is certainly the scenario I would aim for with many clients, however, reality often dictates more pragmatic approaches.
Pingback: Vorschau Excel PowerPivot V2 (SQL Server “Denali” CTP3) « JJ's Blog