TM1 Forum

Hi all,
hopefully this is a simple one - am just trying to figure out how to create either an XL template (for users) or a TI file upload process (for admin) to perform similar function as the Relative Proportional Spread...

...now am aware only the Proportional Spread function is available in macros/TI, but just wanting some ideas on how others have created workarounds for this...?

Thanks!

Matt

P.S. we're on version 9.4 + probably moving on to v9.5 in near future

Slightly off topic... relative percent adjustment - if take your actuals (from the 'Actual' element of the 'Version' dimension) and spread them to budget (in the 'Budget' element of the 'Version' dimension) then you are just taking every leaf result in the cube against Actual and posting it to the same Budget intersection plus or minus some percentage.

Back to relative proportional spread - it works in a similar way, except instead of knowing the percentage in advance, you know both the number you want to spread and the value at a consolidated intersection that you want to take the proportions from (per the underlying leaf values in that consolidation). E.g. you know: and If you have a cube-view based TI that has it's data source set to all leaves rolling up to that intersection then, in the data tab, your code looks like: Obviously, you can extend this code to be more generic, depending on how you want to apply it in your application. You can use Action Buttons in your worksheet to help achieve this.

But... you can just use the built-in relative proportional spread UI that does all this hard work for you!

Hi there,

In a view or in an excel template containing DBRWs,
you can use the relative proportional spread using right mouse click. ( without macros).

If you need to send exactly the same data from one element to another.
You could handle it by doing a simple data transfer via TI.

I am thinking of a case actual and budget.
Initially budget is empty.

From an excel template users could relative proportional spread into budget using the relativity to actual to replicate part of actuals into budget.

From a TI you could transfer perimeters of actuals into budget based on parameters to determine the perimeter to copy replicate.

I am guessing you would like to enable them to do several relative proportional spread at the time and therefore the need of VBA and/or TI ?

You could maybe explain a bit more what you are trying to achieve so people could provide more tips or tricks...

Hope this helps,

Hi guys,
thanks for the responses - so to clarify, I do know how to do the "right click one by one" method and preloading Actuals to Plan etc... My question is more around how to create a dynamic template process, where users can perform complex data spreads in bulk using any number or level of elements

e.g.
-for cost centres 1234, 5678, 9012 for Jan 2013, Feb 2013, Jun 2013, reproportion all Expense Allocations based on Actuals month Feb 2012, with a growth of 10% per month etc
-for all Credit Cards products, increase Interchange revenue account by 10% month on month across 2012
etc

Ideally I would like to have an XL template arranged similar to the following:

Reference Proportions FROM Cell: Update TO Cell:
Version Period Cost Centre Account Product Version Period Cost Centre Account Product Amt

...which the user can populate with any number or combination of variables, then hit a button to load the data spread.

fleaster wrote:My question is more around how to create a dynamic template process, where users can perform complex data spreads in bulk using any number or level of elements

Yes, bulk requirements are where the in-built UIs become tedious to use.

fleaster wrote:Ideally I would like to have an XL template arranged similar to the following:

Reference Proportions FROM Cell: Update TO Cell:
Version Period Cost Centre Account Product Version Period Cost Centre Account Product Amt

...which the user can populate with any number or combination of variables, then hit a button to load the data spread.

To automate the type of spreads you want you could either use TI, or the API. I think you can use a variation of the allocation TI method I proposed, with another TI before that to call that spreading process for every row of your input sheet. It can be a bit cumbersome having people fill out spreadsheets and then have them save as .csv onto the TM1 server where a TI process can access it - have you considered asking your users to fill out the information (per your template) into a cube? Then a TI process could easily process that view, and call the downstream process for each input line.

If you wanted to avoid TI and stay within the Excel UI, then you could use VBA to automate the spreading functions in the API. I've implemented this in the past and can't recommend doing that unless your requirements are so niche and complex that you simply have no alternative. Although perfectly functional, once you have reasonably large cubes, the performance issues and difficulty in trouble-shooting API methods really start to stack up. TM1 is supposed to allow people to go home at 5pm... but the only time I was still working past midnight was with this application!

fleaster wrote:e.g.
-for cost centres 1234, 5678, 9012 for Jan 2013, Feb 2013, Jun 2013, reproportion all Expense Allocations based on Actuals month Feb 2012, with a growth of 10% per month etc
-for all Credit Cards products, increase Interchange revenue account by 10% month on month across 2012

Sounds like you actually want relative percent adjustment as well as relative proportional spread !

I think you need to break down your process in smaller steps to find the right/applicable solution.

-for cost centres 1234, 5678, 9012 for Jan 2013, Feb 2013, Jun 2013,
reproportion all Expense Allocations based on Actuals month Feb 2012, with a growth of 10% per month etc...

I read that as follow :

1 - Jan 2013, Feb 2013, Jun 2013 for cost centres 1234, 5678, 9012 are initialised as equal to Actuals month Feb 2012 for each cost centres.
So at this point, Jan 2013 or Feb 2013 or Jun 2013 = Feb 2012 for each cost center.

Can be done by TI ( Copy from X to Y).
Can be initiated from spreadsheet. ( action button).

If only the proportionality of Feb 2012 needs to be kept but the total figures is different, then
2 - Proportional Spread the new number on top for each cost centre for Jan 2013, Feb 2013, Jun 2013 to change the total and keep the proportions.
Done by TI by loading and proportional spreading, the first copy step was used to drive proportionality.

or if the value and the proportionality are set at the end of step 1 and you derive the new number from the growth percentage
2 - Apply an increase to that figure of 10%
Various ways of doing so but likely to be TI + growth percentage cube.

I find the spreading usage very depending of the level of skill of the users.
An Advanced user could do it all by himself with a cube view and a swiss knife.
A Basic user would struggle to understand the rational behind spreading and therefore need a lot of easy baby steps he/she can follow.
Also traceability of the baby steps and being able to come back to the previous step is something you want to consider.

Not sure all of it will fit in one spreadsheet but you could end up with a Menu spreadsheet which link the different steps of the process via action buttons...

rmackenzie wrote: have you considered asking your users to fill out the information (per your template) into a cube? Then a TI process could easily process that view, and call the downstream process for each input line.

Yes, I have considered this - in fact our current process is to have a cube containing the balances and another cube containing allocation % to split (both reasonably large cubes) - a TI process is then run to split proportionally. This works ok, however I've found it a bit clunky to run (takes 10-15min), so am looking for other ways to make it more self-service + real-time.

rmackenzie wrote: If you wanted to avoid TI and stay within the Excel UI, then you could use VBA to automate the spreading functions in the API. I've implemented this in the past and can't recommend doing that unless your requirements are so niche and complex that you simply have no alternative. Although perfectly functional, once you have reasonably large cubes, the performance issues and difficulty in trouble-shooting API methods really start to stack up. TM1 is supposed to allow people to go home at 5pm... but the only time I was still working past midnight was with this application!

Sounds like you actually want relative percent adjustment as well as relative proportional spread !

hehe sounds like fun... I haven't had any experience creating APIs - did you have any code samples you'd be willing to share?
...so after you debugged the API, did it work ok in terms of performance etc...?

Sounds like you are up to speed with what I can propose

Waiting for rmackenzie API update

fleaster wrote:... our current process is to have a cube containing the balances and another cube containing allocation % to split (both reasonably large cubes) - a TI process is then run to split proportionally. This works ok, however I've found it a bit clunky to run (takes 10-15min), so am looking for other ways to make it more self-service + real-time.

Is that 10-15 minutes to do all the spreads per the data in both cubes? Perhaps you could include the }Clients, or }Groups, dimension in one of those cubes, or combine them into one? Then perhaps it would be easier for a single user to run the spreads that they are interested in rather than running all the spreads at the same time. I suppose performance is going to be a big factor there to get that working nicely in a multi-user, multi-spread environment where people are potentially queuing to get their spreads in the forecast. If you were down to a couple of seconds per spread then this may work well. What steps did you take to fine-tune the TI you have already?

fleaster wrote:did you have any code samples you'd be willing to share?

Ah, unfortunately, I'm not in a position to share. However, the base methods are reasonably well described here. Once again, I'd have to say that if you can achieve your goals in TI, it will be preferable as TI will most certainly be a much faster solution than using the API, and likely more maintainable as well.

What steps did you take to fine-tune the TI you have already?]

**tears out hair** well... first of all we're talking about millions of combinations in both cubes... so one step I was forced to take was to break up the process by exporting/importing temp .txt files etc which seemed to improve things...

...to explain the TI logic in simple hypothetical terms, let's just say we have 3 cubes:

Balances = stores $ balances by Cost Centre (CC)
Allocation = stores % product splits by CC
Output = stores final $ balances by % product splits by CC

So the Balances cube may have:
Cost Centre Value
1234 $100
5678 $1000

And the Allocations cube has:
Cost Centre Product Value/Proportion
1234 ABC 30%
1234 DEF 70%
5678 ABC 50%
5678 DEF 50%
9001 XYZ 100%
9002 XYZ 90%
9002 ABC 10%

After all users across the company have entered into Balances + Allocations, the TI process is run. This trawls through data from the Allocations cube and uses it to generate balances to the Output cube (with Balances cube as a reference). ie

Cost Centre Product Output Value
1234 ABC 30% x $100 => $30
1234 DEF 70% x $100 => $70
5678 ABC 50% x $1000 => $500
5678 DEF 50% x $1000 => $500
9001 XYZ 100% x $0 => $0
9002 XYZ 90% x $0 => $0
9002 ABC 10% x $0 => $0
etc... then tally up the sums.

In a nutshell, this is similar to how our current TI process runs, but think of millions of combinations, with other dimenions like GL account, period etc thrown into the mix - which is why it takes a little while

Not sure if this is the most efficient the TI design can get, but wanted to explore from another angle to achieve similar via user driven real-time data spreading (hopefully with less volumes in the one go)

Perhaps you could find some logic to associate a user, or group of users, with a group of cost centres. Then when you run the allocation/ spread process, it would only zero-out and recalculate the area of the Allocations cube related to those cost centres?

Yes I was thinking of that ie isolating groups of cells that could be updated separately... unfortunately there are alot of times when changes are done full scale across the board, and it is necessary to do a 100% run to generate total country results...

...Ok on a side point, not sure if this makes sense, but it seems like it is difficult to use TI to proportionalize balances in a "one-to-many" relationship - ie you have to start with the "many side" as your record source, in order to consolidate them to your "one/few" records...

This means I need the TI process to trawl through "many" millions of records, most of which may have a corresponding $0 balance... not sure if this is clear, but I think this is the reason the process takes a while, so wondering if my TI logic is right or not...

Note: with a relational db like MS Access however, it is quite easy to run a query to get "one-to-many" records... am wondering if something similar can be replicated with TM1...?? :p

Does anyone know if TM1 10.2 has added this functionality?

RJ! wrote:Does anyone know if TM1 10.2 has added this functionality?

If you're referring to a way of performing relative proportional spread via TI, then no, it wasn't documented as a new feature in 10.2

You're still going to have to roll-your-own solution.