Dataspreading by rule?

[Matya]

Hi Guys,

Are there some best practises how to make a dataspreading rule?

I have written this rule:
['Maintenance cost'] = N: IF(ATTRN('Time', !Time, 'workdays') = 1,
(DB('General costs', 'Maintenance cost', ELPAR('Time', !Time, 1)) \
ATTRN('Time', ELPAR('Time', !Time, 1), 'workdays')),
0);

At the source cube I have a time dimension with month at the leaf level, and at the target cube the days are the leaf.

It is not a complicated rule, but...
- It is just spreading over one dimension. If I want to spread the data over 2 or 3 or 8 dimensions, the rule becomes very-very complex.

- It is just equal spreading. If I want propartional spread, the rule is more complex.

- The months are consolidated by the days. If I add a new level(week) between the months and days, I have to rewrite all my spreading rule (because of ELPAR functions).

So I'm looking for the best practise. Could anybody help me?

Thx,
Matya

[David Usherwood]

Few suggestions:
a What you are doing is an allocation exercise - calling it 'spreading' confuses things as spreading is a TM1 feature not a requirement
b As you've found, elpar() is not a good function to use as you cannot control it properly, especially if you have multiple parents. Build and use attributes instead.
c The key approach for allocations should be:

Code: Select all

    ['Output'] = n:
       ['Input',<high levels>] *
       ['Driver'] \
       ['Driver',<Totals>]
       ;

If you follow this model then you can easily allocate over multiple dimensions. Don't forget the feeder, which is likely to be best as:

Code: Select all

     ['Input',<high levels>] =>
       ['Output',<Totals>];  

HTH

[lotsaram]

However you may find if the cube is large and you need to allocate over multiple dimensions then the area to feed will be large and dense and performance may suffer horribly as a result. Unless it is a small discrete allocation problem easily solvable by rules then my default position is to use TI for allocations for performance reasons.

[tomok]

As others have suggested, this is probably not a good candidate for rules. Another option is to do it in a TI process. You have the function CellPutProportionalSpread available to you in TI. Even though the name implies proportional spreading, it can also be used to do equal spreading. Just break the TI into two. Have the first TI put a 1 in every leaf cell underneath your chosen parent node(s) then have the second TI take the amount to be allocated and call the CellPutProportionalSpread function. it will result in an equal amount being allocated to each leaf cell (down every tree in the cube) below the chosen intersection. I have used this many times before and it works like a charm.

[Matya]

Thank you guys, you helped a lot!

[Matya]

Hi Guys,

I have got an other rule/feeder problem.

I have a cube with the project incomes, a cube with work-time and a cube with project outcome. I would like to allocate the project incomes to the projects by using the work-time as base.

(In this case, We could name the cubes like this for example: ProjectIncomeCube, WorkTimeCube and ProjectOutcameCube)

I have written this rule:
['Income'] = N: DB('ProjectIncomeCube','Income', !Project, ATTRS('Time', !Time, 'parent_root')) *
DB('WorkTimeCube','Fact time', !Employee, !Time, !Project) \
DB('WorkTimeCube', 'Fact time', ATTRS('Employee', !Employee, 'parent_root'), ATTRS('Time', !Time, 'parent_root'), !Project) ;

With this rule I would like to allocate the income to the employees and to the time. (ATTRS('Time', !Time, 'parent_root') is the root member of the time dimension and the ATTRS('Employee', !Employee, 'parent_root') is the root member of the employee dimension)

I have written this feeder at the ProjectIncomeCube:
['Income'] => DB('ProjectOutcome', 'Income', ATTRS('Employee', !Employee, 'parent_root'), !Project, ATTRS('Time', !Time, 'parent_root'));

I think this feeder may causes overfed, because it feds the all employee dimension and the all time dimension. Am I right?

If I use conditional feeder, I could run TI process just separetly. (with conditional feeders, the paralell TI process run is prohibited) Is it true?

Could You suggest me a perfect feeder?

(As You mentioned before, the best way for data allocation is the TI process, but it is a small cube and the rule doesn't cause performance problem)

Thx,
Matya

[Duncan P]

Your expression has 3 parts. The value will only be non-zero if all three parts are non-zero. This means that you can feed from any one of them. Choose the part that most closely matches the structure of the target, which in your case looks like the second part.