Intro To MDX Decathlon–.members Function (5/10)

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

In 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 introduce a function that can provide an easy way to form a set: .members.

The .members function returns a set of the members of the dimension, hierarchy, or hierarchy level provided. We will cover each of those three instances.

[<<Dimension>>].members

You can use the .members function directly on a dimension when that dimension has one and only hierarchy. When you do, you get a set that comprises all of the members of that hierarchy, in order according to the hierarchy’s configured sort order in the cube. Query 1 shows an example of calling the .members function directly on a dimension with only one hierarchy in my Borg cube. The [Series] dimension has only one hierarchy, [Series Name].

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].members ON ROWS
FROM [Borg]

 

Result 1

image

Notice that the result of the .members function on my [Series].[Series Name] hierarchy includes the [All] member. In a later post in this series, we will discuss a way to exclude the [All] member and just get the [Series Name] values themselves. 

[<<Dimension>>].[<<Hierarchy>>].members

Attribute Hierarchy

The only difference between Query 2 below and Query 1 is that we specify the name of the hierarchy whose members we want in our set. Although leaving off the hierarchy name, as in Query 1, is technically allowed when the dimension has only one hierarchy, I prefer specifying the hierarchy name anyway, as in Query 2. To me, this makes my MDX more complete and easier for another person to follow.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].members ON ROWS
FROM [Borg]

 

Result 2

image

Notice the Result 2 is identical to Result 1. They both return the exact same set of members. You notice, also, that they both dealt with the same Attribute Hierarchy. Since you have been basing your life around the release of each new post in this series, you will recall from the first post, Intro To MDX Decathlon – Cube Space: The Final Frontier (1/10), that an Attribute Hierarchy is one that contains only a single Attribute. The [Series].[Series Name] hierarchy contains only the [Series Name] attribute.

User Hierarchy

If you call the .members function on a User Hierarchy, which is composed of more than one Attribute, you still get all of the members of the hierarchy in order, but now you have more levels to deal with. Query 3 shows an example of calling the .members function on my [Date].[Date Hierarchy] user hierarchy from my Borg cube. You will note the Query 3 also has a WHERE clause as well as the NON EMPTY keyword (which we get to later in this series). I did this so that the results returned by the .members function would fit into a tighter space so that you can notice the pattern of the returned members.

Query 3

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , NON EMPTY [Date].[Date Hierarchy].members ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

 

Result 3

SNAGHTML337d89

Just as a reminder, my [Date].[Date Hierarchy] user hierarchy has the following structure:

Level Attribute Hierarchy
0 [All]
1   [Calendar Year]
2     [Calendar Quarter]
3       [Calendar Month]
4         [Full Date]

Result 3 still starts with the All member. The next member you see is [Calendar Year].[1987]. This is the FIRST member immediately below the All member that that meets the criteria. We then proceed through all of the members in 1987 before getting to 1988, which would make sense since my [Date Hierarchy] is in ascending Full Date order. Each path in the hierarchy is exhausted before going to the next path. So, every member in [Calendar Quarter].[1987–Q3] appears before anything in [Calendar Quarter].[1987–Q4]. Likewise, every member in [Calendar Month].[1987-09] appears before anything in [Calendar Month].[1987-10]. And so on. You can see that the results of using .members on a User Hierarchy will travel up and down the levels in the hierarchy in order to show every member of every level while still keeping everything in the sort order configured for this hierarchy in the cube. This behavior can be fantastic for some reporting scenarios. Just keep in mind that for very deep or populous hierarchies, this can return a lot of members. Use it wisely.

[<<Dimension>>].[<<Hierarchy>>].[<<Level>>].members

With User Hierarchies, sometimes you just want to work with the members of a particular level. When you use .members to do this, you just get the members of that level. Query 4 shows an example of doing this. The only difference between Query 4 below and Query 3 is that we are adding in the [Calendar Year] level before using the .members function.

Query 4

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , NON EMPTY [Date].[Date Hierarchy].[Calendar Year].members ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

 

Result 4

image

You will notice that the [All] members is not returned. This is because the [All] member is not at the [Calendar Year] level of my [Date Hierarchy]. It is the level above.

Making that one, tiny change to the query from Query 3 produced vastly different results in Query 4. That is something to keep in mind when dealing with hierarchies in MDX. Little things can have a big impact.

 

 

 

 

 

 

 

In this post, we looked at the behavior of the .members function in different scenarios. An important thing to remember is that .members outputs a Set. So, you can pass a .members expression to another function that takes a Set as input in order to nest functions. Doing so can allow you to implement some very sophisticated logic in your MDX queries.

Comments

Pingback from Intro To MDX Decathlon–Introduction | Mark V SQL
Time August 27, 2012 at 1:02 pm

[…] 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) 5. Intro To MDX Decathlon – .members Function (5/10) […]

Comment from geeta
Time October 21, 2013 at 10:19 pm

Good information.

What if i have multiple hierarchy and would like to pass that as the parameter means user can select anything but it should consider as parameter.

Comment from Mark V
Time October 28, 2013 at 7:05 pm

Hi, Geeta. I am not sure I am understanding what you mean. Can you clarify a bit?

Comment from geeta
Time November 2, 2013 at 6:38 pm

Hello

Thanks for the response.

I have GL account and for this GL account we do have multiple hierarchy and while passing this to the drill though i would like the code to accept any hierarchy which user select.

I could not find any IIF condition for the characteristic Eg: if i have DataSource as fired in the sender report i may have XGL when it display in the receiver report it should take its parallel pair that is XGL1. how to go with the IIF condition . Please can you helping his as well

Thanks

Write a comment