Module 13: Using Excel

as an OLAP Client

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
Information in this document, including URL and other Internet Web site references, is subject to
change without notice. Unless otherwise noted, the example companies, organizations, products,
people and events depicted herein are fictitious and no association with any real company,
organization, product, person or event is intended or should be inferred. Complying with all
applicable copyright laws is the responsibility of the user. Without limiting the rights under
copyright, no part of this document may be reproduced, stored in or introduced into a retrieval
system, or transmitted in any form or by any means (electronic, mechanical, photocopying,
recording, or otherwise), or for any purpose, without the express written permission of Microsoft
Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual
property rights covering subject matter in this document. Except as expressly provided in any
written license agreement from Microsoft, the furnishing of this document does not give you any
license to these patents, trademarks, copyrights, or other intellectual property.

 2001 Microsoft Corporation. All rights reserved.

Microsoft, MS-DOS, Windows, Windows NT, ActiveX, BackOffice, FoxPro, JScript, MS-DOS,
PivotChart, PivotTable, PowerPoint, Visual Basic, Visual C++, and Visual Studio are either
registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.

© 2001 OLAP Train, LLC. All Rights Reserved. Developed by OLAP Train LLC, for Microsoft
Corporation and distributed under license.

Other product and company names mentioned herein may be the trademarks of their respective
owners.
 Module 13: Using Excel as an OLAP Client iii

Instructor Notes
Presentation: Microsoft® PivotTable® Service (PTS) is bundled with Microsoft Office 2000.
30 Minutes PTS works not only with Microsoft SQL Server™ 2000 Analysis Services, but
also with data sources such as relational databases.
Lab:
30 Minutes 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

Preparation Tasks
To prepare for this module, you should:

Read all the student materials.

Read the instructor notes and margin notes.

Complete the demonstration.

Practice integrating the demonstrations with the lecture.

Complete the labs.

Review the Trainer Preparation presentation for this module on the Trainer
Materials compact disc.

Review any relevant white papers that are located on the Trainer Materials
compact disc.
iv Module 13: Using Excel as an OLAP Client

Demonstration: Creating a PivotTable

The following demonstration procedures provide information that will not fit in
Demonstration: the margin notes or is not appropriate for student notes.
10 Minutes
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.
5. Click the OLAP Cubes tab and then click <New Data Source> from the
list. Click OK.

To define the cube

1. In the Create New Data Source dialog box, type 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.
 Module 13: Using Excel as an OLAP Client v

4. From the database list, click the Module 13 database, and then click Finish.
The Create New Data Source dialog box appears again.
5. For entry 4, click the Sales cube from the list. Click OK to close the dialog
box.

Note Creating an OLAP Cube data source creates a file in the

Application Data\Microsoft\Queries folder for the current user. The location
of the Application Data folder varies depending on the version of Microsoft
Windows® you are using, and whether Windows is set up for multiple users.
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.
vi Module 13: Using Excel as an OLAP Client

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
list to Excel, the resulting PivotTable report will include only the
selected levels.
2. Can you update the subtotal labels in a PivotTable so that they match the
total of only the visible cells?
Unfortunately, you cannot make the subtotals match the total of only
visible cells. However, you can turn off subtotals for dimensions.
3. What happens if the PivotTable toolbar becomes small and does not include
all the dimensions and measures?
Click the PivotTable in the worksheet. The toolbar should return to its
original state.
4. How do you retrieve the PivotTable toolbar if you accidentally close it?
Right-click the Excel toolbar, and click PivotTable from the list of
possible toolbars.
5. Does an Office PivotList support events?
If you write Microsoft Visual Basic® code and are familiar with creating
event handlers to react to the behavior of users, you might be interested
to know that an Office PivotList supports events for numerous user
actions. In contrast, an Excel PivotTable report does not have any
events.
 Module 13: Using Excel as an OLAP Client vii

Module Strategy
Use the following strategy to present this module:

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
to create a local cube from an OLAP source.

Creating OLAP Enabled-Web Pages
Explain to students that Office 2000 makes it easy to create Web pages from
Office documents. Describe and show how to create a Web page from a
PivotTable report, combining the lecture and demonstration. Next, describe
how to manipulate a PivotList—using the Field List, adding and removing
items, and filtering and pivoting.
 Module 13: Using Excel as an OLAP Client 1

Overview
Topic Objective
To provide an overview of
the module topics and
objectives.
Office 2000 OLAP Components
Lead-in
Using Excel PivotTables
In this module, you will learn
about the OLAP
Using PivotCharts
components available in
Office 2000.
Working with Local Cubes

Creating OLAP-Enabled Web Pages

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

Microsoft® PivotTable® 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.
2 Module 13: Using Excel as an OLAP Client

Office 2000 OLAP Components

Topic Objective
To introduce the various
Office 2000 OLAP
components.
PivotTable Service
Lead-in
Excel 2000 PivotTables
These are the Office 2000
OLAP components that are
Office Pivot Web Control
discussed in this module.

Local Cubes

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

Excel 2000 PivotTables are the focus of this module. Other important OLAP
Delivery Tips features exist, however, in Office 2000. This module reviews the various OLAP
Briefly describe each of the
components and define how
components of Office 2000.
they are used to access
OLAP data. PivotTable Service
PivotTable Service (PTS) is the mandatory client component for querying
Except for PTS, do not go
into detail, because each
Analysis Server.
component is covered later While PTS is a component of Analysis Services, it is also bundled with
in the module.
Office 2000. PTS places OLAP functionality on every Office 2000 desktop,
regardless of whether users have access to an Analysis Server.
PTS has no built-in user interface. It is an invisible component that provides
OLAP functionality to Excel 2000, third-party applications, and custom
applications. PTS provides the OLE DB for OLAP and Microsoft ActiveX®
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.

Tip 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.
 Module 13: Using Excel as an OLAP Client 3

Excel 2000 PivotTables

In Excel 2000, PivotTables are Analysis Server-aware and, more precisely,
OLE DB for OLAP-aware. As a result, you use PivotTables to analyze large
data sets, unlike earlier versions of Excel.

Note An Excel PivotTable is not the same object as PivotTable Service (PTS).
However, Excel uses PTS when manipulating PivotTables.

PivotTables provide basic OLAP functionality to Excel users. They provide an

intuitive interface that allows slice and dice functionality—pivoting, drill-down,
drill-up, and so on.
In addition, charts are now OLAP-aware and provide OLAP functionality with
direct manipulation of the chart.

In order to create PivotTables based on external data from OLAP or relational

databases, you must install Microsoft Query. Query is not installed by default as
part of the Office 2000 installation. You must specify it explicitly during setup.

Office Pivot Web Control

Several ActiveX components are bundled with Office 2000 and are designed for
use on Web pages, Microsoft Visual Basic® forms, and other ActiveX
containers.
One of the ActiveX components is the Pivot Web control. The pivot component
functions similarly to an Excel PivotTable. In this module, you will learn how
to create a Web page that includes the pivot component.

Local Cubes
PTS supports the ability to create local cubes derived from an Analysis Server
or from a relational source. This feature is available to users in two forms:

Excel 2000 contains an interface for defining local cubes that is covered in
this module.

Local cubes can be created programmatically.
4 Module 13: Using Excel as an OLAP Client

Using Excel PivotTables

Topic Objective
To introduce the concept of
PivotTables.
Lead-in
Defining a Data Source
In this section, you learn
Interacting with a PivotTable
how to create, manipulate,
and format a PivotTable
Working with PivotTables
based on an Analysis
Server cube.
Formatting PivotTables

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

In versions of Excel before version 2000, a PivotTable report used a memory-
Delivery Tips cache method for providing values to the report. In this method, the PivotTable
Present this entire section
as an informal
imported values from an Excel list or a database table, and stored the values in
demonstration, integrating memory. When a user manipulated the PivotTable report, the report retrieved
your lecture from the slide- necessary values from the memory cache.
driven sections with a
demonstration of building
PivotTable reports have been an extremely popular feature of Excel, but the
and manipulating a memory-cache method for retrieving values limited the amount of data that
PivotTable report from the could be manipulated.
Module 13 Sales cube.
In Office 2000, Excel provides a second method for providing values to a
Encourage students to PivotTable report—an OLAP cube. A PivotTable report can now communicate
follow you on their with the PivotTable Service to retrieve values from Analysis Server.
computers. In the following
lab, students are asked to
In this section, you learn how to create, manipulate, and format a PivotTable
build a PivotTable report by based on an Analysis Server cube.
using the wizard.
 Module 13: Using Excel as an OLAP Client 5

Defining a Data Source

Topic Objective
To define a data source in
Excel.
Lead-in
The first time you build a
PivotTable against an OLAP
cube, there are many steps
involved, including the
definition of a data source.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

Creating an Excel PivotTable report based on an OLAP cube is a
Delivery Tips straightforward process. You use the Excel PivotTable Report Wizard, which
Begin your informal
demonstration starting with
uses the Microsoft Query application to define and create an OLAP query file.
this procedure for defining The OLAP query file provides all the information necessary to connect to the
an OLAP cube and an Excel OLAP Server cube. When Microsoft Query returns control to the Excel
data source. PivotTable Report Wizard, the wizard uses the OLAP query file to connect to
the server cube.
Encourage students to
follow along with your You can connect to a regular cube, a virtual cube, or a linked cube in a
informal demonstration on PivotTable. Before you can connect to a cube—whether regular, virtual, or
their computers. 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
In the lab that follows, you can connect to them.
students will create
PivotTable reports on their After you create the PivotTable report, neither Microsoft Query nor the OLAP
own. 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
Key Point steps involved. A number of these steps relate to defining a data source.
You can connect to a
regular cube or to a virtual
Defining the source is a one-time process per cube per client computer.
cube in a PivotTable. Before
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

Interacting with a PivotTable

Topic Objective
To describe the mechanics
of interacting with a
PivotTable.
Lead-in
A PivotTable report consists
of four areas. The first three
areas—the page area, the
column area, and the row
area—contain member
names, and are called axes.
The fourth area, the data
area, contains the values of
the measures.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

An Excel PivotTable report is similar in some ways to the cube browser
Delivery Tips included with the Analysis Manager, but it allows more control over the
Continue with the informal
demonstration showing
appearance of the resulting values.
various selection, pivot, and A PivotTable report consists of four areas. The first three areas—the Page
drill down operations.
Fields area, the Column Fields area, and the Row Fields area—contain member
Encourage students to
names, and are called axes. The fourth area, the Data Items area, contains the
follow along with your values of the measures.
informal demonstration,
including trying out toolbar The PivotTable Toolbar
options on their own.
The PivotTable toolbar contains one button for each measure, plus one button
In the lab that follows, for each non-measure dimension. Following are the button identifications:
students will create

A single row of buttons in the toolbar contains either measures or
PivotTable reports on their
own.
dimensions, but not both.

An icon at the left of the row of buttons indicates whether the buttons in the
row are measures or dimensions.

You can drag measure buttons—and only measures—to the data area.

You can drag buttons for other dimensions—but not measures—to any of
the axes—page, column, or row.

If a dimension or measure has a long name, the toolbar button does not show
the entire name. If you hover the mouse over a button, Excel displays a screen
tip showing the full name and an additional indicator of whether the button is a
dimension or a measure.
 Module 13: Using Excel as an OLAP Client 7

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 can pivot page, row, and column data to the other axes by dragging the
dimension tiles.

Drilling Down in Dimensions

Much of the benefit of working with an OLAP cube is the ability to drill down
to detailed members, and to drill back up to see higher-level members. An
Excel PivotTable report allows you to navigate members in this manner, either
one at a time, or all the members of a level together.
To drill-down a dimension hierarchy, double-click the member. The children of
the member are displayed unless the member is already at the bottom level.
You can also drill down on a member by selecting the member label and
clicking Show Detail on the PivotTable toolbar. Click Hide Detail to drill up.
8 Module 13: Using Excel as an OLAP Client

Working with PivotTables

Topic Objective
To introduce various
methods to enhance
PivotTable reports.
Filtering
Lead-in
Highlighting a Structured Selection
The following are additional
capabilities that enhance the
Displaying Specific Members
ability to manage the data
and organization of a
Refreshing a PivotTable
PivotTable report.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

The following are additional capabilities that enhance the ability to manage the
data and organization of a PivotTable report.

Delivery Tips Filtering

Continue with the informal You use page fields—slicer dimensions, in OLAP terminology—to filter the
demonstration showing data in a PivotTable. Click the dropdown arrow next to the member name, and
various features from this select a member from the outline to act as the filter.
section.
When you select a member in a page field, the entire PivotTable report retrieves
Encourage students to data for that one member of the dimension. If you want to include multiple
follow along with your
members from a dimension, move the dimension from the page field to a
informal demonstration on
their computers.
column or a row field.

In the lab that follows, Highlighting a Structured Selection

students will create
Structured selection is a feature that simplifies viewing complex PivotTables.
PivotTable reports on their
own.
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.
 Module 13: Using Excel as an OLAP Client 9

Displaying Specific Members

When a dimension is oriented as a row or a column, by default all the members
of the displayed level are shown. However, there may be occasions when you
want to show only certain members.
Use the drop-down arrows next to the dimension names. Clicking a drop-down
arrow causes an outline to be displayed. Select the check boxes for members
you want to display, and clear the check boxes for the members you want to
hide. Two small check marks will cause a drill-down—that is, the children of
the member to be displayed.

Refreshing a PivotTable
If the data in a cube changes, right-click any cell in the table and click Refresh
Data. Analysis Server is re-queried and the data is refreshed.
A pivot operation will also re-query the Analysis Server. There is no option to
turn off a data refresh on pivot operations.
A user might create a PivotTable, save it, and open it several weeks later but
forget to refresh the report. In this case, the user can unwittingly view old data.
To avoid this problem, you can automate the refresh process. Right-click any
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

Formatting PivotTables
Topic Objective
To describe various
formatting options for
PivotTables.
Lead-in
A PivotTable report has
many formatting options.
You will learn about some of
the most useful ones.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

You use the PivotTable Options dialog box to implement various formatting
Delivery Tips options. You can access this dialog box by right-clicking any cell in the
Continue with the informal
demonstration showing
PivotTable and then clicking Table Options.
various features from this There are other ways to apply formatting to PivotTables and PivotTable cells:
section.

Applying AutoFormats
Encourage students to
follow along with your The easiest way to format a PivotTable is by using an AutoFormat. Right-
informal demonstration on click any cell in the PivotTable, and then click Format Report. The
their computers. AutoFormat dialog box opens. You then choose from a gallery of styles.
The AutoFormat persists, even as you manipulate the PivotTable.
In the lab that follows,
students will create

Formatting cells
PivotTable reports on their You can format specific cells in a PivotTable by right-clicking the cell and
own. 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.
The advantage of this technique is that the formatting is applied to the entire
measure, even if the cells are not contiguous, and the formatting persists
even as the measure is pivoted.
 Module 13: Using Excel as an OLAP Client 11

Using PivotCharts
Topic Objective
To demonstrate
PivotCharts.
Lead-in
Excel 2000 creates
PivotChart reports that are
fully interactive and
integrated with PivotTable
reports.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

In Excel versions before Excel 2000, it was possible to create charts based on
Delivery Tips the data in a PivotTable. However, such charts were not entirely integrated with
Continue with the informal
demonstration showing
PivotTables—for example, the chart would not update properly to synchronize
students how to create a with changes in the PivotTable layout.
PivotChart.
Excel 2000 creates Microsoft PivotChart® reports that are fully interactive and
Encourage students to
integrated with PivotTable reports. A PivotChart is always linked to a
follow along with your PivotTable. For example:
informal demonstration on

Changing a PivotTable report automatically updates a PivotChart.
their computers.

Manipulating buttons on a PivotChart changes the layout of the PivotTable
In the lab that follows, report.
students will create
PivotCharts on their own. 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.
12 Module 13: Using Excel as an OLAP Client

Creating a PivotChart
There are two methods for creating a PivotChart:

When creating a new PivotTable, at the first step of the PivotTable wizard,
click the PivotChart option.

After you create a PivotTable, right-click any cell in the table, and then click
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.

Note 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.
 Module 13: Using Excel as an OLAP Client 13

Demonstration: Creating a PivotTable

Topic Objective
To demonstrate the creation
of a PivotTable that
connects to OLAP cubes.
Lead-in
In this demonstration, you
will learn how to create a
PivotTable that connects to
OLAP cubes.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

In this demonstration, you will learn how to create a PivotTable that connects to
Delivery Tips OLAP cubes.
The steps for this
demonstration are included
in the Instructor Notes.

If you performed an
extensive demonstration
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

Lab A: Creating PivotTables and PivotCharts

Topic Objective
To introduce the lab.
Lead-in
In this lab, you will create a
PivotTable and a PivotChart
from the Sales cube.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

Objectives
Explain the lab objectives.
After completing this lab, you will be able to:

Create a PivotTable.

Create a PivotChart.

Estimated time to complete this lab: 15 minutes

 Module 13: Using Excel as an OLAP Client 15

Exercise 1
Creating a PivotTable
In this exercise, you will create and manipulate a PivotTable by using the
Module 13 database.

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

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.
16 Module 13: Using Excel as an OLAP Client

4. From the database list, click the Module 13 database, and then click Finish.
The Create New Data Source dialog appears again.
5. From the list, click the Sales cube from the list. Click OK to close the
dialog.
6. The Choose Data Source dialog 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.

To define the PivotTable dimensions.

1. On the PivotTable toolbar, hover the mouse pointer over the button labeled
Sales U…
The screen tip gives the full name of the measure, Sales Units, and informs
you that you can drag the button to the PivotTable data area. This message
confirms that the button is a measure.
2. Drag the Sales Units button from the PivotTable toolbar onto the data area,
which is labeled Drop Data Items Here.
The PivotTable report changes from a skeletal framework to an actual
report, showing a single cell, the total of Sales Units.
3. Hover the mouse pointer over the button labeled Product.
The screen tip repeats the name of the dimension and informs you that you
can drag the button to the PivotTable. This message confirms that the button
is a dimension. You can drag this button to any axis on the PivotTable
report.
4. Drag the Product dimension button to the row axis of the PivotTable report,
which is cell A4 on the worksheet.
The Category level members appear as row headings.
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.
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.
 Module 13: Using Excel as an OLAP Client 17

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.
8. Click the drop-down arrow next to the State dimension button in the page
axis.
9. Expand North America, USA, and North West, click Washington, and
then click OK.
The list for a dimension on the page axis contains a hierarchy of members,
and each member has a plus sign but not a check box. On the page axis, you
can select only a single member from a dimension. The list for a dimension
on the row or column axis also contains a hierarchy of members, but each
member has a check box. On the row or column axes, you can select
multiple members from a dimension.

To add multiple dimension to a single axis

1. Drag the Year level button from the column axis to the left edge before the
row axis, to the left of column A.
The row axis now has three columns of labels—one for the Year level of
the Time dimension, one for the Category level of the Product dimension,
and one for the Subcategory level of the Product dimension. The drop-
down arrow that appears only on the top level of each dimension is the only
visible indicator of which label starts a new dimension.
2. Drag the Employee dimension button from the toolbar to the row axis, to
the left of the Year level.

Tip Be sure that you drag the Employee dimension and not the Employee
Count measure.

Multiple dimensions—and multiple levels for a single dimension—can exist

on a single axis.
3. Drag the Sales Dollars measure button from the toolbar to the data area.
As soon as you add a second measure to the PivotTable report, the measures
act as if they form a new Data dimension. The default location for the Data
dimension is the row axis, but you can move the dimension to the column
axis.
4. Drag the Data dimension button to the column axis.
You cannot drag the Data dimension to the page axis; it can go on either the
column axis or the row axis. To remove a measure from the Data
dimension, follow the same procedure that you use to hide a member from
an ordinary dimension.
18 Module 13: Using Excel as an OLAP Client

5. Click the drop-down arrow next to the Data dimension button.

6. Clear the check box next to the Sales Dollars measure, and then click OK.
Hiding a measure from the Data dimension simply deletes the measure from
the report. If only one measure remains on the report, the Data dimension
goes away.

To format a PivotTable report

1. In the PivotTable, right-click any cell, and then click Table Options.
2. Select the Merge Labels check box.
3. Look over some of the other formatting options available in the PivotTable,
and then click OK.
Each member’s label is now centered in a single, merged cell next to its
children.
4. On the toolbar, click Undo to change the labels back to the unmerged form.
You might want to show the Category and Subcategory labels for the
Product dimension in a single column, with the Subcategory indented.
You cannot do that directly in a PivotTable report, but you can get the same
effect by applying various formats.
5. Double-click the Category level button.
6. In the PivotTable Field dialog box, click Layout.
7. In the PivotTable Field Layout dialog box, click Show Items In Outline
Form. Leave all the check boxes clear, and then click OK
8. Click OK to close the PivotTable Field dialog box.
Each Category label now has a blank cell to its right in the Subcategory
column. You can now adjust the width of the Category column to make the
row columns appear as one.
9. With the Category level button selected, click the Format menu, point to
Column, and then click Width.
10. Type 1.5 and then click OK.
The Category and Subcategory labels now appear to be part of a single,
indented list.
11. Leave the PivotTable open for the following exercise.
 Module 13: Using Excel as an OLAP Client 19

Exercise 2
Creating a PivotChart
In this exercise, you will create and manipulate a PivotChart starting from the
PivotTable you created in Exercise 1.

To prepare to create a PivotChart

1. In the PivotTable report, drag the Year level button up to the page axis.
2. Drag the State button to the column axis.
3. Drag the CEO button off the PivotTable.
4. Click the Category level button, and then on the toolbar, click Hide Detail.

To create a PivotChart
1. Right-click any cell in the PivotTable, and then click PivotChart.
A new worksheet appears, containing a chart with product category labels
across the bottom, countries listed in the legend, and a Time button above
the chart. These are interactive PivotChart controls.
2. Click the drop-down arrow adjacent to the Time button.
3. Expand the tree, click Quarter 1 under 2001, and then click OK.
You use the PivotChart buttons just as you use the PivotTable report
buttons.
4. Click the Category button, and then on the PivotTable toolbar, click Show
Detail.
The chart changes to show columns for only the subcategories. The
PivotChart is intelligent enough not to show totals from the report; it
displays the Category labels under the appropriate Subcategory labels.
5. Right-click Country, and then from Group and Outline, select Show
Detail.
6. Click Region, and then click Show Detail again.
Once again, the PivotChart shows the appropriate detail, by using the parent
levels of the hierarchy only as part of the labels.
7. On the PivotTable toolbar, click the PivotChart menu, and click Hide
PivotChart Field Buttons.
This removes the buttons, making the chart suitable for printing or for
displaying in a meeting.
8. Switch back to the PivotTable, and notice the changes made to the report
layout.
When you manipulate PivotCharts, the source PivotTables reflect the
changes.
9. Leave the workbook open to create local cubes in the next lab.
20 Module 13: Using Excel as an OLAP Client

Working with Local Cubes

Topic Objective
To introduce the concept of
local cubes.
Lead-in
Defining Local Cubes
Structurally complete OLAP
Creating Local Cubes
cubes that reside on client
computers are called local
cubes.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

Office 2000 includes all the necessary components to create and use cubes
offline—that is, a structurally complete OLAP cube that resides on client
computers. Such offline cubes are called local cubes.
 Module 13: Using Excel as an OLAP Client 21

Defining Local Cubes

Topic Objective
To define local cubes.
Lead-in
A local cube functions in

Are Similar to Server-Based Cubes, but Contain Less
much the same way as a Data
server-based cube.

Can Communicate with Any PTS-Based Client
Application

Can Be Created Two Ways

Excel PivotTable Report

Microsoft Query

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

A local cube functions in much the same way as a server-based cube. It contains
dimensions complete with hierarchies, and it contains measures. You typically
store much less data in a local cube than in a server-based cube, and a local
cube does not include many of the capabilities of a server-based cube. For
example, a local cube does not include aggregations, member properties, shared
dimensions, virtual dimensions, virtual cubes, partitions, or write-back tables.
Any client application that can interact with a server-based cube can also
interact with a local cube, because the client application communicates with
PTS, and PTS can communicate either with the Analysis Server or with a local
cube. PTS is also the component that creates local cubes.
Office 2000 includes two different approaches for creating a local cube. With
either approach, PTS creates the local cube, but the steps to create the local
cube are different in each case.
One option for creating local cubes is to connect to an Analysis Server by using
an Excel PivotTable report, and then use the PivotTable report to create a local
cube that is a subset of the server-based cube. The other option is to use
Microsoft Query to define a local cube.
22 Module 13: Using Excel as an OLAP Client

Creating Local Cubes

Topic Objective
To explain the two types of
data sources that are used
to create local cubes.
Creating a Local Cube from an OLAP Source
Lead-in
Creating a Local Cube from a Relational Source
You can create local cubes
either from an OLAP source
or from a relational
database source.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

You can create a local cube from either an OLAP source or a relational
Delivery Tips database source.
Continue with the informal
demonstration showing
students how to create a Creating a Local Cube from an OLAP Source
local cube from a By using Excel 2000, you can create a local cube that uses an OLAP cube as its
PivotTable.
source. To create a local cube from an OLAP source, perform the following
Encourage students to
steps:
follow along with your 1. Create a PivotTable based off an OLAP cube.
informal demonstration on
their computers. 2. From the PivotTable toolbar, click the PivotTable menu, and then click
Client-Server Settings.
The PivotTable toolbar is the only place in Excel 2000 where the Client-
Server Settings command is located.
3. Click Create local data file.
4. Step through the Create Cube File Wizard, and specify the dimensions,
levels, and top-level members included in the local cube.
5. Name and save the local cube file.

Note You can also create a local cube from an OLAP source
programmatically.
 Module 13: Using Excel as an OLAP Client 23

Creating a Local Cube from a Relational Source

Even if you do not have an Analysis Server available to you, you can create and
use a local cube by creating it directly from a relational data source.
The PivotTable Service, which is included with Office 2000, can create and
access a local cube. To create a local cube directly from a relational data source,
you use the Microsoft Query application. Microsoft Query includes a wizard for
turning a relational data set into a cube.
When you create a local cube, you first create a relational rowset by using
standard SQL. In that rowset, you can join dimension tables with the fact table,
and can create calculated columns.
Once you define the relational rowset, you then use the Create Cube File
Wizard to design dimensions, hierarchies, and measures for the cube.
24 Module 13: Using Excel as an OLAP Client

Creating OLAP-Enabled Web Pages

Topic Objective
To describe the creation of
OLAP-enabled Web pages
in Excel 2000.
Saving PivotTables as Web Pages
Lead-in
Manipulating the OLAP Control
Office 2000 makes it easy to
create Web pages from
Displaying the Field List
Office documents.

Adding and Removing Items

Filtering and Pivoting

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

Office 2000 makes it easy to create Web pages from Office documents.
Delivery Tips Excel 2000 facilitates the creation of OLAP Web pages that use the Office
Continue with the informal
demonstration, showing
Pivot component.
students how to create a The PivotTable converts to an ActiveX Web component. This page is viewable
Web page from a
by any user, though a license for Office 2000 is required even if Office 2000 is
PivotTable.
not installed on the computer.
Encourage students to
follow along with your Saving PivotTables as Web Pages
informal demonstration on
You create Web pages from existing PivotTable reports that access an Analysis
their computers.
Server cube or a local cube as the data source.
To create a Web page from an existing PivotTable report, perform the following
steps:
1. From the File menu in your PivotTable worksheet, click Save as Web
Page.
2. In the Save pane, click Selection:Sheet, and then select the Add
interactivity check box.
3. Click Publish, click PivotTable in the Item to publish list, select the Add
interactivity with check box, and then click PivotTable functionality in
the Viewing options pane.
4. Define the Web page name and file location in the File name box.
5. Select the Open published web page in browser check box, and then click
Publish.

The default browser opens and includes the Pivot Web component. From
here, you manipulate the Pivot Web component and interact with
dimensions, levels, and measures.
Microsoft calls the Pivot Web component a PivotList. PivotLists are similar in
many respects to Excel PivotTables.
 Module 13: Using Excel as an OLAP Client 25

Manipulating the OLAP Control

In an Excel PivotTable report, you interact frequently with dimensions and
members, but not with levels. Levels of a dimension appear and disappear as
you show or hide detail for members on the report. You must display levels in
order from top to bottom—you cannot skip a level—and the button for the top
level is the only one that ever displays a drop-down list.
In an Office PivotList, you interact much more directly with levels in a
dimension. In addition to how PivotLists approach levels, there are several
differences between a PivotList and a PivotTable. The main difference is the
use of the field list in a PivotList.

Displaying the Field List

Adding a dimension to the PivotList requires that the Field List be displayed.
You do this by clicking the Field List toolbar button.
Using the Field List is similar to using the PivotTable toolbar. The Field List
gives you the ability to add dimensions, levels, and members to areas in the
PivotList.

Adding and Removing Items

To add or remove items, you drag a dimension, level or total (measure) from
the Field List to the PivotList.
Like an Excel PivotTable, a PivotList has four distinct regions: Filter region,
row region, column region, and data region.
To remove an item, drag its button outside the PivotList, until a red X displays,
and then drop the item.

Filtering and Pivoting

Filtering in PivotLists is identical to filtering in PivotTables. You click the
drop-down arrow, and then select a member from the outline.
Pivoting in PivotLists is also identical to pivoting in Excel. You drag the
dimension buttons to different regions of the PivotList.
26 Module 13: Using Excel as an OLAP Client

Lab B: Working with Local Cubes and Web Pages

Topic Objective
To introduce the lab.
Lead-in
In this lab, you will create a
local cube and a Web page
from a PivotTable.

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

Explain the lab objectives.
Objectives
After completing this lab, you will be able to:

Create a local cube.

Create a Web page from a PivotTable.

Prerequisites
• To complete this lab, you must first complete lab A, Creating PivotTables
and PivotCharts, found earlier in the module.

Estimated time to complete this lab: 15 minutes

 Module 13: Using Excel as an OLAP Client 27

Exercise 1
Creating a Local Cube
In this exercise, you will create and manipulate a local cube by using the
Module 13 database.

To create a local cube from an OLAP source

1. Switch to the PivotTable you created in lab A, Creating PivotTables and
PivotCharts.
2. From the PivotTable toolbar, click the PivotTable menu and then click
Client-Server Settings.
The Client-Server Settings dialog box appears.
3. Click Create local data file.
This starts the Create Cube File Wizard.
4. In the introduction page, click Next.
5. At step 2 of the wizard, select the Employee dimension checkbox.
Here you specify the dimensions and the levels that you want to include in
the local cube. You do this by selecting the checkboxes of the dimensions
and levels.
6. Click Next.

Note The list of dimensions includes all dimensions in the source cube,
regardless of whether all the dimensions exist in the PivotTable.

7. At step 3 of the wizard, select the CEO check box, and then expand each of
the levels to determine the members that are included in the local cube.
Here you specify which members from the top level of each dimension will
be included in the cube. You do this by selecting the members in the top
levels you previously selected.
8. Click Next.
9. At step 4 of the wizard, save the Sales.cub cube file in
C:\Moc\2074A\Labfiles\L13, and then click Finish.
10. Click OK, and realize that your PivotTable connects to the Sales.cub local
cube file that you created.
11. Open Microsoft Windows® Explorer, and find the Sales.cub cube file in
C:\Moc\2074A\Labfiles\L13.
The file contains all data and structures necessary to connect to the local
cube.
12. Close Windows Explorer, and leave the Excel workbook open for the
following exercise.
28 Module 13: Using Excel as an OLAP Client

Exercise 2
Creating a Web Page from a PivotTable
In this exercise, you will create a Web page from a PivotTable by using the
Module 13 database.

To create a Web page from a PivotTable report

1. From the open PivotTable report, click the File menu and then click Save as
Web Page.
2. In the Save In list, navigate to C:\Moc\2074A\Labfiles\L13.
3. In the middle of the dialog box, click Selection: Sheet, and then select the
Add Interactivity checkbox.
4. Click Publish.
The Publish as Web Page dialog box appears.
5. Below the Item to Publish pane, click PivotTable in the Choose list.
6. In the Add interactivity with list, click PivotTable functionality.
7. Select the Open published web page in browser checkbox.
8. Click Publish.
Microsoft Internet Explorer opens, and the PivotTable Web control appears.
It connects to the local cube that you created in the previous exercise.

To manipulate the PivotList

1. In Internet Explorer, on the PivotList toolbar, click Field List.
2. Click the Employee dimension, click Filter Area in the list at the bottom of
the Field List, and then click Add to.
The Employee dimension appears in the page axis, or in other words the
filter area.
3. Drag the Time dimension from the page axis to the column axis.
You manipulate the dimensions, levels, and members in the PivotList by
using the Field List or by dragging the appropriate tiles to the axes.
4. Create various report layouts by using the Field List and by dragging
dimensions and levels.
5. Close Internet Explorer, and then close Excel.
 Module 13: Using Excel as an OLAP Client 29

Review
Topic Objective
To reinforce module
objectives by reviewing key
points.
Office 2000 OLAP Components
Lead-in
Using Excel PivotTables
The review questions cover
some of the key concepts
Using PivotCharts
taught in the module.

Working with Local Cubes

Creating OLAP-Enabled Web Pages

*****************************ILLEGAL FOR NON-TRAINER USE*****************************

1. When you define an OLAP data source for a PivotTable, is the definition
located on the Analysis Server or the client computer?
The definition is located on the client computer.

2. Can a PivotTable use a virtual cube as its source?

Yes.

3. Is Analysis Server required to create a PivotTable?

No.

4. Can you derive local cubes from relational data sources?

Yes.

5. To browse a Web page that includes a PivotList, must the user have
Office 2000 installed?
No. However, the user must have a valid Office 2000 license.
THIS PAGE INTENTIONALLY LEFT BLANK