Contents
Overview 1
Building the Store Expense Cube 2
Lab A: Building the Store Expense Cube 7
Updating the Store Expense Cube 12
Lab B: Modifying the Store Expense Cube 13
Review 20

Module 7: Case
Study—Creating the
Store Expense Cube

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.


SQL Server
™
2000 Analysis Services. In the
labs, students are given high-level procedures and must recall the specific steps
and actions for creating various dimension and cube structures. Students create
a preliminary cube, and then must make changes to the cube by applying
dimension and level properties.
After completing this module, students will be able to:
!
Create a cube based on user requirements.
!
Update dimensions and add new dimensions to a cube.

Materials and Preparation
This section provides the required materials and preparation tasks that are
needed to teach this module.
Required Materials
To teach this module, you need the following materials:
!
Microsoft PowerPoint
®
file 2074A_07.ppt

Preparation Tasks
To prepare for this module, you should:
!
Read all of the student materials.
!
Read the instructor notes and margin notes.
!

1. In Analysis Manager, right-click the server, click Restore Database, click
the Look in list, find and click the file
C:\Moc\2074A\Labfiles\L07\Answers\Module 07A.CAB, click Open, and
then click Restore.
2. Expand the Module 07A database, double-click the Data Sources folder,
right-click the Module 07 data source, and then click Edit.
3. Verify that localhost is selected in step 1 and that Module 07 is selected in
step 3. Click OK.

!
To view the Store Expense cube
1. Expand the Cubes folder, right-click Store Expense, and then click Edit.
2. Point out that the cube contains the dimensions shown in the following
table.
Dimension Dimension type

Scenario Shared
Time Private
Store Shared
Store Manager Private
Account Shared
Department Shared

3. Mention that the in real-world OLAP databases, Time dimensions usually
are defined as shared dimensions used within multiple cubes. The lab
contains a private Time dimension to challenge students by presenting a
different method for creating a Time dimension.
4. Highlight the fact that the cube contains one measure—Amount.
5. Drill down on each dimension and verify that students have the correct
number of levels in each dimension and that the levels are in the correct

To view the Store Expense cube
1. Expand the Cubes folder, right-click Store Expense, and then click Edit.
2. Point out that the cube contains the dimensions shown in the following
table.
Dimension Dimension type

Scenario Shared
Time Private
Store Shared
Store Manager Private
Account Shared
Department Shared
Store Type Virtual

3. Browse the cube, and notice the Scenario dimension.
The Scenario dimension does not contain the All Scenario member.
4. Notice the Time dimension. It does not contain the All Time member.
5. Pivot the Store dimension to the row area in the browser.
6. Double-click the Store Country level heading, double-click the Store State
level heading, and notice that the Mexico state is hidden.
7. Drill down to the Store Name level and notice the names of the stores.
They contain both the city name and the store name.
8. Browse the Store Manager dimension, and notice the First Letter level
that groups the store managers by the first letter of their names.
9. In the Store Manager dimension, notice that no null member names exist at
the First Letter level. All null member names are hidden.

Demonstration:
10 Minutes
vi Module 7: Case Study—Creating the Store Expense Cube

1. Where in Analysis Manager do you make changes to existing dimensions?
Where you update existing dimensions depends on the type of
dimension that requires modification. You modify shared dimensions in
the Dimension Editor. You modify private dimensions in the Cube
Editor.
2. Why do you want a dimension to be balanced in structure?
When a dimension is balanced, you take advantage of precalculations
for cube aggregations. Therefore, user queries execute faster than if the
queries were made against dimensions without precalculated
aggregations.
Balanced dimensions contain the same number of levels in each
dimension branch. You create a balanced dimension by defining a
standard dimension (not a parent-child dimension) when you create it
in the Dimension Wizard or the Dimension Editor.
Unbalanced dimensions must be defined as parent-child dimensions,
and do not have the same number of levels in each dimension branch.
Parent-child dimensions provide flexibility in dimension structure,
allowing for complex hierarchies such as charts of accounts and
organizational structures. However, query performance can be slower
when you use parent-child dimensions than when you use standard
dimensions, because aggregates are not stored for parent-child
dimensions.
3. In lab B, why do students process the Store dimension after updating the
Member Name Column of the Store Name level?
Students process and rebuild the Store dimension after the update of
the Member Name Column because change to the dimension structure
affects the processing of the cube. Without the rebuild, cube processing
fails in the Store Expense cube. The dimension does not force a rebuild
at cube processing time, which is why students rebuild the dimension in
the Dimension Editor.

A, open the archived OLAP database and explain the Store Expense cube.
!
Updating the Store Expense Cube
Explain to students that they will modify the Store Expense cube in lab B.
After students complete lab B, open the archived OLAP database and
explain the Store Expense cube.

Module 7: Case Study—Creating the Store Expense Cube 1

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Overview
!
Building the Store Expense Cube
!
Updating the Store Expense CubeIn this module, you will apply what you learned about the topics covered in
modules 1 through 6 in course 2074A, Designing and Implementing OLAP
Solutions with Microsoft SQL Server 2000, by creating an online analytical
processing (OLAP) cube and several dimensions from start to finish.
After completing this module, you will be able to:
!
Create a cube based on user requirements.
!
Update dimensions and add new dimensions to a cube. Topic Objective

Analyzing the MeasuresYou will build the Store Expense cube to analyze expenses for stores. The
expenses exist for all stores in each of the store geographies. You will track
expenses by store managers to determine the amount that each manager spends.
In addition, you will determine expenses by store departments.
Expenses are stored in two years: 2000 and 2001. For now, all the data that
exists in the database corresponds to current year actual expenses. However, in
the future, the Store Expense cube will be used to create the expense budget.
Therefore, budget and forecast members exist in the Scenario dimension to
store the budgets.
In the future, Net Sales values will be used to calculate Net Income from Total
Expenses. However, the current database does not contain Net Sales values.
Therefore, the Net Income values will be negative.
In this section, you will learn about the schema of the database used to create
the Store Expense cube by using Microsoft
®
SQL Server
™
2000 Analysis
Services. In addition, you will learn about the dimensions and measures that
you will create in the Store Expense cube.
Topic Objective
To introduce the first section
of the module, which
introduces the database,
dimensions, and levels in
the Store Expense cube.
Lead-in

position
!
department
Topic Objective
To introduce the Store
Expense schema that
students use to build the
Store Expense cube.
Lead-in
You will be creating the
Store Expense cube by
using the tables in the
Module 07 SQL Server
2000 database.
Delivery Tip
Deliver this page either by
using the slide directly or by
opening the diagram Store
Expense Schema in SQL
Server Enterprise Manager.
4 Module 7: Case Study—Creating the Store Expense Cube

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Introducing the Dimensions and Levels
!
Scenario
!
Time

!
Quarter
!
Month
Verify that the Time dimension levels are specified with the proper Level Type
properties.
Topic Objective
To introduce the dimensions
and levels created in the
Store Expense cube.
Lead-in
You will create six
dimensions in the Store
Expense cube.
Delivery Tips
For classes with more
experienced students,
briefly highlight the fact that
they will be creating several
dimensions in lab A. Do not
cover the dimension
structures in detail.

For less advanced classes,
use this page as an
opportunity to prepare
students for the lab by
discussing each of the
dimensions in detail.
Module 7: Case Study—Creating the Store Expense Cube 5

Use the descriptions of Department and Position to define the member names.

6 Module 7: Case Study—Creating the Store Expense Cube

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Analyzing the Measures
MeasureYou will create one measure in the Store Expense cube named Amount. You
create only one measure because the Account dimension contains various
expense and revenue accounts that aggregate based on a rollup column.
The amount column in the expense_fact table is the source of the Amount
measure. You define the measure when you initially create the Store Expense
cube.

Topic Objective
To describe the use of
measures in the Store
Expense cube.
Lead-in
You will create one measure
in the Store Expense cube
named Amount.
Module 7: Case Study—Creating the Store Expense Cube 7

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY



BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

For More Information
This module uses the Analysis Manager to create a new cube with several
dimensions. For more information, see the following references.
The Dimension Editor
For more information about using the Dimension Editor interface and creating
dimensions, see module 4, "Building Dimensions Using the Dimension Editor,"
and module 5, “Using Advanced Dimension Settings,” in course 2074A,
Designing and Implementing OLAP Solutions with Microsoft SQL Server 2000.
The Cube Editor
For more information about using the Cube Editor interface, see module 6,
"Working with Cubes and Measures," in course 2074A, Designing and
Implementing OLAP Solutions with Microsoft SQL Server 2000.
Estimated time to complete this lab: 30 minutes

Module 7: Case Study—Creating the Store Expense Cube 9

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Exercise 1
Creating the Store Expense Cube
In this exercise, you will create a new cube, Store Expense, which contains six
dimensions with varying specifications, levels, and structures. You will process
the cube and confirm that the cube is correct by verifying cube values.
The lab provides high-level procedures for you to follow. If you do not
remember the specific steps for a procedure, refer to the modules outlined in the
For More Information section of the lab.

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Use the following table to create the dimensions with various dimension
specifications.
Dimension Name Dimension Type Levels/Members

Scenario Shared One level with four
members: Adjustment for
Budget input, Current
Year’s Actuals, Current
Year’s Budget, Forecast
Time Private Three levels: Year,
Quarter, Month
Store Shared Four levels: Store
Country, Store State,
Store City, Store Name
Store Manager Private One level: Store Manager
Account Shared Parent-child hierarchy
with Account Description
member names
Department Shared Three levels: Area,
Department, Position

2. Name the cube Store Expense.
3. Process the cube, without defining aggregations or storage mode.

!
To verify cube results
1. In the cube browser, verify that you retrieve $2,527.11 for the following
member combination:

in the Instructor Notes.

Encourage students to
follow along with the
demonstration by comparing
their Store Expense cube
to the archived version that
you demonstrate.
12 Module 7: Case Study—Creating the Store Expense Cube

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Updating the Store Expense Cube
!
Updating the Dimensions
$
Scenario
$
Time
$
Store
$
Store Manager
$
Account
!
Creating the Dimension
$
Store Type

Store Expense cube.
Module 7: Case Study—Creating the Store Expense Cube 13

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Lab B: Modifying the Store Expense Cube Objectives
After completing this lab, you will be able to:
!
Make changes to existing dimensions.
!
Add dimensions to a cube.
!
Verify results of a cube.

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

Lab Setup
To complete this lab, you need to first complete lab A, Building the Store
Expense Cube, found earlier in the module.

Exercise 1
Modifying the Store Expense Cube
In this exercise, you will update the Store Expense cube, adding a new
dimension, modifying existing dimensions, and verifying results.
The lab provides high-level procedures for you to follow. If you do not
remember the specific steps for a procedure, refer to the modules outlined in the
For More Information section of the lab.
If you still cannot determine the proper steps for a single procedure, refer to the
corresponding answer files, which are located in:
C:\Moc\2074A\Labfiles\L07\Answers
!
To update the Scenario dimension
1. In the Module 07 database in Analysis Manager, expand the Shared
Dimensions folder, right-click Scenario, and then click Browse Dimension
Data.
2. Notice the member All Scenario.
It does not make sense to have an All member in the Scenario dimension,
because the four Scenario members do not add up to a meaningful value.
3. Remove the All Scenario member from the Scenario dimension.

!
To update the Time dimension
• Remove the All Time member from the Time dimension.

!
To update the Store dimension
1. Open the Dimension Editor for the dimension Store.
2. Browse the dimension in the Data pane and then notice the two Mexico
members: