Problem solve Get help with specific problems with your technologies, process and projects.

Shining the light on PowerPivot for SQL Server 2008 R2

With the promise of providing simple, self-service BI analysis for users, the time has come to put Microsoft’s new PowerPivot technology through its paces.

In past years, one criticism of Microsoft’s business intelligence (BI) offerings has been that while SQL Server Analysis Services (SSAS) was much simpler to use compared to competing products, the company still did not provide a solution to help business users easily pull data together into one place and perform analysis using slice and dice techniques.

While Microsoft Excel could do many things, more advanced BI functionality still required users to buy third-party tools. The folks at Microsoft realized that in order to comply with their new mantra of “BI for the masses,” they needed to include such a tool for business intelligence users. After extensive research and development, the company started introducing a new and exciting product called PowerPivot.

PowerPivot is basically just an add-on for Excel 2010, with the only requirement being Microsoft Office 2010 for Windows. Unfortunately, there is no PowerPivot for Mac because PowerPivot includes some code and functionality from the SSAS engine. Office 2010 is set for a May 2010 release, but you can download the beta version in the meantime if you are itching to test PowerPivot out.

Microsoft PowerPivot allows you to stay within Excel as you develop a BI application. Once you are done, you can give your file to other users within your organization as a simplest form of deployment. If you want to deploy your application to many users -- even those outside of your network -- you will need to deploy it to Microsoft SharePoint 2010. I will describe the SharePoint integration later in this article.

Getting started with PowerPivot for Excel

So let’s look at PowerPivot in detail. You can start building a BI application by bringing data into Excel using the PowerPivot add-on. From there you are able to read data from various data sources such as databases (SQL Server, Access, Oracle, etc.) or data files (text files, Excel, etc.).

PowerPivot also has a new data source, as it can read data from existing SQL Server Reporting Services (SSRS) reports. As you probably know, each report has one or more datasets. After connecting to Reporting Services, PowerPivot allows you to select a dataset from a report. It will then run the report and bring the data into Excel. Microsoft implemented this feature upon realizing that a lot of companies have already invested effort into building reports and queries, so the ability to utilize reports as data sources should help those companies utilize the work that’s already been done.

The screenshot below demonstrates tables being imported from a database into PowerPivot.

Figure 1. Using PowerPivot to import tables from a dataset (click to enlarge)
Using PowerPivot to import tables from a dataset

As you can see, tables can be renamed and the import can be filtered to only bring over the data you need. Regardless of the source, each dataset you bring into PowerPivot ends up on a separate tab. Figure 2 below shows a tab with data imported into PowerPivot, with a calculated column added to the data.

Figure 2. Data imported into PowerPivot (click to enlarge)
Data imported into PowerPivot

Once you bring data into Excel, it becomes disconnected from the data source and therefore static by nature. This allows you to work disconnected from the data sources and easily distribute the finished PowerPivot application, including the data.

The storage format in PowerPivot is extremely fast and efficient; it can store millions of rows in less than a hundred megabytes. It uses the latest database storage technologies in order to quickly scan and process the data. According to the PowerPivot development team, PowerPivot can scan millions of rows -- multiple times -- in less than a second. But if you need to refresh the data, you can still do it from Excel as long as you have access to the data sources.

Once your data is in Excel, you can start building pivot tables, as shown in the Figure 3.

Figure 3. Building pivot tables for BI with PowerPivot (click to enlarge)
Building pivot tables for BI with PowerPivot

As you select fields for each data source, PowerPivot creates visual filters called slicers. These allow you to easily create subsets of the data by “slicing” the source data. When you drill into related data tables, it is necessary for PowerPivot to understand the parent-child relationship between datasets. PowerPivot can actually detect that a relationship might be missing and will even suggest one using a relationship-matching algorithm. The snapshot below shows possible relationships in a database and the relationship editor dialog in PowerPivot.

Figure 4. Managing database relationships with PowerPivot (click to enlarge)
Managing database relationships with PowerPivot

After you create your pivot tables and slicers, the final step is to generate some graphs and modify the report to make it look more professional and presentable. Below is an example of a completed business intelligence application with the data pulled from the sample AdventureWorks database. As you select different criteria in the slicers, the pivot table and graphs will automatically update the underlying numbers.

Figure 5. A completed BI report using PowerPivot (click to enlarge)
A compelted BI report using PowerPivot

As you can see, it is not very difficult to create impressive BI reports with PowerPivot, and you can go even further by extending the application with Excel formulas. Microsoft purposely chose Excel as the front-end application for PowerPivot since most business users are already so familiar with it.

Keep in mind that PowerPivot is suitable for simple BI solutions and ad-hoc analysis with smaller data sizes. If you want to do more complex analysis with cubes, you still need to use SQL Server Analysis Services. In other words, PowerPivot is not here to replace SSAS, but rather to be used as a complementary tool where it makes sense.

SharePoint integration with PowerPivot

Now let’s look at the deployment options. As I mentioned above, finished BI applications can be shared by simply distributing the final Excel file. If you want the application to be available to a wider audience over the Web, however, you need to deploy it to the Enterprise edition of SharePoint 2010 with Excel Services and SSAS.

Once you upload the PowerPivot application to SharePoint, SQL Server Analysis Services will use the data and model to create SSAS cubes. When users access the PowerPivot application in SharePoint, they will actually be browsing the cubes directly in SSAS, without the need to download the entire dataset (which is necessary when dealing only with Excel files). A SharePoint deployment has another advantage – you can schedule automatic refreshes of the data in the PowerPivot application, provided the SharePoint server can access all data sources.

In my opinion, the biggest con with using the PowerPivot enterprise features is the need for SharePoint 2010 Enterprise edition. In the past, Microsoft marketed its BI stack as a combination of SQL Server Analysis Services, Integration Services and Reporting Services. Therefore, I would expect a product like PowerPivot to be integrated with Reporting Services for the Web front-end features and then have the data backend hooked up to SSAS just like with SharePoint.

Since SharePoint is a complex product, many smaller companies will likely shy away from it and instead opt to utilize PowerPivot purely in Excel mode. If you look at the Microsoft BI website, the platform is now marketed as a combination of SQL Server 2008 R2, Office 2010 and SharePoint 2010. Judging from that, it seems SharePoint is here to stay and only time will tell if its inclusion with the BI stack was the right choice by Microsoft.

Editor's note: Find out where PowerPivot ranked in our list of the 10 most important changes to SQL Server 2008 R2.

Roman Rehak is a senior database architect at in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

Dig Deeper on Microsoft SQL Server 2008 R2

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.