PowerPivot: A Cool Way to Prototype
Jamin Mace, Senior BI Architect
Wednesday, January 19, 2011
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.
We were in our second day and the workshop was going great when the client asked if they could see some of their data in a PerformancePoint Services (PPS) dashboard. I admit that at first 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, and 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!”
NOTE: This post is also available on Jamin's BI blog, www.jaminmace.com.