Contents
Overview 1
Office 2000 OLAP Components 2
Using Excel PivotTables 4
Using PivotCharts 11
Lab A: Creating PivotTables and
PivotCharts 14
Working with Local Cubes 20
Creating OLAP-Enabled Web Pages 24
Lab B: Working with Local Cubes and Web
Pages 26
Review 29

Module 13: Using Excel
as an OLAP Client

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Information in this document is subject to change without notice. The names of companies,
products, people, characters, and/or data mentioned herein are fictitious and are in no way intended
to represent any real individual, company, product, or event, unless otherwise noted. Complying
with all applicable copyright laws is the responsibility of the user. No part of this document may
be reproduced or transmitted in any form or by any means, electronic or mechanical, for any
purpose, without the express written permission of Microsoft Corporation. If, however, your only
means of access is electronic, permission to print one copy is hereby granted.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual

®
Service (PTS) is bundled with Microsoft Office 2000.
PTS works not only with Microsoft SQL Server
™
2000 Analysis Services, but
also with data sources such as relational databases.
Microsoft Excel 2000, a major component of Office 2000, provides powerful
online analytical processing (OLAP) functionality to developers and users.
This module is an overview of Excel 2000 OLAP features. It gives students the
opportunity to create and manipulate the various Office 2000 OLAP interfaces.
After completing this module, students will be able to:
!
Understand the various Microsoft Office 2000 OLAP features.
!
Create a PivotTable from an OLAP cube.
!
Create PivotCharts.
!
Create local cube files.
!
Create a Web page containing Pivot Web components.
Materials and Preparation
This section lists the required materials and preparation tasks that you need to
teach this module.
Required Materials
To teach this module, you need the following materials:
!
Microsoft PowerPoint
®
file 2074A_13.ppt

The following demonstration procedures provide information that will not fit in
the margin notes or is not appropriate for student notes.
In this demonstration, you will learn how to create a PivotTable that connects to
OLAP cubes.
!
To restore a new database and define a data source
1. In Analysis Manager, right-click the server, click Restore Database, click
the Look in list, click the file C:\Moc\2074A\Labfiles\L13\Module
13.CAB, click Open, click Restore, and then click Close.
2. Double-click Module 13 to expand the database.
3. Below Module 13, double-click the Data Sources folder, right-click the
Module 13 data source, and then click Edit.
4. Click the Connection tab of the Data Link Properties dialog box, and then
verify that localhost is selected in step 1.
5. In step 2, verify that Use Windows NT Integrated security is selected.
6. In step 3, verify that Module 13 is selected.
7. Click Test Connection and verify that the test succeeded. Click OK twice.

!
To define a data source
1. Click Start, point to Programs, and then click Microsoft Excel.
2. From the empty Excel worksheet, click the Data menu, and then click
PivotTable and PivotChart Report.
Step1 of the PivotTable and PivotChart Report Wizard appears.
3. From the Where is the data you want to analyze pane, click External data
source, and then click Next.
Step 2 of the wizard appears, which contains a button to specify the external
data source.
4. Click Get Data.
Microsoft Query starts and the Choose Data Source dialog box opens.

The file has the name you gave to the data source, with .oqy—for OLAP
Query—as the extension, and is registered with the Microsoft Excel OLAP
Query file type. Double-clicking the OLAP query data source file starts
Excel and creates a new PivotTable report based on that data source.

6. The Choose Data Source dialog box appears again. Click OK.
7. Step 2 of the PivotTable Wizard appears again. Click Next and then click
Finish.
A skeletal PivotTable report appears on the worksheet, along with a
PivotTable toolbar that displays all the dimensions and measures in the
cube. Note
vi Module 13: Using Excel as an OLAP Client

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Other Activities
Difficult Questions
Below are difficult questions that students may ask you during the delivery of
this module and answers to the questions. These materials delve into subjects
that are within the scope of the module but are not specifically addressed in the
content of the student notes.
1. Can you export an Office PivotList back to an Excel PivotTable report?
Yes. To do so, click the Export To Excel toolbar button. Interestingly, if
you show only selected levels in a PivotTable list and then export that

!
Office 2000 OLAP Components
Begin by defining the Office 2000 OLAP components—PivotTable
Services, Excel 2000 PivotTables, Office Pivot Web control, and local
cubes—and describe how each component is used to access OLAP data.
!
Using Excel PivotTables
In this section, you show how to create, manipulate, and format a
PivotTable based on an OLAP cube. Integrate your lecture with a
demonstration. First, show how to define the data source. Next, show how
to use the PivotTable toolbar and how to drill down and pivot in a
PivotTable. Describe how to use various methods that enhance PivotTable
reports, such as filtering and refreshing. Finish the section by describing
various formatting options for PivotTables.
!
Using PivotCharts
Explain that Excel 2000 creates PivotChart
®
reports that are fully interactive
and integrated with PivotTable reports. Emphasize that a PivotChart is
always linked to a PivotTable. Describe the two methods of creating a
PivotChart and integrate your lecture with a demonstration showing students
how to create a PivotChart from an existing PivotTable report. Finish by
describing some unique characteristics of PivotCharts.
!
Working with Local Cubes
Define local cubes as structurally complete OLAP cubes that reside on
client computers. Compare and contrast local cubes and server-based cubes.
Explain that you can create a local cube from either an OLAP source or a
relational source. Combine your lecture with a demonstration showing how

Service (PTS) is bundled with Microsoft Office 2000.
PTS works not only with Microsoft SQL Server
™
2000 Analysis Services, but
also with other data sources, such as relational databases.
Microsoft Excel 2000, a major component of Office 2000, provides powerful
online analytical processing (OLAP) functionality to developers and users.
This module is an overview of Excel 2000 OLAP features. It gives you the
opportunity to create and manipulate the various Office 2000 OLAP interfaces.
After completing this module, you will be able to:
!
Understand the various Office 2000 OLAP features.
!
Create a PivotTable from an OLAP cube.
!
Create PivotCharts.
!
Create local cube files.
!
Create a Web page containing Pivot Web components.

Topic Objective
To provide an overview of
the module topics and
objectives.
Lead-in
In this module, you will learn
about the OLAP
components available in
Office 2000.

Data Objects (Multidimensional) (ADO MD) object interface.
Because PTS communicates with relational databases, PTS provides OLAP
functionality for organizations that do not use Analysis Server.

You do not need to install PTS on every client computer. To provide
OLAP capability in a thin-client environment that does not require much
memory usage on client computers, you can install PTS on another computer
that acts as a middle tier and provides OLAP access for the client computers.

Topic Objective
To introduce the various
Office 2000 OLAP
components.
Lead-in
These are the Office 2000
OLAP components that are
discussed in this module.
Delivery Tips
Briefly describe each of the
components and define how
they are used to access
OLAP data.

Except for PTS, do not go
into detail, because each
component is covered later
in the module.
Tip
Module 13: Using Excel as an OLAP Client 3


!
Excel 2000 contains an interface for defining local cubes that is covered in
this module.
!
Local cubes can be created programmatically.

Note
4 Module 13: Using Excel as an OLAP Client

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY #
##
#

Using Excel PivotTables
!
Defining a Data Source
!
Interacting with a PivotTable
!
Working with PivotTables
!
Formatting PivotTablesIn versions of Excel before version 2000, a PivotTable report used a memory-
cache method for providing values to the report. In this method, the PivotTable
imported values from an Excel list or a database table, and stored the values in

Encourage students to
follow you on their
computers. In the following
lab, students are asked to
build a PivotTable report by
using the wizard.
Module 13: Using Excel as an OLAP Client 5

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Defining a Data Source Creating an Excel PivotTable report based on an OLAP cube is a
straightforward process. You use the Excel PivotTable Report Wizard, which
uses the Microsoft Query application to define and create an OLAP query file.
The OLAP query file provides all the information necessary to connect to the
OLAP Server cube. When Microsoft Query returns control to the Excel
PivotTable Report Wizard, the wizard uses the OLAP query file to connect to
the server cube.
You can connect to a regular cube, a virtual cube, or a linked cube in a
PivotTable. Before you can connect to a cube—whether regular, virtual, or
linked—the cube must be processed and available for client queries. In addition,
you must be given security access to the cubes on the Analysis Server before
you can connect to them.
After you create the PivotTable report, neither Microsoft Query nor the OLAP
query file is used again unless you want to create a new PivotTable report.
The first time you build a PivotTable against an OLAP cube, there are many
steps involved. A number of these steps relate to defining a data source.

you can connect to a cube,
either regular or virtual, the
cube must be processed
and available for client
queries. In addition, you
must be given security
access to the cubes on the
Analysis Server before you
can connect to them.

6 Module 13: Using Excel as an OLAP Client

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Interacting with a PivotTable An Excel PivotTable report is similar in some ways to the cube browser
included with the Analysis Manager, but it allows more control over the
appearance of the resulting values.
A PivotTable report consists of four areas. The first three areas—the Page
Fields area, the Column Fields area, and the Row Fields area—contain member
names, and are called axes. The fourth area, the Data Items area, contains the
values of the measures.
The PivotTable Toolbar
The PivotTable toolbar contains one button for each measure, plus one button
for each non-measure dimension. Following are the button identifications:
!
A single row of buttons in the toolbar contains either measures or

various selection, pivot, and
drill down operations.

Encourage students to
follow along with your
informal demonstration,
including trying out toolbar
options on their own.

In the lab that follows,
students will create
PivotTable reports on their
own.
Module 13: Using Excel as an OLAP Client 7

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

The following are things you can do from the toolbar to arrange the layout of
the PivotTable:
!
Drag one or more dimensions from the PivotTable toolbar to the Row Fields
area.
!
Drag one or more dimensions to the Column Fields area.
!
Drag one or more dimensions to the Page Fields area.
!
Drag at least one measure to the Data Items area—a PivotTable must have
at least one data item.
You can place more than one dimension in the page, row, and column regions.

You use page fields—slicer dimensions, in OLAP terminology—to filter the
data in a PivotTable. Click the dropdown arrow next to the member name, and
select a member from the outline to act as the filter.
When you select a member in a page field, the entire PivotTable report retrieves
data for that one member of the dimension. If you want to include multiple
members from a dimension, move the dimension from the page field to a
column or a row field.
Highlighting a Structured Selection
Structured selection is a feature that simplifies viewing complex PivotTables.
When you click a member, data pertaining to that member is highlighted, even
when the cells are not contiguous.
To select cells in this manner, point just to the left of a row member, or just
above a column member, until the mouse pointer becomes a small black arrow,
and then click the member.
Topic Objective
To introduce various
methods to enhance
PivotTable reports.
Lead-in
The following are additional
capabilities that enhance the
ability to manage the data
and organization of a
PivotTable report.
Delivery Tips
Continue with the informal
demonstration showing
various features from this
section.


cell in the PivotTable and then click Table Options. This displays the
PivotTable Options dialog box. Select the Refresh on Open check box. The
PivotTable then automatically refreshes when the workbook opens.
10 Module 13: Using Excel as an OLAP Client

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Formatting PivotTables You use the PivotTable Options dialog box to implement various formatting
options. You can access this dialog box by right-clicking any cell in the
PivotTable and then clicking Table Options.
There are other ways to apply formatting to PivotTables and PivotTable cells:
!
Applying AutoFormats
The easiest way to format a PivotTable is by using an AutoFormat. Right-
click any cell in the PivotTable, and then click Format Report. The
AutoFormat dialog box opens. You then choose from a gallery of styles.
The AutoFormat persists, even as you manipulate the PivotTable.
!
Formatting cells
You can format specific cells in a PivotTable by right-clicking the cell and
clicking Format Cells.
!
Formatting Measures
Another method for formatting a measure is to right-click any single cell in
the given measure and click Field Settings. Next, click the Number button
to display Excel’s standard numerical formatting dialog.
Using PivotCharts In Excel versions before Excel 2000, it was possible to create charts based on
the data in a PivotTable. However, such charts were not entirely integrated with
PivotTables—for example, the chart would not update properly to synchronize
with changes in the PivotTable layout.
Excel 2000 creates Microsoft PivotChart
®
reports that are fully interactive and
integrated with PivotTable reports. A PivotChart is always linked to a
PivotTable. For example:
!
Changing a PivotTable report automatically updates a PivotChart.
!
Manipulating buttons on a PivotChart changes the layout of the PivotTable
report.

Because of this linkage, there is no way, for example, to create a chart that
shows dates as the X-axis labels if that chart is based on a PivotTable that
shows dates as column headings. Moving the dates to the X-axis of the chart
will move dates to the row axis of the PivotTable report.
If you want the layout of a PivotTable report to be independent of a PivotChart
report, you must copy the PivotTable report before creating the PivotChart.
Topic Objective
To demonstrate
PivotCharts.
Lead-in

PivotChart.

To create and manipulate a PivotChart from an existing PivotTable report,
perform the following steps:
1. From an existing PivotTable report, right-click any cell, and then click
PivotChart.
A new sheet, Chart 1, is added to the workbook immediately before the
worksheet containing the PivotTable.
2. Manipulate the dimensions and measures by using the same PivotTable
toolbar you worked with earlier.
Any changes that you make to the PivotChart update the original
PivotTable.
3. Switch back to the PivotTable worksheet to see the changes made by the
PivotChart manipulations.

Charting Characteristics
PivotCharts are similar to normal Excel charts in many respects, but they have
special characteristics:
!
Dragging the dimension buttons can manipulate the chart and underlying
table. These buttons provide functionality similar to the PivotTable
counterparts.
!
When the layout of the PivotTable is changed, the chart responds to the
change, and vice versa. Changing the chart type entails the same techniques as with normal Excel
charts, and is outside the scope of this course. For more information, refer to the
Microsoft Excel Help.

throughout your lecture, and
covered all topics found in
this demonstration, you may
want to skip this
demonstration, and have
students perform lab A now.

Tell students that some of
the demonstration
procedures are repeated in
the lab. If students follow
along with your
demonstration, they can
skip the identical procedures
in the lab.
14 Module 13: Using Excel as an OLAP Client

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Lab A: Creating PivotTables and PivotCharts Objectives
After completing this lab, you will be able to:
!
Create a PivotTable.
!
Create a PivotChart.


!
To define a data source
1. Open Microsoft Excel.
If Excel is already open, switch to an empty worksheet.
2. From an empty Excel worksheet, click the Data menu, and then click
PivotTable and PivotChart Report.
Step 1 of the PivotTable and PivotChart Report Wizard appears.
3. From the Where is the data you want to analyze pane, click External data
source, and then click Next.
Step 2 of the wizard appears, which contains a button to specify the external
data source.
4. Click Get Data.
Microsoft Query starts and the Choose Data Source dialog box opens.
5. Click the OLAP Cubes tab and then click <New Data Source> from the
list. Click OK.

16 Module 13: Using Excel as an OLAP Client

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

!
To define the cube
1. In the Create New Data Source dialog box, type Market Sales for the data
source in entry 1.
2. For entry 2, click Microsoft OLE DB Provider for OLAP Services 8.0
from the list, and then click Connect.
The Multidimensional Connection dialog box appears.
3. Click the Analysis Server option, and type localhost in the Server box.
Click Next.
A list of databases defined on the Analysis Server displays.

5. Drag the Time dimension button to the column axis of the PivotTable
report, which is cell B3 on the worksheet.
The Year members appear as column headings.
Module 13: Using Excel as an OLAP Client 17

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

6. Drag the State dimension button to the page axis of the PivotTable report,
which is cell A1 of the worksheet.
You can choose whether to display a dimension button on the page axis. If
you do not include a dimension on a PivotTable report, it behaves as if it
were on the page axis with the default member selected.

!
To drill down to descendants
1. Double-click the Bread category member in the row axis.
The subcategories that are children of Bread appear.
2. Double-click Bread again to hide the children.
3. Click the Category level button above the Bread member.
4. On the PivotTable toolbar, click Show Detail to drill down to the children
of all members at the Category level.
5. Click the drop-down arrow next to the Category button.
The members of the Category level appear, each with a check box that
contains a double check mark. The double check mark indicates that the
member is visible, along with at least some of its children.
6. Expand Bread, and then clear the check box next to Bread.
This hides the Bread member along with all its children.
7. Select the check box to put a single check in the box, and then click OK.
All the descendants of Bread disappear. A single check mark indicates that
the member is visible but all its children are hidden.