Performance Comparison - Direct SUM vs Line Item Subset
Hi all,
Was looking to get some community feedback on two different SUM approaches and which one would be more efficient from a model engine point of view.
The scenario,
I'm looking to SUM line items into a central P&L by account. The source module lines do not have account as a list, so we'll be using something similar to [SUM: Mapping Module.Account] to map the line items to an account.
I will stress that this is more for examples where there's potentially a dozen or more lines to SUM.
The options,
Option 1. Directly SUM line item from source module to central P&L, e.g.
- Line 1[SUM: Mapping Module.Account] + Line 2[SUM: Mapping Module.Acount] + Line 3[SUM: Mapping Module.Account] + Line 4[SUM: Mapping Module.Account] .. etc
Option 2. Create an LIS with the relevant lines, setup a COLLECT() & mapping module using the LIS and then SUM into the central P&L using a single function, e.g.
- COLLECT Source[SUM: Collect Module Mapping.Account]
The outcome,
Option 1 is the most simplistic approach as the SUM is direct from source to target, but will result in potentially 10+ SUM parameters in the logic as there's so many line items to include.
Option 2 is a bit more complicated to setup initially, requiring an LIS and two additional modules (one to use COLLECT() AND one to map each line to an account), but results in a much cleaner calculation outcome once it's included in the central P&L (as well as arguably better auditability and flexibility).
Whilst we can debate the different pillars of PLANS, I'm looking more to the P for this particular query. Looking forward to hearing your thoughts on the different approaches (or alternatives if appropriate).
Cheers,
L
Best Answer
-
Thank you for this post and question…Short answer is absolutely Option 2 as HyperBlock actually works less but faster as there is only "one" sum formula getting the data from the collect() statement vs. Option 1 there are several sum formulas.
Funny thing, I just went over this exact scenario in the ACE training in San Diego a couple of weeks ago in a before and after scenario.
in "your" Option 1, you would have something similar to this
where the vast majority of "detailed" line items have the same formula:
The much better way is your Option 2 where the same line items are now:
Remember, the Collect() and Calc module, keep your sums OFF so there will be less calculations and aggregations. This reporting module should have SUMs turned on for the aggregations.
The Calc module is defined as the following with the LISS in the rows.
Hope this helps,
Rob
2
Answers
-
Hi @luke_e,
I think you also need a LIS with the 1st option to build the mapping module, right? But, just that you would replace the COLLECT() by some SUM functions.
I would say that the 2nd option is more powerful (almost always better when avoiding complex and long formulae), more sustainable (the performance would be even worst if you add line items in the source module) and easier to audit indeed. You can visit this article around LIS, the section "Transformation" is a similar case as the one you are facing.
Hope this helps!
0 -
@BenjaminNiel Thanks for the response, I'll have a squiz at the link. I can see a lot of use cases for an LIS but a lot of our models were built out prior to LIS functionality being introduced, so it's just a case of sussing out the best ones.
In my option 1, we didn't need an LIS as we had a mapping module with each account as a line item (with a format of list:account), and we'd just do a SUM using the relevant lines.
@rob_marshall Your example was pretty much right on with what I'm dealing with, albeit mine is much worse as far as the number of [SUM: X] parametres in each line, hah.
I guess the other benefit with option 2 is that there's no calculation change required if additional line items became in scope for the central P&L; we'd just need to tick a box, deploy and map the account.
Appreciate the detailed response and glad to know the COLLECT() avenue is the better approach.
Cheers,
L
1 -
@rob_marshall I have a similar situation, but I'm hitting a roadblock & wanted to get your thoughts.
I have a raw data module with 30k deals where I need to sum various values (Up-for-renewal (UFR), QTD Closed, Forecast $, etc) into a module with 3 dimensions (there would be 3 SUM parameters for each value being summed). So far, I have done the following:
- Added the value LIs in the raw data module to a LISS
- Created a COLLECT module to pull the values into the LISS
- Created a CALC module with the 3 dimensions & the LISS applied to sum from the collect module.
However, I then need to get those values from the LISS back into Line Item format, so I can apply additional calculations on some of the values, as well as use them as data inserted into text cards (unable to select different LISS context for each data point on a card).
Currently, I'm just duplicating the module in my step 3 above but with Line Items instead of the LISS, then selecting the LISS item relevant for each item. For example UFR = CALC.Amount[select: LISS.UFR]. I see in the Calc module in your solution above you're able to reference the module with the LISUB directly into Line Items; how is that working?
Any feedback would be appreciated.
Thank you,
Caleb
0 -
In your SYS module that has UFR (the list members), create a line item formatted to the LISS (LISS Mapping)…this is where you can do the lookup, if I am understanding you correctly.
CALC.Amount[lookup: SYS List.LISS Mapping]
0 -
@rob_marshall Not sure I understand. Here are some screenshots for clarity.
Here is the raw data module; UFR $ and Renewal $ Forecast are 2 of the 10 metrics I'm needing to sum into a module w/ the first 3 LIs as dimensions.
I then add UFR $ and Renewal Forecast $ to the LISS, & add a collect formula to pull those into the LISS.
Here is the module where I sum the collect formula into those 3 dimensions.
Here is the formula I use in that "Amount" field to sum from the COLLECT LI.
'DAT01 Retention Data'.'Collect - ARR Summary Metrics'[SUM: 'DAT01 Retention Data'.Quarter, SUM: 'DAT01 Retention Data'.'L1', SUM: 'DAT01 Retention Data'.Renewal Type]
I then need to transform the LISS back into line items, so I can apply additional calculations such as Forecasted RR & % UFR Closed, as shown in this screenshot.
Do I need to create a second LISS of the LIs in the CLC03 module, so I can look up the values from the Original LISS without having to select each LISS item?
Thank you,
Caleb
0 -
I think I understand what you are saying and the answer is yes…Create another LISS for the new line items and then create a SYS LISS2 module that has a mapping line item to the first line item subset. Then you can reference the summed data from CLC03 ARR Summary Consolidated.
Also, I would not have the Collect statement in the first module, have it in another called COL Retention Data as it will be cleaner for the next person.
0 -
You good?
0
