Using PowerPivot with Excel 2010

In this blog article, we’ll step through using PowerPivot for Excel 2010 for building a rich application in Excel.

Note: following screenshots describe the SQL Server 2008 R2 August Community Technology Preview (CTP) functionality for a feature codenamed Gemini. As recently announced, Gemini will be released under the PowerPivot brand.

After installation, Gemini appears on the Excel 2010 ribbon:

Importing Data

Selecting the ‘Load & Prepare Data’ button launches the PowerPivot client window:

As you would expect from any modern tool, the ‘From Database’ button launches a wizard to step you through getting data from a database. More information about data import process and functionality is available on the PowerPivot blog here. For this article, we’ll bring in following tables:

• Media – all movies our hypothetical company rents out,
• Purchases – all purchases our customers have had in the last few years,
• Date – details per date of purchases such as whether it was a weekend, holiday, etc.,
• Time – time of day of purchases, and
• BoxOffice – table of box office sales for some of the movies in the last few years

During the import process, a snapshot of all these tables is imported into PowerPivot and stored in memory using a highly scalable engine. The table Purchase, for example, contains 100 million rows. Saving the file will not only save any contents on the workbook but also all PowerPivot data. i.e. the PowerPivot data is stored as a blob within the Excel workbook to simplify transport as well as remove the management overhead of managing separate database services.

After the import, each table shows up as a tab in the PowerPivot client window. In this case, our database had relationships already defined between these tables and they were automatically recognized up by the PowerPivot Data Import Wizard. Users can, of course, define their own relationships as well.

The PowerPivot client window allows you to operate with this large data set very quickly: common operations such as sorting and filtering typically complete in under a second on common hardware available today (< $1000).

PowerPivot also allows you to extend imported data using calculations which are maintained through data refresh. Various types of calculations and mechanisms for creating them are described on the PowerPivot blog here.

So far, we’ve imported data only from a single source. You can, of course, combine data from a variety of data sources – databases, text files, ATOM data feeds, as well as just Copying/Pasting data directly – and create calculations and/or relationships as if they were a table imported from a single database.

Visualizing Data

Once you have the data, PowerPivot enhances the Excel experience by providing quick templates for frequently used layouts:

Selecting ‘Four Charts’, for example, creates a worksheet with four Pivot Charts and one worksheet each for the PivotTable source of the data, speeding up the view creation process:

PowerPivot overrides the default PivotTable Field List by providing its own Task Pane:

For those users familiar with OLAP Pivot Tables, there are several interesting features.

Instead of seeing dimensions and measures within measure groups, PowerPivot shows a tabular view of the data – just tables and columns. In addition, based on whether a column is dropped in the Values area or on Axis or Legend, the PowerPivot Task pane creates a measure or uses the field as an attribute. For example, if Distributor is dropped  on the Values area, a measure (Count since this is a string column) is created automatically. If, however, Distributor is dropped on AxisFields, it is used to group the data. This highlights a few points:

1. PowerPivot leverages the richness of SQL Services Analysis Services mode internally without imposing dimensional modeling concepts on users, thereby enabling the functionality of OLAP PivotTables with a friendlier, tabular, model of the data, and without requiring IT to create and deploy Analysis Services cubes.
2. All PivotTable and PivotChart queries are answered by the PowerPivot engine running on the desktop using data that’s stored within the Excel workbook. A connection to a server running SQL Server Analysis Services is not necessary.
3. PowerPivot complements the richness of Excel’s visualization facilities – including tables and charts – with a rich, scalable, embeddable, data engine.

Within a matter of seconds a quick view is created…

… which can be easily made more appealing using standard Excel formatting features:

As mentioned earlier, saving the workbook at this point will save both the view above as well as the PowerPivot data and any enhancements such as calculations.

PowerPivot also provides helpers for adding slicers to the workbook:

The ‘Slicers Vertical’ and ‘Slicers Horizontal’ areas create zones on the left and top of the Pivot controls that help layout, align and resize slicers easily:

Summary

The combination of usability of Slicers and the performance of PowerPivot engine provides a very interactive set of views that one could easily mistake for a rich custom application built by IT after weeks of effort. This – Self Service Business Intelligence – is the key value that Gemini provides end users.

This completes a quick run through of PivotTable for Excel 2010. In the next article, we’ll dig in to PowerPivot for SharePoint to see how it enhances the SharePoint collaboration experience.