Published: 21 May 2011
Microsoft PowerPivot for Excel 2010 is a data analysis tool that lets you easily import data from SQL Server and other sources into the Excel environment. Because of PowerPivot’s efficient compression algorithms and in-memory analysis, you can import and manage large quantities of data.
In addition, PowerPivot uses the VertiPaq engine, a column-based data storage technology from SQL Server Analysis Services that puts the power of multicore processors and gigabytes of memory at your fingertips. As a result, you can process millions of rows of data in the time it used to take to process thousands. And because you’re working within Excel, you can leverage a familiar, user-friendly collection of tools.
This is good news for users who want to incorporate complex relational data from SQL Server into analytical calculations within Excel workbooks, which can be integrated into a SharePoint workflow for collaborations efforts. To use PowerPivot, you must be running Microsoft Office 2010 with the PowerPivot add-in installed; for details and download links, go to the Microsoft PowerPivot website. You’ll also need access to an instance of SQL Server.
Putting the data to work: Creating a PivotChart
After you've imported your SQL Server data into Microsoft PowerPivot for Excel, you might wonder what to do with all that data. Fortunately, it’s relatively easy to use PowerPivot to create a table or chart in an Excel worksheet.
For this article, I installed a local instance of the trial version of SQL Server 2008 R2, along with the AdventureWorks2008R2 sample database, which Microsoft makes available for testing and evaluation purposes. I also tested PowerPivot against the AdventureWorks2008 database on a local instance of the pre-R2 SQL Server 2008 software.
When you install the Microsoft PowerPivot add-in, a PowerPivot ribbon (i.e., tab) is added to the Excel workbook window, as shown in Figure 1.
Figure 1. PowerPivot ribbon in Microsoft Excel
The ribbon lets you access the PowerPivot window (see Figure 2), where you import and manage data from SQL Server and other sources.
Figure 2. Using the Table Import Wizard to connect to a SQL Server database
Retrieving data from a SQL Server database
Connect to a database and import SQL Server data by using the Table Import Wizard. To launch the wizard, open the PowerPivot window, click From Database on the menu and then From SQL Server. The first screen of the Table Import Wizard -- Connect to a Microsoft SQL Server Database -- appears, as shown in Figure 3.
Figure 3. Determining how to import data in the Table Import Wizard
When you connect to the database, select the SQL Server instance from the drop-down list, choose the type of authentication and select the database. After you’ve supplied the necessary parameters, test the connection.
Selecting tables to import SQL Server data
You have two choices for importing SQL Server data: Select the tables and views you want to import or write an ad hoc query. Figure 4 shows the second screen of the Table Import Wizard. (By default, the wizard has you selecting from the list of tables and views.) For now, I’ll stick with the first option; later I’ll look at how to define a query.
Figure 4. Selecting tables and views in the Table Import Wizard
The Table Import Wizard, as shown in Figure 5, includes a list of the tables and views in the AdventureWorks2008R2 database. To import data from any of these tables or views, select the checkbox next to the object name. I’ve selected the HumanResources.Employee and the Person.Person tables.
Figure 5. Creating a filter in the Table Import Wizard
I could, at this point, click Finish to complete the wizard and import the complete data sets from both tables into Microsoft PowerPivot. However, I want to limit the data so I’m not importing any more than necessary. The way to do this is to create a filter by highlighting the table or view and then clicking the Preview & Filter button. This launches the Preview Selected Table screen, shown in Figure 6.
Figure 6. Creating a row filter in the Table Import Wizard
As the screenshot indicates, I’ve opened the Preview Selected Table screen for the Employee table. I can now go through the column headers and uncheck any columns I don’t want to include. For this article, I’ve selected only the BusinessEntityID and JobTitle columns. Click OK to return to the list of tables and views.
Next, I create a filter on the Person table. I select only the BusinessEntityID, FirstName and LastName columns. However, for this table, I also want to limit the rows to only those whose value in the PersonType column is “SP,” for salesperson. To do this, I click the down arrow at the top of the PersonType column. This opens a small filter window, shown in Figure 7. I then clear all the check boxes except for the SP value and click OK.
For more on Microsoft PowerPivot
Find out what’s new with Microsoft PowerPivot
Click OK to close the Preview Select Table screen, and then Finish on the Select Tables and Views screen. This launches the final screen of the wizard, which provides a status on the import. If the import is successful, you’ll see a green checkmark next to each table name (shown in Figure 8). Click Close to complete the data import process.
Importing data is usually a straightforward process. The only time I ran into a problem was when the source column exceeded the width permitted by PowerPivot. For example, when I tried to import the entire Person table, I received an error message stating that the AdditionalContactInfo column exceeded Excel’s maximum allowable size. However, once I created my filter, which filtered out that column and others, I had no problems importing the table’s data.
Fetching SQL Server data: the query method
To import data by using a query, launch the Table Import wizard again, select the query option on the Choose How to Import the Data screen and click Next. The Specify a SQL Query screen appears, as shown in Figure 9.
First, enter a name for the query, and then specify a Transact-SQL statement. For this article, I created the following query to retrieve data from the Sales.SalesOrderHeader table:
IS NOT NULL
After you define the query, validate it and then click Finish. A table named Sales is added to the PowerPivot window.
After you’ve imported your data, you can take other steps, such as configuring your relationships or creating calculated columns. You’re ready to create charts and tables, including PivotCharts and PivotTables. Be sure to save your data, however. All the data in the PowerPivot window – along with any charts or tables you create – are saved as a single Excel workbook (.xlsx) file. You can then go back and modify the file as necessary to incorporate any changes you make.
And the best part is, whether you’re creating a calculation from scratch or modifying an existing one, you don’t have to be a SQL Server or business intelligence expert. All you need to do is take advantage of the rich Excel environment and the power and efficiency of PowerPivot.
About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Find more information at http://rhsheldon.com.