Month: December 2013

Using PowerPivot in place of traditional BI

My primary role in my professional life is an architect of Data Warehouse and Business Intelligence solutions.  I get to do this for a variety of customers in different industries using a number of different technologies.  I would be lying if I told you this was not fun.  I really do enjoy what I do.  I’ve got a team of 3 people doing this work with me for our clients, and each of them seem to really enjoy what they do as well.
A few weeks ago, I was able to hear Rob Collie, an ex-Microsoft guy speak on the latest addition to the Microsoft BI solution stack called PowerPivot.  Rob developed early versions of PowerPivot and was instrumental in moving BI into the Office solutions.  He is a great writer and speaker.  Check out his top PowerPivot blog site when you have a minute.
Our consulting team at LBMC Technologies has been using PowerPivot for some time now internally.  We often will perform data analysis, business requirements research, and new system prototyping using PowerPivot.  PowerPivot is being promoted as an ad-hoc self-service reporting solution, all within the comfortable and familiar interface of Microsoft Office Excel (2010 or 2013).  We have seen how financial and operational analysts who traditionally worked in the flexible but cumbersome environments of Access and Excel can now build even MORE complex solutions using PowerPivot and Excel reporting capabilities.  It is really fun to watch this grow in adoption.  I really believe people’s lives will be better.
However, we’ve seen in our consulting practice the modeling tool within PowerPivot is much easier to develop with than the traditional SQL Server Analysis Services developer tool.  The fluidity of the environment allowing you to make incremental changes and see the result of these changes in charts, tables, etc. creates huge boosts in efficiency.  This is NOT just an end-user tool.  A couple of years ago, Rob wrote an article on replacing a traditional Microsoft BI project he had previously done using PowerPivot instead.  The results are incredible.  In one instance, he said a process that took about a week in the traditional technology (due to the higher cost of incremental development) took more like an hour in PowerPivot!  This is crazy.
Here is the rest of the article for those interested in reading about it:
My conclusion
For those who may be currently building and managing solutions using Access and Excel, copying lots of data into big spreadsheets, creating many linked formulas, and doing it all over again each month should really take a look at PowerPivot.  It will fundamentally change how you do your job.  For those BI/IT folks who build solutions in SQL Analysis Services, check out PowerPivot (and it’s Enterprise-version, the Tabular Model) and see what kind of efficiencies you will gain.