Consolidation problem

[abcuser]

Hi,
in Cognos TM1 v9.5 I have two cubes with names "insurance" and "insurance_source". Insurance_source cube is created because I can't load "Insured" measure data into "insurance" cube because I can't load data to consolidated level. But I can pull data from another cube using rules.

Cube insurance:
1. Measure Unpaid is loaded from turbo integrator - data are loaded at invoice level (white cells): WORKS FINE!
2. Measure Insured is loaded from cube "insurance_source" (Buyer_1-->Insured=190 and Buyer_2-->Insured=140) (red arrows). "Insured" data are only available on "buyer level", "Insured" data are not available at "invoice level": WORKS FINE!
3. Uninsured - here is a problem, I would like Uninsured to be calculated:
3.1. Pink rectangle: 0
3.2. Green rectangle: if(['Insured']>['Unpaid'],0,['Unpaid']-['Insured'])
Note: that is the formula I have created in sample below, but it does not work like I would like it to work.
3.3. Black rectangle: should be sum of children
Note: In spreadsheet bellow is a picture of desired uninsured calculation.

How to create such a consolidation?
Thanks

[tomok]

Your problem is really a perfect example of the problems you face when trying to link cubes together that have different dimensionality. If you feel this type of structure is necessary, then you have to live with the limitations it imposes and the troubles you are going to have implementing what you want to have happen. I don't understand why you feel the need to separate this into two cubes. The issue here is that in TM1 you can natively just do three things as it applies to where in the hierarchy a calculation can be applied: 1) limit it to only leaf elements (placing an N: just before the calculation statement), 2) limit it to only consolidations (by placing a C: just before the calculation statement) or 3) having it apply to all levels in the hierarchy, like your statement does. The problem with option 3 is that the consolidations in your hierarchy may not work. Rules always trump consolidations so if you have a rule that applies to a particular consolidated node then the consolidation will not happen, the value will be whataver the rule evaluates to.

I can make the correct calculation in your example happen by some conditional logic based on the hierarchy level but I don't really like that kind of design. If Invoice is always the leaf element in insurence_market and it always rolls to a Buyer then I can use an IF statement to make the rule only apply to Level 1 items in the insurence_market dimension:

{'Uninsured']=IF(ELLEV('insurence_market',!insurence_market)=1,
IF(['Insured']>['Unpaid'],
0,
['Unpaid']-['Insured']),
CONTINUE);

[mattgoff]

The only way I can see to do it would be as tomok describes, to combine a C-level rule and ELLEV, since you effectively need one rule for level 1 consols and no rules (natural consol) for the rest. Even though it's possible, it's going to almost certainly lead to usability issues down the road IMHO since it breaks the natural "consolidation" logic, hiding it inside your rules, and it is inconsistent between consol levels at that.

I would probably solve your problem by creating a dummy base level element in your MARKET dimension; then you only need N-level rules, and the C-levels work naturally.

Matt

[tomok]

I don't know why I didn't think of this earlier, but your solution would be a lot easier if you would just reverse the layout of your two cubes. Instead of having the determination of who is insured or not in the cube with the invoice detail, move that to the cube that has the total of the remittances, basically swapping out the Measures dimensions. That way, you're just referencing in the total of the invoices to the cube with the payments, a one-to-one relationship, and you can do the insured/uninsured rule at the N: level only and your consolidations will work.

[abcuser]

Your problem is really a perfect example of the problems you face when trying to link cubes together that have different dimensionality...

Interesting, a lot of people are saying this from "TM1 world", but this is basic functionality that I actually need in every cube! I am 'migrating' cubes from "Oracle Hyperion Essbase Server" which supports "upper-level" load. This is something like loading data to consolidated levels, which is not supported by TM1. In Essbase I also have had a calculation scripts (some thing similar like rules in TM1) and I was able to change ANY data in ANY cell just by using some advance calculation scripts. As I have read documentation TM1 does not support such complex scripts that could be applied in Essbase. So loading data at "consolidation" level is my basic problem, that I am trying to solve with two cubes in TM1 'attached' together with DB function using rules and that is the ONLY purpose of having two cubes.

I don't understand why you feel the need to separate this into two cubes.

In first post I have written: "Insurance_source cube is created because I can't load "Insured" measure data into "insurance" cube because I can't load data to consolidated level. But I can pull data from another cube using rules."

If there is any other solution for "consolidation" load problem, then I would really like to know about it.

If Invoice is always the leaf element in insurence_market and it always rolls...

Yes, it is.

{'Uninsured']=IF(ELLEV('insurence_market',!insurence_market)=1,
IF(['Insured']>['Unpaid'],
0,
['Unpaid']-['Insured']),
CONTINUE);

This formula is working fine for buyers level (green rectangle - see attachment), but does not work for country, continent and world levels (red rectangle). Instead of getting 30 to those levels I get 0. Consolidation does not work.

Any idea how to fix this formula?

[abcuser]

I would probably solve your problem by creating a dummy base level element in your MARKET dimension; then you only need N-level rules, and the C-levels work naturally.

Yes, I have already had this idea, but above sample is simple sample, actually my production insurance_cube has 8 dimensions, I was just simplifying the sample. So creating dummy levels in every dimension would produce "confusion" to end-users, you know like having "year 0", "organization unit 0" etc. Not very pretty for end-users. I am afraid I will get tons of questions like: "What is year 0? What is organization unit 0? etc."

[Michel Zijlema]

Yes, I have already had this idea, but above sample is simple sample, actually my production insurance_cube has 8 dimensions, I was just simplifying the sample. So creating dummy levels in every dimension would produce "confusion" to end-users, you know like having "year 0", "organization unit 0" etc. Not very pretty for end-users. I am afraid I will get tons of questions like: "What is year 0? What is organization unit 0? etc."

I would also opt for the 'dummy' levels, but of course the elements should have meaningful names, stating whether the dimension is not applicable for a fact or that there is no further detail for the fact in the dimension. The worst dummy elements are the elements called "Dummy".

Michel

[jim wood]

The worst dummy elements are the elements called "Dummy". Michel

We have one of those. Our B&M department (in their wisdom) set it up as holding pot for product that hasn't been properly setup in the product hierarchy. While it worked for a while, people left the company and the new people coming started excluding it from reports thinking it was product that could be ignored. As you said, very dangerous,

Jim.

[abcuser]

I think that mattgoff's idea with dummy members is not the best, but can be used in some rare cases. I have been using this idea in one of my cubes, but dummy level was only on one single dimension that was not disturbing to end-users. But if dummy level in multiple dimensions this can be very distracting to end-users.

[abcuser]

I don't know why I didn't think of this earlier, but your solution would be a lot easier if you would just reverse the layout of your two cubes.

This is nice idea. I have manage to "move" dimension from insurance to insurance_source cube and wrote additional formula in both of cubes. I think the problem is solved. Thanks you all for ideas and solutions.

Tomok, I don't know if you idea was identical to my solution, is it? If not it would be nice to see the solution you proposed.

[tomok]

Tomok, I don't know if you idea was identical to my solution, is it? If not it would be nice to see the solution you proposed.

Close. I wouldn't leave the elements Insured and Uninsured in the insurence_measures dimension. They're not needed there, don't work, and will only confuse your users.

[abcuser]

I wouldn't leave the elements Insured and Uninsured in the insurence_measures dimension.

tomok, I was probably not clear enough. I NEED cube insurance, because this cube has additional 8 dimensions and 14 additional measures. All of the measures are on the invoice level except for Insured which is on the buyer level. So cube insurance_source I only use for "upper-level load" (load at Insured data to invoice cube consolidation level by rules). So cube insurance_source is not accessible by end-users.

They're not needed there, don't work, and will only confuse your users.

In my previous post I have written some formula that actually Insured and Uninsured ARE WORKING fine. The only data that are 0 are on the invoice level which is OK, because Insured data are not available at the invoice level. Can you please check my previous post and look at the picture - see insurance cube all the data for Insured and Uninsured should be correctly calculated to upper-levels (except for invoice level where there are zeros).