Load from Cube1 to Cube2 using DB rule

[A.Pete]

Hi gurus!

So im trying to do a simple rule and i cant get it to work the way im expecting it. Heres what i want to do:

Cube1 has several dimensions but only one im interested in for now. Its called Account. The Account dim has loads of elements that i do not want to load into cube2. I only want to load the consolidated level from dimension Account.

So i built a new Account dimension called AccountNew that has elements that corresponds to the consolidated level in dimension Account. I also created an attribute in Account for the consolidated level. I hope you are following me .

The rule i wrote looks like this in the Cube2 rules.
['Amount'] = DB('Cube1',!Fiscal Period,ATTRS('Account',!Account,'AccountCLevel'),'Amount');

This gives me a syntax error so i followed this thread http://www.tm1forum.com/viewtopic.php?f=3&t=7076 and changed the rule to an explicit account

['Amount'] = DB('Cube1',!Fiscal Period,ATTRS('Account','1234','AccountCLevel'),'Amount');

This compiles but i get no values in my target cube (Cube2). It even went so far that im doing trial and error , witn =N: and =C: but the same result.

Does anyone have any ideas how to fix this?

Thanks alot.
Peter

[Duncan P]

Two things:-

On the right hand side you may only reference dimensions of the target cube using the ! notation. The meaning of !dimension is "the item name in dimension of the cell being targeted".

You don't need the attribute. If the members of AccountNew match the consolidated members of Account then !AccountNew used in the DB expression will select the appropriate consolidated values from the source cube.

Try something like

Code: Select all

['Amount'] = DB('Cube1',!Fiscal Period,!AccountNew,'Amount');

[tomok]

I would add that using the !Dimension is almost the same thing as joining two tables in SQL on a certain field. The two fields in the two tables don't have to be identical, the JOIN will only bring back when the values match. It will work the same way in TM1, only the dimension elements that have the same name will be brought back in the DB function.

[A.Pete]

Thanks for your replies!

So it doesnt matter if its consolidated level or elemetn level? IT just brings over the values that match?

Another question. I have 10 dimensions in cube2 and only 2 in Cube1. I can still write the DB function as stated above?

Br
Peter

[tomok]

I have 10 dimensions in cube2 and only 2 in Cube1. I can still write the DB function as stated above?

No, not exactly. When dimensionality does not match you have to tell TM1 exactly where to get and where to put the unmatched cells. For example, if in the target cube you have a Period dimension, but in the source you don't you would need to hard code the Period element in the target cube into the formula, much like this:

['Jan 2012','Amount'] = DB('Cube1',!Period,!AccountNew,'Amount');

For all dimensions that exist in the target, but not the source, you have to hard code an element into the left hand side of the rule statement for the nonmatched dimensions. The converse is true for dimensions that exist in the source and not the target. You have to hard code an element for each of the nonmatched dimensions on the right hand side of the rule statement.

[Duncan P]

You need to specify in the DB statement the top item of each of the dimensions that are not in the target. It will not do the aggregation over those dimensions automatically. The order of the items in the DB statement is the order of dimensions in the source.

[EDIT] Beaten to the punch by Tomok

[tomok]

As Duncan mentioned, in most cases you would hard code a top level element in the nonmatched dimensions to pull all the data over. However, this really is dependent on the data. Sometimes you may not want all of the data, like a Version dimension, where you have that in the source cube and not the target. It probably wouldn't make sense to pull over all the versions into the target, you probably would want just one version. In any case, it all depends on the data.

[A.Pete]

You guys are the best.
Even though i haven't solved this yet i think i understand it better than i was this morning. So thanks for taking your time to educate a newbie

Br
Peter