Contents
Overview 1
Introduction to Cubes 2
Working with Cubes 5
Introduction to Measures 11
Lab A: Creating Cubes with the Cube Editor 15
Working with Measures 19
Defining Cube Properties 28
Using the Disabled Property 30
Lab B: Using Properties in the Cube Editor 32
Review 35

Module 6: Working with
Cubes and Measures

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

Microsoft
®
SQL Server
™
2000 Analysis Services to create and manipulate
cubes, add measures and dimensions, and assign properties to improve cubes.
After completing this module, students will be able to:
!
Define the required components of cubes.
!
Create cubes by using the Cube Editor.
!
Describe the characteristics of measures.
!
Assign properties to measures.
!
Modify cube properties by using the Cube Editor.
!
Disable levels of shared dimensions.

Materials and Preparation
This section provides the materials and preparation tasks that you need to teach
this module.
Required Materials
To teach this module, you need Microsoft PowerPoint
®
file

2074A_06.ppt


that are within the scope of the module but are not specifically addressed in the
content of the student notes.
1. Will the Validate Cube Structure tool in the Cube Editor find most
problems with a cube?
The Validate Cube Structure tool does not find most problems
associated with a cube. It notifies you if a cube does not contain at least
one measure and at least one dimension. In addition, it notifies you if
not all tables in the cube schema are joined to at least one other table.
However, most errors in a cube are found in the underlying data in the
fact table and dimension tables. Therefore, the only method of ensuring
that the cube is complete and error-free is to process the cube, to watch
for errors in the Process dialog box, and to verify correct data values
within a cube browser.
2. How can you move easily from the Cube Editor to the Dimension Editor to
update shared dimensions?
There is no way to easily move from the Cube Editor to the Dimension
Editor. You must first close the Cube Editor and then open the
Dimension Editor for that dimension. In addition, you cannot minimize
the Cube Editor or any windows in Analysis Manager.
3. How do you add a measure to a cube when the measure is not in the fact
table?
All measures must be found in the fact table defined in the cube
schema. If your cube requires a measure from another fact table, you
can create a view in the relational database management system
(RDBMS) and build a cube directly from the view. You can also create
another cube with the same dimensions and then create a virtual cube
that combines the measures of the two cubes and their shared
dimensions.
4. How do you model measures that require a hierarchy, such as a chart of
accounts?

create simple cubes, add dimensions and measures, and view properties in
the Cube Editor.
!
Working with Measures
Describe each of the measure properties found in the Cube Editor. Explain
each of the Aggregate Function properties and give examples of when to
use Sum, Count, Min, Max, and Distinct Count. Define derived measures
and discuss their limitations in creating calculations.
!
Defining Cube Properties
Explain each of the cube properties available in the Cube Editor. Discuss the
real-world application of each property.
!
Using the Disabled Property
Explain the Disabled property used in the Cube Editor to disable dimension
levels. Finish the module with lab B, which highlights several measure,
dimension, and cube properties.


Module 6: Working with Cubes and Measures 1

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Overview
!
Introduction to Cubes
!

Topic Objective
To provide an overview of
the module topics and
objectives.
Lead-in
In this module, you will learn
about the Cube Editor and
how it can be used to create
and manipulate cubes, add
measures and dimensions,
and assign properties to
improve cubes.
2 Module 6: Working with Cubes and Measures

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

Introduction to Cubes
!
Characteristics of Cubes
!
Analysis Services LimitationsAn online analytical processing (OLAP) cube uses dimension hierarchies to
summarize measures from a fact table. A cube must always have at least one

Converted to Multidimensional Structures
!
Contain Precalculated Summaries of Data Called
Aggregations
!
Contain up to 128 DimensionsUsers interact with OLAP databases by connecting to and manipulating cubes.
The following are important characteristics of cubes:
!
Cubes combine dimensions and measures into one logical storage medium.
!
Cubes provide the mechanism for rapid and uniform response to complex
queries by users.
!
Cubes usually contain subsets of data pulled from a data warehouse at the
lowest level that are then organized into multidimensional structures and
aggregated into more summarized levels.

Relational OLAP (ROLAP) cubes store data in a collection of tables
found in the source relational database management system (RDBMS).
ROLAP cubes store their data in tables, but they have the option to store
dimension data in multidimensional structures.

!
Cubes contain summaries of data called aggregations, which precalculate
according to the aggregation design of the cube.
!
Despite their three-dimensional description, cubes typically contain many

128Dimensions per cube
64 kilobytesRecord size for cube’s source
database tableWhen designing cubes and measures, you need to know the programmable
limits of Analysis Services. In most cases, the limits far exceed real-world
requirements. However, cube and measure requirements sometimes require
changes because of the limitations.
The following table lists some published limits of Analysis Services.
Item Limit

Dimensions per cube 128
Levels per cube 256
Measures per cube 1,024
Calculated members per cube 65,535
Record size for cube’s source database
table
64 kilobytes
Topic Objective
To describe the
programmed limits
associated with cubes and
measures in Analysis
Services.
Lead-in
When designing cubes and
The Cube Editor is one of the primary interfaces for refining and changing
cubes and dimensions. In this section, you explore the basic elements of the
Cube Editor and learn how to use it to create and edit cubes.
You can quickly build a cube by using the Cube Wizard. However, many of the
properties available in the Cube Editor are not available in the Cube Wizard. If
you create a cube by using the Cube Wizard, you will probably use the Cube
Editor later to refine the cube and measures.
Topic Objective
To introduce the basics of
working with cubes.
Lead-in
In this section, you explore
the basic elements of the
Cube Editor and learn how
to use it to create and edit
cubes.

6 Module 6: Working with Cubes and Measures

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Working with the Cube Editor
!
Accessing the Cube Editor
!
Cube Editor Elements
page.
Module 6: Working with Cubes and Measures 7

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Cube Editor Elements
The Cube Editor is similar to the Dimension Editor. The left pane contains a
tree pane at the top, which shows the components of the cube. The left pane
also has a Properties pane at the bottom that contains properties for the cube, its
dimensions, and its dimension levels.
The Cube Editor includes the following major screen elements:
!
Tree pane. Displays folders for dimensions, measures, calculated members,
actions, and named sets. Right-click the items in the tree pane to display the
context-sensitive menus.
!
Properties pane. The Properties button toggles the display of the Properties
pane. The Properties pane contains property settings for the selected item in
the tree pane
Properties differ based on the type of item selected. For example, measures
have different properties from dimensions.
!
Schema pane. The pane on the right displays the underlying RDBMS
schema when the Schema tab is clicked.
!
Preview pane. When you click the Data tab, the pane on the right displays a
cube browser, which allows cube viewing.

8 Module 6: Working with Cubes and Measures


The Cube Editor adds any tables used to build a shared dimension to
the cube schema when you insert an existing shared dimension. If the
dimension key found in the dimension table has the same name as the
dimension key found in the fact table, a join is created automatically. If the
Cube Editor does not create the join automatically, you can create a join
manually by dragging the key from the dimension table to the key in the fact
table.

Topic Objective
To describe the process of
creating a new cube by
using the Cube Editor.
Lead-in
The following steps take you
through the process of
creating a new cube by
using the Cube Editor.
Key Point
The Validate Cube
Structure tool confirms that
the cube contains at least
one measure and at least
one dimension, and that all
tables included in the cube
schema have legitimate
joins.
Note
Module 6: Working with Cubes and Measures 9

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY


BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Setting Dimension Properties
!
Setting Properties for Private Dimensions
$
Administration occurs in the Cube Editor
$
Any level property can be modified
!
Setting Properties for Shared Dimensions
$
Properties apply to all cubes that contain the dimension
$
Administration occurs in the Dimension EditorYou set dimension properties in the Dimension Editor or the Cube Editor. The
editor you use depends on whether the dimension is private or shared.
Setting Properties for Private Dimensions
With a private dimension, you use the Cube Editor, not the Dimension Editor,
to edit all of the dimension properties. You use the Cube Editor’s Properties
pane to change the properties such as the dimension name and the dimension
type.
If a particular level in a private dimension is selected, you can modify all of the
level properties, such as Member Key Column, Member Name Column,
Member Keys Unique, and so on.
Setting Properties for Shared Dimensions
With the exception of properties such as disabling levels and setting

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

Introduction to Measures
!
Defining Measures
!
Adding, Deleting, and Modifying MeasuresMeasures are the numeric data of primary interest to the users of a cube.
Understanding the characteristics and mechanics of measures is fundamental to
working with cubes.
Topic Objective
To introduce the concept of
measures.
Lead-in
Measures are the numeric
data of primary interest to
the users of a cube.
Understanding the
characteristics and
mechanics of measures is
fundamental to working with
cubes
12 Module 6: Working with Cubes and Measures

All cubes by definition contain a measures dimension. The measures in a cube
appear as members of the measures dimension. The measures dimension is
different from other dimensions in several respects.
Measures
The following items are characteristics of measures:
!
A measure must be numeric. In other words, a measure is always
quantitative in nature, such as the number of units, average price, or dollars.
!
A measure must originate from columns in the fact table.
!
When you add, delete, or modify a measure in a cube, the aggregations of
the cube must be redesigned.
!
Each measure is aggregated uniformly across all other dimensions by one of
the five aggregate functions.

Custom rollups, custom rollup formulas, and custom member
formulas allow you to define members that override the default aggregation
functions of measures. For more information on these topics, see module 5,
“Using Advanced Dimension Settings,” and module 11, “Implementing
Calculations Using MDX,” in course 2074A, Designing and Implementing
OLAP Solutions with Microsoft SQL Server 2000.

!
Measures are stored in the cube and consume disk space after the cube is
processed.
Topic Objective
To define several
characteristics of measures

You do not share measures across multiple cubes. The measures dimension
is always private.

14 Module 6: Working with Cubes and Measures

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Adding, Deleting, and Modifying Measures
!
Adding a Measure to a Cube
!
Deleting a Measure from a Cube
!
Modifying a Measure in a CubeThe Cube Wizard walks you through the steps required to create a cube. Once a
cube is defined, however, the only way to add, delete, or modify a measure is
by using the Cube Editor.
Adding a Measure to a Cube
To add a measure to a cube by using the Cube Editor, perform the following
steps:
1. In the Cube Editor, right-click the Measures folder, and then click New
Measure.
2. Select a column from the fact table.

Deleting a Measure from a Cube
To delete a measure from a cube, perform the following step:
!
In the Cube Editor, right-click the measure and click Delete.

Delivery Tip
Open the Cube Editor and
demonstrate how to add,
delete, and modify
measures in cubes.
Importan
t

Module 6: Working with Cubes and Measures 15

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Lab A: Creating Cubes with the Cube Editor Objectives
After completing this lab, you will be able to:
!
Create a new cube by using the Cube Editor.
!
Add measures and dimensions to a cube.

Prerequisites
Before working on this lab, you must have:
!
Experience working with dimensions.
!
An understanding of dimensions, cubes, and measures.


Module 06 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 the list of servers in step 1.
5. In step 2, verify that Use Windows NT Integrated security is selected.
6. In step 3, verify that Module 06 is selected.
7. Click Test Connection and verify that the test succeeded. Click OK twice. !
To create a new cube
1. In Analysis Manager, in the Module 06 database, right-click the Cubes
folder, click New Cube, and then click Editor.
The Cube Editor opens and you see the Choose a fact table dialog box.
2. Click Salesfact, and then click OK. Click Yes to count the fact table
records.
You see the Salesfact table in the Schema pane on the right side of the Cube
Editor.
3. Drag Sales_Dollars from the Salesfact table to the Measures folder in the
Cube Editor tree pane.
4. Double-click State_ID in the Salesfact table.
5. In the Map the Column dialog box, click Dimension, and then click OK.
You see a new dimension beneath the Dimensions folder—State Id. Notice
that there is not a hand beneath the arrows on the icon for the State Id
dimension. This indicates that State Id is a private dimension.
6. Click the State Id dimension, and change the dimension name to State in
the Basic tab of the Properties pane.
7. Expand the State dimension and notice the one level in the dimension—
State Id. Change the name of the level to State.
8. Notice that the other properties in the Basic and Advanced tabs in the
Properties pane can be modified.

dimensions already defined in the Module 06 database. You finish by
processing and browsing the cube to test for correct data.

!
To create the new cube
1. In the upper right corner of the Cube Editor, click the Cube list, and then
click New.
2. Click Salesfact, and then click OK. To count the fact table records, click
Yes.
3. Add the Sales_Dollars and Sales_Units measures to the cube.
4. Right-click the Dimensions folder and click Existing Dimensions.
5. Click the double arrow button (>>) to add all dimensions to the Cube
dimensions list, and then click OK.
Notice that all dimensions are added to the Dimensions folder in the tree
pane. In addition, notice the dimension tables in the Schema pane. When
you add a shared dimension to a cube, its dimension tables are automatically
added to the cube schema.
6. Expand the Product dimension, and notice the properties in the Basic and
Advanced tabs for each of the levels in the Product dimension.
Most properties are disabled because Product is a shared dimension. To
update the properties of a shared dimension, leave the Cube Editor and edit
the dimension by using the Dimension Editor.

!
To process, save, and browse the cube
1. In the Cube Editor, click the Data tab and browse sample data in the new
cube.
2. To verify that the cube contains all required components and joins, on the
Tools menu, click Validate Cube Structure. Click OK.
3. On the Tools menu, click Process Cube. Click Yes to save the cube, and

In this section, you will learn about:
!
Defining measure properties.
!
Aggregating measures.
!
Working with derived measures.
Topic Objective
To introduce the mechanics
of working with measures.
Lead-in
In this section, you will learn
about basic measure
properties, the Aggregate
Function property, and the
use of derived measures in
cubes.