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.
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.
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.