MDX query for Subtree Members and Leaf Members


Suppose we have a GEO dimension. In the dimension we have some of the attributes which are in fact geography at different levels. We also have a hierarchy with these attributes.

The lowest level or the leaf level of this Hierarchy is “Level1 Key” which is in fact brick level geography codes. The top level is Level5 Name, which corresponds to the “National”.

Now we will see some useful mdx queries to retrieve specific type of members from this Hierarchy.

We want to retrieve all the leaf members which is essentially the “Level1 Key” members here.

 

select {[GEO].[Hierarchy].[Level1 Key].members} on rows,

{} on columns

from [Beiersdorf];

 

But if we want to write a generic mdx query assuming that we don’t know the lowest level. We just know the Hierarchy and we want to have all the leaf members.

 

select

{

      filter(

            [GEO].[Hierarchy].members,

      [GEO].[Hierarchy].currentmember.LEVEL.ordinal=[GEO].[Hierarchy].LEVELS.COUNT-1

      )

} on rows,

{} on columns

from [Beiersdorf];

 

This query uses the logic that we are taking all the members of the hierarchy and then matching the ordinal no of each member with the Total level count of the hierarchy. Then if the ordinal no is 1 less then the total level count of the hierarchy, we are meeting the leaf members. This mdx query does not need to hard coded lowest level name.

 

We want to retrieve all the members at all the levels.

 

select {[GEO].[Hierarchy].members} on rows,

{} on columns

from [Beiersdorf];

 

This is really simple.

 

But if we want to get this for a particular subtree of the full hierarchy, then we will need a simple trick. Suppose we want all members of this hierarchy that is rooted at a member of “Level3 Name”.

 

 

We want to get all the members that lye in the subtree of BRUNETTI

 

 

select

{     filter(

            [GEO].[Hierarchy].members,

            ancestor(

                  [GEO].[Hierarchy].currentmember, [GEO].[Hierarchy].[Level3 Name]

            ) is [GEO].[Hierarchy].[Level3 Name].&[1]

      )

} on rows,

{} on columns

from [Beiersdorf];

 

The trick here is to take only the nodes from the hierarchy, that has BRUNETTI as there ancestor at “Level3 Name”.

 

Now we want to get all the leaf members under BRUNETTI, not all the members of the subtree.

This is simply the combination of the above 2 queries.

 

select

{

      filter(

            [GEO].[Hierarchy].members,

            [GEO].[Hierarchy].currentmember.LEVEL.ordinal=[GEO].[Hierarchy].LEVELS.COUNT-1

            and

            ancestor(

                  [GEO].[Hierarchy].currentmember,

                  [GEO].[Hierarchy].[Level3 Name]) is [GEO].[Hierarchy].[Level3 Name].&[1]

            )

} on rows,

{} on columns

from [Beiersdorf];