Rules to distribute aggregated values into more specific one

[najczuk]

I'm trying to create POC TM1 Model in which user will be disparsing aggregated measures into more specific ones based on distiribution map.
To be more straightforward I have an invoice data with following attributes:

Code: Select all

cost month, localization, cost group, department, accounting project, account, invoice number, invoice total value

I'm planning to create 2 cubes:
AggregatedCost cube (invoice number, invoice total value measures) with CostSource dimension (localization, cost group, department, project, account attributes) and CostDate dimension (standard auto-generated attributes) for the basic invoice data
DistributedCost cube (invoice number, invoice distributed value measures) with the same dimensions as for AggregatedCost cube
The idea of DistributedCost cube is based on the fact that we cannot assign some costs to particularproject because it concerns costs like office rent and as many projects are taking place in the same office we have to distribute that cost to all of them.
For example:

May 2014 cost for Office Rent invoice number 001 assigned to PROJECT1 should be distributed to PROJECT1 (30%),PROJECT2(50%),PROJECT3(20%)

The question is how to write a rule which will propagate such data into DistirbutedCost cube?

[blackhawk]

najczuk,

In order to distribute the costs, you are going to need to identify something that ties them together, which may or may not be a third cube, as you eluded to by PROJECT1 (30%),PROJECT2(50%),PROJECT3(20%). What determines that allocation? Is it by department, month, etc? The third cube will need to contain that information.

With that, then basically in the distributed cube you will need to do something like this (assuming that department determines the project allocation):

['Cost'] =N: DB( 'Aggregated Cost', ..., !Measure ) * DB( 'Project Allocation', !Department, !Project, 'Allocation Percent' );

So where you have 0%, you get no allocation. Where you have your 30, 50, 20%, that amount gets allocated to the appropriate project. But someone is going to have to enter/load those allocation amounts somewhere.

Hope that helps.

[najczuk]

Thank you for the previous answer, but what about invoice number. When we divide aggreated cost into many projects using ProjectAllocation Cube, how can we preserve invoice number in target cube? Esspecially that it may happen that many invoice will point to specific Project. Should we think of some Invoice Dimension? How could we then virtualy populate it depending on calculation?

[tomok]

In both of your cubes the Invoice Number should be a dimension in and of itself, rather than as a measure. You can get away with leaving it as a measure in the first cube but writing the rule (and the related feeders) becomes a whole lot more difficult.