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!

PowerPivot a Cool Way to Prototype!

Recently I was at a client conducting a Microsoft Business Intelligence Workshop.  Stonebridge has been delivering these workshops for quite some time and we provide an overview of the full Microsoft BI stack from the SQL Server data platform to Self-Service Reporting utilizing PowerPivot, Excel, or Reporting Services to Corporate Performance Management delivered in SharePoint using PerformancePoint Services.  The workshop was going great and we were in our second day and the client asked if they could see some of their data in a PerformancePoint Services  (PPS) dashboard.  At first I admit that I got a bit nervous and clammy, but then an idea struck like a lightning bolt!  Why not use PowerPivot to build a simple model to serve as the data source for a PPS dashboard?  Two or three hours later after working with the DBA and an AS400 developer we had built a working prototype and were ready to impress the VP of Information Technology! 

Prototyping is a crucial step in the development process, in particular a BI prototype helps in the following ways:

• Gives the user something they can see, touch, and feel
• Reveals data quality issues in the underlying data
• Uncovers additional requirements
• Provides analytic insight quickly or lack of to determine value moving forward with a more permanent solution.

In the past, building a PerformancePoint dashboard prototype has been difficult and expensive namely because many of the visualizations require an Analysis Services cube which takes time to develop.  In addition, dashboards typically have a large number of data sources that have to be combined into a central data mart using SSIS or another ETL tool that again takes time.

I have since completed my second PPS dashboard prototype for another client using PowerPivot as a data source and have one thing to say:  “I am nothing short of impressed with PowerPivot as a prototyping tool!”.

Keep an eye for my next post “PowerPivot Prototyping: Strengths and Weaknesses” for more detail.