Performance Tuning - MDX Query

Hi,

I have some performance troubles with queries of the type

MEDIAN(existing(DimensionX.Attributehierarchie.Attribute.Members),MeasureA/MeasureB)

[also min,max,stddev,variance, avg, etc....]

or similar with different functions... whereas this attriute has 50.000 to 100.000 Members and the related measures are of all types of aggregate functions (sum, count, distinct count, lastnonempty, ..)

While we have some of this measrues on the columns and analyze be an other hierarchy of the DimensionX on Rows it is incredible slow (up to minutes)

Do you have any ideas how to tune?

Best Regards, HANNES

It's hard to say... I assume you've done all the obvious things like partitioned your cube appropriately and made sure your queries are hitting aggregations?

Chris

|||

As you said - partitiond by time ==> the queries are more or less on leave because the set is leave of this dimension - the lastnonempty forces the time dimension to leaves and therefore are only two other dimensions with possible aggregates...

The only thing I could think of is cache (Subcube) where I do a lot of this statements... Is cache already supported? - does it work at processing time only for the partition i want to cache?

Best Regards, HANNES

|||

Well, it could be worth building the necessary aggregation (manually if that's the only way possible) even if there are only two other dimensions. The Cache statement isn't supported and I don't recommend you use it, but some form of cache warming could be useful because the results of your calculated members should be cached after the first time you've run your query.

What are you seeing in Profiler when you run a query? Are you seeing a bulk evaluation taking place, as Mosha describes in various blog entries (eg http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx)? Looking again at your original example, it might also be worth creating a hidden calculated measure defined as Measures.A/Measures.B, setting its Non_Empty_Behavior property as Mosha describes here:
http://sqlblog.com/blogs/mosha/archive/2007/03/06/averages-ratios-division-by-zero-and-non-empty-behavior.aspx
and then altering your existing expression to be Median(existing MyAttribute.Members, Measures.NewCalc) to see if that makes a difference.

Chris

|||

Thanks for the input.

the hidden meausre - we used this approach already.

we have thought about some relational changes and redesign of the cube (to load more aggregated data) - maybe with this we could reach aceptable performance - we will see.

Thanks, HANNES

|||Can you give a full MDX query for exmaple?|||

Thats an more representive example, but we are on the way to redesign the cube...

with

member [Measures].[X] as [Measures].[Betriebskosten Original]/[Measures].[Immobilien Anzahl], visible=0, non_empty_behavior=[Measures].[Betriebskosten Original]

member [Measures].[Median] as Median(existing(Immobilien.Immobilie.Immobilie.Members),[Measures].[X])

select

{[Measures].[X],[Measures].[Median]} on columns,

{

[Immobilien].[Geographie].[Alle Immobilien nach Geographie],

[Immobilien].[Geographie].[Land].&[AUT],

[Immobilien].[Geographie].[Land].&[AUT].children

} on rows

from

[Angebotsanalyse]

where

[Zeit].[Zeit nach Monaten].[Monat].&[2007-02]

The Backgrounds:

Betriebskosten Orginal: LastNonEmpty|||

we have redesigned the cube to include additional measuregroups with the data relationaly pepared for month, quater, week and year level and useing on the measure groups the sum agg function. We do then a calc meausre dependent on the time level we use the appropriate physical member.

The cube become really complex because of this but we get out the results and performace we need.

HANNES