[New to TM1] Rule to sum up total numbers + joining data

[oleisbest]

Hey,

I have a few questions I hope someone can help me answer

I am trying to join data from two different cubes. Cube one includes the name of different accounts and account numbers, while cube two includes the accounts on an aggregated level with planned numbers.

1. I would like to use a rule to sum up the different accounts in one total. I have made a new element in my dimension where I would like to send the total but I can’t seem to get the sum function to work (if this is a function I TM1?). For now I have solved it by using the following rule:

['Konsolidert']=['1199']+['1299']+['1599']+['1999']+['2099']+['2990'];

This rule sum up the 6 account numbers in the element "Konsolidert", but when the complexity increases this is not a good solution. Is there a sum function I can use? Sum all accounts in a given interval?

2. In my second cube I have aggregated numbers, I would like to join these numbers with cube one. The two cubes consists of the following elements:

Cube 1
Car 1
Car 2
Sum 1+2

Car 3
Car 4
Sum 3+4

Cube 2
Sum 1+2
Sum 3+4

The mutual name in both cubes is "Sum 1+2" and "Sum 3+4". I would like to get the value from cube two, for instance "Sum 1+2" and send this value to the same element in cube one.

Any help is very helpful

[David Usherwood]

You're treating TM1 as a relational engine, which it isn't.
Use consolidation in your dimensions to add up numbers - rules are inefficient for pure adding up.
To get the total(s) from one cube into another, use cross cube rules, on the lines of:

Code: Select all

# in destination,
['Sum 1 +2'] = db('Source',.....'Sum 1+2')
# in source
feeders;
['Sum 1+2'] => db('Dest',.....'Sum 1+2')

But watch your feeders as the number of links grows. Feeders fire from base (N) elements only, so you can't simply link matching names. (You can for rules.) Either leave them explicit, as above, or build an attribute against the 'from' dimension to do the lookup for the 'to' dimension.
Fiddly I know, but OK once you are used to it.

[ajain86]

1. Would the elements that you are adding be common in some way? If so, then you can create a hierarchy which would do the sum without the need for a rule:

Parent Member: Konsolidert
Children (all with a weight of 1): 1199, 1299, 1599, 1999, 2099, 2990.

In this, you would be able to add more children as needed and the total would be updated automatically.

2. You can use the DB function to reference a cell in another cube.

[oleisbest]

Thanks a lot guys. I will try it out and see how it works!

I tried getting the DB function to work but all I got was a error message. I will make a new try to see if I overlooked something.

[oleisbest]

You're treating TM1 as a relational engine, which it isn't.
Use consolidation in your dimensions to add up numbers - rules are inefficient for pure adding up.
To get the total(s) from one cube into another, use cross cube rules, on the lines of:

Code: Select all

# in destination,
['Sum 1 +2'] = db('Source',.....'Sum 1+2')
# in source
feeders;
['Sum 1+2'] => db('Dest',.....'Sum 1+2')

But watch your feeders as the number of links grows. Feeders fire from base (N) elements only, so you can't simply link matching names. (You can for rules.) Either leave them explicit, as above, or build an attribute against the 'from' dimension to do the lookup for the 'to' dimension.
Fiddly I know, but OK once you are used to it.

Does the two cubes need to have one mutual identical dimension? Can this be why I havent got my DB-function to work? The two dimensions I try to join only consist of a few mutual elements like the ones listed below:

Cube 1 - Actual numbers
Car 1
Car 2
Sum 1+2
Car 3
Car 4
Sum 3+4

Cube 2 - Budget
Sum 1+2
Sum 3+4

I would like to get the two sums from Cube 2 into the two sums in cube 1. Car 1, car 2, car 3 and car 4 will not have any data transfered as they dont exist in cube 2.

Thanks in advance for all the help!

[cramejia_09]

In TM1, when it comes to adding or subtracting, we must use the logic of hierarchy of elements. In this, associates the relationship of Parents and children. In your case, you can use this hierarchy:

Sum 1+2 = Consolidated Element, Parent of Car 1 and Car 2 elements
Sum 3+4 = Consolidated Element, Parent of Car 3 and Car 4 elements

it must looks like this:

Sum 1+2 will be = 100
-Car 1 if = 45
-Car 2 if = 55
Sum 3+4
-Car 3
-Car 4

So that TM1 can consolidate the values using a consolidated element or a Parent element from its children or N: elements.

In your case you have Two cubes such as Cube 1 - Actual numbers and Cube 2 - Budget, In TM1 rules there nothing to worry about if you have a different element names as long as you know the source and destination elements that you want to supply a value or data.

In Cube 2 - Budget, rules:

[ 'Sum 1+2' ] =N: DB('cube 1 - Actual Numbers','Sum 1+2');
[ 'Sum 3+4' ] =N: DB('cube 1 - Actual Numbers','Sum 3+4');

In Cube 1 - Actual numbers rules;
Skipcheck;

Feeders;
[ 'Sum 1+2' ] => DB('Cube 2 - Budget','Sum 1+2',);
[ 'Sum 3+4' ] => DB('Cube 2 - Budget','Sum 3+4');


Hope it helps. =)

[oleisbest]

Thanks a lot cramejia_09! I finally understood the use of consolidated elements

Unfortunately I can’t get the data to move from cube 1 to cube 2. If you take a look at the picture below you see I have two rater simple cubes I would like to transfer the value 20 and 16 from the cube named "Håkon SLETT" and over to the cube "Håkon SLETT2". I would like the values to be moved to "TALL FRA KUBE 2".

I try using the following rule:

Code: Select all

['TALL FRA KUBE2']=DB('HÃ¥kon SLETT',!HÃ¥kon - Sum biler,!HÃ¥kon - Liste over biler);

But this formula only gives me the following error message:
Line 1: Syntax error on or before: !HÃ¥kon - Sum biler, invalid string expression Rule could not be attached to the cube, but changes were saved.



If someone could help med land this I would be really grateful!

[ajain86]

Try:

['TALL FRA KUBE2']=N:DB('HÃ¥kon SLETT',!HÃ¥kon - Sum biler,!HÃ¥kon - Liste over biler);

The additional "N:" after the equal sign means the rule is applied to only level 0 elements. Did you use the "DB" button in the rule editor to create the rule?

Also, you are doing this in the HÃ¥kon SLETT2 cube, right?

[oleisbest]

Try:

['TALL FRA KUBE2']=N:DB('HÃ¥kon SLETT',!HÃ¥kon - Sum biler,!HÃ¥kon - Liste over biler);

The additional "N:" after the equal sign means the rule is applied to only level 0 elements. Did you use the "DB" button in the rule editor to create the rule?

Also, you are doing this in the HÃ¥kon SLETT2 cube, right?

I get the same error message as above. I created the rule using the DB button. The rule is applied to the cube "HÃ¥kon SLETT2".

[oleisbest]

I think my problem is what described in IBMs help file:

http://publib.boulder.ibm.com/infocente ... 800A7.html

But I dont understand how I can modify this to make it work for me.

Anyone?

[David Usherwood]

@oleisbest, have you written a feeder to match your rule? I've looked at your screen capture but am a bit puzzled because the row dimension apparently has the same name in each , but the elements 'Sum Car 3+4' & 'Sum Car 1+2' are parents in Hakon Slett and n level in Hakon Slett2.

[oleisbest]

@oleisbest, have you written a feeder to match your rule? I've looked at your screen capture but am a bit puzzled because the row dimension apparently has the same name in each , but the elements 'Sum Car 3+4' & 'Sum Car 1+2' are parents in Hakon Slett and n level in Hakon Slett2.

I have not written any feeder yet. But this shouldn't be necessary to make it work right? This will only increase the speed of the calculations if I have understood it right?

With regards to the dimensions I have used two different dimensions in the rows, the names are just a little bit similar. The first is named "HÃ¥kon - Sum biler" while the second one is named "HÃ¥kon - Sum bilder". Did I understand you right?

[David Usherwood]

If you have skipcheck enabled in the destination cube you _must_ have working feeders in the source cube. Do you?

[oleisbest]

If you have skipcheck enabled in the destination cube you _must_ have working feeders in the source cube. Do you?

I have not written any feeders or skipchecks. The only rule I have written is the one above. Maybe it will help if I attach som more pictires:

This is a picture of the cube structure:


This is a picture of the dimensions:

[Gregor Koch]

Hi

Not going into details of how this could be done differently from a model perspective by maybe having one 3 dimensional cube, here is the really short solution to your specific problem.

1. The cubes don't need to have a common dimension
2. Yor DB function, although it might be created by TM1, throws an error because you don't have a 'HÃ¥kon - Sum biler' dimension in your 'HÃ¥kon SLETT2' cube and therefor you can not use it with the '!'...(simplified)

So instead of using in your Slett2 cube

['TALL FRA KUBE2']=N:DB('HÃ¥kon SLETT',!HÃ¥kon - Sum biler,!HÃ¥kon - Liste over biler);

try this

['TALL FRA KUBE2']=N:DB('HÃ¥kon SLETT',!HÃ¥kon - Sum biler SLETT, 'Sum Total');

as an example.

Hope this gets you going.

Cheers

[oleisbest]

Hi

Not going into details of how this could be done differently from a model perspective by maybe having one 3 dimensional cube, here is the really short solution to your specific problem.

1. The cubes don't need to have a common dimension
2. Yor DB function, although it might be created by TM1, throws an error because you don't have a 'HÃ¥kon - Sum biler' dimension in your 'HÃ¥kon SLETT2' cube and therefor you can not use it with the '!'...(simplified)

So instead of using in your Slett2 cube

['TALL FRA KUBE2']=N:DB('HÃ¥kon SLETT',!HÃ¥kon - Sum biler,!HÃ¥kon - Liste over biler);

try this

['TALL FRA KUBE2']=N:DB('HÃ¥kon SLETT',!HÃ¥kon - Sum biler SLETT, 'Sum Total');

as an example.

Hope this gets you going.

Cheers

Hi,

Thanks for the reply! I know this can be done by using 3 dimensions in one cube. The only reason I ‘am doing this is I need this function to work at a later stage. So I would really like to know if it actually can be done

I think you are very close with your example, but when using the formula I still get the same error message. The error message is now pointing at the "!HÃ¥kon - Sum biler SLETT".

EDIT: I got it to work!! I just had to change:
['TALL FRA KUBE2']=N:DB('HÃ¥kon SLETT',!HÃ¥kon - Sum bilder SLETT, 'Sum Total');

Thanks for all the help!

[Gregor Koch]

Hi
Bilder and Biler?
In case you copied and pasted what I have written maybe try starting with the DB button and the replacing the according values yourself.
Also just in case try hardcoding element names from the dimensions in the other cube.

For a more general explanation:

If you have two cubes which have different dimensions but with identical elements in those dimensions you can easy link them by using the
!Dimension Name
syntax, but the dimension you use in the rule has to be the one existing in the cube.
Hence if you write a rule in the 'HÃ¥kon SLETT2' cube, your rule can only use !HÃ¥kon - Sum biler SLETT.

Regards

[oleisbest]

Hi
Bilder and Biler?
In case you copied and pasted what I have written maybe try starting with the DB button and the replacing the according values yourself.
Also just in case try hardcoding element names from the dimensions in the other cube.

For a more general explanation:

If you have two cubes which have different dimensions but with identical elements in those dimensions you can easy link them by using the
!Dimension Name
syntax, but the dimension you use in the rule has to be the one existing in the cube.
Hence if you write a rule in the 'HÃ¥kon SLETT2' cube, your rule can only use !HÃ¥kon - Sum biler SLETT.

Regards

Yes, Bilder and Biler.

Is it also possible to write a rule which says that the element "Car1" in cube one is the same as element "Car2" in cube two? Joining different elements between cubes when the element name is not the same? This should be useful if one have two different account structures that you want to compare. Typically one account structure for aggregated numbers (Budget) and one at a more detailed level (chart of accounts - accounts). Maybe write a rule that adds up account number 1000 and 1010 from cube one and put this sum besides the element "Accounts receivables" in cube two?

[Gregor Koch]

Hi
Yes, that is possible for example by using Attributes.
Before I move on, did you get the rule working so far? Just for my own sanity.

[oleisbest]

Hi
Yes, that is possible for example by using Attributes.
Before I move on, did you get the rule working so far? Just for my own sanity.

Hi,

Yes the rule is now running like a charm