william110 wrote:
I'm relatively new to TI processes and I am hoping to get some help on putting data between two cubes with different elements using an attribute as a lookup. For example:
Cube A Element 123
Cube B Element 456, but has attribute details as 123.
Put data in element 123 into 456 using the attribute as a lookup.
I currently have the following code and I think it has something to do with my CellPutN formula where I need to specify the attribute lookup?
Code: Select all
nValue = CellGetN ( pCubeTarget, pVersionTarget, vYear, vPeriod, vProfitCentre, vAssetClass, vForecastItem, pMeasure);
nAccValue = nValue + vValue;
CellPutN ( nAccValue, pCubeTarget, pVersionTarget, vYear, vPeriod, vProfitCentre, vAssetClass, vForecastItem, pMeasure);
I think that you're doing this the wrong way around. Rather than having the attribute in cube B's dimension, it should be an attribute in cube A's dimension.
The first reason for this is the type of attribute that it will be. If it's an alias in Cube B's dimension, then it has to be unique. So if you had "123" as an alias for "456" in cube B's dimension, that would be fine; you could use the original element name (123) and it would just write to element 456's alias of 123.
However this is only OK
as long as there will never be a cube B element with a principal name of 123. Which is, I think, a rather risky assumption. Also it's very limiting if you ever need to change where the source data goes to in cube B.
If instead it's a string attribute in cube B's dimension, then TM1 has no way of
directly looking it up. You'd have to iterate all of the elements until you find the (first) one which one had an attribute of "123". This would be slow and wasteful.
If, on the other hand, you had an string attribute in the
source dimension which tells the process where to
send the data (so that element 123 in dimension A had an attribute of '456') you could do the following. (Let's assume that the attribute is on the profit centre and is named BTarget.)
Code: Select all
# If vProfitCentre is 123 this will return 456. Note carefully that both the dimension name and the
# attribute name are literal strings while the profit centre name is the variable from your data source.
vTargetProfitCentre = AttrS('ProfitCentre', vProfitCentre, 'BTarget');
# Note that we are now using the TARGET profit centre from cube A's attributes (which we read above) in these formulas.
nValue = CellGetN ( pCubeTarget, pVersionTarget, vYear, vPeriod, vTargetProfitCentre, vAssetClass, vForecastItem, pMeasure);
nAccValue = nValue + vValue;
CellPutN ( nAccValue, pCubeTarget, pVersionTarget, vYear, vPeriod, vTargetProfitCentre, vAssetClass, vForecastItem, pMeasure);
