Consolidations and Dimension Sort Order

[Froyo]

Hi there,

I have an issue with Cells which are on several consolidations.

Example:

I have two dimensions:
Dimension A
Consolidated A
Element B
Element C

Dimension B
Consolidated 1
Element 2
Element 3

Now it is the case, that some of the Dimension B Elements have Rules, what can end in problems for consolidations:



For the cell where the 2 consolidations collide there I have to make a choice, which consolidation should be used. (Dimension A or Dimension B)

As I dont like the idea to use a ConsolidateChildren Rule element for all cells and as its not possible to rebuild the cube with a new dimension order, I experimented with "Re-Order Dimensions".
That seemed to work, because when I use the rules tracer I can see that TM1 uses the dimension which is ordered higher.

But the actual cell value doesn´t change.
So in the example he allways shows 100 but I want him to show 2. Depending on the dimension order the rules tracer shows 2 or 100, but the actual cell allways shows 100.

Any ideas for this behaviour?

Best regards!

[jim wood]

If you don't want to start messing around with physical cube order then for me you need to use ConsolidateChildren. It doesn't need feeding in anyway, so it shouldn't "in theory" effect your cubes performance,

Jim.

[tomok]

As I dont like the idea to use a ConsolidateChildren Rule element for all cells and as its not possible to rebuild the cube with a new dimension order, I experimented with "Re-Order Dimensions".
That seemed to work, because when I use the rules tracer I can see that TM1 uses the dimension which is ordered higher.

First off, let me say that what the Rules Tracer shows is meaningless in this regard. The fact that it changes, depending on the dimension order, sounds like a bug to me. All that really matters is what your cube returns, either in a cube view, or web sheet or Excel sheet. In this case it is the 100. The second thing I'll point out is that once you "override" the natural consolidation algorithm in TM1, be specifying different rules for C and N level elements, AND you want some things to consolidate and others to not, then it is incumbent upon you as the developer to write the rules to do that (usually with combination of IF logic and the ELLEV function). You can't just assume TM1 is going to magically know which C level elements should consolidate and which ones should follow your rule override. Perhaps if you post your rules we might be able to help you. Without that it's a waste of time.

[Froyo]

Hello,

I absolutely don´t want to let TM1 magically know how to consolidate.

In this case TM1 has two options to consolidate. It can consolidate dimension 1 (which would mean to have the 100) or dimension 2 (which would be 2). I just want TM1 to allways consolidate dimension 2.
That´s no case of "sometimes use a rule and sometimes not". There is never a rule for this element but allways two possible consolidations.

So I don´t see why this is a waste of time. The question is, how to tell TM1 which dimension should be used for consolidation, when there are 2 options.
In this case the rule would be, El1 and El2 completely rule derived. Consolidated 1 completely not.

[Wim Gielis]

Maye you overlooked Jim's contribution in this topic?
ConsolidateChildren

[Gabor]

At least prior TM1 releases were very clear on that topic (copy of 9.0 Clients Guide "Overriding C: level elements with rules"):

"You have no control over the order in which TM1 performs dimension consolidations.
Furthermore, depending on which consolidation path is optimal at any given moment, TM1 may alternate between paths.
Consequently, you may request the <example> value twice in the same TM1 session and get different results."

Here the 9.0 conclusion, similar to other replies above:
"You can remedy this situation by writing a rules statement ... as the sum of its immediate children ... ConsolidateChildren..."

Recent releases may have a similar note, I didn't check that.

[Froyo]

Thanks Gabor.

That is the information I was missing. Unfortunately not the one I hoped to get. So TM1 does "the magic" here, which means to may have 2 results for the same request.
In my oppinion not the way I expected TM1 to behave.

So "ConsolidateChildren" seems to be the only possiblity to handle this issue.

@Wim Gielis: Thanks, I did not overlook it, but as mentioned in the opening post, I wanted to avoid the use of rules for this case because our rules in the model are very complex and adding new lines can end up in endless testing and new errors plus adding rules to those cells means, that those consolidations are now write protected.

[Wim Gielis]

Yes, as confirmed by 3 people in this topic, ConsolidateChildren is your only option (unfortunately because me too, I hate using it).