For the past year or more, I’ve been calling PowerPivot the BI tool for the masses. I don’t know who I heard say it first. It very well could have been someone at one of the SQL Saturday events I’ve attended. However, the point is that I recently read an article that talked about how a new product at the time, Web Matrix would revolutionize the way the average person could build their own web site. Microsoft released Web Matrix as a tool that would enable the average businessman to create their own web site. This article was several years old, but I believe that we can say that this prediction has not happened, at least not yet.
The author went on to relate how similar products were introduced to similar fanfare and ultimately failed as well. He started with COBOL, a business oriented computer language built to read like English. Salespeople promoted COBOL as a way for the average businessperson to write their own reports if not their own applications. Decades after making this promise, we now know that the potential was never realized and COBOL has fallen to become a minor product in today’s world of new application development.
COBOL was not the only product to fail. Countless report writers have come and some have gone like Crystal Reports and SQL Server Reporting Services. Each time we would again hear the promise that this tool was the one that would now allow end-users to create their own self-service reports. I think it is safe to say that these tools never became end user tools. However, they did become very powerful and useful tools in the hands of the professional software developers who could now develop reports faster and build more complex reports than they could before these tools.
Another good example is the long list of 4th generational languages that were suppose to make software development so easy that anyone could do it. These tools also found their way into the hands of the professional developers to make them more efficient, but failed to turn the masses into program developers. I’ve always said, ‘Real Programmers Do Use Wizards, They Just Don’t Rely On Them.’
Will PowerPivot and Power View be any different? Perhaps not. Recently I have been emphasizing in my PowerPivot presentations at SQL Saturday events that PowerPivot does greatly enhance the ability of Power Users in your organization to analyze data in ways they could not easily do before. However, the task of building the basic pivot table model, the work that is done in the PowerPivot window to connect to one or more data sources, define custom columns, define hierarchies, create new measure calculations, etc. will most likely remain in the hands of the DBAs or database analysts. The idea of understanding table relationships, DAX expressions, and other BI topics will probably not filter down to the average end user.
With a little work and some training, it may be possible to teach some of the more technically adept department members how to manipulate dimension attributes on a pivot table with different measures, add a bit of formatting to the page to make it look pleasing and save the result to a web page for collaborative sharing or to PowerPoint for a presentation to management. However, I really don’t see even power users building the connections to different databases, linking tables, building new fields, and performing other tasks to make the data available for reporting.
Does this detract from the statement that PowerPivot is BI for the Masses? From my point of view, it does not. Compared to building cubes in SSAS (SQL Server Analysis Services) and reporting on them with SSRS (SQL Server Report Services), PowerPivot tables in Excel are easy to use and easy to format into custom reports. However, I’m not an end user and you probably are not either. Think of PowerPivot as a new tool in your toolbox that lets you respond to user requests. If the tool helps you to answer their questions faster, it is a winning tool. If you can train the user into how to make simple changes to the pivot table in a tool they already know, Excel, they might be able to generate some of their own reports by themselves. If nothing else, you have at least opened their eyes to what is possible even if you have to do it for them.
However, they will still need you for those reports that don’t fit the current pivot table model you have given them. They still need you to add that new table from a new application into the model so they can ask additional questions with the new measures and/or dimensions. They may even still need you to build a more robust model in SSAS and display it in SSRS because they need features and capabilities not found in PowerPivot.
Still, if PowerPivot makes more people aware of how to use business intelligence concepts to support their day-to-day business needs, it still moves us all forward to be just a bit more efficient and a bit more intelligent in our decisions and that is what BI is about.
C’ya next time.