Exploiting PowerPivot & PerformancePoint Services Synergies

UPDATE:
Below is the final PPS prototype we built for the customer on top of PowerPivot.

If your like me you often read about cool and neat technical possibilities but when it comes to business justification well that is another matter. One such example of this for me was reading about the ability to have PerformancePoint Services (PPS) consume a PowerPivot cube just like a traditional SSAS UDM cube. A great whitepaper on integrating PPS and PowerPivot can be found at http://technet.microsoft.com/en-us/library/ff700212.aspx . While the idea is cool enough what sort of a situation would require it? Well, not long after that thought occured to me the situation presented itself...

The Situation
One of our customers has an off the shelf BI package that leverages SSAS UDM cubes. They are quite happy with the data warehouse and cubes provided. Now they are interested in seeing what the front-end of the Microsoft BI stack can offer. They also mentioned that dashboards were important to them. So, another POC opportunity presented itself to demonstrate the power and this time around the interoperability of the front-end stack. The customer's cube(s) had very few calculated measures and we wanted a way to create additional calculations on top of the SSAS UDM cube without altering the cube itself. We also didnt want to get into the business of copying and/or replicating the SSAS UDM cube, we simply wanted to create a custom set of calculations and then persist them somewhere else. In addition, we wanted to show the capabilities of PowerPivot and also demonstrate the rich interactivity that PPS offers including the Decomp Tree and Drill Across features.

The Solution
You can probably guess the solution. We built a PowerPivot model that sourced it's data from an underlying SSAS UDM. Next we leveraged DAX to create additional time intelligence calculations such as YTD, LY YTD, and YTD Chg % for a handful of base measures. Another nice side effect of doing this is we get to leverage the in-memory (IMBI) storage mode of published PowerPivot workbooks as opposed to the MOLAP/ROLAP/HOLAP storage of traditional SSAS UDM cubes. This is partially where the upcoming Denali BISM architecture will add value as well.











Finally we finished out the PowerPivot artifact with a consuming dashboard inside of the Excel workbook to demonstrate the end-to-end PowerPivot story.



With the PowerPivot work done we now turned our attention to the PPS side. We wanted to demonstrate that with PPS the business can extend the value of PowerPivot by allowing PPS users to reuse someone else's investments into PowerPivot model and DAX. This provides the business the ability to take a base PP model and view its underlying model from a different perspective while still leaving the base PowerPivot workbok in tact for its own usage. For example, in the PowerPivot dashboard above we did not create a chart that showed sum of quantity by just month to reflect total monthly seasonality across all years. In addition, while the PPS Show Details functionality does not work against PowerPivot models the Decomp Tree & Drill Across features do!

Thanks for Reading,
Derek