How can I write an MDX query to return members with ID and Name?

Question

I'm trying to write an MDX query that returns a result that includes a list of members, their Id's, and a measure value. In this example, I have a fact table that is rating values from a survey. Each rating value has a foreign key to the Question dimension which has Id and Name columns. The information I want is each quesion's Id and Name with the aggregate average rating. So far I can get Id and Rating, or Name and Rating, but I am not sure how to get both.

Here is the query I have tried:

SELECT
NON EMPTY {[Measures].[Rating]} ON COLUMNS,
NON EMPTY {[Question].[Question Id].Members, 
           [Question].[Question Caption].Members} ON ROWS
FROM [Ratings]

And it almost works except I get duplicate values, here is a sample result:

[Question].[10]                             3.5
[Question].[11]                             4.2
[Question].[12]                             4.9
[Question].[13]                             4.0
[Question].[10].[blar abc]                  3.5
[Question].[11].[blar def]                  4.2
[Question].[12].[blarrr]                    4.9
[Question].[13].[something else]            4.2

You can see that Question 10 shows up twice with a 3.5 rating, once without the name and once with it.

Is what I want to do possible with MDX? What am I missing? This is being executed by Mondrian.

Ezequiel Muns Ezequiel Muns · Accepted Answer · 2013-01-10T00:01:50

This depends on how the Question dimension's hierarchies are set up. I will assume here that the hierarchy is

All Questions
    > Id 
        > Name

(This is probably not the best way to set up this hierarchy, as Caption is only a property of a given Question member, not a level, but I digress).

What you're doing wrong is that you're creating a set that includes all members from both levels (Id and Caption). Normally the higher-level members (e.g. [Question].[13]) would have rolled-up measure values but because each Id has only got one Caption, the average rating is the same at both levels.

One solution is not to use the Id level, and extract the Id from the UniqueName of the Caption members.

SELECT
NON EMPTY {[Measures].[Rating]} ON COLUMNS,
NON EMPTY {[Question].[Question Caption].Members} ON ROWS
FROM [Ratings]

If you really wanted the Id as a cell in your result, you could alternatively use a Calculated Member to hold that value:

WITH MEMBER [Measures].[QuestionId] AS Iif(
        Not IsEmpty([Measures].[Rating]), 
        [Question].CurrentMember.Parent.Name, 
        NULL
    )
SELECT
NON EMPTY {[Measures].[QuestionId], [Measures].[Rating]} ON COLUMNS,
NON EMPTY {[Question].[Question Caption].Members} ON ROWS
FROM [Ratings]

The Iif function makes sure we have a NULL member when we have no rating, otherwise you'd get a row for every question regardless.

EDIT: You can use a one-level hierarchy that goes All Questions > Question (if you have any other analytically useful categorisation of questions you can add other levels in between).

The Question level represents one question instance, so a bunch of things you can do:

Make the 'unique identifier' (member key, given by the column value in the schema) of this level the Question ID.
Make the 'user identifier' (given by the nameColumn value in the schema) the Question Caption.
You can add other useful properties that don't make sense in a hierarchy to this level (through the Property tag in the schema).

You can then select a question by Caption as [Question].[the caption here], you can select it by Id as [Question].&[Id here]. If you want to retrieve Properties you can do it like this:

SELECT
NON EMPTY {[Measures].[Rating]} ON COLUMNS,
NON EMPTY {[Question].[Question].Members} 
    DIMENSION PROPERTIES [Question].Key, [Question].[other property name] ON ROWS
FROM [Ratings]

Properties are added as metadata in the resulting CellSet.

How can I write an MDX query to return members with ID and Name?

1 Answers