Spreading data in a procedure

Post Reply
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Spreading data in a procedure

Post by 20 Ton Squirrel »

The function CellPutProportionalSpread will sprinkle a value across all leaf elements in a consolidation but what if we want to align that spread along two axis?

For example,
Image

The body of the tables have inputs for products by quarter. A new edition of the report rolls around so these need to be updated. The analyst knows row and column totals (bright green areas), so these are updated. This new grand total is distributed into the body of the table.

This would be Iterative Proportional Fitting (IPF). I can perform this in Excel by making a series of tables or with a VBA function.

I was curious if there was an out-of-the-box method for this in TM1. There are a variety of spreading techniques in the PAW interface, as shown below, but what about as a function in a procedure?

Image
War teaches us geography, getting old teaches us biology.
TomBr
Posts: 32
Joined: Tue Jun 03, 2008 6:56 pm

Re: Spreading data in a procedure

Post by TomBr »

Have you looked at the TI function CellPutProportionalSpread

https://www.ibm.com/docs/en/planning-an ... onalspread
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: Spreading data in a procedure

Post by 20 Ton Squirrel »

TomBr wrote: Mon Sep 27, 2021 4:29 pm Have you looked at the TI function CellPutProportionalSpread

https://www.ibm.com/docs/en/planning-an ... onalspread
I looked into that particular function but only it spreads across only one axis. I need to balance inputs against two separate axes. Since TM1 offered CellPutProportionalSpread I was hoping there were similar functions of that nature that expand on how data could be spread. Seems like that's the only one available, sadly.

I was able to accomplish this by creating a separate cube that included an extra dimension for iterations. The formulae for Iterative Proportional Fitting are rather simple but it gets a little more complex handling the dimensional structure. From a perspective of usage/calculation efficiency, this method works quite well since you can throw many measures from the same cube into the same bank of rules.

From a development perspective it is less ideal because each source cube will require a separate cube to handle the IPF iterations. My project will require this in several areas, so it just adds overhead to what I'm building.

Ultimately, I'm just a lazy developer that wants a magical function to solve my issues. ;)
War teaches us geography, getting old teaches us biology.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Spreading data in a procedure

Post by David Usherwood »

I looked into that particular function but only it spreads across only one axis
Just tested with Sdata and confirmed CellPutProportionalSpread works multidimensionally.
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: Spreading data in a procedure

Post by declanr »

David Usherwood wrote: Mon Sep 27, 2021 7:45 pm
I looked into that particular function but only it spreads across only one axis
Just tested with Sdata and confirmed CellPutProportionalSpread works multidimensionally.
Because OP is looking for an iterative proportional spread, they don’t quite just want proportional.

Rather they are looking for (if I have understood):
1/ go from prod A to D and proportional spread on row total
2/ apply a HOLD on each of the 4 row totals
3/ proportional spread on Q1 for total products
4/ apply a HOLD on Q1, total products (keep old hold)
5/ proportional spread on Q2, total products (keep old holds)
6/ apply a HOLD on Q2, total products (keep old holds)
7/ proportional spread on Q3, total products.

Theoretically Q4 as the only one not held should have also been adjusted accordingly and all holds can be released at that point.

You could look at doing it in TI as you can apply holds by playing with the control cubes… just remember to remove the holds at the end.
Declan Rodger
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: Spreading data in a procedure

Post by 20 Ton Squirrel »

declanr wrote: Mon Sep 27, 2021 8:43 pm Because OP is looking for an iterative proportional spread, they don’t quite just want proportional.

Rather they are looking for (if I have understood):
1/ go from prod A to D and proportional spread on row total
2/ apply a HOLD on each of the 4 row totals
3/ proportional spread on Q1 for total products
4/ apply a HOLD on Q1, total products (keep old hold)
5/ proportional spread on Q2, total products (keep old holds)
6/ apply a HOLD on Q2, total products (keep old holds)
7/ proportional spread on Q3, total products.

Theoretically Q4 as the only one not held should have also been adjusted accordingly and all holds can be released at that point.

You could look at doing it in TI as you can apply holds by playing with the control cubes… just remember to remove the holds at the end.
You're on the right track, declanr, that's an interesting idea to apply the IPF process procedurally. I could write that procedure to accept any cube/dimensions/measure. Since TM1 doesn't offer IPF anywhere I'd simply write it m'self. That would eliminate the need for external cubes for iterative computations, too. The only drawback is that the process isn't a live rule.

TM1Py could likely do this via procedure, also, but IBM won't install that package on cloud-based servers.

I wish TM1 had more statistical functionality to accomplish things like this as RULES. I have no idea how it could actually be DONE but I can certainly whinge about it. ;)
War teaches us geography, getting old teaches us biology.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Spreading data in a procedure

Post by David Usherwood »

TM1Py could likely do this via procedure, also, but IBM won't install that package on cloud-based servers.
TM1Py talks to the TM1 server via the RestAPI - it does not have to be installed on the IBM cloud. Install it on a server (or client) over which you have control.
Post Reply