Tuesday, January 11, 2011

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.

No comments: