none
Querying a referenced dimension in MDX RRS feed

  • Question

  • Using SSAS 2008 R2, I have a single fact table in my cube. Dimension A (performance metrics) and Dimension B (organisations) are joined together in Dimension C, which is joined to the fact table. A and B are not joined directly to the fact table, but are both referenced through the intermediate Dimension C.

    My plan was to query the cube by using Dimensions A and B, and not C. However, I have attributes in C (those relating to the combination of performance metrics and organisation) which I cannot access without including Dimension C in my MDX query. This slows performance down significantly. The fact data is coming through fine, as are the attributes (properties) from Dimension A and B.

    What options are available to me to get the attributes from Dimension C in my result set, without having to add the dimension with a cross join?

    I would rather not create a load of STRTOMEMBER members if I can avoid it.

    Thanks.

    Friday, January 20, 2012 4:20 PM

All replies

  • My plan was to query the cube by using Dimensions A and B, and not C. However, I have attributes in C (those relating to the combination of performance metrics and organisation) which I cannot access without including Dimension C in my MDX query.


    I assume that when you say that you have attributes in C which you cannot access without including Dimension C that you actually meant to say Dimension A or B?

    Can you clarify what happens when you don't include dimension C? Do you get an error? Does the query just take a long time?

    Are you using materialized or non-materialized reference relationships?

    You will find that non-materialized reference relationships will always be slower than regular relationships or materialized reference relationships as the relationship is resolved at query time which allows for dynamic updates of the intermediate dimension. If you don't specifically need the non-materialized behaviour either changing the relationships to a materialized. Or creating a view with the fact table and dimension C joined, then using regular relationships should give you better performance.


    http://darren.gosbell.com - please mark correct answers
    Monday, January 23, 2012 3:49 AM
    Moderator
  • When I query with a cross join of A and B, the properties of C are returned NULL. When I create a member to see what the CURRENTMEMBER of C is, it returns the "All" member if it is not explicitly queried. It appears that although A and B reference C, and return the correct data from the fact table, C does not have a currentmember, and I cannot access the properties from the key hierarchy.

    I have found a workaround, which is to make the key of C a compound key of A and B (rather than a single surrogate key), and then use STRTOMEMBER to access the properties. But this is far from ideal and requires a new member to be created for every property, rather than simply by returning the properties.

    The referenced relationships are materialised.

    Monday, January 23, 2012 8:48 AM