Best way to transfer values between mapped items with rules

[tm1starter]

Hello all

I've two cubes;

Employee
-Scenario
-Period
-Measure
-Employee

Result
-Scenario
-Period
-Account

And with the next mapping table between Measures and Accounts

Measure1 > Account1
Measure2 > Account2
Measure3 > Account3

I want to get the values for the account with a rule;

You could do this in a few ways

1
Make a rule and a feeder for each account and measure combination. In my opinion this is not a good solution because for each new combination you have to update your rules

2
Make an attribute in the Account dimension named 'Source', you will get the next rule in the result cube;

Code: Select all

[]=DB('Employee', !Scenario, !Period, ATTRS('Account', !Account, 'Source'), 'All employees');

What will the feeder look like in the Employee cube? And how do we handle accounts with more than one source?
I think this isn't the ideal solution yet.


3
Make an attribute(Alias) in the measure dimension called 'TargetAccount' you will get the next rule;
I guess, this will not work unless you do I with a TI process.


any other suggestions? Like look-up cubes, subsets, consolidations or others

Thanks in advance

[ajain86]

Since, you have accounts that could be associated with multiple measures, then I do not see any way that your task could be accomplished without a TI process.

You can create a look-up cube or just put an alias in the measure dimension (only if 1 measure can only be associated with 1 account).

[tm1starter]

One account maps only one measure and the otherway round. So I have two options left;

1 Make an attribute in the account dim named source, which will give this rule;

Code: Select all

[]=DB('Employee', !Scenario, !Period, ATTRS('Account', !Account, 'Source'), 'All employees');

2 Make a alias('TargetAccount') in the measure dim, which will give this rule;

Code: Select all

[]=DB('Employee', !Scenario, !Period, !Account, 'All employees');

I like option 2 better because the measure dim has less elements, so it is easier to maintain.

But what will the feeder look like in the employee cube for both options?

[Marcus Scherer]

Do you have more than two cubes in your model? Is your example only an excerpt of the whole model? If not, why do you have two dimensions, measure and account. Measure is a subset of account in your case -option 2. You could use one dimension and one cube. Your "result" reporting you could achieve selecting "All employees" in views. Then set security for employees dimension in case that some groups shall not see single employee data.
Just some thoughts because you mentioned maintenance.

[tm1starter]

Marcus, the example is a simplification of the model there are more cubes in the model, more dims in the cubes, and more measures in the measure dim which are not mapped to accounts.

[tm1starter]

I think I Have the solution for the feeder;

Create a consolidation in the measure dim, 'TransferedValues'

For option 2

Code: Select all

['TransferedValues' ]  => DB('Result', !Scenario, !Period, !Measure);

The downside is that you have to maintain both the consolidation and the alias, but I see no other options (with rules)