Decreasing the length of your formulas

All too often, when analyzing a model, we see very long, convoluted, and complex formulas. These are commonly nested IF THEN ELSE statements and can be in excess of 5,000 characters. 

Line item subsets (LISS) can be used to shorten the length of the formula while making the solution more dynamic, scalable, maintainable—and will, in turn, increase the health of the model, as well as the overall performance.

Problem statement: I need to summarize line items from three different source modules by customers, products, and scenarios, but when the version changes, I need the line items to render the proper information for the customer and product.

Model design

Lists:

• Transactions—list of all transactions
• Company Flat—list of all companies
• Product Flat—list of all products
• Scenarios—list of scenarios, very easily could have used the native versions
• Metrics—list of the metrics needing to be reported (units, price, net amount)

Modules:

• Transaction01 Actual Data: loaded from the source system

• Transaction02 Plan Data: loaded from the source system

• Transaction03 Budget Data: loaded from the source system

Let’s take a look at a way that is possible—but is hard to maintain, performs poorly, and contradicts many Planual rules.

Solution #1:

Create a module dimensionalized by Customer Flat, Product Flat, Scenarios, Metrics, and Time. I am using the Metrics list (comprising of Units, Price, and Net Amount) because I only want to have one line item defined for the final solution.

The formula for the line item Solution #1:

IF ITEM(Scenarios) = Scenarios.Actuals THEN IF ITEM(Metrics) = Metrics.Units THEN 'TRANS01 Actual Data'.'Units - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Price THEN 'TRANS01 Actual Data'.'Price - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Net Amount THEN 'TRANS01 Actual Data'.'Net Amount - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE IF ITEM(Scenarios) = Scenarios.Plan THEN IF ITEM(Metrics) = Metrics.Units THEN 'TRANS02 Plan Data'.'Units - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Price THEN 'TRANS02 Plan Data'.'Price - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Net Amount THEN 'TRANS02 Plan Data'.'Net Amount - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE IF ITEM(Scenarios) = Scenarios.Budget THEN IF ITEM(Metrics) = Metrics.Units THEN 'TRANS03 Budget Data'.'Units - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Price THEN 'TRANS03 Budget Data'.'Price - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Net Amount THEN 'TRANS03 Budget Data'.'Net Amount - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE 0

The good news is, this works. But it violates so many best practices, yet we see this all the time. This is relatively tame compared to what we normally see in that this is only 1,535 characters (with spaces), while we routinely see upwards of 4,000 to 7,000 character formulas.

Let’s look at what is wrong with the formula:

Hard coding (Planual Rule: 2.02-12 – No Hardcoding to List Members)

The formula contains hardcoded references to different lists (Scenarios and Metrics). What if a new Scenario is created or a new Metric is created? The model builder will have to remember every line item that is similar to the above and update it. Similarly, if one of those list members needs to be removed, currently that is not possible because those members are hardcoded to this line item. Additionally, if these lists (Scenario and Metrics) need to be marked as Production Data lists (for ALM), this would not be possible, and you would get the following: error:

To get around the hardcoding, you could use SYS modules with Booleans.

This does remove the hardcoding and makes it a bit more dynamic, but the formula is still not efficient:

IF SYS Scenarios Properties.Actual? THEN IF SYS Metrics Properties.Units? THEN 'TRANS01 Actual Data'.'Units - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF SYS Metrics Properties.Price? THEN 'TRANS01 Actual Data'.'Price - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF SYS Metrics Properties.Net Amount? THEN 'TRANS01 Actual Data'.'Net Amount - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE IF SYS Scenarios Properties.Plan? THEN IF SYS Metrics Properties.Units? THEN 'TRANS02 Plan Data'.'Units - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF SYS Metrics Properties.Price? THEN 'TRANS02 Plan Data'.'Price - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF SYS Metrics Properties.Net Amount? THEN 'TRANS02 Plan Data'.'Net Amount - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE IF SYS Scenarios Properties.Budget? THEN IF SYS Metrics Properties.Units? THEN 'TRANS03 Budget Data'.'Units - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF SYS Metrics Properties.Price? THEN 'TRANS03 Budget Data'.'Price - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF SYS Metrics Properties.Net Amount? THEN 'TRANS03 Budget Data'.'Net Amount - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE 0

You have to remember, even though this formula is defined only once for the line item, it is getting calculated on the number of cells that line item contains. So, if this line item contains 1 million cells, this formula is getting processed 1 million times.

Maintenance

This was touched on a bit earlier, but there is no way this can be easily maintained, easily understood by new model builders, nor is it scalable. When you are creating the logic for these line items, you have to remind yourself you may not just be designing this for you to maintain; you may pass on to either the client or to another model builder and want this process to be as seamless as possible.

Poor performance

Planual Rules:

1.07-02 Version Line Item subsets

2.02-01 Nested Ifs

2.02-02 <12 expressions in a formula

2.02-03 No Repeated expressions

2.02-18 Break up formulas

Long nested IF THEN ELSE statements are bad for performance because this formula is now single-threaded. This means it will traverse the formula until it gets a “hit”, in this case where the Scenario and Metric combination match. Remember, it is also better to break up the formulas into multiple line items versus squeezing the logic into one giant line item. 

Another aspect of performance is the number of functions in a formula. Look at all of the SUM’s in this formula (nine of them), yet they’re almost identical with the only differences being the line item and source of the data. It would be much more efficient if a calculation module was created to do the sums separately, but how do you do that with different source modules and different line items? This is where a line item subset comes into play and makes life much easier.

The solution:

Let’s take a look at how we can solve this problem in a better way by consolidating our source data, creating a dynamic way to align our metrics, and bringing it all together with a better performing formula. As you can see below, the data flows from left to right without traversing back to previous modules.

Step 1 - Line Item subset

Create a line item subset (LISS) using the three source modules and check all of the metrics.

If you are not familiar with how to create a line item subset see here.

Step 2 - Collect statement

• Create a module dimensionalized by the line item subset you just created and the lists of the source modules of the line item subset—in this case, Transactions.
• Create a new line item, number formatted, with the formula being COLLECT(). This collect statement essentially gets the data for all of the line items and turns the line items into a “dynamic” list. Also, since this is a “staging” module, you will be able to change Summary option to None.

Step 3 - Calculation module

• Create a calculation module to sum the data from the different source modules to the desired dimensions—in this case, Customer Flat, Product Flat, the line item subset (to get the “dynamic” line items), and Time. 

Why is this so important? Remember the nine different SUM’s in the original solution? Well, we have reduced those down to just one, yet we are getting the exact same results.

Step 4 - Mapping module

• Create a “mapping” module which aligns the metrics (list) to the versions list with the line item being formatted as the line item subset. 

What is great with this solution is it is 100% dynamic in that we can map the relevant “metrics” to versions without changing the formula. If we don’t want to calculate these metrics for any combination, then just remove or blank out the values.

Step 5 - The results

• In the original solution module, dimensionalized by Customer Flat, Product Flat, and Time, create a new line item named Solution 2, formatted as number. 
• For the formula, simply type 'CALC01: Sum of Metrics'.Data[LOOKUP: 'Map01: Metrics to Scenarios'.Mapping]

Basically, we are doing a lookup of the values that we summed in step 3 (the single summation formula) based on the mapping values in step 4.

Step 6 - Adding adjustments or a different source

Now, let’s say the use case changed, and now we want to add adjustments to the result, but only for the Plan version. If we had the original solution with the big IF THEN ELSE statement, we would have to reengineer to incorporate this new source for only the Plan version. With the new solution, we can simply create a new line item subset, enter the values into the mapping module for the correct version, and then update the final formula.

• Adjustment module: Same dimensionality as the results (Customer Flat, Product Flat, and Time).

• Create a new line item subset using the above module as the source.

• Create a “collect” module for this data with the summaries turned off.

• Create a new line item in the Mapping module, but this time format it to the line item subset you just created for Adjustments.

Notice, we only entered line items for the Plan, but we could do this for the other versions as well.

• In our reporting module, you can either create a new line item or continue with Solution 2 and type this in for your formula: 'CALC01: Sum of Metrics'.Data[LOOKUP: 'Map01: Metrics to Scenrios'.Mapping] + 'COLC02: Collection of Adjustments'.Data[LOOKUP: 'Map01: Metrics to Scenarios'.Adjustment Mapping]

As you can see, the data for the Plan version is different, but when we change the version to Actual or to Budget, the numbers will be the same because we are not adding the adjustments in for those versions (in the mapping module, those line items were blank).

Conclusion

By creating an additional module or two, as well as employing a line item subset, you can greatly increase the overall health and performance of your model while also making it much easier to scale and maintain. For more information on Line Item Subsets, please go to this article (Line Item Subsets Demystified).