Introduction to Tuples
A tuple is a combination of members from different dimensions. Each tuple has a single value (possibly null).
Every data cell in a result set is a tuple. For example, consider the following query:
SELECT MEASURES.[%COUNT] ON 0, homed.city.members ON 1 FROM demomdx
%COUNT
1 Cedar Falls 110
2 Centerville 99
3 Cypress 112
4 Elm Heights 118
5 Juniper 122
6 Magnolia 114
7 Pine 121
8 Redwood 111
9 Spruce 93
This query returns a set of nine tuples. For example, the first tuple is a combination of Cedar Falls (from the City dimension) and %COUNT (from the Measures dimension).
Creating Tuples
You can create a tuple directly, via the following syntax:
(member_expr1, member_expr2, member_expr3, ...)
Where member_expr1, member_expr2, member_expr3, and so on are member expressions.
In other implementations of MDX, each of these member expressions must be associated with a different dimension. This means that a tuple cannot include more than one member from the same dimension.
In InterSystems MDX, a tuple expression can include more than one member expression from the same dimension. In most cases, the result is null, because in most cases, a record belongs to only one member. However, in InterSystems IRIS Business Intelligence, a level can be based on a list value, which means that a given record can belong to multiple members. For example, the tuple (allerd.soy,allerd.wheat) represents all patients who are allergic to both soy and wheat.
Fully and Partially Qualified Tuples
A tuple is either fully qualified or partially qualified:
-
If the tuple expression refers to each dimension in the cube, the tuple is fully qualified. A fully qualified tuple refers to a very small number of records and is too granular to be commonly used.
-
If the tuple expression does not refer to each dimension in the cube, the tuple is partially qualified. A partially qualified tuple can be very useful, especially when used to filter the data used by the query.
If a tuple refers to only one member, the tuple is equivalent to that member. For example, the following expressions both access the same data:
(colord.red)
colord.red
The expression (colord.red) is a tuple expression uses the Red member of the ColorD dimension.
The expression colord.red is a member expression that refers to the Red member of the ColorD dimension.
Each expression accesses only the patients whose favorite color is red.
Sets of Tuples
You can create sets of tuples, by enclosing a comma-separated list of tuple expressions within curly braces:
{tuple_expression1, tuple_expression2, ...}
(Note that in other implementations of MDX, for any tuple in a set, you must construct each tuple in the same way. For example, if the first tuple uses dimension A in its first list item, all the other tuples must do so as well. InterSystems MDX does not have this restriction.)
You can also create sets of tuples by using the CROSSJOIN or NONEMPTYCROSSJOIN functions. For example:
SELECT MEASURES.[%COUNT] ON 0, CROSSJOIN(gend.gender.MEMBERS,homed.city.members) ON 1 FROM demomdx
%COUNT
1 Female->Cedar Falls 58
2 Female->Centerville 41
3 Female->Cypress 51
4 Female->Elm Heights 53
5 Female->Juniper 58
6 Female->Magnolia 58
7 Female->Pine 64
8 Female->Redwood 58
9 Female->Spruce 47
10 Male->Cedar Falls 52
11 Male->Centerville 58
12 Male->Cypress 61
13 Male->Elm Heights 65
14 Male->Juniper 64
15 Male->Magnolia 56
16 Male->Pine 57
17 Male->Redwood 53
18 Male->Spruce 46
You can use these set expressions in all the places where set expressions are permitted: