Intro To MDX Decathlon–Sets: A Shirt of a Different Color (4/10)

20 August, 2012 (13:00) | Decathlon, MDX | By: Mark V

In the previous post in this series, Intro To MDX Decathlon – The Trouble With Tuples (3/10), we discussed how to reference a particular cell in a cube by using a Tuple. Now it’s time to group them together using a concept we haven’t discussed yet: the Set. Its basic definition is deceptively simple:

Set: A collection of tuples.

The basic syntax of a set is essentially a list of Tuples, separated by commas, surrounded by curly braces {}. You can see this in Figure 1.

Figure 1

image

As a concept, we can liken the concept of a Set to the uniforms within Starfleet in the Star Trek franchise. The members of Starfleet wore uniforms of a certain color depending on their role. In Figure 2, an excellent Lego depiction of the main characters of Star Trek: The Next Generation, you see Red, Blue, and Yellow uniforms.

Figure 2

image

Image URL: http://www.flickr.com/photos/dunechaser/163959411/sizes/z/in/photostream/

The characters are wearing shirts that are designed to group them together by their job function.

Essentially, the colors follow this pattern:

Red: Command

Yellow: Tactical, Security, Engineering (possibly considered Operations)

Blue: Science, Medical

Light Blue: Well… Wesley.

This grouping only works when there are properties shared amongst members of the group. Sets in the cube space also have to have some shared properties to be valid.

Prime Directives of Sets

Sets in MDX must follow the following rules to be valid.

Hierarchality: All Tuples in a set must reference the same hierarchies. This is essentially the Apples to Apples rule. Figure 3 shows an example of a valid Set. “Hierarchality” is also a great word to throw around at parties.

Figure 3

image

The Set in Figure 3 is valid because the members are both from [Hierarchy Y]. This Set is a good example of Hierarchality.

Figure 4 shows an example of an invalid Set.

Figure 4

image

The Set in Figure 4 is invalid because one member comes from [Hierarchy Y] and the other comes from [Hierarchy X]. These members do not have Hierarchality.

The code in Query 1, below, shows a set in my Borg cube.

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , {
          [Series].[Series Name].[Star Trek: The Original Series] -- Tuple A
        , [Series].[Series Name].[Star Trek: The Next Generation] -- Tuple B
      } ON ROWS
FROM [Borg]

 

Result 1

image

Dimensionality: All Tuples in the Set must reference the same Dimensions in the same order. This rule comes into play when you are declaring multi-part Tuples as members of the set.

Figure 5 shows an example of a valid Set.

Figure 5

image

The Set in Figure 5 is valid because the first tuple references [Hierarchy Y] and then [Hierarchy Z]. Likewise, the second tuple references [Hierarchy Y] and then [Hierarchy Z]. These tuples have the same Dimensionality.

Figure 6 shows an example of an invalid Set.

Figure 6

image

This Set is not valid because the first Tuple references [Hierarchy Y] and then [Hierarchy Z] while the second Tuple reverses the order of the hierarchies, referencing [Hierarchy Z] and then [Hierarchy Y]. These tuples do not have the same Dimensionality.

Query 2 shows an example of a Set created from multi-part tuples in my Borg cube.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , {
          ([Series].[Series Name].[Star Trek: The Original Series]
            , [Season].[Season Name].[Season 01]) -- Tuple A
        , ([Series].[Series Name].[Star Trek: The Next Generation]
            , [Season].[Season Name].[Season 01]) -- Tuple B
      } ON ROWS
FROM [Borg]

 

Result 2

image

The Named Set

For readability and organization, you can also give your sets names. You declare a Named Set using the WITH keyword. Figure 7 shows the basic syntax.

Figure 7

image

Query 3, below, will return the same result as Query 1, but is a bit more organized by using a Named Set.

Query 3

WITH SET MyFavorites AS
{
      [Series].[Series Name].[Star Trek: The Original Series] -- Tuple A
    , [Series].[Series Name].[Star Trek: The Next Generation] -- Tuple B
}
SELECT
      [Measures].[Episode Count] ON COLUMNS
    , MyFavorites ON ROWS
FROM [Borg]

 

Result 3

image

Result 3 is identical to Result 1.

The Range Operator “:”

The Range Operator is immensely helpful in creating Sets when dealing with ordered hierarchies, which should be just about all of them. The Range Operator takes two arguments, the Starting Member and the Ending Member.

[Starting Member]:[Ending Member]

It returns an ordered set of all member from the Starting Member to the Ending Member, inclusive of both. So, given a list of members [A],[B],[C],[D], the expression [A]:[D] would return the entire list of members.

Query 4 shows an example of a Named Set from my Borg cube made up of all of the members of the [Calendar Year] hierarchy in the 1980s decade.

Query 4

WITH SET TheEighties AS
{
      [Date].[Date Hierarchy].[Calendar Year].[1980] 
      ,[Date].[Date Hierarchy].[Calendar Year].[1981]
      ,[Date].[Date Hierarchy].[Calendar Year].[1982]
      ,[Date].[Date Hierarchy].[Calendar Year].[1983] 
      ,[Date].[Date Hierarchy].[Calendar Year].[1984]
      ,[Date].[Date Hierarchy].[Calendar Year].[1985]
      ,[Date].[Date Hierarchy].[Calendar Year].[1986]
      ,[Date].[Date Hierarchy].[Calendar Year].[1987]
      ,[Date].[Date Hierarchy].[Calendar Year].[1988]
      ,[Date].[Date Hierarchy].[Calendar Year].[1989]
}
SELECT
      [Measures].[Episode Count] ON COLUMNS
    , TheEighties ON ROWS
FROM [Borg]

 

Result 4

image

Since the [Calendar Year] hierarchy is ordered by the Year values, you can also use the Range Operator instead of specifying all ten years individually. Query 5 shows the same query logic as Query 4, but uses the Range Operator, resulting in much cleaner code.

Query 5

WITH SET TheEighties AS
{
      [Date].[Date Hierarchy].[Calendar Year].[1980] -- Start
      :[Date].[Date Hierarchy].[Calendar Year].[1989] -- End
}
SELECT
      [Measures].[Episode Count] ON COLUMNS
    , TheEighties ON ROWS
FROM [Borg]

 

Result 5

image

Result 4 and Result 5 are identical, even though Query 5 is much easier to read and is much shorter.

In this post, we introduced the concept of the Set and explained the rules around their use. We also discussed how to create a Named Set to make your MDX a bit more organized. We closed by showing how the Range Operator can be of great use in creating sets from within ordered hierarchies.

Comments

Pingback from Intro To MDX Decathlon–Introduction | Mark V SQL
Time August 21, 2012 at 10:25 am

[…] 1. Intro To MDX Decathlon – Cube Space: The Final Frontier (1/10) 2. Intro To MDX Decathlon – The Basic MDX Query (2/10) 3. Intro To MDX Decathlon – The Trouble With Tuples (3/10) 4. Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10) […]

Comment from Steven Ladas
Time August 26, 2012 at 9:37 pm

Thanks a bundle for the solid introduction to MDX. You presented the information in the exact fashion I required; simply and with association to star fleet!

Comment from Mark V
Time August 27, 2012 at 9:40 am

Thanks, Steven! It’s great to hear this is helpful for you. :)

Pingback from Intro To MDX Decathlon–.members Function (5/10) | Mark V SQL
Time August 27, 2012 at 1:00 pm

[…] the previous post in this series, Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10), we defined a Set and also discussed the rules that valid sets must follow. In this post, we will […]

Pingback from Intro To MDX Decathlon–PERIODSTODATE() Function (10/10) | Mark V SQL
Time October 1, 2012 at 1:03 pm

[…] diligence for the last ten weeks, you will recall the explanation of the Range operator in the Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10) post. Therefore, you will know that a more succinct expression for the above set would […]

Write a comment