PowerPivot for Excel and SharePoint
- 4/14/2011
Getting Started
PowerPivot for Excel 2010 is a managed Excel add-in that is part of the SQL Server 2008 R2 release. Microsoft offers it as a free download, available at http://www.powerpivot.com/download.aspx .
Installing PowerPivot for Excel
Starting with the 2010 release, Excel comes in two versions: a 32-bit version and a new 64-bit version. PowerPivot also comes in 32-bit and 64-bit versions—and your versions should match. So if you are using the 32-bit version of Excel, you should install the 32-bit version of PowerPivot, and if you are using the 64-bit version of Excel, install the 64-bit version of PowerPivot.
To get started with PowerPivot and the examples in this chapter, perform the steps in the following procedure.
To get started with PowerPivot
Install Excel 2010 from the Office suite along with the Office Shared Features. The Office Shared Features include Visual Studio Tools for Office (VSTO) 4.0, which is a prerequisite for using PowerPivot for Excel.
Download and install PowerPivot for Excel, available at http://go.microsoft.com/fwlink/?LinkId=207852 .
Install SQL Server 2008 R2. You can use an existing SQL Server 2008 or 2008 R2 instance.
For the examples in this chapter, you will use the relational database from the “Microsoft Contoso BI Demo Dataset Retail Industry.” You can download the Contoso sample database (ContosoBIDemoBAK.exe) from http://go.microsoft.com/fwlink/?LinkId=214637 . After downloading the file, run it to extract the relational backup file.
Open SQL Server Management Studio and connect to your SQL Server 2008 instance, and then restore the Contoso relational backup database that you downloaded and extracted in the preceding step.
After you have installed both Microsoft Excel 2010 and PowerPivot for Excel, you’ll see a new PowerPivot tab on the Excel ribbon, as shown in the following illustration.
)
On the PowerPivot tab, click PowerPivot Window. A new window opens with the PowerPivot ribbon, as shown in the following illustration. Using the PowerPivot window, you can import data from various sources, manage relationships, filter, and create calculated columns with DAX.
)
Installing PowerPivot for SharePoint
PowerPivot for SharePoint must be installed by an IT professional because the installation requires administrative access to servers. Installing PowerPivot for SharePoint is potentially a very complex task, depending on the configuration of your SharePoint farm. Fortunately, some good white papers have been published, which describe the setup process in detail. These include:
“PowerPivot for SharePoint—Single Server Installation” (http://msdn.microsoft.com/en-us/library/ff963565.aspx)
“PowerPivot for SharePoint—Existing Farm Installation” (http://msdn.microsoft.com/en-us/library/gg144594.aspx)
Creating a PowerPivot Workbook
When you have successfully installed both Microsoft Excel 2010 and PowerPivot for Excel and have the Contoso relational database ready for use, you are ready to create a BI application with PowerPivot. The first step is to import the data.
Importing data
There are many places from which you might want to import data, including the following:
A relational database (SQL Server, Oracle, Teradata, DB2, and so on)
A cube (SQL Server Analysis Services database)
A Reporting Services report
A data feed
Text files
Windows Azure Marketplace DataMarket
Data stored in your clipboard
The following sections explore some of these methods in more detail.
Importing from a Relational Database
To import data from a relational database, you need to identify the database, make a connection to it, and select the data you want. You can practice these steps in the following procedure.
To import database data
On the Home tab of the PowerPivot window, click From Database, as shown in the following illustration, and then click From SQL Server.
The Table Import Wizard starts. Fill in the Server Name field with the server name and instance of the SQL Server you want to access (or select a database from the drop-down list).
From the Database Name drop-down list, select the ContosoRetailDW database you installed earlier, as shown in the following illustration, and then click Next.
In the next wizard screen, to import data from the selected relational database, you can choose to either select from a list of tables and views or write a custom SQL query to retrieve the data. For this exercise, select the Select From A List Of Tables And Views To Choose The Data To Import option, as shown in the following illustration.
The Select Tables And Views page shows a list of tables. Select the check boxes next to the DimChannel, DimDate, DimGeography, DimProduct, DimProductCategory, DimProductSubcategory, DimPromotion, DimStore, FactInventory, and FactSales tables. You can see some of these tables selected in the following illustration.
On the Select Tables And Views page, you can also click either Select Related Tables or Preview & Filter. Clicking Select Related Tables selects tables that have relationships with the table or tables you have already selected; clicking Preview & Filter displays the first 50 rows of the currently selected table, looking similar to the following illustration. This view can be useful when you want to verify that a selected table is indeed the table you want to import. Selecting or deselecting the check boxes in the header row lets you select or exclude columns from this view.
Close the Preview page shown in the preceding figure, if you opened it, and then, in the Select Tables And Views dialog box, click Finish.
At this point, PowerPivot for Excel sends a command to the VertiPaq engine to create the PowerPivot data store, which is an Analysis Services in-memory database, retrieving the data you specified from the relational SQL Server data source you selected.
After the import operation starts, you can see the quantity of rows imported as the VertiPaq engine processes the tables. PowerPivot for Excel also tries to import any existing relationships between the tables being imported.
When the operation completes, on the Import Summary dialog box, click Close.
)
)
Notice that the PowerPivot window fills with the data you imported, as shown in the following illustration. The tables are organized as separate tabs, each accessible from the bottom of the window just like a typical Excel worksheet.
)
Choose one of the sheets—that is, one of the tables—to see all its columns and rows. At the bottom of the PowerPivot for Excel window is a Records field, which shows you how many rows that particular table has loaded. The FactSales table, for example, has more than three million rows—and all that data is available. You can scroll through it seamlessly, thanks to the VertiPaq engine, which uses a columnar technology to achieve high compression rates and processing power. When you scroll, apply filters, or perform calculations, PowerPivot for Excel sends queries in the background (known as tabular queries) to the VertiPaq engine, which retrieves the results amazingly fast.
Importing from Windows Azure Marketplace DataMarket
Released in October 2010, Windows Azure Marketplace DataMarket (referenced hereafter as Azure DataMarket, for simplicity) is a service with which developers and information workers can easily discover, purchase, and manage premium data subscriptions (some of these data subscriptions are free; some are not) that reside in the Windows Azure platform. By bringing data with a wide range of content from authoritative commercial and public sources together into a single location, Azure DataMarket is perfect for PowerPivot users who want to enrich their applications in innovative ways.
To accommodate this new service from Microsoft, PowerPivot for Excel was updated from its first version—partly to provide users with a better experience when using Azure DataMarket and partly to optimize connections to the Azure DataMarket data feeds. The changes implemented are relatively small but should make a significant difference for those of you who plan to work with data from Azure DataMarket data feeds.
Before you can start using Azure DataMarket, you must have a registered account. You can register your Windows Live ID account for free here: https://datamarket.azure.com/register/.
To import data from Azure DataMarket, you can perform the steps in the next exercise.
To import data from Azure DataMarket
On the Home tab of the PowerPivot for Excel window, click From Azure DataMarket, as shown in the following illustration.
On the Connect To An Azure DataMarket Dataset page of the Table Import Wizard, fill in the Azure DataMarket Dataset URL field with the address for the dataset that you want to import data from. In the Security Settings box, copy your account key into the Account Key field. (Your account key is available at the My Data page, in the Account Keys section at the Azure DataMarket website, when you subscribe to a dataset.) For illustrational purposes, we will import data from the 2006–2008 Crime in the United States (Data.gov) dataset at https//api.datamarket.azure.com/Data.ashx/data.gov/Crimes/, as shown in the following illustration. The Crime dataset subscription is free of charge.
Alternatively, from the Azure DataMarket Dataset webpage, you can elect to open a query in PowerPivot (from a drop-down menu). A dialog box appears that asks you to open or save the query. If you choose to save, PowerPivot saves an Atom file at a location you specify. The Atom file contains the query information; you can use it in the Azure DataMarket Dataset URL field in the Table Import Wizard by clicking Browse and navigating to the file’s location.
PowerPivot imports the data from the Crime dataset and makes it available on a new tab in the PowerPivot window, as shown in the following illustration.
)
)
Pasting from the Clipboard
You can also import data into PowerPivot for Excel by pasting it from the clipboard. PowerPivot can use any data that is in a tabular format, which includes tables copied from a webpage. When you click Paste on the Home tab of the PowerPivot window, the Paste Preview dialog box opens, as shown in the following illustration. Paste Preview lets you view the data that will be copied as a new table. While still in Paste Preview, you can choose to use the values of first row as the columns headers.
