Contents
Overview 1
Using Set Creation Functions 2
Lab A: Using Set Creation Functions 13
Using Set Manipulation Functions 20
Lab B: Using Set Manipulation Functions 30
Using Subquery Set Functions 36
Lab C: Using Subquery Set Functions 52
Working with Dimension Interactions 57
Lab D: Working with Dimension
Interactions 64
Review 67 Module 6: Creating and
Manipulating Sets
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

members. Some of the functions retrieve sets from the metadata defined in the
cube's dimensions. Some of the functions manipulate those sets to create new
sets that meet specific analytical purposes.
After completing this module, students will be able to:
!
Use functions to create a set.
!
Use functions to manipulate a set.
!
Create a set from subqueries.
!
Manipulate dimension interactions by using advanced functions.
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_6.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.

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 6: Creating and Manipulating Sets v

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

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. Do the Members, Children, and Descendants function really create sets?
These functions begin with something that is not a set—a dimension, a
level, or a member—and return something that is a set. In that sense,
they create sets. This is different from most other set functions, which
begin with one or more sets and return a different set.

2. How would you get all the leaf-level members of an entire dimension?
Use the Descendants function with the All Level member as the
common ancestor.

Online to find and learn unfamiliar functions.
vi Module 6: Creating and Manipulating Sets

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

6. In both Books Online and the Functions list of the Calculated Member
Builder, the syntax for the Descendants – Level function shows that you
cannot use the third argument—Desc_flags—without also using the second
argument—Level. Is that true?
No. You can omit the Level argument, leaving commas to show its
place, and still use a flag such as LEAVES as the third argument. The
documentation is incorrect.

7. What are the different ways you can join together two sets, and what are the
differences between them?
There are three ways to combine two sets: the Union function, the plus
operator (+), and simply enclosing the sets in braces. The plus operator
requires an extra set of braces anyway, and there is no difference
between using commas or plus signs to separate sets. The Union
function can combine only two sets at a time, but it defaults to strip
duplicate members. If you do want duplicate members, it may be easier
to just combine the sets with braces. If you want to strip duplicate
members, you may want to use the Union function.

8. Would you ever use the NON EMPTY keyword to create a calculated
member?
No. It is used only to create a query statement that returns a report. In
a calculated member, you can use the NonEmptyCrossjoin function to
combine sets from multiple dimensions and remove tuples that produce
an empty result.

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

viii Module 6: Creating and Manipulating Sets

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Use the following additional strategies to present this module:
!
Using Set Creation Functions
Begin by explaining why it is important to be able to create a set of
members. Next, describe the use of the Members function, explaining the

!
Using Set Creation Functions
!
Using Set Manipulation Functions
!
Using Subquery Set Functions
!
Working with Dimension InteractionsThe ability to work with sets is critical both to retrieving data from an online
analytical processing (OLAP) cube in a multidimensional expression (MDX)
query statement and creating powerful MDX expressions.
In this module, you will be introduced to functions that return sets of members.
Some of the functions retrieve sets from the metadata defined in the cube's
dimensions. Some of the functions manipulate those sets to create new sets that
meet specific analytical purposes.
After completing this module, you will be able to:
!
Use functions to create a set.
!
Use functions to manipulate a set.
!
Create a set from subqueries.
!
Manipulate dimension interactions by using advanced functions.

Topic Objective
To provide an overview of
the module topics and

cube. These sets can appear as labels on the axes of a report, controlling which
cube values appear.
In later sections, you will learn how to manipulate these simple sets of
members.
The group activity introduces the use of two basic functions that generate sets—
the Members function and the Descendants function. The following topics
follow the group activity:
!
Members Function
This topic describes how to return members of specified levels and
dimensions by using the Members function.
!
Children Function
This topic describes the Children function. This function returns the
children of a specified member.
!
Single-Level Descendants Function
This topic describes the Descendants function. The simplest form of this
function returns a set of members at a specific level, lower in the hierarchy
tree than the member and level names given in the query. You can also use
the Descendants function to return all the members that descend from a
single member.
!
Leaf-Level Descendants Function
The Descendants function can also be used to return a set of members from
the lowest level of a dimension—the leaf-level members. To return a set that
includes only leaf-level members, you use an optional third argument in the
Descendants function.

Topic Objective

related functions.
!
To open the MDX2093A application and template file
In this procedure, you use the MDX2093A sample application to open
templates you will use in the remainder of the group activity.
1. In Microsoft
®
Windows
®
Explorer, navigate to C:\MOC\2093A\LabFiles,
double-click MDX2093A.exe, and then click OK to connect to the server.
2. When asked to open a query file, navigate to the folder
C:\MOC\2093A\LabFiles\L06, and open the file SetCreate.mdx.
3. In the DB list, select Market, and then in the Cube list, select Sales. The
first template looks like the following text:
--Members of a Dimension
-------------------------------------
SELECT
{[Sales Dollars], [Sales Units]} ON COLUMNS,
«Set» ON ROWS
FROM Sales Topic Objective
To learn how to create sets
by using the Members and
Descendants functions.
Lead-in
In this group activity, you will
learn how to generate sets

The labels on the Rows axis appear from all levels of the Product
hierarchy. The member names in a level are sorted according to the Order
By Column property defined in the dimension.

The MDX2093A application displays members from multiple levels in a
single column, but indented. This differs from the browser in the Analysis
Manager and the Microsoft Office PivotTable
®
list, which use a separate
column for each level. It is the client application—not the MDX statement—
that determines how levels are displayed. !
To show the children of a member
In this procedure, you use the Children function to create a set that consists of
members that are the children of a specified member.
1. In the Queries list, select Children of a Member.
2. Click in the «Set» token. In the Syntax Examples list, expand the Set
folder, and then double-click Children.
The Children function must follow a single member. Do not confuse the
Children function with the Members function. A member has children. A
level or a dimension has members.
Delivery Tip
CreateDimensionD.txt in
C:\MOC\2093A\Demo\D06\
Answers contains the
completed MDX expression
for this procedure.
Note

To show single-level descendants of a member
In this procedure, you use the Descendants function to create a set of members
that are all descendants of one specified member.
1. In the Queries list, select Descendants at a Level.
2. Click in the «Set» token. In the Syntax Examples list, expand the Set
folder, and then double-click Descendants.
3. Remove brackets around optional arguments, and then delete the third
argument.
The first argument is a member. It is the common ancestor for all the
members returned by the function.
4. Click in the «Member» token and type [Bread]
The second argument is a level. It is the level from which you want to
retrieve members.
5. Click in the «Level» token and type [Product Name]. The resulting
statement looks like this:
SELECT
{[Sales Dollars],[Sales Units]} ON COLUMNS,
Descendants([Bread],[Product Name]) ON ROWS
FROM Sales

Note
Delivery Tip
CreateDescendantsLevelD.t
xt in
C:\MOC\2093A\Demo\D06\
Answers contains the
completed MDX expression
for this procedure.
6 Module 6: Creating and Manipulating Sets


Descendants([Bread]) ON ROWS
FROM Sales

5. To view the results of the query, click the Run Query button.

The set on the Rows axis contains all the members of the Product
dimension that share Bread as a common ancestor.

Note
Delivery Tip
CreateDescendantsAllD.txt
in
C:\MOC\2093A\Demo\D06\
Answers contains the
completed MDX expression
for this procedure.
Module 6: Creating and Manipulating Sets 7 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

!
To create a set of the leaf-level descendants of a member
In this procedure, you use the Descendants function to create a set of the leaf-
level members of a parent-child dimension. In a parent-child dimension, leaf-
level members—members that have no children—are not always on the same
level.
1. In the Queries list, select Leaf-Level Descendants.
2. Click in the «Set» token. In the Syntax Examples list, expand the Set
folder, double-click Descendants, and then delete any brackets indicating

completed MDX expression
for this procedure.
8 Module 6: Creating and Manipulating Sets BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Members Function
!
Returns a Specified Set of Members
!
Retrieves All Members of a Level or Dimension
!
Must Use Hierarchy Version If the Dimension Has
Explicit Hierarchy
[Time].[Quarters].Members
[Time].[Quarters].Members
[Time].[Fiscal].Members
[Time].[Fiscal].Members
[Product].Members
[Product].Members
Returns the Members of the Quarters
Level of the Time Dimension
Returns the Members of the Quarters
Level of the Time Dimension
Returns the Members of the Product
Dimension
Returns the Members of the Product
Dimension
Returns the Members of the Fiscal

appears. This means that if a dimension has a hierarchy—for example, the
Time dimension, which has Calendar and Fiscal hierarchies—you must
use the entire Dimension.Hierarchy name.
!
Because the Members function returns a set of members, it appears in the
Set group of the Syntax Example list.
Topic Objective
To explain the syntax and
results of the Members
function.
Lead-in
The Members function
returns members of specific
levels or dimensions.
Example 1
Example 2
Example 3
Delivery Tip
Because students have
already been exposed to the
Members function in the
previous group activity, treat
this topic as a review—that
is, do not spend a lot of
time.
Caution
Module 6: Creating and Manipulating Sets 9 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

!
Is Similar to the Parent Function
$
Moves down the tree instead of up
$
Returns a set instead of a single member
!
Is Easy to Confuse with Members Function
!
Appears in the Set Group of the Syntax Examples List
[1998].Children
[1998].Children
Returns the set of all quarters in 1998
Returns the set of all quarters in 1998The Children function returns members that have the specified member as a
parent.
The following example returns all the quarters in 1998.
[1998].Children

When you use the Children function, consider the following facts and
guidelines:
!
The Children function is analogous to the Parent function. There are two
main differences:
• The Children function moves down the tree, whereas the Parent
function moves up.
• The Children function returns a set, whereas the Parent function
returns a single member.

this topic as a review—that
is, do not spend a lot of
time.
Ask why Children returns a
set whereas Parent returns
a member. Because the
hierarchy, by definition, gets
larger as you move down.
Related Functions
Delivery Tip
These related functions are
included for reference only.
Do not spend any time
explaining them.
Module 6: Creating and Manipulating Sets 11 BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Single-Level Descendants Function
!
Returns a Set of Members at a Specific Level or
Distance
!
Is Similar to the Ancestor Function, but Moves Down
!
Returns Descendants at All Levels If You Omit Level
!
Appears in the Set Group of the Syntax Example List
Descendants([1998],[Calendar Month])

it a starting member and a level name or distance number. The only
difference is that the Ancestor function moves up, and the Descendants
function moves down.
!
If you omit the level argument from the Descendants function, it returns all
the members that descend from the specified member, without regard to the
level. In effect, you get the subtree with the specified member as the top
member.
!
Because the Descendants function returns a set of members, it appears in
the Set group of the Syntax Example list.

Topic Objective
To describe how the
Descendants function can
be used to return a set of
members at one level.
Lead-in
The Descendants function
is similar to the Ancestor
function.
Syntax
Example 1
Example 2
Delivery Tip
Because students have
already been exposed to the
Descendants function in
the previous group activity,
treat this topic as a review—

argument to specify the levels from which you want to return the descendants.
Descendants(«Member», [«Level»],LEAVES)
The following example returns all the employees who report—directly or
indirectly—to Maya Gutierrez.
Descendants ([Maya Gutierrez], , LEAVES)) MDX includes the following other functions that return sets similar to the
Descendants function:
!
The Descendants function has additional optional arguments that allow you
to return various subsets of the descendants of a member.
!
The Ancestors function returns the set of ancestors between a specified
member and a specified level—or the top of the tree—with one member
from each level.
You can find more information about these functions by searching on the
function name in Microsoft SQL Server Books Online.
Topic Objective
To describe how the
Descendants function can
be used to return the leaf-
level members under a
specified member.
Lead-in
The Descendants function
can also be used to return a
set of members that are all
leaf-level members.
Syntax

through 5 in course 2093A, Implementing Business Logic with MDX in
Microsoft SQL Server 2000.
Estimated time to complete this lab: 15 minutes

Topic Objective
To introduce the lab.
Lead-in
In this lab, you will create
sets by using the Members
and Descendants
functions.
Explain the lab objectives.
14 Module 6: Creating and Manipulating Sets BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

Exercise 1
Using Set Creation Functions
In this exercise, you will use functions that retrieve a set from a dimension—the
Members function and the Descendants function.
As you complete each procedure, compare the result as shown in the procedure
screen shot to the result on your own computer. If there is a difference, recheck
your entry and refer back to the related group activity procedures as necessary.
If you still cannot reconcile your result, then refer to the answer file, which is
located in:
C:\MOC\2093A\Labfiles\L06\Answers.
You can copy and paste expressions from this file into the query pane for any
given procedure.
Before beginning the exercises in this lab, verify that the following objects exist
BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

!
To show all the members of a dimension
In this procedure, you use the Members function to generate a report that
includes all members of the Employee dimension. MembersDimension.txt in
the Answer folder is the completed MDX expression used in this procedure.
1. In Windows Explorer, navigate to C:\MOC\2093A\LabFiles, double-click
MDX2093A.exe, and then click OK to connect to the server.
2. When asked to open a query file, navigate to the folder
C:\MOC\2093A\LabFiles\L06, and double-click the file LabA.mdx.
1. In the DB list, select Market, and then in the Cube list, select Sales.
2. Modify the initial MDX statement to create a report that shows Sales
Dollars and Sales Units across the Columns axis and all the members of the
Employee dimension down the Rows axis.
What MDX query statement did you use?
SELECT
{[Sales Dollars], [Sales Units]} ON COLUMNS,
Employee.Members ON ROWS
FROM Sales
____________________________________________________________
____________________________________________________________
____________________________________________________________

3. Click the Run Query button , and then verify that the Results pane, which is
located in the lower left corner of the MDX Builder, appears similar to the
following table.

BETA MATERIALS FOR MICROSOFT CERTIFIED TRAINER PREPARATION PURPOSES ONLY

!
To show single-level descendants of a member
In this procedure, you use the Descendants function to generate a report that
displays a set of members from a single level that share a common ancestor.
MembersDescendantsLevel.txt in the Answer folder is the completed MDX
expression used in this procedure.
1. With the LabA.mdx file open in the MDX2093A sample application, in the
Queries list, click Descendants on a Level.
2. Modify the initial MDX statement to create a report that shows Sales
Dollars and Sales Units across the Columns axis and all the Meat products
down the Rows axis.
What MDX query statement did you use?
SELECT
{[Sales Dollars], [Sales Units]} ON COLUMNS,
Descendants([Meat],[Product Name]) ON ROWS
FROM Sales
____________________________________________________________
____________________________________________________________
____________________________________________________________
3. Run the query, and then verify that the first few rows of the Results pane
appear similar to the following table.