How to Create PowerPivot Applications in Excel 2010

104646.zip

SQL Server PowerPivot is a collection of client and server components that enable managed self-service business intelligence (BI) for the next-generation Microsoft data platform. After I introduce you to PowerPivot, I'll walk you through how to use it from the client (i.e., information producer) perspective.

PowerPivot

PowerPivot is a managed self-service BI product. In other words, it's a software tool that empowers information producers (think business analysts) so that they can create their own BI applications without having to rely on traditional IT resources. If you're thinking "but IT needs control of our data," don't worry. IT will have control of the data PowerPivot consumes. Once the data is imported into PowerPivot, it becomes read only. IT will also have control of user access and data refreshing. Many of PowerPivot's most compelling features require the use of a centralized server for distribution, which pushes the product's usage toward its intended direction of complementing rather than competing against traditional BI solutions. This managed self-service BI tool effectively balances information producers' need for ad-hoc analytical information with IT's need for control and administration.

Key features such as the PowerPivot Management Dashboard and data refresh process are why PowerPivot is categorized as a managed self-service BI product. Other key features include:

• The use of Microsoft Excel on the client side. Most business users are already familiar with how to use Excel. By incorporating this new self-service BI tool into Excel, Microsoft is making further headway in its goal of bringing BI to the masses.
• Excel's PivotTable capabilities have improved over the years. For example, Excel 2010 includes such enhancements as slicers, which let you interactively filter data. PowerPivot further enhances Excel 2010's native PivotTable capabilities. The enhancements include Data Analysis Expressions (DAX) support, relationships between tables, and the ability to use a slicer on multiple PivotTables.
• A wide range of supported data sources. PowerPivot supports mainstream Microsoft data repositories, such as SQL Server, SQL Server Analysis Services (SSAS), and Access. It also supports nontraditional data sources, such as SQL Server Reporting Services (SSRS) 2008 R2 reports and other Atom 1.0 data feeds. In addition, you can connect to any ODBC or OLE DB compliant data source.
• Excellent data-processing performance. PowerPivot can process, sort, filter, and pivot on massive data volumes. The product makes extremely good use of x64 technology, multicore processors, and gigabytes of memory on the desktop.

PowerPivot consists of four key components. The client-side components are Excel 2010 and the PowerPivot for Excel add-on. Information producers who will be developing PowerPivot applications will need Excel 2010. Users of those applications can be running earlier versions of Excel. Under the hood, the add-on is the SSAS 2008 R2 engine running as an in-memory DLL, which is referred to as the VertiPaq mode. When information producers create data sources, define relationships, and so on in the PowerPivot window inside of Excel 2010, they're implicitly building SSAS databases.

The server-side components are SharePoint 2010 and SSAS 2008 R2 running in VertiPaq mode. SharePoint provides the accessibility and managed aspects of PowerPivot. SSAS 2008 R2 is used for hosting, querying, and processing the applications built with PowerPivot after the user has deployed the workbooks to SharePoint.

What You Will Need

For this demonstration, you don't need access to SharePoint 2010, as I'll focus on the client-side experience. What you will need is a machine or virtual machine (VM) with the following installed:

• Excel 2010. The beta version is available at www.microsoft.com/office/2010/en/default.aspx.
• PowerPivot for Excel add-on, which you can obtain at www.powerpivot.com. The add-on's CPU architecture must match Excel 2010's CPU architecture.
• The relational database engine and SSRS in SQL Server 2008 R2. You can download the SQL Server 2008 R2 November Community Technology Preview (CTP) at www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx. Note that if you're deploying SSRS on an all-in-one machine running Windows Server 2008 or Windows Vista, you need to follow the guidelines in the Microsoft article at msdn.microsoft.com/en-us/library/bb630430(SQL.105).aspx.
• SQL Server 2008 R2 AdventureWorks sample databases, which you can get at msftdbprodsamples.codeplex.com.

The PowerPivot for Excel add-on and AdventureWorks downloads are free. As of this writing, Excel 2010 beta and SQL Server 2008 R2 November CTP are also free because they have yet to be commercially released.

Finally, you need to download several T-SQL scripts and a sample Excel workbook. To do so, click the Download the Code Here button near the top of the page, and save the 104646.zip file on your machine.

The Scenario

The exercise I'm about to walk you through is based on the following fictitious business scenario: AdventureWorks has become a dominant force in its respective industries and wants to ensure that it retains its most valuable resource: its employees. As a result, AdventureWorks has conducted its first internal employee morale survey. In addition, the company has started tracking employee promotions. Management wants to analyze the results of the employee survey by coupling those results with the employee promotion data and core employee information (e.g., employees' hire dates, their departments). In the future, management would also like to incorporate outside data (e.g., HR industry data) into the analysis.

The AdventureWorks BI team is too overwhelmed with resolving ongoing data quality issues and model modifications to accommodate such an ad-hoc analysis request. As a result, management has asked you to develop the AdventureWorks Employee Morale PowerPivot Application. To construct this application, you need the core employee information, the employee promotions data, and the employee morale survey results. Although all the data resides behind the corporate firewall, it's scattered in different repositories:

• The core employee information is in the DimEmployee table in the AdventureWorks DW (short for data warehouse) for SQL Server 2008 R2 (AdventureWorksDW2008R2).
• The list of employee promotions is in an SSRS report.
• The results of the employee morale survey are in an Excel 2010 worksheet named AW_EmployeeMoraleSurveyResults.xlsx.

To centralize this data and create the application, you need to follow these steps:

1. Create and populate the FactEmployeePromotions table.
2. Use SSRS 2008 R2 to create and deploy the employee promotions report.
3. Import the results of the employee morale survey into PowerPivot.
4. Import the core employee data into PowerPivot.
5. Import the employee promotions report into PowerPivot.
6. Establish relationships between the three sets of imported data.
7. Use DAX to add a calculated column.
8. Hide unneeded columns.
9. Create the PivotCharts.

Step 1

In AdventureWorks DW, you need to create a new fact table that contains the employee promotions data. To do so, start a new instance of SQL Server Management Studio (SSMS) by clicking Start, All Programs, Microsoft SQL Server 2008 R2 November CTP, SQL Server Management Studio. In the Connection dialog box, connect to a SQL Server 2008 R2 relational database engine that has the AdventureWorksDW2008R2 database pre-installed. Click the New Query button to open a new relational query window. Copy the contents of Create_FactEmployeePromotions.sql (which is in the 104646.zip file) into the query window. Execute the script by pressing the F5 key.

Once the FactEmployeePromotions table has been created, you need to populate it. Assuming you still have SSMS open, press Ctrl+Alt+Del to delete the existing script, then copy the contents of Populate_FactEmployeePromotions.sql (which is in 104646.zip) into the query window. Execute the script by pressing the F5 key.

Step 2

With the FactEmployeePromotions table created and populated, the next step is to create and deploy the employee promotions report in SSRS 2008 R2. First, open up a new instance of the Business Intelligence Development Studio (BIDS) by clicking Start, All Programs, Microsoft SQL Server 2008 R2 November CTP, SQL Server Business Intelligence Development Studio.

In the BIDS Start page, select File, New, Project to bring up the New Project dialog box. Choose the Report Server Project Wizard option, type ssrs_AWDW for the project's name, and click OK. In the Report Wizard's Welcome page, click Next.

In the Select the Data Source page, you need to create a new relational database connection to the same AdventureWorks DW database used in step 1. After you defined the new data source, click Next.

In the Design the Query page, copy the contents of Employee_Promotions_Report_Source.sql (which is in 104646.zip) into the Query string text box. Click Next to bring up the Select the Report Type page. Choose the Tabular option, and click Next.

In the Design the Table page, select all three available fields and click the Details button to add the fields to the details section of the report. Click Next to advance to the Choose the Table Style page, where you should leave the default settings. Click Next.

At this point, you'll see the Choose the Deployment Location page shown in Figure 1. In this page, which is new to SQL Server 2008 R2, you need to make sure the correct report server URL is specified in the Report server text box. Leave the default values in the Deployment folder text box and the Report server version drop-down list. Click Next.

In the Completing the Wizard page, type EmployeePromotions for the report's name. Click Finish to complete the report wizard process.

Next, you need to deploy the finished report to the target SSRS report server. Assuming you specified the correct URL for the report server in the Choose the Deployment Location page, you simply need to deploy the report. If you need to alter the target report server settings, you can do so by right-clicking the project in Solution Explorer and selecting the Properties option. Update your target report server setting and click OK to close the SSRS project's Properties dialog box.

To deploy the report, select Build, then Deploy ssrs_AWDW from the main Visual Studio 2008 menu. To confirm that your report deployed correctly, review the text that's displayed in the Output window.

Step 3

With the employee promotions report deployed, you can now turn your attention to constructing the PowerPivot application, which involves importing all three data sets into PowerPivot. Because the employee morale survey results are contained within an existing Excel 2010 worksheet, it makes sense to first import those results into PowerPivot.

Open the AW_EmployeeMoraleSurveyResults.xlsx file, which you can find in 104646.zip, and go to the Survey Results tab. As Figure 2 shows, the survey results are in a basic Excel table with three detailed values (Position, Compensation, and Management) and one overall self-ranking value (Overall). The scale of the rankings is 1 to 10, with 10 being the best value. To tie the survey results back to a particular employee, the worksheet includes the employee's internal key as well.

With the survey results open in Excel 2010, you need to import the table's data into PowerPivot. One way to import data is to use the Linked Tables feature, which links data from a parent worksheet. You access this feature from the PowerPivot ribbon, which Figure 3 shows.

Before I explain how to use the Linked Tables feature, however, I want to point out two other items in the PowerPivot ribbon because they're crucial for anyone using PowerPivot. On the left end of the ribbon, notice the PowerPivot window button. The PowerPivot window is the main UI you use to construct a PowerPivot model. Once the model has been constructed, you can then pivot on it, using a variety of supported PivotTables and PivotCharts in the Excel 2010 worksheet. Collectively, these components form what is known as a PowerPivot application. To launch the PowerPivot window, you can either click the PowerPivot window button or invoke some other action that automatically launches the PowerPivot window with imported data. The Linked Tables feature performs the latter behavior.

The other item you need to be familiar with in the PowerPivot ribbon is the Options & Diagnostics button. By clicking it, you gain access to key settings, including options for tracing PowerPivot activity, recording client environment specifics with snapshots, and participating in the Microsoft Customer Experience Improvement Program.

Now, let's get back to creating the AdventureWorks Employee Morale PowerPivot Application. First, click the PowerPivot tab or press Alt+G to bring up the PowerPivot ribbon. In the ribbon, click the Create Linked Table button. The PowerPivot window shown in Figure 4 should appear.

Notice that the survey results have already been imported into it. At the bottom of the window, right-click the tab labeled Table 1, select the Rename option, type SurveyResults, and press Enter.

Step 4

The next step in constructing the PowerPivot application is to import the core employee data. To do so, click the From Database button in the PowerPivot window, then select the From SQL Server option. You should now see the Table Import Wizard's Connect to a Microsoft SQL Server Database page, which Figure 5 shows.

In this page, replace SqlServer with Core Employee Data in the Friendly connection name text box. In the Server name drop-down list, select the SQL Server instance that contains the AdventureWorks DW database. Next, select the database labeled AdventureWorksDW2008r2 in the Database name drop-down list, then click the Test Connection button. If the database connection is successful, click Next.

In the page that appears, leave the default option selected and click Next. You should now see a list of the tables in AdventureWorks DW. Select the check box next to the table labeled DimEmployee and click the Preview & Filter button in the lower right corner.

In the Preview Selected Table page, drag the horizontal scroll bar as far as possible to the right, then click the down arrow directly to the right of the "Status" column header. In the Text Filters box, clear the (Select All) check box and select the Current check box. This action will restrict the employee records to only those that are active. Figure 6 shows a preview of the filtered data set.

With the employee data filtered, go ahead and click OK. Click Finish to begin importing the employee records. In the final Table Import Wizard page, which shows the status of the data being imported, click Close to complete the database import process and return to the PowerPivot window.

Step 5

In Step 2, you built a SSRS 2008 R2 report that contains employee promotion data. You now need to import this report's result set into the PowerPivot application. To do so, click From Data Feeds on the Home tab, then select the From Reporting Services option. You should see the Table Import Wizard's Connect to a Data Feed page, which Figure 7 shows.

In the Friendly connection name text box, replace DataFeed with Employee Promotions in the Friendly connection name text box and click the Browse button. In the Browse dialog box, navigate to your SSRS report server and select the recently built EmployeePromotions.rdl report. When you see a preview of the report, click the Test Connection button. If the data feed connection is successful, click Next.

In the page that appears, you should see that an existing report region labeled table1 is checked. To change that name, type EmployeePromotions in the Friendly Name text box. Click Finish to import the report's result set. When a page showing the data import status appears, click the Close button to return to the PowerPivot window.

Step 6

Before you can leverage the three data sets you imported into PowerPivot, you need to establish relationships between them, which is also known as correlation. To correlate the three data sets, click the Table tab in the PowerPivot window, then select Create Relationship. In the Create Relationship dialog box, select the following:

• SurveyResults in the Table drop-down list
• EmployeeKey in the Column drop-down list
• DimEmployee in the Related Lookup Table drop-down list
• EmployeeKey in the Related Lookup Column drop-down list

Click the Create button to establish the designated relationship.

Perform this process again (starting with clicking the Table tab), except select the following in the Create Relationship dialog box:

• SurveyResults in the Table drop-down list
• EmployeeKey in the Column drop-down list
• EmployeePromotions in the Related Lookup Table drop-down list
• EmployeeKey in the Related Lookup Column drop-down list

To confirm the creation of both relationships, select Manage Relationships on the Table tab. You should see two relationships listed in the Manage Relationships dialog box, as Figure 8 shows.

 

Step 7

Because PowerPivot builds an internal SSAS database, a hurdle that the Microsoft PowerPivot product team had to overcome was exposing MDXs in an Excel-like method. Enter DAX. With DAX, you can harness the power of a large portion of the MDX language while using Excel-like formulas. You leverage DAX as calculated columns in PowerPivot's imported data sets or as dynamic measures in the resulting PivotTables and PivotCharts.

In this example, let's add a calculated column that specifies the year in which each employee was hired. Select the DimEmployee table in the PowerPivot window. In the Column tab, click Add Column. A new column will be appended to the table's columns. Click the formula bar located directly above the new column, type =YEAR(\\[HireDate\\]), and press Enter. Each employee's year of hire value now appears inside the calculated column. To rename the calculated column, right-click the calculated column's header, select Rename, type YearOfHire, and press Enter.

Don't let the simplicity of this DAX formula fool you. The language is extremely powerful and capable of supporting capabilities such as cross-table lookups and parallel time-period functions.

Step 8

Although the data tables' current schema fulfills the scenario's analytical requirements, you can go the extra mile by hiding unneeded columns. (In the real-world, a better approach would be to not import the unneeded columns for file size and performance reasons, but I want to show you how to hide columns.) Hiding columns is useful when you think a column might be of some value later or when calculated columns aren't currently being used.

Select the SurveyResults table by clicking its corresponding tab at the bottom of the PowerPivot window. Next, click the Hide and Unhide button on the Column ribbon menu. In the Hide and Unhide Columns dialog box, which Figure 9 shows, clear the check boxes for EmployeeKey under the "In Gemini" and "In Pivot Table" columns. (Gemini was the code-name for PowerPivot. This label might change in future releases.) Click OK.

Following the same procedure, hide the remaining unneeded columns listed in Table 1.

Table 1: Remaining Columns That Need to be Hidden
Data Table	Column to Hide
DimEmployee	EmployeeKey
DimEmployee	ParentEmployeeKey
DimEmployee	EmployeeNationalIDAlternateKey
DimEmployee	ParentEmployeeNationalIDAlternateKey
DimEmployee	SalesTerritory
DimEmployee	SalesTerritoryKey
DimEmployee	FirstName
DimEmployee	LastName
DimEmployee	MiddleName
DimEmployee	NameStyle
DimEmployee	HireDate
DimEmployee	BirthDate
DimEmployee	LoginID
DimEmployee	EmailAddress
DimEmployee	Phone
DimEmployee	Marital Status
DimEmployee	EmergencyContactName
DimEmployee	EmergencyContactPhone
DimEmployee	StartDate
DimEmployee	EndDate
EmployeePromotions	EmployeeKey

Step 9

In the PowerPivot window, click the PivotTable button on the Home tab, then select Four Charts. In the Insert Pivot dialog box, leave the New Worksheet option selected and click OK.

To build the first of the four pivot charts, Overall Morale by Department, select the upper left chart with your cursor. In the Gemini Task Pane, which Figure 10 shows, select the Overall check box under the SurveyResults table. (Note that this pane's label might change in future releases.) In the Values section of the Gemini Task Pane, you should now see Overall. Click it, highlight the Summarize By option, and select Average.

Next, select the DepartmentName check box under the DimEmployee table. You should now see DepartmentName in the AxisFields (Categories) section.

Finally, you need to create the PowerPivot application's slicers. Drag and drop YearOfHire under the DimEmployee table and Promoted under the EmployeePromotions table onto the Slicers Vertical section of the Gemini Task Pane. Your PivotChart should resemble the one shown in Figure 11.

The PivotChart you just created is a simple one. You can create much more detailed charts. For example, you can create a chart that explores how morale improves or declines for employees who were recently promoted. Or you can refine your analysis by selecting a specific year of hire or by focusing on certain departments. Keeping this in mind, create the three remaining PivotCharts, using any combination of attributes and morale measures. (For more information about how to create PowerPivot PivotCharts, see www.pivot-table.com.)

After all four PivotCharts are created, you can clean the application up for presentation, if desired. Select the Page Layout Excel ribbon and clear the Gridlines View and Headings View check boxes in the Sheet Options group. Next, expand the Themes drop-down list in the Page Layout ribbon and select a theme of your personal liking. You can remove a PivotChart's legend by right-clicking it and selecting Delete. To change the chart's title, simply click inside it and type the desired title.

Want to Learn More?

After providing you with a quick overview of PowerPivot, I concentrated on the client experience by discussing how to create a PowerPivot application that leverages a variety of data sources. If you want to learn about the PowerPivot server-side experience or DAX, there are several helpful websites. Besides Microsoft's PowerPivot site (powerpivot.com), you can check out PowerPivotPro (powerpivotpro.com), PowerPivot Twins (powerpivottwins.com), Great PowerPivot FAQ (powerpivotfaq.com/Lists/TGPPF/AllItems.aspx), or PowerPivot and DAX Information Hub (powerpivot-info.com).