Basics for Understanding PowerPivot

I often met the questions about PowerPivot these days. "What is it?" "Why should we have it?" "Can PowerPivot do this for me?" 

Well, the best way is to make a list of most basic descriptions of PowerPivot and share with those who need it. So here they are.

• What is PowerPivot

PowerPivot is a new data analysis technology in a way that it makes the "impossible" usage of EXCEL (in the past) to be possible now. So business analysts do not always have to wait for a few months to see the end-result of IT implementation. They can instead do self-service and make many of the BI solution implementations on their own (self-service).

• Self-service BI compared to corporate BI

PowerPivot  (together with Excel, and most importantly, SharePoint) enables the self-service BI capabilities of a company. But this does not mean the era of Corporate BI is over. Instead, corporate BI will have an even more important status since the business is now able to specify more clearly on their requirements and is even able to provide a prototype plus data and results that can be used to validate the IT implementations.

• PowerPivot is not a standalone tool

PowerPivot integrates with Excel (2010 since it needs the size extensions enabled from this version of Excel) and SharePoint 2010.

• Data Analysis Expressions (DAX)

This is one of the key extensions of PowerPivot (in addition to the in-memory analysis engine and the compression algorithms). DAX is a collection of formula elements, i.e., functions, operators and constants that can be used to make expressions or do calculations. It is also available to the Tabular Model projects.

• Managed BI collaboration environment

Clearly PowerPivot has enhanced the possibilities of spread marts and too many standalone worksheets across an enterprise. That's why it is critical to establish a managed BI collaboration environment before promoting the self-service BI concept. Here exists lots of process, rule, training, and SharePointing work. With a SharePoint site, the business can clearly do the self-service work while the IT department puts on certain audit-trails and analysis to find out the need for optimizations, further maintenance and even development of Corporate BI solutions.

• Technical specifications

There is no doubt that PowerPivot includes a good in-memory database. Here is the technical specification that one should always remember. "....The maximum file size of a PowerPivot workbook is 2 GB. There are no restrictions on the amount of data users can import into a workbook, but workbooks exceeding the maximum file size can’t be saved. A 2-GB workbook typically corresponds to a 4-GB dataset, considering a 2:1 compression ratio.... Performance tests show that PowerPivot for Excel can load more than 100 million rows and maintain adequate processing performance with 2 GB of memory. However, test results vary depending

on the compressibility of the data. For fastest processing performance, Microsoft recommends multi-core processors and more than 4 GB of RAM...." (this is from the document "Microsoft SQL Server PowerPivot Planning and Deployment").

• Key elements in PowerPivot for SharePoint

The Timer service (automatically refresh the data), the PowerPivot web services and the PowerPivot Management Dashboard.

• The best picture that shows the roles in BI at enterprises

Look at this one  (or use the following URL http://i.technet.microsoft.com/dynimg/IC396763.jpg)

• The real key to success

Having a focused effort to delivery training, technical documentation and support to the "Power users," and business users is the real critical factor to the success of self-service BI. A good practice is to making a good knowledge base for common questions and training materials.

• Understand the performance gain and loss

What PowerPivot provides a a great performance if everything is inside your memory. To initially get the data from somewhere, there is a potential bottleneck on the network bandwidth. In that case, do not blame PowerPivot but try to decentralize the data just like reducing the heavy traffic around the central station of your city.

• Security and access

PowerPivot is definitely bring challenges to those who work with data access and security control . As a "fat-client" layer in an enterprise landscape, it is very important to identify the security needs and utilize SharePoint as a mitigation layer.