Questions about aggregation

[kudzis]

Hello,

I have a cube with multiple dimensions ('Time', 'Measures', and others) and for specific measures I want to consolidate to average (instead of sum) on all dimensions except 'time'. For example, my 'clients' dimension has 3 levels - country > city > klient; my time dimension has 2 levels - year > month.
I want to see averaged countries and cities for each month, but sum of all months in year level. I have achieved this by creating "normal" average and using ConsolidateChildren to calculate year: ['2011'] = ConsolidateChildren('Time'); Is this the most effective way (considering simplicity of rules and performance)?

My second question is about calculating averages. Right now, each measure i'm averaging needs two aditional measures:
['counter'] = N: abs(sign(['measure to be averaged'] ));
['averaged measure'] = N: ['measure to be averaged']; C: ['measure to be averaged'] \ ['counter'];

Since I need to average ~20 measures, I'm afraid that adding 40 additional measures will affect my performance. Is there a more efficient way to calculate averages?

[Gregor Koch]

Hi
If your situation is that you have a cube that needs to store values, counters and averages for all measure you could consider adding a dimension ('datatype' or something like that) that has "Value", "Counter" and "Average" as elements. Makes the writing of generic rules quite simple then, plus you only ever have to add one measure and have catered for 'the other two measures' already.

But even if you choose to add 40 measures... why would that be a performance problem? Obviously calculating 20 averages will take longer than calculating one average. But I'd be surprised if their is a big performance difference it the different approaches for an individual average.

On the other hand I would be curious if this

['counter'] = N: abs(sign(['measure to be averaged'] ));

performs better than this

['counter'] = N: IF(['measure to be averaged']<>0,1,0);

Which is kind of a linear approach vs conditional.

Personally I try to work around ConsolidateChildren as much as possible and only really use it in 'feeder less consolidation rules' where the data (also basis for the feeder) sits directly in the children. Maybe it is just me, but in most other cases (especially in cubes with lots of dimensions) it didn't perform all that well. Please don't read this as "ConsolidateChildren doesn't perform well" or the other way round.
Are you trying to sum the actual value on the years or are you summing the average (that would be interesting)? Does that actually work for you?

[kudzis]

Are you trying to sum the actual value on the years or are you summing the average (that would be interesting)? Does that actually work for you?

Hello,

for a year, I'm summing averaged values of each month. I've talked with my client and I managed to convince him he needs only 7 aggregated measures, so I'll be doing averaging the old way, with additional measures.

[Steve Rowe]

I'm also not a big fan of consolidate children, a more old school way of solving this problem would be

['counter'] = N: abs(sign(['measure to be averaged'] ));
['averaged measure'] = N: ['measure to be averaged'];
['averaged measure'] = C:
If( Ellev ('Time' ,!Time)>0, stet
['measure to be averaged'] \ ['counter']);

You'll need allowseperateNandC=T in your cfg for this to work.

You'll need to test and see if consolidate children gives you better perofrmance

HTH