Opinions and Best Practices for Allocation Model

Post Reply
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Opinions and Best Practices for Allocation Model

Post by michaelc99 »

Good Afternoon Everyone,

I am looking to solicit some advice on creating a TI process to replace an existing allocation model. Currently, Allocation Rates are generated within Excel by pulling data from GL MAIN (cube) then uploading the computed rates into a separate cube called, "Production Allocations." Once the rates have been loaded, I run a separate TI process to clear Product Line Adjustments (measure) in GL MAIN, followed by another TI process to repopulate Product Line Adjustments in GL MAIN using the newly updated rates multiplied by balances from GL MAIN.

Would it make more sense to replace/remove the Product Allocation cube and store (access) the Allocation Rates in GL MAIN, or would the existing model be sufficient for the Allocation process? Note: Even manually, the full process takes less than a few minutes to run, so this new TI process would move us toward running Chores as opposed to manual intervention.

Provided I keep the existing model in-tact and replace the Excel-model with a TI process, I'd love to hear some different strategies on ways to replace over 800 DBR calculations with a reasonable number of calculations within TI. There are only three dimensions that could change based on their relative row/column position.

I was leaning toward creating a new attribute in two (maybe three) of the "variable" dimensions and write the formula to fire on only the intersection points where Allocation Flag = Y.

Thank you in advance,
Michael
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Opinions and Best Practices for Allocation Model

Post by MarenC »

Hi Michael,

Could you not create a new cube that includes the 'variable' dimensions, along with a measure dimension to hold an element called something like Allocation Flag?

Then base any rules/TI process logic on the flag in this cube?

I would personally hold the rates in a separate cube as you have suggested.

Maren
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Opinions and Best Practices for Allocation Model

Post by michaelc99 »

MarenC wrote: Tue Nov 30, 2021 10:49 am Hi Michael,

Could you not create a new cube that includes the 'variable' dimensions, along with a measure dimension to hold an element called something like Allocation Flag?

Then base any rules/TI process logic on the flag in this cube?

I would personally hold the rates in a separate cube as you have suggested.

Maren
Thank you for replying, Maren!

I am attaching a screenshot that I hope should provide a some more context into my challenge. The fields in rows 1-9 remain constant for every calculation in the spreadsheet. However, the cells highlighted in yellow can change for every row/column. What I am trying to avoid is replicating the below formula for all 800+ calculations.

Calc001 = A \ B;
CELLPUTSN('Product Allocation Cube', 'Calc001', 'dim1', 'dim2', 'dim3');

If I am understanding it correctly, It sounds like your recommendation would be to create a third cube and create an Allocation Flag for Entity, Channel and Product Center. Then, in the "A" portion of the formula above, I would write the following:

Value = CELLGETN('GL MAIN', 'Actual', 'Control Total', ATTRS ( 'ENTITY MAIN', !ENTITY MAIN, 'ALLOCATION FLAG' ) @= 'Y', 'USD', 'Control Total', 'Control Total', ATTRS ( 'CHANNEL MAIN', !CHANNEL MAIN, 'ALLOCATION FLAG' ) @= 'Y', ATTRS ( 'PRODUCT CENTER MAIN', !PRODUCT CENTER MAIN, 'ALLOCATION FLAG' ) @= 'Y', 'Control Total', 'Control Total', '!PERIOD MAIN', 'Total') \ B;

CELLPUTSN('Product Allocation Cube', ' Value ', 'dim1', 'dim2', 'dim3');

Am I on the right track?

Thank you in advance,
Michael
Attachments
Alloc.png
Alloc.png (27.4 KiB) Viewed 15140 times
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Opinions and Best Practices for Allocation Model

Post by MarenC »

Hi Michael,

your CellPut and CellGet notation is a bit of a mystery to me and pretty confusing, but leaving that aside...

Assuming you do this via TI process:

vFlag = CellGetN('New Flag Cube', vEntity, vChannel, vProduct, 'Allocation Flag');
vRate = WhereeverTheRateisHeld;
vValue = CELLGETN(TheValueFromGLMain);

If(vFlag @='Y');
CellPutN(vValue / vRate, 0);
Endif;

Maren
Post Reply