changing a consolidation against specific scenarios

[dj74]

I have a cube containing a dimension with elements for actual and budget and another dimension with weeks consolidated into months with another 2 dimensions. Actuals will be posted each week and consolidated into months however for budgets it will be vice versa - I need to load these as months but have a rule or something that allows me to overwrite the consolidation and pro rata into weeks on a 5 4 4 basis. Not up to speed on rules so any assistance would be grateful.

[paulsimon]

Hi

There are a couple of ways to do this.

One is to use Spreading, with a reference cube that gives the 4/5 split

Another possible way to do this is to add a Budget Input element to the Version dimension

Load the Budget against eg the first Week element under each Month, but load it into the Budget Input element rather than Budget

Then write rule like this:

['Budget'] = N:
DB( 'My Cube', 'Budget Input', ELCOMP( 'Week', ELPAR( 'Week', !Week, 1) , 1 ) , !Dim1, !Dim2 )
\
ELCOMPN( 'Week', ELPAR( 'Week' , !Week , 1 ) ) ;

The rule will calculate the Budget against each Week from the Budget Input held against the first week below the parent month, ie it goes up a level from the week to the month, and then takes the first component of the month which will be the first week in the month.

It then divides by the number of components of the parent month, so if the month has 4 weeks below it, then it will divide the Budget by 4 and put a quarter of it into each week.

The feeder would be

['Budget Input'] => DB( 'My Cube', 'Budget', ELPAR( 'Week', !Week, 1) , !Dim1, !Dim2 ) ;

This will feed the Budget Input across to the Budget, and by feeding the consolidation above the week level, ie the Month, it will automatically feed each Week within the month.

Another approach would be to use Attributes instead of the hierarchy navigation.

Another Approach would be to set up a Cube to hold the Budget which has a Month dimension instead of a Week Dimension. You then enter the Budget into this Cube, and use a rule like this in MyCube to pull in the Budget.

['Budget'] = N:
DB( 'Budget Cube', ELPAR( 'Week', !Week, 1) , !Dim1, !Dim2 )
\
ELCOMPN( 'Week', ELPAR( 'Week' , !Week , 1 ) ) ;

This uses ELPAR to go from a Week to a Month, so we translate from the My Cube which has Week as its lowest level to Budget Cube which has Month as its lowest level.

The feeder would be written in the Budget Cube

[] => DB( 'MyCube', 'Budget', !Month, !Dim1, !Dim2 ) ;

Personally I prefer this to putting everything into one cube.

Another approach would be to use a TI process to split the Budget, as you load it in and allocate it to the right month. This is often a good approach as the Budget tends to be static once agreed. At the time it is being agreed it could be held in the Budget Cube, at Month level, since we won't have Actuals at that time to compare to the Budget (Though its not exactly unknown for companies to finally agree their Budget 4 months into the year).

There are lots of possible approaches. Hopefully the above gives you some ideas.

Regards


Paul Simon

[dj74]

Excellent....thanks for the help