Reporting Options for Analysis Services Cubes: Microsoft Excel 2003
MS Office 2003 continues to build upon the
robust OLAP reporting features for the desktop user that began to appear, in
earnest, in MS Office 2000. The basic operation of the PivotTable
report has remained the same as with previous versions. We will examine
the PivotTable report in a hands-on fashion in the sections that follow,
to update the information we provided in Reporting Options for Analysis Services Cubes: MS Excel
2002. Next we
will examine another Excel 2003 OLAP reporting option, the Microsoft Office
Excel Add-in for SQL Server Analysis Services, discussing its capabilities
and providing information regarding where to obtain this exciting add-in.
PivotTable Reports in MS Office Excel 2003
When a PivotTable
report accesses a multidimensional cube, it receives data from a specified Analysis
Server via the PivotTable Service. As the first exercise in our
tutorial, we will create a PivotTable report that accesses the Warehouse
sample cube that comes along with Analysis Services. We will use the PivotTable
Wizard found in Excel 2003 in our initial efforts. The procedures
we cover are essentially the same for Excel 2000 and 2002,
although some of the terminology used in the latter, as well as the appearance
of dialogs / other objects, differ, to a degree.
The Wizard accesses
the Microsoft Query application in Excel to build a query file. Query
file creation is a one-time event for any given PivotTable report, and
defines the connection between Microsoft Excel and the Analysis
Connecting Excel to
Wizard walks us through a guided process for connecting Excel 2003 to
a given Analysis Services cube. We begin by taking the following steps:
1. Open a new Excel 2003 workbook.
2. Click Data (top menu), then
select PivotTable and PivotChart Report, to initialize the PivotTable
and PivotChart Wizard, as shown in Illustration 1.
Illustration 1: Initializing
the PivotTable / PivotChart Wizard ...
The PivotTable and
PivotChart Wizard - Step 1 of 3 dialog appears.
3. Select the External Data Source
radio button on the Step 1 of 3 Wizard dialog.
the radio button to the immediate left of "PivotTable" is
selected in the lower half of the dialog, underneath "What kind of
report do you want to create?"
The PivotTable and
PivotChart Wizard - Step 1 of 3 dialog appears as depicted in Illustration
Illustration 2: The Step
1 0f 3 Wizard Dialog, with Settings
and PivotChart Wizard - Step 2 of 3 dialog appears, as shown in Illustration
Illustration 3: The Step
2 0f 3 Wizard Dialog
we specify the source of our data. For this tutorial, we will use the sample Warehouse
cube that accompanies an installation of Analysis Services.
Click the Get
Query starts, and
presents the Choose Data Source dialog.
Click the OLAP
dialog box appears as depicted in Illustration 4.
Illustration 4: The
Choose Data Source Dialog
highlight <New Data Source>, as necessary.
New Data Source dialog appears.
following in Box 1 of the dialog, where we name the
OLE DB Provider for OLAP Services 8.0 in Box 2.
New Data Source dialog appears as shown in Illustration 5.
5: The Create New Data Source Dialog
Click the Connect...
Connection dialog appears.
Ensure that the Analysis
server radio button is selected as the location of the multidimensional
data source we wish to access.
In the Server text box,
type the name of the server.
Multidimensional Connection dialog appears, as depicted in Illustration
6 (where we see the name of one of my servers, MOTHER).
6: The Multidimensional Connection Dialog
NOTE: The name localhost can be supplied,
if Excel and the cube share the same server, according to the Microsoft
The next dialog of the Multidimensional
Connection appears, asking that we select the targeted Analysis Services
database. Here we will select the FoodMart 2000 database that
accompanied the Analysis Server installation, as shown in Illustration
7: Select the FoodMart 2000 Database
NOTE: The databases that appear within the selection list
of the Multidimensional Connection - Select the database ... dialog will
differ, depicting those Analysis Services databases that exist within
your own environment.
The Create New Data
Source dialog reappears, with the new target data source indicated to the
right of the Connect... button.
Select the Warehouse
cube in Box 4
of the dialog, where we notice
that all cubes in the Foodmart 2000 Analysis Services database appear.
After selecting the Warehouse
cube, the Create New Data Source dialog should resemble that depicted in
8: The Completed Create New Data Source Dialog
return to the Choose Data Source dialog.
the Warehouse Cube data source remains selected, as shown in Illustration
Illustration 9: Our
Warehouse Cube Data Source is Selected
Click OK, to return to the Step 2 of 3
dialog, where we left off with the PivotTable and PivotChart Wizard.
2 of 3 dialog appears, as depicted in Illustration 10. Note that a
new message, "Data fields have been retrieved," now appears to
the right of the Get Data button.
10: Indication that Data Fields have been Retrieved (Message Circled)
An empty PivotTable
report appears, allowing us to begin browsing the cube / designing the
report immediately. In addition to the PivotTable report template, the PivotTable
toolbar and the PivotTable Field List appear; the PivotTable
Field List provides a selection of report building components (we discuss
these in the next section).
The Analysis Server
is now providing the dimension and measures information to the PivotTable
report directly from the cube.