KPI’s in PowerPivot Denali (SQL Denali CTP3)

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):


5 thoughts on “KPI’s in PowerPivot Denali (SQL Denali CTP3)

  1. 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.

    • 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.

  2. 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.

  3. Pingback: Vorschau Excel PowerPivot V2 (SQL Server “Denali” CTP3) « JJ's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s