Disabling Consolidation Levels

[mce]

Hi,

I am trying the find out what is the best way to disable consolidation levels in a cube, meaning not to display any values in consolidation elements.
This might be the requirement for cubes holding Price or Unit Cost info, you may not want to display any aggregations in aggregate levels of product dimensions or time dimension.

I know that we can easily do it using the following rule statement:

Code: Select all

[]=C:0;

1 - Is there any other way of achieving this requirement?
2 - Is it more expensinve to have this rule than leaving it without any rule and let consolidations being calculated as default?

Obviously using a seperate product dimension that does not have C levels or zero weights is not considered as an option.

Thanks in advance for replies.

Regards,

[lotsaram]

If you have this requirement that no consolidations period should be calculated then there is nothing wrong with this approach, it is what I would do.
(You could also use [] = C: Undef if you want to display #N/As rather then zeros).

It should not be expensive as rules are only calculated on demand, a "dormant" rule value that has not been queried costs nothing - also this particular rule requires no feeding.

As the rule is there to prohibit invalid consolidations being queried than presumably users would only query a view with consolidations rarely or by accident ...

[michalb]

I do not want to show any values at consolidated element "Measures", because it would be misleading since children of this Element are just different measures (some are percenteges, some not etc.). I created calculation:
['Measures']=0;
Unfortunately this consolidation is still visible adter suppressing zeros. I guess because rule on this consolidation is not fed and it still containg these consolidated values, but is it any way to feed it? Is it worth to feed it in terms of performance (assumming I'm dealing with very large cubes)?

scrn.png (28.31 KiB) Viewed 1573 times

[Wim Gielis]

Do users need to see the element ?
Can’t you just use a subset rather than a consolidation ?
You would not feed this one.
Or, set the weights to 0.
What about element securiteit of hide it ?
Or, hide the values with formatting as c:;; or show as zero as c:"0";"0";0

Not every option is as useful

[Adam]

I do not want to show any values at consolidated element "Measures", because it would be misleading since children of this Element are just different measures (some are percenteges, some not etc.)

Elaborating on what Wim rightly suggested, a good rule of thumb is to set the weights to zero in a parent-child relationship so you're not aggregating items that shouldn't be aggregated, i.e. a value and a percentage.

I created calculation:
['Measures']=0;
Unfortunately this consolidation is still visible adter suppressing zeros.

A C-level rule is nothing more than an overlay to natural value (consolidation). That natural value is still there hiding under the overlay c-level rule. The only way to make sure this is zero-suppressed is to ensure all child elements have a value of zero, so the natural consolidation is zero.

Given natural consolidation, what usually trips up people is the opposite. Setting a rule of ['Measures']=C:1; continues to be zero-suppressed until that consolidation naturally has an N-level child element with a non-zero weight -and- a non-zero value.

Is it worth to feed it in terms of performance (assumming I'm dealing with very large cubes)?

Why feed if you want it hidden (and you don't feed C-level items).

One additional way to "hide" is to do element/cell security ... you can set the c-level to "NONE". That way it's hidden for non-admin users.

[michalb]

Thanks for your replies, it's a compendium of knowledge. I chose what suits me best.