Contents
Overview 1
Allocating Values from a Grand Total 2
Lab A: Allocating Values from a Grand Total 6
Allocating Values from a Subtotal 10
Lab B: Allocating Values from a Subtotal 16
Allocating Values Across Multiple Dimensions22
Lab C: Allocating Values Across Multiple
Dimensions 30
Review 35 Module 8: Case
Study—Implementing
Budget Allocations
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

The assignment is to allocate Budget Units based on Actual Units. Analysts in
a fictional company have already entered Budget Units values at a high level
into a Sales Budget cube. In the Market database, the Sales Actual and
Budget cubes have been joined in a virtual cube named Actual and Budget.
The student’s job is to allocate the Budget Units in the new Actual and
Budget virtual cube to the lowest levels of all the dimensions based on the
patterns of Actual Units that are contained in the same cube. Actual Units
reflect historical sales patterns. Allocating Budget Units to lower levels based
on historical sales patterns is a typical budget methodology.
In addition to allocating budgets to lower levels, students will also create
expressions that roll up—or aggregate—the lower-level budgeted units back to
higher levels.
After completing this module, students will be able to:
!
Allocate values from a grand total across a dimension.
!
Allocate values from an intermediate level across a dimension.
!
Create a calculated member that allocates values across three dimensions.
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_08.ppt

restoring a database archive.
!
To prepare for group activities and labs
In this procedure, you restore the Market database, which is a .cab file type.
1. Start Analysis Manager.
2. In the left pane, expand the Analysis Services folder.
3. Expand the Server icon and verify that the Market database does not exist.
4. Right-click the Server icon, and then click Restore Database.
5. Navigate to the C:\Moc\2093A\Batches folder.
6. Select Market.cab, click Open, and then click Restore.

If the Market database already exists from a previous group exercise or lab,
and cubes within the database contain extraneous information, you can return
the Market database and its cubes to a beginning position by either:
Deleting any calculated members that were created in a specific cube, and then
saving the cube.
- or -
Repeating the preceding restore database procedure.

Module 8: Case Study—Implementing Budget Allocations 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.

allows you to enter a single set. You can think of it as a query with a
single axis. If you need to summarize detail values that are calculated at
the intersection of two or more dimensions, you must combine the sets
from those dimensions into a single set, which is what the Crossjoin
function does.
6. What is the difference between the CoalesceEmpty function and the
combination of IIF and IsEmpty functions?
You could replace CoalesceEmpty with a combination of IIF and
IsEmpty. The CoalesceEmpty function avoids the need to duplicate the
value expression, and it also allows you to combine a series of tests in a
single function.
vi Module 8: Case Study—Implementing Budget Allocations BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY Module Strategy
This module is one extended case study project, broken into three steps. Each
step is a section, and each section becomes more complex. The final calculated
member is extremely complex.
Major sections of this module begin with a group activity followed by a lab.
The following are guidelines for delivering materials in the context of group
activities:
!
Using group activities to introduce new content
You often introduce new concepts or functionality while delivering the
procedures within a group activity as a live demonstration. For example,
you may present a new MDX function by showing first its construction and
then its result set as an actual calculated member formula or within a query
BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Use the following additional strategies to present this module:
!
Allocating Values from a Grand Total
Explain to students that they will be allocating Budget Units, which exist at
the grand total level, to Actual Units, which exist in the cube at the lowest
levels of detail. Describe the two-part process that accomplishes this: first
create allocation ratios and then multiply the grand total values by the
allocation ratios to create detailed values.
!
Allocating Values from a Subtotal
Tell students that this section is an extension of the previous section—
Product, another dimension, is being added to the allocation process.
Explain that budgets for Product are entered at an intermediate level,
thereby complicating the allocation process. Describe the process that
allows them to allocate based on a subtotal—first use the Ancestor function
to find the subtotal to allocate, allocate to lower levels—as in the previous
section—and then aggregate to the higher levels by using the Sum function
in conjunction with the Descendants function.
!
Allocating Values Across Multiple Dimensions
Explain to students that in this section, they will build on the calculated
member that they created in the previous sections to now include a third
dimension—the Time.Calendar dimension. Explain that to include more
than one dimension in the set for a Sum function, they will use the
Crossjoin function to join the sets from the individual dimensions. Finish
by telling students that they will also use the CoalesceEmpty function to

patterns. Allocating Budget Units to lower levels based on historical sales
patterns is a typical budget methodology.
In addition to allocating budgets to lower levels, you will also create
expressions that roll up—or aggregate—the lower-level budgeted units back to
higher levels.
After completing this module, you will be able to:
!
Allocate values from a grand total across a dimension.
!
Allocate values from an intermediate level across a dimension.
!
Create a calculated member that allocates values across three dimensions.

Topic Objective
To provide an overview of
the module topics and
objectives.
Lead-in
This module is a case study
in which you and the
instructor will work through a
single, complex project.
2 Module 8: Case Study—Implementing Budget Allocations BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Allocating Values from a Grand Total
!
Start with Existing Values

This ratio is a percentage of total calculation, which you learned in a
previous module.
!
Multiply the allocation ratio for the current member of the State dimension
by the Budget Units value for All State to create the allocated budget.

Topic Objective
To define, conceptually, how
the allocation formula is
constructed.
Lead-in
Now you will learn how to
allocate values at a higher
level to values at the detail
level.
Delivery Tip
This slide topic sets
important foundations for
the following group activity.
Review the concepts and
procedures carefully and
check that students
understand.
Module 8: Case Study—Implementing Budget Allocations 3 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Group Activity: Allocating Values from a Grand Total


members of a new
dimension.
4 Module 8: Case Study—Implementing Budget Allocations BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

4. In the MDX Builder, in the DB list select the Market database, and then in
the Cube list select the Actual and Budget cube. The initial query, Grand
Total Allocation, looks like this:
WITH
MEMBER Measures.[Allocated Budget] as ‘Null’
SELECT
{[Actual Units], [Budget Units], [Allocated Budget]}
ON COLUMNS,
{ [All State] , [USA] , [North West], [Washington]}
ON ROWS
FROM [Actual AND Budget]

5. Run the query.

On the Columns axis, the result set shows the Actual Units and Budget
Units measures, which are stored in the Actual And Budget virtual cube,
along with the empty Allocated Budget measure, which is defined as part of
the query.
On the Rows axis, the result set shows one member from each level of the
State dimension. When analysts entered the original data, they did not break
out the budget along the State dimension. Therefore, the Budget Units
measure shows a total only for the All State member.


Ask students why you do
not need a member from the
State dimension in the
numerator tuple.
Because you want the
current member, and
CurrentMember is the
default for each unspecified
dimension.
Module 8: Case Study—Implementing Budget Allocations 5 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

2. Run the query.

In the Allocated Budget column, the result set shows the percent each state
contributes to the total for Actual Units.

!
To allocate a budget over an entire dimension
In this procedure, you allocate the Budget Units value across members at all
levels of the State dimension.
You now need to simply multiply the ratio by the total Budget Units. You want
the Budget Units for the All State member, not for the current member, so you
must explicitly include All State in the tuple, as with the tuple already in the
denominator.
1. At the beginning of the expression for the Allocated Budget member, insert
([Budget Units],[All State]) *
. The final query looks like this:

for this procedure.
6 Module 8: Case Study—Implementing Budget Allocations BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Lab A: Allocating Values from a Grand Total Objectives
After completing this lab, you will be able to:
!
Divide a base value into the total to create a ratio.
!
Multiply the ratio by a new total to allocate values.

Prerequisites
Before working on this lab, you must have successfully completed modules 1
through 7 in course 2093A, Implementing Business Logic with MDX in
Microsoft SQL Server
™
2000.
Estimated time to complete this lab: 40 minutes

Topic Objective
To introduce the lab.
Lead-in
In this lab, you will create a
calculated member that
allocates values to each

the LabA.MDX query file.
4. In the MDX Builder, in the DB list select the Market database, and then in
the Cube list select the Actual and Budget cube.
5. Change the expression for the Allocated Budget member to create a ratio
that shows the percent each state contributes to the total.
What MDX expression did you use?
WITH MEMBER Measures.[Allocated Budget] AS
'[Actual Units]/([Actual Units],[All State])'
SELECT
{[Actual Units], [Budget Units], [Allocated Budget]}
ON COLUMNS,
{ [All State] , [USA] , [South West], [California]}
ON ROWS
FROM [Actual AND Budget]
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________
Delivery Tip
The procedures in this
exercise essentially
replicate the previous group
activities but without the
answers.

Students having difficulty
with the procedures should
first refer back to the group
activity procedures and then
go to the answer file for

{[Actual Units], [Budget Units], [Allocated Budget]}
ON COLUMNS,
{ [All State] , [USA] , [South West], [California]}
ON ROWS
FROM [Actual AND Budget]
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________
Module 8: Case Study—Implementing Budget Allocations 9 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Run the query, and then verify that the result grid appears similar to the
following table.