PowerPivot

The best is saved for last. Microsoft offers a powerful analysis tool: the free PowerPivot for Excel 2010 add-in. This add-in is based on the latest technology, allowing processing of large data groups (up to several million rows), optimized integration of data, and the ability to publish analyses on the Internet with Microsoft SharePoint 2010. The following list details PowerPivot’s advantages:

• PowerPivot combines native Excel 2010 functions with an in-memory engine to allow users to evaluate large data groups interactively and to use the results for calculations. This is possible with the new memory management system, which uses column-oriented compression. Everything is done in the memory of your computer to accelerate data operations.

• PowerPivot optimizes the integration of data from different sources, including enterprise databases, spreadsheets, reports, CSV files, and data feeds.

• PowerPivot also overrides the row limits for Excel 2007 and Excel 2010 worksheets.

• You can access PivotTables, data averages, and other familiar analysis features of Excel to create reports.

• You can publish your analyses in SharePoint 2010 to share them with other users in your organization. Other users working with Excel Services reports can use the same functions, such as data averages and fast queries.

• Applications and reports in SharePoint 2010 are automatically updated with simultaneous version tracking.

Further information, tutorials, and the download of the PowerPivot add-in can be found at http://www.powerpivot.com.

Using PowerPivot in Excel 2010

In Excel 2010, with the PowerPivot add-in installed, click the PowerPivot button on the PowerPivot tab to switch from Excel to the PowerPivot window. At first the window is empty and the ribbon contains many unavailable buttons. You have to embed the data to be analyzed. Click the From Database button (see Figure 2-54) and select From SQL Server.

Figure 2-54. The Get External Data group on the Home tab of the PowerPivot window.

Note

The Microsoft example database used is ContosoRetailDW, which at the time of this writing is available for download at

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=18279

The ContosoBIdemoBAK.exe download file contains two files to extract: ContosoRetailDW.bak and an Excel list. ContosoRetailDW.bak is a Microsoft SQL Server backup that you will need to restore in SQL Server.

Enter the server name in the Table Import Wizard, specify the credentials, and select the database name (see Figure 2-55).

Figure 2-55. The first step in getting the database connection in the Table Import Wizard.

In the next step, keep the preselected option to make a selection from the table and view list (see Figure 2-56).

Figure 2-56. The second step in the Table Import Wizard.

Select the FactSales table. This table contains 3.4 million rows of transaction data. Click the Select Related Tables button (see Figure 2-57). Six related root data tables are included. Click the Finish button.

Figure 2-57. Selecting tables and views from the SQL Server database.

If you select large tables, it can take a few minutes to load the data. The wizard shows the loading progress (see Figure 2-58).

Figure 2-58. The data is loaded.

After the data is loaded, the tables are displayed in the PowerPivot window. The window (see Figure 2-59) looks like an Excel worksheet but has different functionality. Save the workbook. It has a size of 87 MB.

If you want to be able to publish the data in SharePoint, ask your administrator to increase the maximum size for uploaded data (which is 50 MB by default).

Figure 2-59. The PowerPivot window contains the loaded tables.

If the source database has a database relationships chart—that is, a definition of all relationships—the chart will be automatically used by PowerPivot. Right-click a column in the lookup table to view the associated table. You can check and add table relationships on the Design tab (see Figure 2-60).

Figure 2-60. The Design tab in PowerPivot.

If you click Manage Relationships on the Design tab, you get an overview of the defined relationships (see Figure 2-61). You can edit the relationships and create any new relationships necessary to embed tables from other sources.

Figure 2-61. An overview of all relationships between the tables.

If you view the DimStore table, you might notice that the region, country, and location information for the stores is missing. This information is linked through the GeographyKey field in the DimGeography table. To add the DimGeography table from the same source, perform the following steps:

1. Click the From Database button and select From SQL Server (see Figure 2-54, shown earlier).

2. Enter the server name and the credentials in the Table Import Wizard. Select the ContosoRetailDW database.

3. In the next step, keep the option to make a selection from the table and view list (see Figure 2-56, shown earlier).

4. In the last step of the Wizard, select the check box for the DimGeography table and click the Finish button.

5. Select the DimStore table and click the Create Relationship button on the Design tab.

   

6. In the Edit Relationship dialog box, select the GeographyKey column for the DimStore table. Select DimGeography as the linked search table and GeographyKey as the linked search column. Click OK to exit.

The list you need for data analyses is complete. You don’t need to add more tables to create a Pivot evaluation. Perform the following steps:

1. On the Home tab in the PowerPivot window, click the PivotTable arrow (see Figure 2-62). As you can see, templates for PivotTables and PivotCharts are available.

   

   Figure 2-62. Templates for Pivot evaluations.

2. In the menu, select Chart And Table (Vertical). You will be switched to the Excel window with both empty Pivot objects on the worksheet.

3. Select the chart area and drag the ChannelName field from the DimChannel table into the Slicers Vertical area.

4. Right-click the new data average element and, in the shortcut menu, select the Field Settings command. Enter Sales Channel in the Custom Name box. Click OK to exit.

5. Drag the ContinentName and RegionCountryName fields from the DimGeography table into the Slicers Horizontal area. In the field settings for the elements, change the custom names to Continent and Country/Region.

6. Drag the CalendarYear field from the DimDate table into the Row Labels area. The custom name in the field settings should be Year.

7. Drag the TotalCost and SalesAmount fields from the FactSales table into the Values area.

8. Select the Measure command in the shortcut menus for both elements. In the measure settings for the elements, change the custom names to Costs and Revenue.

Figure 2-63 shows the settings you have made so far.

Figure 2-63. The design of the PivotChart in the PowerPivot report.

If necessary, format the chart (for example, set the number format for the axes). The second set of steps revolves around the PivotTable. To complete the PivotTable, perform the following steps:

1. Select the area for the PivotTable below the chart. You might notice that the slicers for the chart are already set. Keep these settings.

2. Drag the CalendarYear field from the DimDate table into the Column Labels area. The custom name in the field settings should be Year.

3. Drag the Manufacturer field from the DimProduct table into the Row Labels area. The custom name in the field settings should be Manufacturer.

4. Drag the TotalCost and SalesAmount fields from the FactSales table into the Values area. Again, change the custom names to Costs and Revenue.

Change the formats in the PivotTable (see Figure 2-64)—for example, change the number format or column width. The result should look similar to Figure 2-65. Name the sheet containing the PowerPivot report, and save your work.

Figure 2-64. The design of the PivotTable in the PowerPivot report.

A new worksheet with the associated PivotTable was created for the chart. Don’t delete this worksheet, and remember that each time you change the PivotTable this change is also applied to the chart.

Figure 2-65. A PowerPivot report consisting of a chart and a table using the same slicers.

For PowerPivot reports, you need only to know how to create and design PivotTables and PivotCharts. In other words, your Excel know-how is sufficient to work with PowerPivot.