Our Blog

where we write about the things we love



Excel PowerPivot grows up with Microsoft SQL Server 2012

Since its inception, I have been impressed with Excel PowerPivot. It was released early in 2010 to provide greater analytical functions to business users. I have recently been using the beta version of PowerPivot that will be released along with SQL Server 2012 and I am impressed at how it has matured as an end user tool.

PowerPivot is a free Excel 2010 add-in that gives you the ability to create Business Intelligence solutions in the familiar spreadsheet environment. It is a powerful tool for business analysts and accountants who know their data well and are comfortable with Microsoft Excel, and whom do not have a background in IT development. PowerPivot will quickly link data together from disparate sources and apply business logic to create Business Intelligence solutions. All data is stored in memory so PowerPivot is incredibly fast and performs impressively well, handling multi-million row tables with ease.

To date, I have been reluctant to use it for anything more than prototyping. PowerPivot has been called by some as ‘spreadsheet hell on steroids’ because although it is really powerful, the models can get too big to manage and can potentially spread through an organisation without any form of control. You can load the PowerPivot sheets into Microsoft SharePoint Enterprise, but with a big PowerPivot model that process is slow and manageability is limited. So although business users love the PowerPivot capability, IT professionals get nervous about how to manage its use within their organisation.

With the up-coming release of Microsoft SQL Server 2012 this position has changed. The PowerPivot engine has been brought into SQL Server itself. This provides manageability and scalability without compromising on the ease and simplicity of use. With the new version of PowerPivot you can simply create a new SQL Server Analysis Services database by restoring it from a PowerPivot model. You simply select the PowerPivot model and it creates the database. You can then create roles to secure the database as well as manage the data refreshes and monitor performance and usage - the best of both worlds.

In my opinion this function is a big deal. It allows for better separation of duties in the development of Business Intelligence solutions. The business experts who understand the business goals and processes – and know the data – can participate in the creation of data models, building and enhancing the business logic themselves prior to passing the model to the IT professionals who can ensure it is secure and can perform in a managed environment.

PowerPivot does have some limitations in function (compared to traditional Analysis Services databases), but it is easier to learn and faster to use, improving the productivity of those who use it.

We always recommend that our Business Intelligence clients take a long-term view creating a Business Intelligence Roadmap aligned to their business goals and then work incrementally to add functionality. Consistent with this approach I suggest that clients look seriously at how they can incorporate PowerPivot into their technology roadmap and plan for adjustments to development roles and processes to ensure a successful introduction of the tool.

In summary, the enhancements to PowerPivot mean that it has evolved from a great end user tool for analysis and prototyping to becoming something that can be fully integrated into the development lifecycle. The solution also provides better linkage between the business and technical teams, which has to be a positive result, too.

If you would like to find out more about any of these areas, or have specific questions, please leave a comment or contact us.

Posted by: Nick Draper, Enterprise Applications Consultant | 24 January 2012

Tags: Excel PowerPivot

Top Rated Posts

Blog archive

Stay up to date with all insights from the Intergen blog