Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: Performance Issue in my MDX query

    Hello,
    I'm new to OLAP systems and MDX, and am doing some testing on Microsoft Analysis Service 2000 SP3, the database is Microsoft SQL Server 2000 SP3. In the cube I designed, the fact table contains purcahse information including the cost and quantity of the parts and the suppliers of the parts. There are 2 measures, qtyAvailable and cost. Two dimensions are involved, which are part and supplier. Here is what I 'm going to do:
    1. calculate sum(qtyAvailable * cost * 0.0001) for all the items in the fact table, let us call this value sum1
    2. find out in the fact table all of those parts with their sum( qtyAvailable * cost ) greater than sum1

    Here is the MDX to do the 2 things above:
    with member [Measures].[prod1] as '[Measures].[qtyAvailable] * [Measures].[cost]'
    with member [Measures].[prod2] as '[Measures].[prod1] * 0.0001'
    with member [Measures].[sum1] as 'sum(crossjoin([part].members, [supplier].members), [measures].[prod2])'
    with member [Measures].[sum2] as 'sum(crossjoin([part].currentmember, [supplier].members), [measures].[prod1])'
    select {[Measures].[sum2]} on columns,
    Filter({[part].members}, ([measures].[sum2]>[Measures].[sum1]) ) on rows
    from cube1

    It takes 9 seconds to calculate only sum1 by using another MDX. The value of sum1 is 8256865.23.
    If I replace sum1 in the MDX provided above with 8256865.23, it takes several minutes to finish.
    But it keeps running for hours if I run the MDX query above with [sum1] instead of 8256865.23. So the calculation of sum1 seems to be the bottle neck. In my MDXquery, it iterates thru the members of the dimension [part]. I don't know whether [sum1] is calculated repeatedly for each iteration or not. However, Sum1 will be constant during the running of the whole MDX query. So sum1 only needs to be calculated once. I tried to use cache to improve the performance but it didn't work.
    Can anyone help to tell whether there is anyway to optimize this query?
    Thanks so much
    Roy

  2. #2
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41
    Do you have to do this using MDX? It seems like a sub-select could do the same job.

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Hi, Phikappa,
    It's just a testing. What I'd like to know is for a calculated member, which will be repeatedly used in a MDX query (like sum1 in this case), is there any way to do the calculation just once within the same query?
    Thanks
    Roy

  4. #4
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41
    Have you tried using a Calculated Cell to hold the value of sum1 to do your comparisons against?

    Typically, I'll create a column in my fact table for sum1. That way, I have more trust in the number.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •