PowerPivot models

A PowerPivot model consists of one or more data sources and data relationships that form the basis of the PowerPivot application. Once constructed, the PowerPivot model can be used within Excel using Pivot Tables or PivotCharts to analyze the data. When you install the PowerPivot for Excel Add-In, you see a new tab or “ribbon” in Excel 2010 for PowerPivot. Within this ribbon, you can launch the PowerPivot Window which is the main user interface for developing PowerPivot models.

The data sources can range from relational databases to multi-dimensional cubes, or if necessary, from text files or data embedded in reports. When using SQL Server 2008 R2, the data is dramatically compressed using a column compression technology called “VertiPaq” providing fast access and sorting.

Using the Design ribbon within the PowerPivot Window, we can define relationships between the data tables, if necessary.

Once a PowerPivot model is defined, we can then create a PivotTable or PivotChart in an Excel 2010 worksheet as normal, using the model as source. A nice innovation in Excel 2010 is the Slicer. This gives an interactive push-button type filter to your PivotTable and PivotChart which is very intuitive to use. Think of it as an interactive legend that can be used for filtering.

There are some useful samples out there that you can use to learn the technology. Here is a good example:  http://www.microsoft.com/downloads/details.aspx?familyid=1AE63BFB-C303-44E3-AE44-7413D499495D&displaylang=en

More on PowerPivot to come as I continue testing.



Related Courses

Developing and Implementing a SQL Server 2008 Database (M6232)

Implementing and Maintaining SQL Server 2008 Analysis Services and Business Intelligence Solutions (M6234)

Maintaining, Troubleshooting, and Developing Solutions with Microsoft SQL Server 2008 Reporting Services (M6236)

SQL Server 2008 for Administration (M6231, M6232)

Writing Queries Using Microsoft SQL Server 2008 Transact-SQL (M2778)

In this article

Join the Conversation