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

By Mark on August 20, 2012 • ( 5 Comments )

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

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

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

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]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Result 1

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

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

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

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

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

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

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

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.

Categories: Decathlon, MDX

Tagged as: Decathlon, MDX

5 replies »

Pingback: Intro To MDX Decathlon–Introduction | Mark V SQL
Steven Ladas says:

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!

LikeLike
Mark V says:

August 27, 2012 at 9:40 am

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

LikeLike
Pingback: Intro To MDX Decathlon–.members Function (5/10) | Mark V SQL
Pingback: Intro To MDX Decathlon–PERIODSTODATE() Function (10/10) | Mark V SQL

Mark Vaillancourt

I drink coffee and know things

Categories

Archives

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

Prime Directives of Sets

The Named Set

The Range Operator “:”

5 replies »

Categories

Archives

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

Prime Directives of Sets

The Named Set

The Range Operator “:”

Share this:

Like this:

Related

5 replies »