Excel 2007's Powerful PivotTables

Excel 2007's Powerful PivotTables

New features boost Excel's ability to analyze SQL Server Analysis Services OLAP cubes

In the Microsoft business intelligence (BI) platform, the SQL Server Analysis Services (SSAS) 2005 OLAP engine is the component of choice for summarizing and presenting vast amounts of data. On the desktop, though, Microsoft Excel is typically the tool of choice for analyzing, organizing, and making sense of data. It's no surprise, then, that most OLAP tools offer some type of integration with Excel. The built in PivotTable (and PivotChart) reports in Excel have traditionally provided one way of browsing OLAP cubes—albeit with certain restrictions and missing functionality. I'll highlight several key new PivotTable enhancements that make Microsoft Office Excel 2007 a much better client for SSAS 2005 OLAP cubes. (I'll discuss Excel 2007's integration with SSAS's data-mining features in an upcoming article.)

PivotTable Evolution

PivotTable reports make it possible to aggregate large amounts of data across different categories of interest and to more easily analyze, explore, and present this data. When Microsoft Excel 2000 was introduced, users had the option of using an OLAP cube as the data source for a PivotTable. This feature let users analyze much greater volumes of data than they could in a spreadsheet and also allowed much of the CPU- and memory-intensive computation to occur on the server. However, as an OLAP client, Pivot Tables had two major drawbacks:

  • Many of the advanced SSAS features—such as drill-through/actions, member properties, and server-side formatting—weren't recognized or available.
  • PivotTables are effectively "locked." In other words, you can't insert additional rows or columns in a PivotTable, and referencing PivotTable data in other spreadsheet cells is problematic.

Microsoft addressed some of these drawbacks when it introduced the Excel Add-in for SQL Server Analysis Services, a free download for Microsoft Office Excel 2003 and Excel 2002 that offers a PivotTable–like experience—but with better SSAS support and integration with "native" Excel functionality. (For more information about the add-in, see "Integrating SQL Server & Office 2003," May 2006, InstantDoc ID 49688 and the Web-exclusive article "Microsoft Accelerates in BI Space," June 2004, InstantDoc ID 43076.) In Excel 2007, most of the features in the Excel add-in are incorporated directly into the built-in PivotTables, along with many new features. Let's take a closer look at some of those features.

Getting Started: Establishing a Connection

To work through the examples in this article, make sure you have access to an instance of SSAS 2005 with the AdventureWorks sample SSAS database. This sample database isn't installed by default. You'll need to download the database (included with several other sample databases) at http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf79f46-4312-af89-6ad8702e4e6e&displaylang=en and follow the installation instructions at http://msdn2.microsoft.com/en-us/library/ms143804.aspx or from the SQL Server 2005 CD-ROM. I also recommend you apply SQL Server 2005 Service Pack 2 (SP2) to the SSAS instance so that all Excel 2007's features will be enabled. (See the SQL Server 2005 SP2 Readme file for more information about SP2's support of Microsoft Office 2007 system BI features.) Then, open Excel 2007 and click the Insert Ribbon tab. Note that most Office 2007 applications (Microsoft Office Outlook 2007 is a notable exception) now use a UI element called the Ribbon, which replaces the traditional menus and toolbars. You'll see the PivotTable command at the far left of the Ribbon; click this command to display the Create PivotTable Window. Select the Use an external data source option, then click Choose Connection.

Here you'll see the first major enhancement to Excel in the form of enhanced connection-management capabilities, as Figure 1 shows. In addition to seeing connections specific to the worksheet and your computer, you'll also see a section called Connection files on the Network. This set of connections is created from an Excel Services Data Connection Library (DCL) on a Microsoft Office SharePoint Server 2007 site (Excel Services delivers Excel as a thin client through SharePoint). Setting up (and distributing) connections on an end-user machine can take a bit of work, so—if your organization deploys SharePoint 2007—these network connections will help reduce complexity.

Figure 1: Excel 2007's connections-management feature

Go ahead and click Browse for More, then click New Source. Select Microsoft SQL Server Analysis Services as the data source. Enter your server name. Then, in the Select Database and Table window, select the AdventureWorks DW database, then select the Finance perspective. In the Save Data Connection File and Finish window, click Finish to conclude the wizard, then click OK in the Create PivotTable window. You should now see a PivotTable in your worksheet and a PivotTable Field List, as Figure 2 shows. At the top of the Excel Ribbon, you'll also see the text PivotTable Tools (if you don't see this text, make sure the PivotTable has been selected in your worksheet). When you click the text, you'll see the Ribbon's Options tab, which contains different PivotTable and PivotChart commands.

Figure 2: Creating a new PivotTable to work with data from the AdventureWorks database

An Enhanced Field List

The first thing you might notice in the PivotTable Field List is the built-in support for measure groups—a new feature in SSAS that allows cubes to be built from multiple fact tables. In our example, the Exchange Rates and Financial Reporting measure groups are separately displayed at the top of the field list; you can also filter the entire field list (by using the Show fields related to drop-down box at the top of the list) to display only the measures, dimensions, or Key Performance Indicator (KPIs) related to a specific measure group. The field list also recognizes dimension display folders (e.g., notice the Calendar and Fiscal folders in the Date dimension), named sets (e.g., the Summary P&L Set in the Account dimension), and KPIs. At the bottom of the field list, you'll see four different areas: Report Filter, Column Labels, Row Labels, and Values. These areas represent a new way of authoring reports.

A New report-Authoring Experience

In the field list's Financial Reporting measure group area, look at the Amount measure. Notice that Amount shows up in the PivotTable. If you've applied SP2 to the SSAS instance, notice also that the server-side formatting (in this case, currency) is recognized. Amount also appears at the bottom of the field list in the Values area. Next, in the Account dimension, check the Accounts hierarchy; the hierarchy will appear in the PivotTable (as rows) and in the field list's Row Labels area. Finally, expand the Date dimension's Fiscal folder and check the Date.Fiscal hierarchy; it will appear in the PivotTable (as columns) and in field list's Column Labels area. If you followed me so far, your PivotTable and field list should look like those in Figure 3. (Note that I've rearranged my field list by using the Fields Section and Areas Section Stacked view so that the areas are displayed side by side with the field section.)

Figure 3: PivotTable containing AdventureWorks data and PivotTable field list

Next, try to add and rearrange fields on the report. Unlike the old PivotTable drag-and-drop experience in Excel 2003, you now add or remove fields by simply checking or unchecking them. Likewise, to rearrange a field in a report (e.g., move a field from a row to a column), you simply drag and drop a field between the areas in the field list. Although I'm experienced with PivotTables in Excel 2003, I've very quickly come to prefer this new report-authoring approach. But if you decide you like the "old way" of doing things, you can right-click a PivotTable, select the PivotTable Options menu item, then check the Classic PivotTable layout option in the Display tab. While you're in this Options window, you might also want to select the Show calculated members from OLAP server check box to ensure that server-defined calculated members are visible.

Another change in PivotTable reports has to do with the default row layout when navigating a dimensional hierarchy (or when displaying two or more dimensions on a row). For example, if you expand the Balance Sheet dimension member (to expand a dimension, click the + sign next to the dimension name in the PivotTable report), notice how the two child dimensions (Assets and Liabilities and Owners Equity) are indented—but still remain in the same column. This feature lets you navigate hierarchies without additional columns being allocated—nice for maintaining screen real estate and/or when you've defined other Excel calculations to the right of your PivotTable.

OLAP Formulas

At the beginning of the article, I mentioned that one drawback of PivotTables is they're "locked." This is still the case with Excel 2007; you can't insert rows or columns into a PivotTable. But you can now convert a PivotTable to a set of OLAP formulas— which will then let you take full advantage of Excel's free-form capabilities. (To convert the PivotTable, from the Ribbon's Options Tab, select the OLAP tools command, then select Convert to Formulas.) Figure 4 shows a PivotTable that I've converted. Notice that the selected cell, B8, is defined using the CUBEVALUE function. There are seven new CUBE functions in Excel 2007 that let you fetch dimension members, measure values and member properties, and perform other related tasks. Notice also the Gross Margin% row (row 9), doesn't come from the cube; rather, I inserted this row and used standard Excel calculations to reference the OLAP-based Gross Sales and Gross Margin values in each respective column.

Figure 4: PivotTable converted to OLAP formulas

Here are three other important points about OLAP formulas:

  • Notice in Figure 4 that the row filter (Date.Fiscal Year) still has a filter symbol in its value cell. When you convert a PivotTable to OLAP formulas, you have the option of retaining the row filters. This option gives you the best of both worlds in that you can customize your worksheet (e.g., insert new columns and rows) while still having the ability to filter the OLAP data by different dimension members.
  • You don't have to start with a PivotTable to use OLAP formulas. Rather, you can use these formulas on a cell-by-cell basis.
  • Converting a PivotTable to a set of OLAP formulas is a one-way operation; you can't revert back to a PivotTable.

More Great Features... and a Missing One

Several other PivotTable features that are new in Excel 2007 improve upon Excel's OLAP integration. They include

  • Member-property support. Member properties (which are now simply referred to as attributes in SSAS 2005) are visible when hovering over a dimension member. Attributes let you see additional information about a dimension member (e.g., a phone number for a customer). You can optionally choose to have attributes displayed in a PivotTable report alongside the dimension member.
  • KPI support. Excel 2007 can display the value, goal, status, and trend for a KPI—including the graphical indicator defined for a KPI's status and trend.
  • Support for actions. Drill-through is now considered a cube action, and Excel 2007 does a nice job of returning drill-through results into a new, formatted worksheet.
  • Enhanced filtering. With SSAS 2005 SP2 applied, you can filter dimension members according to date, label, and measure values.

Notwithstanding all the new PivotTable features, one that's missing is the ability to create calculated fields (what you'd think of calculated members with respect to an OLAP cube). On the Options tab in the Excel Ribbon, there's a command called Formulas (located in the Options tab's Tools section); for OLAP reports, the Formulas command is disabled. Although OLAP Formulas provide a workaround, I'm still hoping calculated fields for OLAP will be enabled in a future service pack or release.

OLAP and More

Excel, in my opinion, will continue to be the desktop tool of choice for analyzing, organizing, and making sense of data. The OLAP-specific enhancements in Excel 2007 greatly improve its usefulness as a means to analyze and manipulate data from OLAP cubes. In addition to the features I've covered here, Excel 2007 also offers an enhanced PivotChart experience—backed by a new graphics engine to provide better charting and visualization capabilities. For more examples, details, and discussions concerning Excel 2007 and SSAS, check out the Microsoft Excel 2007 blog at http://blogs.msdn.com/excel/archive/tags/analysis+services/default.aspx

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.