Transfer data between cubes via rules and attributes

[zamp900]

Hi - I am quite new to TM1 and am trying to move data between two cubes with slightly different dimensions. One has detailed accounts from the chart of accounts, another has an altered/summarised view of accounts - but to make it complicated, the mapping from account -> summary depends on the employee type. The matrix can get a bit complicated, which rules out alternate hierarchies as an option. I am trying to achieve the desired result via an attribute on the Account Code dimension.

I have been troubleshooting my rule to figure out where I'm going wrong, and I have got to this point. Note I have simplified my cube structures for troubleshooting which is why some of the names are a bit funny.

['Placeholder' ] = N: IF(ATTRS('Account Code', '80000', 'SUP_ACC')@=!Analysis Accounts, DB('Raw Accounts Data', '80000', !Month, 'Base Currency'),STET);

This rule returns the correct values in the summary cube for the 80000 account. However, thats not much good to me because I need it to work for the whole dimension at once - I am sure there is another function I can add in to achieve this, but I can't figure it out.

Any help would be greatly appreciated. I'm going round and round in circles on this one!

[jim wood]

HAve you coinsidered creating a lookup cubde containing detailed accounts and employee? Create the mapping needed in here and your rule would work slightly differently. It would look something like:

['Placeholder' ] = N: DB('Raw Accounts Data', DB('Mapping',!Account,!Employee), !Month, 'Base Currency');

Or something like this....

Jim.

[zamp900]

Hi - thank you for your response. Yes, I have tried that - it would be my preferred approach - but I can't seem to make that work either. Ignoring Employee Type for the moment, and just trying to make a simple account -> analysis account mapping work. What am I missing?

My rule is:
['Placeholder' ] = N: DB('Raw Accounts Data', DB('Mapping Cube', !Analysis Accounts, 'TST Mapping'), !Month, 'Base Currency');

My source cube has dimensions Account Code, Month, Currency

Mapping cube has dimensions Account Code, Mapping (which has one string element called TST Mapping - at this stage I have entered the mapping - spelling triple-checked - for the 80000 account to map to Employee Salaries Overhead, which is an element in the Analysis Accounts dimension, and I know it definitely has data - as confirmed in my original post)

Target cube has dimensions Analysis Accounts, Month, TST Placeholder (which has one numeric element called Placeholder)

Any ideas to help? The rule above is causing my target cube to grey out, but not giving me any data. Thanks again for your help.

[jim wood]

Does this information need to be dynamic? Have you considered using TI instead? It might be worth using TI as a test. If you export your raw cube to a flat file (or use a view as a source) and then try to import it into the summary cube. You'll have to bring the mapping cube in to the process. Play around with this. At the very least it will help you understand where the rule may be going wrong,

Jim.

[paulsimon]

zamp900

I think that the problem may be this. You must be writing the rule in the Summary Cube for it to pull in the data. I am assuming that !Analysis Accounts is the name of the Summarised Accounts dimension. However, from your description, your mapping cube has the detailed account dimension. Therefore it will be doing a lookup based on your summary account into a cube based on the detailed account, and therefore probably isn't getting any matches, hence the greyed out cells.

This would also explain why it worked when you hardcoded 80000 into the rule since you were giving it a valid detailed level account.

You need to create the inverse of the attribute cube. It needs to take in a summary level account and map to a detailed level account (in practice this would have to be an appropriate consolidation of the detailed level accounts).

You will still need your original mapping cube to drive the feeder from the detailed cube to the summary cube.

Personally, unless you really need this to be real time, I would go with Jim's suggestion and do this via TI instead. Then you only need your current mapping cube.

I have done some very complex 3:1 rule based mappings in the past but the complexity of the rules is not trivial, and there is a need to re-process feeders when string based mappings change, which can take as long as running a TI process.

Regards

Paul Simon

Hi - thank you for your response. Yes, I have tried that - it would be my preferred approach - but I can't seem to make that work either. Ignoring Employee Type for the moment, and just trying to make a simple account -> analysis account mapping work. What am I missing?

My rule is:
['Placeholder' ] = N: DB('Raw Accounts Data', DB('Mapping Cube', !Analysis Accounts, 'TST Mapping'), !Month, 'Base Currency');

My source cube has dimensions Account Code, Month, Currency

Mapping cube has dimensions Account Code, Mapping (which has one string element called TST Mapping - at this stage I have entered the mapping - spelling triple-checked - for the 80000 account to map to Employee Salaries Overhead, which is an element in the Analysis Accounts dimension, and I know it definitely has data - as confirmed in my original post)

Target cube has dimensions Analysis Accounts, Month, TST Placeholder (which has one numeric element called Placeholder)

Any ideas to help? The rule above is causing my target cube to grey out, but not giving me any data. Thanks again for your help.

[zamp900]

Thanks team - looks like TI is the best option - the data doesn't need to by dynamic, so I'll head down that path now. Should be interesting.... I have only done some very simple TI coding before, so fingers crossed it goes smoothly.