Re: High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Deepak Puri (deepak_puri_at_progressive.com)
Date: 02/29/04 

Date: Sat, 28 Feb 2004 21:38:53 -0800

Can you provide an idea of what the generated MDX looks like (it is
presumably too large in its entirety)?

It may be too tall an order to ask that all auto-generated MDX queries
execute efficiently, without any tweaking. You could not similarly
expect a relational reporting tool to always generate efficient SQL, no
matter how complex the query or table indexes (well, maybe one day).
When you say that the query returns in 3 mins without the dimension with
3000 members, just excluding 2 members in the filter alone may cause a
3000-fold increase in on-the-fly aggregation, ie. an extrapolated time
of 9,000 mins. You might achieve this exclusion more efficiently with
custom MDX (for example, if all your base measures have sum or count
aggregation, you can subtract the aggregation for just the excluded
members from the total).

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Can SSAS deal with "non-standard" table schemas?
    ... relational schema and the multidimensional one. ... generated by one script that defines the aggregation formula based on ... with MDX Scripts - this is the worst solution because you don't get ...
    (microsoft.public.sqlserver.olap)
  • Re: Calculations
    ... >if you wish this calculation to be valid only at the ... >need to restrict your MDX expression by testing the level ... aggregation function ... if you wish to re-aggregate Gross Profit ...
    (microsoft.public.sqlserver.olap)
  • RE: Better approach for common business problem ? (long post)
    ... here's the MDX to do the member substitution I talked ... much quicker to SUM if you had built the right aggregations on your cube. ... /*Need to use nasty string functions to get IIF to return members or sets. ... > by looking at the Query Number counter. ...
    (microsoft.public.sqlserver.olap)
  • Re: Paging woes
    ... Your solution would work perfectly if i knew wich dimensions use this ... I'm building a general purpose client that ... but you can still reference them in your MDX; ... >> I need to display all members of a level in a web based client. ...
    (microsoft.public.sqlserver.olap)
  • Re: How to simplify such set {MemberI:MemberN}?
    ... (an interface which ask the user and then ... This formula will use all aggregated members instead-of the day level ... If we aggregate ower this set in MDX query ...
    (microsoft.public.sqlserver.olap)