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:
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.
Here are just a few of the strengths that I’ve found promising:
- Analytic Chart and Grid Report Types are supported against PowerPivot data sources
- Drill Down and Cross Drill is supported
- 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.
Here are few of the weaknesses that I hope will be resolved in future versions:
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!