Creating Solutions with Microsoft PowerPivot for Excel and PowerPivot for SharePoint

Summary: Learn how to use Microsoft PowerPivot to create, analyze, and share Microsoft Excel 2010 workbooks in innovative ways by creating end-to-end solutions with PowerPivot for Excel and PowerPivot for SharePoint.

Applies to: Excel 2010 | Office 2007 | Office 2010 | Open XML | PowerPoint 2010 | SharePoint Foundation 2010 | SharePoint Server 2010 | VBA | Word 2010

Published:   April 2011

Contents

• Overview of Business Intelligence and Microsoft PowerPivot

• PowerPivot Scenario

• The Solution to the Scenario

• Synopsis

• Additional Resources

Overview of Business Intelligence and Microsoft PowerPivot

Business intelligence (BI) refers to computer-based techniques that are used to analyze, forecast, and report on business data to improve business decisions. BI enables you to transform raw data into meaningful and useful information. It condenses huge amounts of data into multi-dimensional structures that you can use in PivotTables and PivotCharts in Microsoft Excel.

One of the drawbacks of previous Excel versions is the limit on the number of rows that you can manipulate. For Microsoft Office Excel 2007 and later versions, that limit is one million rows. In contrast, Microsoft SQL Server PowerPivot for Excel enables you to build BI applications that can contain tens or even hundreds of millions of rows of data. PowerPivot for Excel enables you to create and edit applications that can integrate data from various external data sources, enhancing that data with custom calculations, and using that data for analysis in PivotTables and PivotCharts.

PowerPivot for Excel is a free, managed add-in that extends the capabilities of Excel 2010. It enables users to create and edit PowerPivot applications and to use the Excel interface to work with PowerPivot data.

As this article discusses, PowerPivot applications look exactly like regular Excel workbooks. However, they include much more data and metadata embedded in the workbook than a regular workbook has that is not connected to an external data source. Additionally, you can connect the PowerPivot application to many more types of external data sources. For more information about PowerPivot for Excel, see Additional Resources.

In previous versions of Excel, it is difficult to share data, calculations, and other workbook features with other users. In many cases, collaborators used email or uploaded the workbook to a share on the network, which typically produced many versions of the workbook across an organization. Syncing the data from these different versions was very difficult and time-consuming.

With PowerPivot for SharePoint, you can collaborate, share, and report on data in PowerPivot for Excel applications. In other words, use PowerPivot for Excel to create PowerPivot applications, and use PowerPivot for SharePoint to share those applications. PowerPivot for SharePoint can also provide a thin-client capability to PowerPivot applications when it is used with Excel Services.

In contrast to PowerPivot for Excel, PowerPivot for SharePoint installs on top of SharePoint Server 2010. It requires you to use Windows Server 2008 R2 or Windows Server 2008 Service Pack 2 on a 64-bit computer and that you use Microsoft SQL Server 2008 R2 Enterprise Edition.

After you install PowerPivot for SharePoint, your published PowerPivot workbooks reside in a PowerPivot Gallery that enables you to display the workbooks in different interactive views. You can also create reports from the current PowerPivot workbook and manage the schedule for data refreshes from the gallery.

You can find links to more information about PowerPivot for SharePoint later in this article. You can also find more information by visiting the PowerPivot home page.

PowerPivot Scenario

To see the advantages of PowerPivot applications, consider the following scenario.

Imagine that you are a sales manager and that you want to examine sales data for each of your sales channels between the years 2007 and 2009. You also want to share this data easily with other managers.

In this scenario, you follow these steps:

• Import data into the workbook from an external data source.

• Apply a filter to a table.

• Create relationships between tables.

• Create calculations by using Data Analysis Expressions (DAX).

• Create and manipulate a PivotTable and a PivotChart to display sales data.

• Share the workbook by using PowerPivot for SharePoint.

The Solution to the Scenario

The following sections provide the solution to the scenario.

Importing Data from a Relational Database

First, you must get the data into the PowerPivot Window. You can import data into PowerPivot for Excel workbooks from several different sources:

• Relational databases

• SQL Server Analysis Services

• Reporting Services reports

• Text files

• Excel workbook tables

• The clipboard

In this section, you import data from a relational database. In this instance, you use the Northwind sample database and import the Employees and Orders tables into Excel.

To import data from a relational database

1. Open the PowerPivotTutorialSample.xlsx workbook. On the PowerPivot tab, click PowerPivot Window.

2. In the PowerPivot window, click the From Database button, and then choose From Access.

3. In the Import Table Wizard dialog box, click the Browse button by Database Name, and then navigate to the Northwind database. By default, this is named Database2.accdb after the download.

4. Click Next.

5. In the Choose How to Import the Data page, use Select from a list of tables and views to choose the data to import, and then click Next.

6. In the Source Data column, select Employees and then click Preview and Filter.

7. In the Preview Selected Table page, scroll to the right until you see the Attachments column.

8. Clear the box next to the title of the column and then click OK to prevent the column from being imported into Excel.

9. Notice that on the Preview Selected Table page, in the Filter Details column for the Employees table, Applied filters is now displayed to let you know that a filter is applied to the table (see Figure 1). Click Finish.

   Figure 1. A filter is applied to the table

   

    

10. After you import the Employee table into the PowerPivot window, click Close.

Import Data from a Linked Table

In this section, you import data from a linked table in the sample workbook. First, you import a table into Excel from the Northwind database. Next, you create a link to the new worksheet. Finally, you establish a relationship between the new table and the Employee table in the PowerPivot window.

To import data from a linked table

1. If you are still in the PowerPivot window, click the Excel icon in the upper-left corner to switch to the Excel workbook.

2. In Excel, click the Data tab.

3. In the Get External Data group, click From Access.

4. In the Select Data Source dialog box, navigate to the Northwind database (Database2.accdb), and then click Open.

   In the Select Table dialog box, scroll down, select the Orders table, and then click OK.

   In the Import Data dialog box, in the Where do you want to put this data section, select New worksheet (see Figure 2), and then click OK to import the table into a new worksheet.

   Figure 2. Import Data dialog box

   

    

5. Right-click the tab for the new worksheet and then rename it Orders.

6. Next, you bring data from the worksheet that you just created into the PowerPivot window. Click the Orders tab and then on the PowerPivot tab, click the Create Linked Table icon.

7. PowerPivot creates a new linked table with the source data from the Orders worksheet. In the PowerPivot window, you see a new table named Table_Database2.accdb and a link symbol on the tab.

8. Right-click the new worksheet tab and rename it Orders.

9. Now create a relationship between the Employees table and the Orders table in the PowerPivot window. On the ribbon, click the Design tab, and then click Create Relationship.

10. In the Create a lookup relationship between two tables dialog box, under the top Tables drop-down list, select the Orders table, and then in the Column drop-down list, select EmployeesID as shown in Figure 3.

11. In the Related Lookup Table drop-down list, select the Employees table, and then in the Related Lookup Column drop-down list, select the ID as shown in Figure 3.

    Figure 3. Create a lookup relationship between two tables dialog box

    

     

12. Click Create to create the relationship.

    To see this relationship or any other relationships between tables, click Manage Relationships on the Design tab.

Create Calculations by Using Data Analysis Expressions

In this section, you create calculations by using Data Analysis Expressions (DAX). DAX provide a set of functions and operators that help you write expressions for calculations that you want to apply to your PowerPivot data. DAX functions have a relational database skew (a focus on tables and columns) that makes them easy for Excel users to apply. Additionally, after you create a DAX calculation, you can create PivotTables and PivotCharts that look familiar to Excel users.

You can group DAX functions into the following major types:

• Math and Trig

• Data and Time

• Statistical

• Logical

• Text

• Filter

• Time Intelligence

You can group DAX calculations into the following categories:

• Creating calculated columns by using simple functions such as sums and counts, or creating more complex calculations by using multiple tables and aggregations.

• Using measures, which are aggregated values that typically are added to the Values drop-zone on a PivotTable field list.

To see DAX in action, perform the steps in the procedures that follow. For now, consider that in the dbo_FactSales worksheet, there are TotalSales and TotalCost columns. To calculate profit, you just create the expression Profit = TotalSales – TotalCost and PowerPivot automatically applies the calculation to each row in the column. To do the same thing in Excel, you must create the expression and then paste the expression into each row.

To calculate markup by using DAX

1. In the dbo_FactSales worksheet, scroll to the last column and select it.

2. In the expression window type =, and then scroll to the left and select the UnitPrice column. This adds dboFactSales[UnitPrice] to the expression.

3. Type the minus symbol - and then select the UnitCost column.

4. Press the Enter key. After you press the Enter key, PowerPivot calculates the markup for each row and posts the results in the new column.

5. Right-click the CalculateColumn1 column header and rename it Markup as shown in Figure 4.

   Figure 4. Markup column

   

    

Now look at a different calculation across related tables. Here you want to find the total profit for the sales of a group of products. You can use DAX in the following procedures to calculate the total profit from product sales.

To calculate product sales by using DAX

1. In the dbo_DimProduct table, scroll to the last column and type =SUMX(RELATEDTABLE(dbo_FactSales), dbo_FactSales[TotalProfit]).

2. Press the Enter key. The total profit for each product is displayed in the row for that product as shown in Figure 5.

3. Right-click the column header and name it TotalProfit.

   Figure 5. TotalProfit column

   

    

There is a one-to-many relationship between the dbo_DimProduct and dbo_FactSales tables where a product can contain multiple entries in the sales data table. The RELATEDTABLE function enables you to retrieve data from the source table (dbo_FactSales) into the current table (dbo_DimProduct). When the data is returned from the source table, the SUMX function aggregates the data for each row in the destination table. And because each row in the dbo_DimProduct specifies a unique product, the data from the related table is the total profit for each product.

Create a PivotTable and PivotChart

If you want to share your analysis with your co-workers or management, you must put it into a form that is easy to manipulate and review. In this section, you create a PivotTable with the data in the PowerPivot window. Note that if you already have the Excel file, the PivotTable and PivotChart already exists in the worksheet titled Sales by Channel; however, you can create a similar PivotTable and PivotChart from the following procedures.

Figure 6 shows the result of the following two procedures.

Figure 6. Completed PivotTable

To create the PivotTable

1. On the Home tab of the PowerPivot window, click the PivotTable drop-down, and then select Chart and Table (Horizontal).

2. In the Create PivotChart and PivotTable (Horizontal) dialog box, select the option for the New Worksheet, and then click OK. A new worksheet titled sheet1 is displayed with the layouts for a PivotTable and a PivotChart side-by-side as shown in Figure 6. Also notice that there is a PowerPivot Field List that is similar to the field list for regular PivotTables with the additions of drop-zones for Slicers Vertical and Slicers Horizontal. Slicers help you filter and analyze data across the related PivotTables and PivotCharts. The field list contains all of the tables in the PowerPivot window with the first table expanded.

   Figure 7. PivotTable and PivotChart worksheet

   

    

3. Next, expand the dbo_FactSales table and then drag the TotalSales field to the Values drop-zone. Columns in the Values drop-zone are known as measures, which mean that you can aggregate the contents of the column. Right-click the TotalSales measure, click Edit Measure, and then in the Custom Name box, change the name to Sales by Channel. Also notice that you have the option to change the aggregation type for the values of this column to Sum, Count, Min, Max, or Average as shown in Figure 8.

   Figure 8. Changing a measure name

   

    

4. In the list of tables, expand the dbo_DimChannel table, and then drag the ChannelName column to the Row Labels drop-zone to display the sales channels Catalog, Online, Reseller, Store row labels in the PivotTable.

5. Expand the dbo_DimDate table and then drag the CalendarQuarter to the Column Labels drop-zone to add the appended year and quarter labels as columns headers in the PivotTable.

6. Next, from the dboDimDate table, drag the Year, Quarter, and Month fields to the Slicers Vertical drop-zone. These slicers are displayed on the left-side of the PivotTable in Figure 6.

You have now created a sheet with data that you can analyze. Slicers can filter the data in the PivotTable by year, month, and quarter.

To create the PivotChart

1. Click 2008 in the Year slicer.

2. Select the PivotChart.

3. From the dbo_FactSales table, drag the TotalSales column onto the Values drop-zone.

4. Expand the dbo_DimChannel table and then drag the ChannelName column onto the Axis Fields drop-zone.

5. From the dbo_DimDate table, drag the CalendarQuarter column onto the Legend Field drop-zone. The PivotChart now reflects the sales for each channel and quarter in 2008 as shown in Figure 9. Also notice that the PivotTable changes the data displayed for the selected slicers.

   Figure 9. PivotChart reflects sales data for each channel in each quarter in 2008

   

    

Sharing Your Data with Other People

There are a many ways that you can share an Excel workbook across the organization. One traditional way is to create a file share on a server and then upload the sales report to the share. That solution requires you to give each user the permissions to view and download the report. Another traditional way to share a workbook is to send a version of it to each manager. The biggest drawback in that solution is version control; if one manager makes changes to a local copy of the workbook, you must somehow synchronize each change with a master workbook, and then redistribute the updated workbook to each of the managers.

PowerPivot provides capabilities that are integrated with SharePoint Server 2010 and that enable you to share and manage the PowerPivot data in workbooks. PowerPivot for SharePoint also enables you to interact with published PowerPivot workbooks.

To install and configure PowerPivot for SharePoint on your Windows Server 2008 R2 computer, see Installation (PowerPivot for SharePoint) on MSDN. You can also find links to more resources at the end of this article.

To verify that PowerPivot for SharePoint is installed and configured correctly, open a web browser in administrator mode and browse to https://servername, where servername is the name of the computer on which you installed PowerPivot for SharePoint. You should see a link to the PowerPivot Gallery on the left-side of the page as shown in Figure 10.

Figure 10. PowerPivot Gallery

Now you are ready to publish your workbook.

To publish the workbook

1. Open the PowerPivotTutorialSample.xlsx workbook.

2. Click on the File tab, click Save and Send, click Save to SharePoint, and then click Save As.

3. Type https://servername/PowerPivot Gallery in the folder path of the Save As dialog box, and then click Save. Excel publishes the workbook to your SharePoint Server 2010 server and opens it in the web browser as shown in Figure 11. Excel Services is responsible for rendering the workbook in the web browser. You can now interact with the workbook exactly as you would if you had opened it in Excel.

   Figure 11. Workbook is rendered by Excel Services

   

    

After you publish your PowerPivot workbook to the PowerPivot Gallery, PowerPivot displays the workbook by using different views as shown in Figure 12. These views enable you to work interactively with the workbook. You can hover over each thumbnail view and see the larger view on the left side of the window.

Figure 12. PowerPivot Gallery views

The PowerPivot Gallery also enables you to create Reporting Services or Excel reports and refresh the PowerPivot data at set intervals. In Figure 12, they are the two icons in the upper-right corner of the window.

Synopsis

This article introduced you to Microsoft PowerPivot. You learned how to integrate data from various external sources into PowerPivot for Excel and then to analyze that data by using DAX. You created a PivotTable and PivotChart that you can use to look at the data in many views. You saw how to upload the workbook to PowerPivot for SharePoint so that it can be shared with other users. This article only scratched the surface of all that is possible with Microsoft PowerPivot. Experiment with your own PowerPivot workbooks and use these techniques to your own advantage!

Additional Resources

To find more information about the subjects discussed in this article, see the following resources.

• Microsoft PowerPivot website

• PowerPivot for Excel

• PowerPivot Overview

• Step by step guide on installing PowerPivot for Excel

• Step by step guide: PowerPivot for SharePoint installation on a single machine

• Installation (PowerPivot for SharePoint)

• SQL Server PowerPivot for SharePoint