Daisy Chaining versus Avoiding Duplicate Formulas?

cbrookes
cbrookes
in Modeling

Hi All, 

I have a question about daisy chaining versus not repeating a formula. From what I understand, the engine will calculate the below module faster if I didn't reference Division and Deal Size in the Selected line item.

Line itemSelectedDivisionDeal Size
Formula(ISBLANK(Division) OR FilterSelections.Division = Division) AND (ISBLANK('Deal Size') OR FilterSelections.'Deal Size' >  'Deal Size') DAT01.DivisionDAT01.'Deal Size'

 

This makes sense because the engine will have to calculate Division and Deal Size before calculating Selected.

I think in this example it is reasonable to reference the data module directly in the Selected line item. 

However, let's say we have a similar example but the Division and Deal Size line items require an IF THEN statement and multiple SUMS. In this case it seems the Planual provides conflicting guidance. Do I avoid duplicating my formula or do I worry about daisy chaining?

Additionally, where does auditability fit in? The Selected line item is going to get very complicated if I try to avoid daisy chaining. 

Thanks,

Chris

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @cbrookes 

     

    Well done, great question.  So yes, daisy chaining is bad and is frowned upon for exactly what you stated and your solution in the first part is correct (reference the SYS module).  Remember, it is always faster for the system to read data than it is process it which is why we want people to do the calculation once, and then reference it many times.  A perfect example of this is finding the code of a particular list member or finding the parent.  The good way is to put this in a SYS module and the reference, but folks forget about the SYS module and go ahead and do it in the module they are working on.  Well, this happens very often when you have multiple model builders, and before you know it, the system is doing code(item(list)) in multiple places.  Additionally, if this isn't done in a SYS module, but in a module with multiple lists and they aren't using a subsidiary view, then that formula is being kicked off way too many times due to the extra dimensionality.

     

    As for the second part of the question, the IF THEN Else piece.  We see this a lot, where If x then sum something, else if it is Y, then grab a different line item, but the sum piece is still the same.  For this, it is best to use a calculation module and utilize a line item subset (LISS).  That way, in the calc module, everything is already being calculated, and now instead of using a IF statement, you can use a lookup.  With that said, this won't work every time, but what it does do (hopefully) is to get people thinking in a multi dimensional way instead of a sequential way.

     

    Another example:

    If item(list A) = x or item(list A) = y or item(list A) = z then line item A[sum: Division] else 0.  This can be modifed by using a SYS module on List A and creating a Boolean for this particular logic, call it Active, where the Boolean is true for x, y, and z.  So now your statement is: 

     

    If SYS List A.Active then line item A[sum: Division] else 0

     

    Lastly, it is better to break your Selected formula up into multiple line items and then have one that checks for both pieces of logic.  Yes, you can do it one, but what if another piece of logic needs if Division is blank and the user selection picked a certain Division?  Well, the short answer is you will have to redo that logic again.  Instead, give this a try:

     

    Line item 1: (ISBLANK(Division) OR FilterSelections.Division = Division)....formatted as a boolean

    Line item 2: (ISBLANK('Deal Size') OR FilterSelections.'Deal Size' >  'Deal Size') ....formatted as a boolean 

     

    Filter: Line item 1 and Line item 2....formatted as a boolean

     

    More often than not, sequential solutions can be figured out by letting the system work its magic and do what it was intended for, working it out multidimensionally.  So, in a nutshell, you want to avoid daisy chaining and long winded If Then Else statements.

     

    Hope this helps and clears things up.

     

    Rob

Categories