PowerPivot Prototyping: Strengths and Weaknesses

In my last post PowerPivot a Cool Way to Prototype, I talked about the importance to prototyping a BI solution as well as consideration for using PowerPivot as a tool for developing a prototype solution. In this post, I’d like to discuss some of the Strengths and Weaknesses of using PowerPivot as a prototyping tool for PeformancePoint dashboards and other Analytic Reporting solutions.

In my past years at Stonebridge, I have been the engagement lead for multiple PerformancePoint Dashboard prototypes. Always the #1 challenge when creating a PPS prototype is the balance between the level of effort spent on activities required to create a solid data platform for the prototype. These activities include analyzing data sources, developing a data mart, developing ETL (Extract, Transform, and Load) packages, and developing an Analysis Services (SSAS) cube. Approximately 80% of prototype development is spent on these activities. The other 20% is spent on developing the User Interface components: KPIs, Scorecards, Charts, Grids, Dashboard Pages, etc.

But Jamin, It’s Just a Prototype…

Some might say Jamin let’s be realistic, “Why do we need to complete these activities for a prototype?” My response is that while PerformancePoint has several nice features for prototyping (fixed values, tabular data sources such as Excel for scorecards, etc.), most of the reports types and many of the features such as drill down require an Analysis Services cube as the data source. Secondly, Analysis Services works best when placed on a data mart that has been designed using a Kimball style “Star Schema”. This means that we often need to restructure data in terms of conformed dimension and facts before we can proceed further. This is where our new friend PowerPivot comes into play!

PowerPivot in a Nut Shell…

PowerPivot allows powerful analysis to be performed against extremely large datasets and is comprised of two components:

PowerPivot for Excel

PowerPivot for SharePoint

The resulting PowerPivot model published to SharePoint can be used as a data source in a manner almost identical to that of Analysis Services. In fact, a special sandboxed cube is published to an Analysis Services instance running in SharePoint Integrated mode. For more information visit the official PowerPivot Website.

PowerPivot Strengths

Here are just a few of the strengths that I’ve found promising:

PerformancePoint can utilize a PowerPivot model published to SharePoint as a multi-dimensional data source much like Analysis Services. See the TechNet article “Create a PowerPivot data connection (PerformancePoint Services)”.

• Analytic Chart and Grid Report Types are supported against PowerPivot data sources
• Drill Down and Cross Drill is supported

Disparate Data sources can easily be easily integrated into a central data model

• Conformed dimensions can be created to commonly slice metrics
• Relationships can be easily managed to allow multiple data sources to be related to conformed dimensions
• Large number of data sources are supported including: Excel, SQL Server, Oracle, Teradata, Analysis services, etc.

More complex calculated measures that are necessary for most dashboards such as Year-To-Date (YTD), Month-To-Date (MTD), etc. can be created using DAX functions.

PowerPivot Weaknesses

Here are few of the weaknesses that I hope will be resolved in future versions:

Lack of support for user defined hierarchies that allow a user to drill seamlessly down a path such as Product Category –> Product Subcategory –> Product. PowerPivot currently supports attribute hierarchies only

Lack of support for actions such as drillthrough, launch URL, and Reporting Services actions

In Summary…

Despite a few weaknesses, PowerPivot is a great tool to reduce the amount of time it takes to build the data platform for a prototype! There is also no doubt that Microsoft will resolve these weaknesses in future releases. Until then, Happy Prototyping!