I’d like to, first, preface this post by letting you know that this is not a technical how-to article so much as it is a functional why-to. I stumbled into a great example of how the technologies being developed around the newer trends towards self-service business intelligence actually work, and work very well.
I recently had the opportunity to meet with a potential client in the healthcare sector. The topic of conversation was Self-Service BI and our take on this new twist of information worker empowerment. This organization was interested in an end-to-end BI solution. Through previous meetings and a fact finding engagement with Aptera they were already aware of the importance of a correctly architected BI infrastructure. They were still, however, on the fence as to the best, most efficient way to surface the intelligence information to their information workers, analysts, and executives. That was the purpose for this particular meeting. We were to cover the all of the great ways Microsoft offers to actually get the info to the people that need it. SharePoint, PerformancePoint, Excel, Reporting Services… these were all topics which were discussed and they all went over very very well, but it was PowerPivot and its incredible abilities to do what it does as easily as it does it that dominated the discussion. Frankly, it’s a good thing I waited until we had covered the other BI offerings first before moving into the PowerPivot conversation. I’m almost afraid that, had we started with it, we would have been continually pulled back to it by the meeting attendees. Such was the excitement level around this product, and, frankly, I’d be happy to add myself as one of the excited fans of PowerPivot. Seriously… this thing rocks!
PowerPivot is an Excel add-in that allows analysts to capitalize on their own knowledge of the data, as well as their existing skillset with Excel, to analyze data in much the same way they would with an Analysis Services cube. It is certainly not a replacement for Analysis Services, or for any of the other BI reporting mechanisms that Microsoft offers, but it is a phenomenal way to augment these other technologies while allowing a more “self-service” approach to reporting, basically helping to get IT out of the report writing business.
For this client, it was important for them to see the technologies being used with their own data in order fully understand how the tools would or could be used in their organization. Typically, this can add a significant amount of extra work. Building out the mechanisms to support this very specific data set is very much akin to a full-on proof of concept project. Combine this with the requirement that it all be ready to present in 3 days’ time, I was very leery about committing to having it done, especially with the large workload from other clients and projects going on concurrently.
But this is where PowerPivot really comes into its own and shines. This is one of the major reasons why augmenting an overall BI strategy with PowerPivot can add so much power. I’ll cut right to the chase. After the potential client sent me their organization’s data, not only was I able to create a very powerful reporting mechanism that allowed the user to slice, dice, summarize, and interact with their own data, I did it in an hour… less than an hour. Frankly, the bulk of it was ready to use in about 20 minutes. This included tying data sources together with new relationships and creating a few simple but powerful DAX expressions. The remaining 40-ish minutes were really spent playing with the data, creating nice-looking graphs and charts to act as a spring board during the presentation.
Now, I’ve been working with PowerPivot since it was still code-named Gemini, but this one particular episode really brought back home to me why this is such a game changer.