What’s better than storing 100,000 rows of data in Excel? How about a million? 10 million? Heck 100 million? (Insert Dr. Evil reference here). With the introduction of PowerPivot, anyone who has Excel 2010 can now take advantage of some very nice rapid BI development. PowerPivot is an Excel Add In that is available for download from http://www.powerpivot.com/download.aspx.

However, PowerPivot is more than just an Add In, it truly a self-service Business Intelligence tool that is enhanced by SharePoint 2010. Between Excel and SharePoint end users now have access to perform their own rapid development of BI solutions without the learning curve of Analysis Services/MDX using and environment they are already comfortable with.

Data sources of all shapes and sizes can be consumed and users are not limited to a particular source. Users can consume data Fact and Dimension data from one or more Analysis Services Cubes and in the same solution connect to a SSRS2008R2 Report and even a separate MySQL or Oracle Database. Connectivity is only virtually unlimited with access to the Microsoft suite and any other OLE DB/ODBC accessible sources.

Demo Time

For today we will keep things limited to the Excel side which is anyone with Excel 2010 can start to play with. Once PowerPivot it installed click on the PowerPivot tab and then click on the PowerPivot Window.

Once Power Pivot loads, start by selecting a source. In this example we will use the native “From Database” and choose “From SQL Server”. Then set the connection information as shown in the example.

The next step is to choose whether you will pull directly from tables or to write your own query. If you forgo selecting from tables you will have to manually set relationships (or let PowerPivot take its best guess) later on. My choice is to let the database set the design by default to make things consistent and follow the performance design of the underlying database.

From this point we can now start selecting the tables / views that we want to include. One of the nice things about this is that if you have a well-designed structure that uses foreign key relationship constraints you can select just a few core tables. Then by clicking on the “Select Related Tables” the PowerPivot wizard will find any that are related by the database constraints.

By clicking on “Finish” PowerPivot will begin the process of importing data. Depending on row volume this could take a few seconds on up to several minutes depending upon size. For very large, multi-million row tables it could take a bit of time. In any case you will see a nice progress meter for each table.

Once the data is imported you can start to browse the newly populated PowerPivot workbook. This workbook looks very similar to the standard Excel workbook, however, it is really a read-only display of the PowerPivot data that is used for some quick visual data profiling.

Through this view you can really begin to see how PowerPivot shines. If you loaded even multi-million row tables you can quickly see how fast and powerful it can be just by some quick sorts and filters in the interface.

From here the real fun begins. By clicking on the “PivotTable” we can start the process of consuming this data into an interactive Excel report. Select from the options listed.

This will take you to the blank PivotTable within the Excel worksheet with the PowerPivot Field List on the right.

From this point you can begin dragging and dropping fields into the PivotTable and additionally add in Filters that will appear above or beside the PivotTable.

From this point you can create almost limitless forms of analysis. You can include additional charts and other diagrams as well.

Taking things a step further this same technology can be incorporated into SharePoint 2010 to allow for users to Share and collaborate on simple to moderately complex Dashboards that all users can access.

Ground Rules

This all sounds great. So what are the caveats?

Run on 64bit machine. Microsoft recommends is as do I. 32 bit will work but your time and sanity will suffer.

The more memory the better. Sure the data is compressed but with millions of rows of potential data you need to think like a server.

This is not a replacement for Analysis Services/Reporting Services. Though there is considerable overlap, PowerPivot is designed to enhance the functions already performed in Excel. More complex analysis, scalability and security are not possible with PowerPivot.

Data is Disconnected. As data in the source changes this is not readily reflected. In Excel data must be refreshed manually. SharePoint allows this to be scheduled. In both cases this is a complete reload of the underlying PowerPivot Data.

Office 2010. Need I say more? Well if I do then just remember, PowerPivot is only available with Excel and SharePoint 2010.

Last Words

The PowerPivot Add In for Excel 2010 offers the end user a considerable amount of power on a desktop machine to do some fairly detailed Business Intelligence without the need for a Server running Analysis Services, Reporting Services or even a box sitting under someone’s desk running access Access. PowerPivot however lack much of the scale, power and security of these other tools. If you are looking for a quick, cheap and not-so-dirty way to work with data PowerPivot is the way to go.