# Operators

The MDX language, similar to other query languages such as SQL or programming languages, has several operators. An operator is a function that is used to perform a specific action and takes arguments. MDX has several types of operators. Similar to other languages, MDX contains the arithmetic operators, logical operators, and special MDX operators.

### Arithmetic Operators

Regular arithmetic operators such as +, , *, and / are part of the MDX arithmetic operators. Just as with programming languages, these operators can be applied between two numbers. The + and operators can also be used as unary operators for numbers. Unary operator means the operator can be used with a single operand (single number) in MDX expressions such as + 100 or 100.

### Set Operators

The +, , and * operators, in addition to being arithmetic operators, are also used to perform operations on the MDX Sets. The + operator is used to union two Sets, the operator is used to evaluate the difference of two Sets, and the * operator is used find the cross product of two sets. Cross product of two sets results in all possible combinations of the tuples in each set and helps in retrieving data in a matrix format. For example if you have the two sets {Male, Female} and {2003, 2004, 2005} then the cross product of the two sets represented as {Male, Female} * {2003, 2004, 2005} is {(Male,2003), (Male,2004), (Male,2005),(Female,2003),(Female,2004),(Female,2005)}. The following examples show some of the operations on sets using the set operators.

Example 1

The result of the MDX expression

`     {[Customer].[Country].[Australia]} + {[Customer].[Country].[Canada]} `

is the union of the two sets as shown here:

`     {[Customer].[Country].[Australia], [Customer].[Country].[Canada]} `

Example 2

The result of the MDX expression

`     {[Customer].[Country].[Australia],[Customer].[Country].[Canada]}*     {[Product].[Product Line].[Mountain],[Product].[Product Line].[Road]} `

is the cross product of the sets as shown here:

`     {([Customer].[Country].[Australia],[Product].[Product Line].[Mountain])     ([Customer].[Country].[Australia],[Product].[Product Line].[Mountain])     ([Customer].[Country].[Canada],[Product].[Product Line].[Road])     ([Customer].[Country].[Canada],[Product].[Product Line].[Road])} `

### Comparison Operators

MDX supports the comparison operators <, <=, >, >=, =, and <>. These operators take two MDX expressions as arguments and return TRUE or FALSE based on the result of comparing the values of the MDX expression.

Example

The following MDX expression uses the (greater than) comparison operator, >

`     Count (Customer.[Country].members) > 3 `

In the above example Count is an MDX function that is used to count the number of members in Country hierarchy of Customer dimension. Because there are more than three members, the result of the preceding MDX expression is TRUE.

### Logical Operators

The logical operators that are part of MDX are AND, OR, XOR, NOT, and IS, which are used for logical conjunction, logical disjunction, logical exclusion, logical negation, and comparison, respectively. These operators take two MDX expressions as arguments and return TRUE or FALSE based on the logical operation. These logical operators are typically used in MDX expressions for cell and dimension security, which you learn about in Chapter 18.

### Special MDX Operators — Curly Braces, Commas, and Colons

The curly brace represented by the characters { and } is used to enclose a tuple or a set of tuples to form an MDX set. Whenever you have a single tuple, the curly brace is optional because Analysis Services implicitly converts the single tuple to a set. When there is more than one tuple to be represented as a set or when there is an empty set, you need to use the curly brace.

You have already seen the comma character used in several earlier examples. The comma character is used in forming a tuple that contains more than one member. By doing this you are eventually creating a slice of data on the cube. In addition, the comma character is used to build sets comprised of tuples where the tuples are separated by the comma character. In the set {(Male,2003), (Male,2004), (Male,2005),(Female,2003),(Female,2004),(Female,2005)} the comma character is not only used to form tuples but also to form the set of tuples.

The colon character is used to define a range of members within a set. The members within a set are ordered based on the key or the name of the member, whichever is specified while creating the set. The colon character is used between two non-consecutive members in a set to indicate inclusion of all the members between them, based on the ordering. For example, if you have the following set

`     {[Customer].[Country].[Australia], [Customer].[Country].[Canada],     [Customer].[Country].[France], [Customer].[Country].[Germany],     [Customer].[Country].[United Kingdom], [Customer].[Country].[United States]} `

The following MDX expression

`     {[Customer].[Country].[Canada] : [Customer].[Country].[United Kingdom]} `

results in the following set

`     {[Customer].[Country].[Canada], [Customer].[Country].[France],     [Customer].[Country].[Germany], [Customer].[Country].[United Kingdom]} `

Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176

Similar book on Amazon