Budget planning model with dynamically shifting costs

Post Reply
grusty
Posts: 13
Joined: Thu Oct 11, 2018 10:11 pm
OLAP Product: IBM Planning Analytics
Version: 2.0
Excel Version: 2013 32 bit

Budget planning model with dynamically shifting costs

Post by grusty »

Hi Everyone,

I'm implementing into our budget planning model a project cost input cube with a relative time dimension. For example the user could enter the project costs at a time relative to the project completion date. So any costs incurred leading up to the project would be entered into Weeks -W01 to -W10, project completion week is W00, weeks post completion are +W01, +W02 etc. I then pull the amounts into a summary cube with our fiscal calendar dimensions and the relative time dimension. Based on inputted project completion dates, the costs will be allocated to the corresponding fiscal year and period. The idea being that as we re-forecast throughout the year, we can dynamically shift these costs in the model as project dates change or on the fly in planning meetings. The issue I have is with feeders. I am required to overfeed a lot of cells as a relative time could correspond to any fiscal year and period, so each cell in the input cube is feeding 65 cells in the summary cube. I believe I am doing this the most efficient way possible given the requirements, however I'm wondering if there is a better alternative. Has anyone come across a similar model and what was your solution to dynamically shift costs based on date inputs?
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Budget planning model with dynamically shifting costs

Post by declanr »

Personally I have had similar requirements and my preference would be to resolve it with users inputting directly to the cube with period/year dimensions in it and utilising MDX and/or a dynamic websheet to only show them the relevant ranges for their projects.

But failing that if you really need the user to input to a cube that has the alternative dimensions (the +W01 element style dim) then you must already have a mapping cube to show how that corresponds to the fiscal year and period dimension (or at least some method of converting) so you can do feeders along the lines of:

Code: Select all

['Value']=> DB ( 'Summary Cube', !dim1, !dim2, ..., DB ( 'Period Map', !ProjectWeekDim, !Project, 'Financial Year' ), DB ( 'Period Map', !ProjectWeekDim, !Project, 'Financial Month'),..., 'Target Measure' );
If you use an alternative method to "pull" the values into the summary cube I would really recommend having the period mapping cube/s as it can make tracing a lot easier.

You certainly want to get away from the level of overfeeding you have though as it will certainly degrade performance and as you need to add extra years and projects it will only get worse.
Declan Rodger
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Budget planning model with dynamically shifting costs

Post by paulsimon »

Hi

I think that the problem that you are going to encounter with any rules and feeder approach to this is that it will work the first time. However, if you subsequently change the end period for a project, it is not going to automatically feed across to the revised periods, unless you do a re-process feeders. The only other alternative is to overfeed to all possible periods, and you already know the problem of that.

We do a similar thing in our model. However, we use a TI to transfer the data across.

I would have a Sheet where they select the Project and enter the data for the relative periods and then they enter the expected end period for the project, and click a TI Button that transfers the data from that input cube across to the appropriate periods. It should clear out any existing data for the project prior to that, in all periods, since the existing periods could be different to the current ones.

If your real period dimension is a single time dimension with Year and Period combined and if you control the order of creation so that base level periods appear in order, before any consolidations, then you can just use a dimix offset from the project end period to do the population.

If you have separate year and period dimensions, it will be a little harder, but still manageable within a TI process.

I would only go with a rule based approach if there is a real need to see numbers entered for a project appearing in real time in the main cube. If not, I think a TI process based approach will work better for you.

Regards

Paul Simon
grusty
Posts: 13
Joined: Thu Oct 11, 2018 10:11 pm
OLAP Product: IBM Planning Analytics
Version: 2.0
Excel Version: 2013 32 bit

Re: Budget planning model with dynamically shifting costs

Post by grusty »

Thank you both. Currently the overfeeding is limited to the next five fiscal years/periods using an alternate heirarchy in the fiscal year dimension called '(All Future Years)' which willed be updated once a year, however not ideal. Performance is not suffering (for now). I had thought about using TI to populate the values in the summary cube. I may explore that option. I think it will satisfy the requirement if I set it up so the user has to run the TI after making a change to the project dates. Thanks again.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Budget planning model with dynamically shifting costs

Post by paulsimon »

Hi Grusty

Another thing you could try is the relatively new TM1S.CFG parameter

ForceReevaluationOfFeedersForFedCellsOnDataChange=T

I experimented with some simple feeders of the form :

[]=>DB('summary',!project, DB('relsource',!project,'EndMth')) ;

[]=>DB('summary',!project, dimnm( 'month' , dimix( 'month' , DB('relsource',!project,'EndMth') ) - 1 ) ) ;

[]=>DB('summary',!project, dimnm( 'month' , dimix( 'month' , DB('relsource',!project,'EndMth') ) - 2 ) ) ;

You can extend this to more periods since it will only feed if there is a non-zero value in the relative period.

It seemed to work. However, the CFG setting does have performance implications so I would exercise caution. It is a relatively new setting and I do recall something on the forum about it not working in all cases.

A simpler approach for the right hand side might be to have a consol for each mth to feed to that gives that mth and X mths before it, but that can get unworkable unless all your projects are the same duration which they probably aren't.

I would give the TI approach a go.

Regards

Paul Simon
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Budget planning model with dynamically shifting costs

Post by Wim Gielis »

For me, definitely a TI approach.

Also, include a visual check whether both cubes match for the given project. If not it would (probably) mean that the transfer process did not run.

You could also include the transfer process in a chore.m and run it say every couple of hours.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Budget planning model with dynamically shifting costs

Post by lotsaram »

declanr wrote: Wed Feb 20, 2019 7:15 pm ... my preference would be to resolve it with users inputting directly to the cube with period/year dimensions in it and utilising MDX and/or a dynamic websheet to only show them the relevant ranges for their projects.
But this is precisely what you can't do for the reasons the OP described. Start date for projects can change! This doesn't necessarily affect the phasing of costs relative to the project timeline (relative time from start date), but it certainly affects the real distribution of costs when allocated to the correct fiscal or calendar months and when aggregating across projects. If a project start date changes (and they do all the time) then you don't want to force everyone to move their forecasts.

I think the design is correct in terms of having a generic month 1 .... month N or week 1 .... week N time dimension against which project costs are forecast and a separate reporting cube using standard time dimension for overall company cost and cashflow forecasts. With the "lookup key" for moving the data being a "start date" attribute held against each project. We have a few customers in project based businesses (construction, infrastructure) and this design has proved very serviceable. I agree with previous comments that TI is more appropriate for allocating from the relative project time to standard fiscal time dimension than rules. This will give much better performance and even with a lot of data the transfer process should be very quick.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Bakkone
Posts: 119
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Budget planning model with dynamically shifting costs

Post by Bakkone »

Go with TI like the others said.

I have been in a similar position were I would demo the "live" variant and the TI process. The end users always choose the TI variant as the process can be made so quick its really more of a "save" button than actually running a process. But the live variant quickly becomes sluggish as the amount of projects rise.
Post Reply