Home > MDX Challenges > MDX #3 – Use three-part name to exclude All in a dimension

MDX #3 – Use three-part name to exclude All in a dimension

Unlike TSQL, a simple MDX query can include the All level in a dimension to allow use to see a measure aggregated across ALL members in the dimension. This is a powerful and welcome blessing given to us from the Analysis Services and MDX engine.

However, this blessing can be annoying in the Reporting Services when we want to do the aggregation within the Reporting Services by using the grouping feature.

In the SSRS reports I designed, I often use the three-part naming convention for dimensions to exclude the ALL level in a dimension. This allows me to get measure values only for the members in the dimension hierarchy without the aggregation at ALL level.

Three-Part Name

Here is an example of the three-part naming convention for dimensions:

[Trans Reason].[Reason].[Reason]

The first part is the name of the cube dimension. The second part is the name of the attribute hierarchy. The third part is the name of the default level for the attribute hierarchy, which is the same as the attribute hierarchy.

If you have a user hierarchy for a dimension, the above still applies with the second part being the name of the user hierarchy and the third part being the level you want. The difference is that the level name will be likely different from the user hierarchy.

So these will be the general usages for both attribute hierarchy and user hierarchy.

[Dimension].[Attribute Hierarchy].[Level]
[Dimension].[User Hierarchy].[Level]

Take a look at the following four MDX query results.

1. Using the three-part name to exclude the ALL level

image 

2. With only two-part name (dimension and hierarchy), only ALL is returned.

(ALL is the default member for the hierarchy in the design.)

image

3. With two-part name and AllMembers function ALL level and all the children members are returned.

image 

4. With two-part name and Children function, the ALL level is also excluded.

(So the three-part name is not the only way.)

image

So next time when you sit down to write MDX queries, do not hesitate to experiment and draw conclusions for yourself.

Categories: MDX Challenges
  1. January 15, 2013 at 9:23 pm

    Great tip! Was struggling with this for an hour! thanks!
    -G

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: