Assembling the Solution

Our solution will take advantage of PowerPivot for Excel’s ability to load data from a SQL Server database to the SQL Server Analysis Services database installed by the PowerPivot add-in for Excel. After defining a connection to SQL Server, specifically to the AdventureWorksDW2008R2 database, we will construct a table of Internet sales, summarized by product key. Finally, we will apply a sort and a value filter to get the top ten products, ranked by all-time Internet sales.

SQL Server As a PowerPivot Data Source

One of the easier data sources to set up for use by PowerPivot is a SQL Server database. After installing the sample databases, your SQL Server 2008 R2 instance should contain a database named AdventureWorksDW2008R2. In this section, we will configure the connection between PowerPivot for Excel and the AdventureWorks corporate data warehouse in SQL Server.

To begin, from a new Excel worksheet, select the PowerPivot Window ribbon element to open the PowerPivot user interface. From within PowerPivot, select the From Database item contained in the Get External Data set of ribbon items, as illustrated in Figure 2-1. Finally, launch the Table Import Wizard by selecting the “From SQL Server database” option.

Figure 2-1. Importing from SQL Server

Starting the Table Import Wizard

The Table Import Wizard begins your guided path through the process of getting data from the SQL Server database table into PowerPivot’s SSAS datastore. The first step is to configure the database server and other parameters to establish the connection between PowerPivot and SQL Server. To complete this first dialog, you need only enter localhost for the Server name, leave the default Use Windows Authentication radio selection, and choose AdventureWorksDW2008R2 for the “Database name”. If you are utilizing a SQL Server database that does not reside on your local machine, you will substitute the server name and authentication mode that applies to your environment. Figure 2-2 shows these choices.

Figure 2-2. Table import connection dialog

When your Table Import Wizard connection dialog looks similar to Figure 2-2, click the Test Connection button to ensure you can connect to SQL Server and access the AdventureWorksDW2008R2 database. If you see anything but a “connection succeeded” message, verify your SQL Server Developer Edition and SQL Server 2008 R2 Sample Databases installations. If your connection succeeds, click the Next button to continue the Table Import Wizard.

Wrong data import dialog? If the data import dialog box has the title Data Connection Wizard, as in Figure 2-3, you have attempted to create a connection from Excel, not from the PowerPivot for Excel window. Click the PowerPivot menu from within Excel to reveal a ribbon of PowerPivot selections. Choose PowerPivot Window to get back the PowerPivot for Excel window.

Figure 2-3. Excel’s Data Connection Wizard

Selecting the Table

The next step in the Table Import Wizard is the selection of the table that contains the data we want to manipulate in PowerPivot. The default radio button at the next step, “Select from a list of tables and views to choose the data to import,” will accommodate this perfectly. Alternatively, if you possess skills and experience with SQL, the other radio selection could be used to write a query as the source of the PowerPivot import.

At this point, a list of the tables contained in the AdventureWorksDW2008R2 database is presented in a dialog box similar to the one shown in Figure 2-4. Select the FactInternetSales table by clicking the check box as shown. The PowerPivot Table Import Wizard will generate a friendly name for the table, placing it in the Friendly Name column of the selection list. In your own solutions, you may want to alter the table name prior to import by overwriting this value. Clicking the Finish button will begin the import process; PowerPivot will load data from the table into the PowerPivot for Excel SSAS database for you to work with locally.

Figure 2-4. Table selection list

Monitoring the Import

The next sign of data import work being performed by PowerPivot will be a view of the import process, similar to Figure 2-5. When you begin working with other data sources, the Message area may indicate information related to any import errors that occur. However, for our sample dataset, you should see all 60,398 records successfully loaded.

Figure 2-5. Table import success

Reviewing the Results

The PowerPivot interface will display all columns and rows for our imported data. The bottom scroll bar can be used to bring columns in the far right of the FactInternetSales table into view. Similarly, the right-hand scroll bar can be used to move additional rows into view. Alternatively, the record count in the bottom left-hand corner of the PowerPivot window can be used to navigate to the first, last, or a specific record number in the active PowerPivot table. The example FactInternetSales PowerPivot data table is illustrated in Figure 2-6.

In addition to importing the data into the in-memory SQL Analysis Services database, PowerPivot has also added metadata (column and table names) to the PowerPivot data table. With a SQL Server database as the data source, the column names for our PowerPivot table are identical to the column names in the source database. For instance, the first column of the FactInternetSales table in the SQL Server database is named ProductKey. Likewise, the first column in our destination PowerPivot table is named ProductKey. Additionally, the source table from which we imported the Internet sales data was named FactInternetSales. Therefore, PowerPivot’s Table Import Wizard has named the resulting PowerPivot table FactInternetSales.

Figure 2-6. Import complete

Data Refresh

Because a connection to SQL Server was defined, this PowerPivot solution will have the ability to refresh the PowerPivot FactInternetSales table from the SQL Server database. This can be done within the PowerPivot for Excel interface, via the Refresh selection in the Get External Data area of the PowerPivot ribbon. Additionally, using PowerPivot for SharePoint’s scheduled workbook refresh feature can import new data into a PowerPivot solution in an unattended, scheduled scenario (see Chapter 7).

Creating the Report

With the data from FactInternetSales successfully imported into PowerPivot, the report of top ten products, by Internet sales, can be swiftly created. Since the PowerPivot window is for interacting with the content and structure of data, and the Excel window contains the feature set for assembling reports and charts, we will need to be in the Excel window. The Excel icon in the upper left-hand corner of the PowerPivot window will bring the Excel workbook back to the forefront, allowing the creation of our PowerPivot report.

Within the Excel Workbook view, select the PowerPivot ribbon. From the PowerPivot ribbon, insert a PivotTable into the current worksheet using the menu selection shown in Figure 2-7. Next, place the cursor in the PivotTable; this will cause the PowerPivot Field List to appear in the right-hand side of the Excel window.

Figure 2-7. Inserting a PivotTable

If you have used Excel PivotTables, the PowerPivot Field List may be familiar. The PowerPivot Field List is the primary interface for placing data into tables (PivotTables) and charts (PivotCharts) within the Excel workbook. Moving data from a row to a column of a PivotTable is accomplished by dragging a field from the top window of the field list into one of the Row, Column, or Values windows within the PowerPivot Field List interface.

The PowerPivot Field List also contains features for the unique slicer feature of PowerPivot. A PowerPivot slicer is a user interface component that implements a data-aware means of selecting sets of data for analysis. Slicers will be described in greater detail and employed in Chapter 3.

Additionally, the PowerPivot Field List contains features for creating custom calculations using Data Analysis Expressions (DAX). Data Analysis Expressions and the related included functions comprise the language for programming PowerPivot calculations from both the data source in the PowerPivot window and the workbook via the PowerPivot Field List.

To continue the AdventureWorks Top Ten Sales report, drag the ProductKey from the top window in the PowerPivot Field List to the Row Labels area. This will rather quickly place a row for each of AdventureWorks’ 158 products, with a row in the FactInternetSales table, into the PivotTable located in the worksheet. Similarly, drag SalesAmount from the field list to the Values window in the bottom right-hand area of the PowerPivot Field List. PowerPivot will respond by adding a column titled Sum of SalesAmount to the PivotTable in the worksheet. At this point, we have the unique identifier (ProductKey) for every product AdventureWorks has sold via the Internet channel and the total sales for each.

Narrowing to the Top Ten

As our task was to create a table of the top ten products by all-time sales through the Internet channel, we are only halfway to our goal. We have the ProductKeys, which for our “Hello World” exercise, we will assume are well known throughout the organization. Relating a key value to get a description is something we will cover in Chapter 4. We could, rather inelegantly, sort the table in descending order by the Sum of Sales Amount column, print the resulting worksheet, and draw a line to indicate the top ten. But this is PowerPivot, and we have more graceful means of accomplishing our goal.

Narrowing our list of all products to the top ten items by sales is a simple as applying existing PowerPivot functionality. Clicking the context menu drop-down to the right of the Row Labels text in the PivotTable will reveal a sort and filter context menu similar to the one shown in Figure 2-8. Selecting Value Filters and then Top 10 will predictably generate a dialog box prompting for parameters by which to determine the Top 10 in our PivotTable. PowerPivot, in its infinite wisdom, will determine by default how to sort the column, and the default values for the remaining settings will be fine for our “Hello World” example. In truth, PowerPivot will use any measure in the PivotTable as the basis of the sort. Because our PivotTable has only one measure, we are assured of using the correct measure. Clicking OK at the sort options dialog will narrow our product table to the top ten by Sum of SalesAmount.

Figure 2-8. The Row Labels menu

At this point, you should have a PivotTable that looks similar to the one shown in Figure 2-9. Because this is still Excel, we can apply formatting to our values and change the column headers to make a more professional-looking report. In Figure 2-9, a number format has been applied to the Sum of SalesAmount column. Additionally, the column headers have been renamed by simply typing over the values to rename the first column to Product Key and the second to Total Sales.

Figure 2-9. Final top ten products table

At this point, the PowerPivot for Excel solution can be saved just like any Excel file, choosing any file name within the existing limits of the Operating System and Excel. I have named the example Chapter2.xlsx, in my local My Documents folder. If you choose a different filename or location, make note so you will be able to follow along in the exploration of what PowerPivot for Excel is doing in the next section.