none
Suggestion of Building MDX Query RRS feed

  • Question

  • Hi,

    I am creating a small app that builds a MDX query. I want the query to be performant and so in I am considering some cases and building the query. I have cross joins of dimension members

    I have two questions on how to build my query. Require some inputs/directions:

    Scenario : User can browse Multiple dimension Hierarchies and Attributes and select specific members or all.

    Lets consider this scenario : I have two dimensions A and B. A has two attributes A1, A2. B has two Attributes B1, B2

    User selects all members of A1, Some Members of A2, All members of B1, Some Members of B2


    Currently my Query Builds Sets for Selected members in A2 (Lets Say A2Set) and in B2 (Lets Say B2 Set) and in row axis does a cross join of A1, A2Set, B1, B2Set

    So my query looks like  :

    WITH

    SET [A2Set] AS
    (
    EXISTS([A].[A2].[A2].Members,
    {
     <Selected members for A2>
    }
    )

    SET [B2Set] AS
    (
    EXISTS([B].[B2].[B2].Members,
    {
     <Selected members for B2>
    }
    )

    Select {[Measures].[Measure1], [Measures].[Measure2]} on columns,
    ([A].[A1].[A1] * [A2Set] * [B].[B1].[B1] * [B2Set]) on rows from <Cube Name>


    Here are my questions :

    1. While creating the query, which is better to use  (Exists or Exclude), for example : if total members count in A2 is 100 and user selected 75 of them, what is better to use :

    1st case :
     SET [A2Set] AS
    (
    EXISTS([A].[A2].[A2].Members,
    {
     <75 Selected members for A2>
    }
    )

    2nd case :

    SET [A2Set] AS
    (
    EXCEPT([A].[A2].[A2].Members,
    {
     <25 Selected members for A2>
    }
    )

    2. I read some article on Performance improvement in MDX query on cross joins and i came across two points which i am not able to figure out a optimum way . the two suggestions are :
     a) keep attributes/hierarchies of same dimension one after another in a cross join, which is what i am doing
        If user selects all in everything : ([A].[A1].[A1] * [A].[A2].[A2] * [B].[B1].[B1] * [B].[B2].[B2])
     b) Keep the larger set on the left hand side of cross join
     In my case, there can be various combinations. Like A1 and A2 are of the same dimension, B1 and B2 are of same dimension. But the number of members can be different. For example : A2 may have less members than B1. In that case how do i arrange.

    I have three options :
    1. Keep as in option (a). Same dimension keep together.
    2. Keep as in Option (b). Arrange based on number of members selected.
    3. Group same dimension attributes/hierarchies together, and in each group arrange them by the number of members selected.

    Your suggestions please.

    - Girija
     


    Girija Shankar Beuria
    Saturday, October 22, 2011 12:04 AM
    Answerer

All replies

  • A clarification: did you mean Except(), rather then EXCLUDE()?
    - Deepak
    Saturday, October 22, 2011 3:36 AM
    Moderator
  • Hi Deepak,

     

    Sorry, My Bad. Yes it is EXCEPT.

     

    - Girija


    Girija Shankar Beuria
    Saturday, October 22, 2011 3:57 AM
    Answerer
  • No suggestions :(
    Girija Shankar Beuria
    Wednesday, October 26, 2011 8:43 PM
    Answerer
  • Why do you have to do an EXISTS or an EXCEPT. If you already have the list of members, you can construct the set as-is

    SET [A2Set] AS
    (
    {
     <Selected members for A2>
    }
    )

    ...or am I missing something?

     

     


    -Remember to mark as helpful/the answer if you agree with the post.
    Wednesday, October 26, 2011 10:39 PM
  • Hi,

     

    I can do that too.

    I am creating the query like the format i have given above. I know this is not the optimum one. So i require suggestion on the query format as well as specific questions that i have.

     

    - Girija


    Girija Shankar Beuria
    Wednesday, October 26, 2011 11:49 PM
    Answerer
  • Anyone, suggestions ?

     

    - Girija


    Girija Shankar Beuria
    Monday, October 31, 2011 7:56 PM
    Answerer