Some Notes on MS PowerPivots

Following up from here; http://opinionsonoffer.blogspot.com/2012/04/business-intelligence-using-ms.html

Have been meaning to get this typed out for the longest time now; and the longest time it has been :) So here it is; as promised the following are some of the notes I took down from a session on power pivots, please feel free to point out any misconceptions should you see them.

• Powerpivots look to offload the work of a Corporate Business Intelligence team and bring it to the end users. This is basically a plugin to the existing MS Excel suite.
• This is achieved to some extent by Excel and its pivoting functionality; but if you were to take around 4000 - 5000 records and pivot it generally it takes upward of 10 secs to process.
• Powerpivots look to ease this down and make it much faster.
• To run powerpivots you require Vertipaq processors which are usually installed and run at a organizational level. Thereby there is no surefire way to bring this technology to home users as yet.
• Powerpivots allow users to pivot and manipulate data volumes in the millions within a second; (in the demonstration We used 1.3 million records). It seems it achieves this by using column based compression, said to reduce the original filesize by almost 40%. (Similar technology that is used in SQL Server 2012 Denali - ColumnStore Indexes). The file size for the 1.3 million records was 18 mb.
• You can further slice and dice this data as necessary.
• First time load however takes some time, with subsequent loads being upto speed.
• Setting up the data is through a wizard functionality; the data source could be; Access, Another PowerPivot, SQL Server etc. Relationships would be autodetected and mapped accordingly.
• These powerpivots can be pushed to a sharepoint view as well, however you would require silver light to view the resulting screens. They are however very intuitive and easy to use. They can moreover be cached for individual users in order to increase performance.
• Using these you can slice and dice the data and even chart it in terms of displaying.
• Security is maintained either through Sharepoint in terms of a published powerpivot or powerpivots in terms of an excel sheet.

So in summary in terms of bringing this to a home user there is still some ways to go; and in terms of even the business user; you will still need to have a knowledge of the tables that are needed and the datasources. However it has brought us a step closer to the elusive business intelligence at your finger tips paradigm.