How do I get dimension properties in MDX

Question

Here is a beginner question. In a MDX query, how do I return dimension properties ? The only way I found is to add this alias on column dimension using WITH MEMBER.

WITH
MEMBER [Measures].[User Email] as [User].CurrentMember.Properties("Email")
SELECT {
    [Measures].[User Email],
    [Measures].[Total Fees Value]
} ON COLUMNS,
NON EMPTY {
    CrossJoin([Order].Children, [User].Children) 
} ON ROWS
FROM [LineItemFact]
WHERE ([DateOfSalePst.Days].[2013])

This seems a bit clunky. Problems come up if I want to display grand totals by adding ([Order].[All], [User].[All]) in the row axis. It will try to calculate sums for all the columns and will return exceptions in the User Email because, naturally, it can't calculate sum on email string.

Isn't there a better way to get properties in MDX ?

FrankPl FrankPl · Accepted Answer · 2013-10-24T19:24:21

You can return the properties by using DIMENSION PROPERTIES:

SELECT {
    [Measures].[Total Fees Value]
} ON COLUMNS,
NON EMPTY {
    CrossJoin([Order].Children, [User].Children) 
}

DIMENSION PROPERTIES [User].[Email]

 ON ROWS
FROM [LineItemFact]
WHERE ([DateOfSalePst.Days].[2013])

How you can access these properties depends on the client tool that you use to access the properties. E. g. in SQL Server Management Studio, you can double click the row header cell to see them, Excel Pivot tables display them as tool tips, and as far as I am aware OLAP4J also delivers them as properties of the row axis members.

But some tools like Reporting Services do not allow accessing properties this way, hence for these, the workaround that you put in your question - defining a calculated member that displays them - is the only way to access them.

With regard to aggregating across these, as long as the property is not available, Analysis Services just returns NULL and does not generate an error. However, I am not sure how Mondrian handles this.

How do I get dimension properties in MDX

1 Answers