Using MDX with DeepSee
Working with Sets
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter discusses how to create and use sets. It discusses the following topics:

Introduction to Sets
A set contains zero or more elements, and there are three general kinds of elements: members, scalar values, and tuples (for information on tuples, see the next chapter, Tuples and Cubes).
You use sets on the axes of MDX queries; you also use them to build other sets. Note that although an MDX set can be empty, you cannot use such an empty set as an axis.
Creating Set Expressions
The general syntax for a set expression is as follows:
{expression1, expression2, ...}
This list can include any number of elements. In DeepSee MDX, if the list includes only one element, you can omit the curly braces.
Each set element can be one of the following:
For complete details, see the DeepSee MDX Reference.
Creating Named Sets
It is often useful to create a set and assign a name to it, so that you can reuse that set in multiple ways. Also, the syntax of a query is often easier to read when you use named sets.
You can create one or more named sets within a query, as follows:
WITH with_clause1 with_clause2 ... SELECT query_details
Where:
Then your query can refer the named set by name in all the places where you can use other set expressions.
For example:
WITH SET testset AS '{homed.city.members}' 
SELECT MEASURES.[%COUNT] ON 0, testset ON 1 FROM demomdx
 
                                    %COUNT
1 Cedar Falls                         184
2 Centerville                         194
3 Cypress                             134
4 Elm Heights                         146
5 Juniper                             176
6 Magnolia                            169
7 Pine                                118
8 Redwood                             182
9 Spruce                              197
Note:
This named set is a query-scoped named set; its scope is the query. For information on session-scoped named sets, see CREATE SET Statement,” in the DeepSee MDX Reference.
Your cubes might contain additional named sets that you can use in all queries; see Defining DeepSee Models.
Order of Members in a Set
Every set has an inherent order (a first member, a second member, and so on).
When you use an MDX function to return a set, that function determines the order of the members in the set. Wherever possible, MDX functions use the natural order of members as specified in the cube definition.
For example, the MEMBERS function returns the members of a level in the order specified in the cube definition (typically in alphabetic order or date order, depending on the level).
When you construct a set as described in the previous section, the order in which you list elements controls the order of the members in the set. For example, suppose that you specify a set as follows:
{gend.gender.MEMBERS,allerd.allergies.MEMBERS}
This set consists of the members of the Gender dimension, followed by the members of the Allergies dimension.
Selecting Subsets
SUBSET returns a set of members from a given set, by position. You specify a set, the starting position, and the number of members to return. The starting position is 0. For example:
SELECT MEASURES.[%COUNT] ON 0,SUBSET(homed.city.MEMBERS,0,3) ON 1 FROM demomdx
 
                                    %COUNT
1 Cedar Falls                           110
2 Centerville                            99
3 Cypress                               112
The EXCEPT function provides another way to get a subset; see the next section.
Also see the chapter Filtering a Query,” later in this book.
Sorting Sets
This section describes ways to sort sets. It discusses the following topics:
Sorting a Set by a Measure Value
It is often useful to sort members by the value of some measure. For example, you might want to sort departments by response, so that you can look at the departments with the slowest responses. Or you might sort products by their sales revenues so that you can look at the top-ranked products.
To return a set in the order that you specify, use the ORDER function. This function takes an argument, typically a measure reference, that specifies the value to use when determining the order of the set members. For example:
SELECT MEASURES.[avg test score] ON 0, 
ORDER(homed.city.MEMBERS,MEASURES.[avg test score],BDESC) ON 1 FROM demomdx
 
                            Avg Test Score
1 Juniper                             75.08
2 Redwood                             75.07
3 Cedar Falls                         75.03
4 Elm Heights                         74.96
5 Pine                                74.76
6 Spruce                              74.47
7 Magnolia                            74.13
8 Cypress                             73.96
9 Centerville                         73.79
The optional third argument can be one of the following:
For example, the following query breaks the hierarchy:
SELECT MEASURES.[avg test score] ON 0, 
ORDER(homed.MEMBERS,MEASURES.[avg testscore],BDESC) ON 1 FROM demomdx
 
                            Avg Test Score
 1 Juniper                            75.08
 2 Redwood                            75.07
 3 32007                              75.07
 4 Cedar Falls                        75.03
 5 38928                              75.00
 6 Elm Heights                        74.96
 7 32006                              74.78
 8 Pine                               74.76
 9 Spruce                             74.47
10 34577                              74.28
11 Magnolia                           74.13
12 Cypress                            73.96
13 Centerville                        73.79
14 36711                              73.79
In contrast, the following preserves the hierarchy:
SELECT MEASURES.[avg test score] ON 0, 
ORDER(homed.MEMBERS,MEASURES.[avg testscore],DESC) ON 1 FROM demomdx
 
                            Avg Test Score
 1 32007                              75.07
 2 Redwood                            75.07
 3 38928                              75.00
 4 Cedar Falls                        75.03
 5 Elm Heights                        74.96
 6 32006                              74.78
 7 Juniper                            75.08
 8 Spruce                             74.47
 9 34577                              74.28
10 Pine                               74.76
11 Magnolia                           74.13
12 Cypress                            73.96
13 36711                              73.79
14 Centerville                        73.79
Selecting a Top or Bottom Subset
It is useful to sort items in some way and then choose a subset from the top or bottom, such as the top five. The following MDX functions enable you to do so.
Applying Hierarchical Order
The HIERARCHIZE function accepts a set of members from the same dimension and returns a set containing those members in hierarchical order, that is, the order specified by the hierarchy. For example:
SELECT MEASURES.[%COUNT] ON 0, 
HIERARCHIZE({homed.36711,homed.38928,homed.[elm heights],homed.Spruce}) ON 1 FROM demomdx
 
                                    %COUNT
1 36711                                  99
2 Spruce                                 93
3 38928                                 228
4 Elm Heights                           118
If the members belong to different hierarchies in the dimension, the different hierarchies are returned in an arbitrary order.
Combining Sets
Sets are the building blocks of MDX queries. When you write an MDX query, you must specify a set to use on each axis. DeepSee supports the following MDX functions that you can use to combine sets:
Filtering a Set by a Measure or Property Value
You can also examine measure values for the members in a set and use those values to filter the set. To do so, you use the FILTER function.
The FILTER function uses a set and a logical expression. It examines a set and returns the subset in which the given logical expression is true for each member. The logical expression typically compares a measure value to a constant or to another measure value. For example:
SELECT MEASURES.[%COUNT] ON 0, FILTER(homed.city.MEMBERS,MEASURES.[%COUNT]>115) ON 1 FROM demomdx
 
                                    %COUNT
1 Elm Heights                           118
2 Juniper                               122
3 Pine                                  121
It is important to understand that this filtering occurs at an aggregate level: The measure value is computed for each possible member in the query. The FILTER function considers those aggregate values and removes members as appropriate.
You can use the same function with member properties as follows:
SELECT homed.h1.CURRENTMEMBER.PROPERTIES("Population") ON 0, 
FILTER(homed.city.MEMBERS,homed.h1.CURRENTMEMBER.PROPERTIES("Population")>20000) ON 1 FROM demomdx
 
                                  ZIP
1 Cedar Falls                        90,000
2 Centerville                        49,000
3 Elm Heights                        33,194
4 Redwood                            29,192
Removing Null Elements from a Set
In some cases, a set might contain null elements. For example, the CROSSJOIN function could potentially return null elements (as is shown in the preceding section).
If you precede the set expression with the keyword NON EMPTY, DeepSee suppresses the null elements. For example:
SELECT MEASURES.[%COUNT] ON 0,NON EMPTY CROSSJOIN(diagd.diagnoses.MEMBERS, 
aged.[age group].MEMBERS) ON 1 FROM demomdx
 
                                    %COUNT
 1 None->0 to 29                        389
 2 None->30 to 59                       333
 3 None->60+                            106
 4 asthma->0 to 29                       40
 5 asthma->30 to 59                      39
 6 asthma->60+                           11
 7 CHD->30 to 59                         12
 8 CHD->60+                              25
 9 diabetes->0 to 29                      1
10 diabetes->30 to 59                    20
11 diabetes->60+                         24
12 osteoporosis->60+                     22
Removing Duplicates
When you combine sets, you may want to remove duplicates. This is true especially when you have created and combined sets in multiple steps. To be certain that the resulting set has no duplicates, you use the DISTINCT function.
For example, suppose that the query must return a specific city as reference, which is needed for comparison to the other cities. Consider the following query, which displays a reference city, followed by a set of cities with a given patient count:
WITH SET refcity AS '{homed.juniper}' SELECT MEASURES.[%COUNT] ON 0, 
{refcity,FILTER(homed.city.MEMBERS,MEASURES.[%COUNT]>115)} ON 1 FROM demomdx
 
                                    %COUNT
1 Juniper                               122
2 Elm Heights                           118
3 Juniper                               122
4 Pine                                  121
Compare to the following query, which removes the duplicate reference city:
WITH SET refcity AS '{homed.juniper}' SELECT MEASURES.[%COUNT] ON 0, 
DISTINCT({refcity,FILTER(homed.city.MEMBERS,MEASURES.[%COUNT]>115)}) ON 1 FROM demomdx
 
                                    %COUNT
1 Juniper                               122
2 Elm Heights                           118
3 Pine                                  121
Counting the Elements of a Set
To count the elements of a set, use the COUNT function. For example:
SELECT COUNT(docd.doctor.MEMBERS) ON 0 FROM demomdx
 
                                     COUNT
                                       41
By default, COUNT considers any empty elements and counts them along with the non-empty elements. If you use the EXCLUDEEMPTY keyword as the second argument, this function returns the number of non-empty elements.
To see this, first consider the following query:
SELECT aged.[age group].MEMBERS ON 0, diagd.diagnoses.MEMBERS ON 1 FROM demomdx WHERE MEASURES.[%COUNT]
 
                           0 to 29           30 to 59                60+
1 None                          389                333                106
2 asthma                         40                 39                 11
3 CHD                             *                 12                 25
4 diabetes                        1                 20                 24
5 osteoporosis                    *                  *                 22
The following query counts the number of members of the Diagnoses level and uses the WHERE clause to get only patients in the age group 0 to 29:
WITH SET myset AS 'diagd.diagnoses.MEMBERS' SELECT COUNT(myset) ON 0 FROM demomdx WHERE aged.[0 to 29]
 
                                     COUNT
                                          5
In this query, COUNT returns 5 because it considers empty members. In contrast:
WITH SET myset AS 'diagd.diagnoses.MEMBERS' SELECT COUNT(myset,EXCLUDEEMPTY) ON 0 FROM demomdx WHERE aged.[0 to 29]
 
                                     COUNT
                                          3