Inter cube calculaton (3 cubes)

[tobtm1]

Hi,

I am quit new to TM1 and have run in to a problem. I have three cubes, lets call them CubeA, CubeB and CubeC.

CubeA has an element called A_element that is calculated.
CubeB has an element called B_element that should be multiplied by a value from CubeC.
C has two dimensions and in the intersection of those, there are a '1' or a '0'.


I have written a rule that works but it only works on the elementlevel:

['A_element'] = N: DB('B', !Dim1, !Dim2, !Dim3, 'dummy_element', !Dim4, 'B_element') * DB('C', !DimX, !DimY);

My problem is that it is NOT working on the consolidated level. Yes, I have read other topics regarding this but I just cant get it to work. The problem is the feeders. My guess (as a beginner in TM1) that the feeder should be placed in CubeB, at least other topics advice this approach. I have also done that like this in CubeB:

['dummy_element', 'B_element] => DB('CubeA', !Dim1, !Dim2, !Dim3, !Dim4, 'A_element');

I would really appreciate some help!

Thanks

[TrevorGoss]

Hello,

Do you want the C level to naturally consolidate or do you want the Rule to work on the C level?

If you want the rule to calculate above N level, you can change your rule to this:

Code: Select all

['A_element'] = C: DB('B', !Dim1, !Dim2, !Dim3, 'dummy_element', !Dim4, 'B_element') * DB('C', !DimX, !DimY); 

I have changed the qualifer from an N: to a C:, this forces the rule to calculated into a consolidated cell.

You can also do this.....

Code: Select all

['A_element'] =  DB('B', !Dim1, !Dim2, !Dim3, 'dummy_element', !Dim4, 'B_element') * DB('C', !DimX, !DimY); 

The qualifer in this case is just =, which means the rule will calculate on all cells, both N and C level.

You need to feed the rule regardless of what you do with the Qualifier, because if N level cells need to be fed for Consolidations to work.

Trevor.

[tomok]

I really, really hate these hypothetical code examples. How can anyone help you without the real stuff? Please post the complete dimensionality for all three cubes and the actual rule statements, including feeders.

[mattgoff]

I really, really hate these hypothetical code examples. How can anyone help you without the real stuff? Please post the complete dimensionality for all three cubes and the actual rule statements, including feeders.

Agreed. It's not uncommon for someone to have the concepts right and when cobbling together their fictional example correct the problem (or introduce new ones). Plus all of these placeholders are much harder to conceptualize than the real names. I suspect that people think they're helping, that by providing dummy code they're simplifying things into a pseudo test case. Not true for the reasons given.

My problem is that it is NOT working on the consolidated level. Yes, I have read other topics regarding this but I just cant get it to work. The problem is the feeders. My guess (as a beginner in TM1) that the feeder should be placed in CubeB, at least other topics advice this approach. I have also done that like this in CubeB:

['dummy_element', 'B_element] => DB('CubeA', !Dim1, !Dim2, !Dim3, !Dim4, 'A_element');

You can feed from either B or C, but assuming the dimensionality of A and B are similar you're less likely to overfeed if you feed from B. That said, if a lot of Cube C is zero, you may be better feeding from Cube C. It's a bit of a black art, but the first step is to get it working-- you can worry about performance and efficiency later.

['A_element'] = N: DB('B', !Dim1, !Dim2, !Dim3, 'dummy_element', !Dim4, 'B_element') * DB('C', !DimX, !DimY);

['dummy_element', 'B_element] => DB('CubeA', !Dim1, !Dim2, !Dim3, !Dim4, 'A_element');

Here's where giving us dummy code instead of real code and real dimensionality breaks down. You're using Dim1..Dim4, DimX, DimY in the rule in Cube A. Assuming that Dim1..Dim4 are dimensions in Cubes A and B, and DimX and DimY are only in C, you're certainly not getting a result with that rule.

I'd rather not spend time guessing about what you meant in the dummy code. If you want assistance, please just provide the actual rule and feeder and a list of the real dimension names in each cube.

Matt

[tobtm1]

Hi,

Thanks for your answers. Yes, I wrote "a simpler example" because I thought it would be easier to understand but maybee its not, sorry about that. Ok, again, here is the example:

Cube DM is calculating the element "Sales" by multipling like (and this works):

['Sales'] = N:DB('Aggregate', 'All Currencies', !Customer, !Month, !Year, 'Dummy_plant', !Product aggregate, !Version, 'Volume') *
DB('Filter',!Plant,!Product aggregate);

Cube "DM" has the following dimensions:

!Customer, !Month, !Year, !Plant, !Product aggregate, !Version, !DM

Cube "Aggregate" has the following dimensions:

!Currency, !Customer, !Month, !Year, !Plant, !Product aggregate, !Version, !Measure

Cube "Filter" has the following dimensions:

!Plant, !Product aggregate


The questions is, where and how do I write the feeders? I have tried this in the "Aggregate" cube and it doesnt work. When I check feeders the cells are "not fed" and the consolidation doesnt work:


['All Currencies','Dummy_plant','Volume'] => DB('DM',!Version,!Customer,!Month,!Year,!Plant,!Product aggregate,'Sales');

[adamTM1]

Only 'Dummy_plant' will be fed in the DM cube. Are you looking at other plants or just that one?

[tobtm1]

Thanks for your answer/question adamTm1,

Actually it is the complete opposite, "dummy_plant" is the one of the plants that I am not that interested in. If that one is fed - no problem but the other plants (100,200, All Plants) is the ones I care about and offcourse that for example products is aggregated and so forth.

As mentioned in the orignal post - I am quit new to this, especially feeders so I am struggling to understand whats really going on with those. If you could point me in the right direction it would be much appreciated.

Best regards

[tomok]

This statement:

['Sales'] = N:DB('Aggregate', 'All Currencies', !Customer, !Month, !Year, 'Dummy_plant', !Product aggregate, !Version, 'Volume') *
DB('Filter',!Plant,!Product aggregate);

indicates you are calculating sales by taking product price from the Filter cube and multiplying it by volume in the Aggregate cube. However, you are pulling your volume from 'Dummy Plant'. Consequently, you are only going to have sales for 'Dummy Plant'. Is that what you intend? If you have volume for each plant in the Aggregate cube the rule would be:

Code: Select all

['Sales'] = N:DB('Aggregate', 'All Currencies', !Customer, !Month, !Year, !Plant, !Product aggregate, !Version, 'Volume') * DB('Filter',!Plant,!Product aggregate);

Volume drives sales so your feeder would be in the Aggregate cube:

Code: Select all

[' All Currencies', 'Volume'] => DB('DM',!Version,!Customer,!Month,!Year,!Plant,!Product aggregate,'Sales');

However, I've got to say I don't understand how you can you have no currency in the DM cube.

[mattgoff]

Cube "DM" has the following dimensions: !Customer, !Month, !Year, !Plant, !Product aggregate, !Version, !DM

['All Currencies','Dummy_plant','Volume'] => DB('DM',!Version,!Customer,!Month,!Year,!Plant,!Product aggregate,'Sales');

Assuming you listed the dimensions in your cube in their correct order, the feeder uses them out of order. This will fail silently. Try:

['All Currencies','Dummy_plant','Volume'] => DB('DM',!Customer,!Month,!Year,!Plant,!Product aggregate,!Version, 'Sales');

[BariAbdul]

Re: Inter cube calculaton (3 cubes)

Post by mattgoff » Thu Apr 30, 2015 8:46 pm
Assuming you listed the dimensions in your cube in their correct order, the feeder uses them out of order. This will fail silently. Try:

Thanks Mattgoff,Could you be kind enough to please explain and elaborate the above a bit and how to figure out the exact feeder order.

[tomok]

What Matt is telling you is that in TM1, when writing rules using the DB syntax, the dimensions have to be listed in the exact order as they appear in the dimension, unlike the shorthand method where they can appear in any order. Furthermore, if you list them out of order, not only will the rule fail, but you will never get an error message telling you why.

[BariAbdul]

Thanks a lot,tomok.Got it.

[adamTM1]

Thanks for your answer/question adamTm1,

Actually it is the complete opposite, "dummy_plant" is the one of the plants that I am not that interested in. If that one is fed - no problem but the other plants (100,200, All Plants) is the ones I care about and offcourse that for example products is aggregated and so forth.

As mentioned in the orignal post - I am quit new to this, especially feeders so I am struggling to understand whats really going on with those. If you could point me in the right direction it would be much appreciated.

Best regards

As mentioned in an post above, correct the ordering so that it matched the dimension order of the cube you are feeding.

Then instead of !Plant use 'All Plants'. As you have restricted to 'Dummy_plant' at the start of the rule this will only feed to 'dummy_plant' through !Plant.

['Dummy_plant','Volume'] => DB('DM',!Customer,!Month,!Year,'All Plants',!Product aggregate,!Version, 'Sales');
Chances are this is overfeeding.

Alternatively perhaps stop using 'dummy_plant' as the volume source and use more relevant info instead, such as a volume number for each plant. Then use:
['Volume'] => DB('DM',!Customer,!Month,!Year,!Plant,!Product aggregate,!Version, 'Sales');

[tobtm1]

Many thanks adamTm1!

When I wrote 'All plants' instead of !Plant the consolidation worked fine! Very nice!

Also thanks to others that have responded offcourse!