Contents
Overview 1
Using Calculated Cells 2
Lab A: Creating a Calculated Cells Formula 17
Review 20

Module 9: Using
Calculated Cells

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.

 2000 Microsoft Corporation. All rights reserved.

Microsoft, BackOffice, MS-DOS, Windows, Windows NT, <plus other appropriate product

!
Retrieve revenues from the Sales cube by using the LookupCube function.

Materials and Preparation
This section provides you with 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 2093A_09.ppt

Preparation Tasks
To prepare for this module, you should:
!
Read all of the materials for this module.
!
Read the instructor notes and margin notes.
!
Practice the lecture and demonstration.
!
Complete the labs.
!
Review the Teacher Preparation materials on the Teacher Preparation
compact disc.

Presentation:
45 Minutes


Module 9: Using Calculated Cells v

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Other Activities
Difficult Questions
The following list presents several 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 in the scope of the module but may not be
specifically addressed in the content of the student notes.
1. Can the LookupCube function retrieve a value from a different database?
No. The cube must be in the same database. If you use linked cubes, you
can technically get values from a cube that is on another server, but it
still must be linked to the same cube.
2. Do you have to process a cube after adding a custom member formula?
Generally, yes. If the dimension is shared, write-enabled, or parent-
child—then you do not need to process the cube after adding a custom
member formula. Otherwise, if the dimension is private, not write-
enabled, or not parent-child, you must perform an incremental update
on the dimension and process the cube using at least the refresh option.
3. Can a custom member formula refer to the value of a calculated member?
Yes. A custom member formula can use any MDX expression that is
meaningful in the context of the cube.
4. Can a LookupCube formula refer to the parent of the current member?
Yes, but it won’t happen automatically. In order to use the Parent
function—or some other member function—you must construct a
string that pieces together the unique name of the member with the rest
of the tuple.
5. What limitations are there on the MDX expression you can use in calculated
cells?

Students follow along
In some cases, you may want to encourage students to follow your live
demonstration on their own computers. This practice works best for simpler
group activities or for a group activity that is not replicated by a later lab.
It is not a problem if a student does not follow your demonstration, or if a
student starts following and then stops before the group activity is
completed. There is no file or structure dependency between group activities
or between a group activity and a later lab.
!
Lab replication of group activity
The exercises in the labs closely follow the group activity procedures but do
not define each step or show the code answer. Encourage students to write
and test the MDX expressions on their own, referring back to the group
activity procedures for clarification. Students may also refer to answer files,
which are available for each procedure in an exercise.
Labs are generally more challenging when students have not followed the
instructor on their own computers during the group activity. However, many
students benefit from the two hands-on experiences of following the group
activity and then completing the labs.
!
Answer files for group activities
Where applicable, answer files are provided for each procedure in group
activities. If necessary to facilitate your demonstration, copy and paste the
correct expression from the answer file into the Calculated Member Builder.

Use the following additional strategies to present this module:
!
Using Calculated Cells
Teach how to use the LookupCube function, which retrieves a value from a
different cube. Then review calculated value types, comparing them with

Lead-in
In this module, you will learn
about calculated cells and
how to use them.
2 Module 9: Using Calculated Cells

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

Using Calculated Cells
!
Group Activity: Creating a Custom Member Formula
!
Group Activity: Creating a Calculated Cells Formula
!
LookupCube Function
!
Calculated Value Types
!
Member Source
!
Formula ScopeIn this module, you will work in the Finance cube, which calculates the income
statement for a fictional company. The cube includes both Actual and Budget

values from a different cube.
Lead-in
In addition to creating
calculated members in a
cube, you can create
dynamic or temporary
calculated members in an
MDX query statement.
Delivery Tip
Briefly explain the bullets on
this diamond slide to provide
context for the upcoming
group activity in which the
items are functionally
demonstrated.
Module 9: Using Calculated Cells 3

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Group Activity: Creating a Custom Member Formula In this group activity, you may follow along on your own computer with your
instructor or observe the demonstration.
You will learn how to create a custom member formula to look up the Actual
values for Revenue from the Sales cube.
!
To review existing Revenue values in the Finance cube
In this procedure, you browse the data in the Revenue member of the Account
dimension.

students are not familiar
with custom member
formulas, you may want to
proceed slowly and
encourage them to follow
along in the group activity.
4 Module 9: Using Calculated Cells

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

2. In the Scenario dimension drop-down list, click the Budget scenario, and
then expand the Net Profit node and the Gross Profit node.

The Revenue value for All Time is 91,200. This is the sum of all the
monthly budgets over 1997 and 1998. Revenue values for Budget were
generated during the same planning process as all the other budget numbers
and were then loaded into the cube from the fact table when the cube was
processed.
3. In the Scenario dimension list, click the Actual scenario.

The Revenue value is blank. Revenue for the Actual scenario are not stored
in the same fact table as the balance of the financial information, but they
can be derived from the Sales cube.
The Revenue member is simply one member of the Account dimension. It
requires special handling—that is, you must treat it differently from the
other members. You can use the LookupCube function to retrieve the
revenue values from the Sales cube, but Revenue is the only member that
should use the LookupCube function.
One way to treat a single member differently from the rest of the dimension
is to use a custom member formula. With a custom member formula, the