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
Opinions and Best Practices for Allocation Model
-
- Posts: 46
- Joined: Mon Jul 26, 2021 12:55 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: Office 365
-
- Regular Participant
- Posts: 355
- 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
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
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
-
- 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
Thank you for replying, Maren!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
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 (27.4 KiB) Viewed 15218 times
-
- Regular Participant
- Posts: 355
- 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
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
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