Text Concatenation | Model Optimization Team series

modeloptimizationteam

Hello! Welcome to the Model Optimization team's monthly blog post. This is an eight-part series, following the topics we see occurring most frequently in slow customer models. Our aim is to inform and guide how to build with best practice intentions for these model building topics.

Each month, two of our analysts will take you through how to spot and improve these formula structures! We hope these can help with your model performance.

All the best - Cianna Bramley (Model Optimization Manager)

……

This month's blog is brought to you by Rosa Eliza Dimaano and Lip Chean Soh.

We will discuss Text Concatenation in the seventh series of our Model Optimization blog. This article will demonstrate how problematic text usage is for the Anaplan model and how much of a factor it plays in the model's poor memory and computation efficiency.

Additionally, we will explain in this blog how to locate it to your model and how to make it better.

Why is this bad?

In computational systems, text takes more memory, which can result in poor performance and lengthy model open time when frequently occurring in a model. The topic of this blog will examine text concatenation, a prevalent problem that mostly results in poor performance in many Anaplan models.

We can find more details about the comparison of Anaplan cell types that highlight the performance and memory use of text in this article: Memory usage from text concatenation.

How do we find it?

There are three ways to find concatenated text in your model:

1. Manual inspection:
   
   1. Search the blueprint of the module and look for a line item with TEXT (“.”, “_”, “123456”, etc.) in your formulas.
      
   2. Download the line items and search in the csv file.
      
      1. Go to Modules and click "Export".
         
      2. Click "Run Export".
2. MAPs: this is a free report your BP can provide up to twice per month. It shows Best Practice checks which includes highlighting concatenated text occurrences.
3. Request Model Optimization reports through Hypercare Analysts or BPs, which analyze the models for best practices and Planual violations including Text Concatenate, as well as providing specific suggestions on how to improve slow formulas.

How do we improve it?

To fix this issue, we suggest the following steps:

1. Perform Text concatenation with the fewest possible cells.
2. Split common joins to separate line items.
3. Make use of IF ISBLANK() when joining text.
4. Use other data types instead of Text where possible (eg. List formatted / dates).

Examples

Example 1: Add Text with fewer cell count

In this example we can add the text “_” to the list with fewer cell count List. For this example “Shipping Methods” is smaller than “P2 Products” (see cell count of formula extracts below).

In Systems modules create line items for CODE( ITEM( 'P2 Products' ) ) and CODE( ITEM( Shipping Methods ) ) .

In the SYS11 Shipping Methods system module you can also perform the concatenation.

Then, reference the created 'Code P2 Products' and ‘Conca_Shipping Methods’ to the original line item ‘Shipping Product’.

The original becomes:

As per Planual 2.02-05 Create "joins" in smallest hierarchy, creating another line item for joining text with fewer cell counts, it will help to disregard text parts that impact the memory and make the formula slow. With this formula, it clears a lot of memory in the final text. This will be a lot of help, especially to line items with thousands or millions of cells.

Example 2: Split common joins to separate line items

The example shows multiple joins of text. To minimize the use of memory we must try to avoid avoid multiple joins and split common joins to separate line items.

The original formula becomes:

Note: If the Constant text like ”_” always reference to different line items. It is suggested to create a System module.

Example 3. Make use of IF ISBLANK() when joining text.

As we can see the Shipping Method of Orders consist of Blank and it can cause memory use when it is joined with other text.

In Planual 2.02-04 Text Strings: Text can cause memory issues and is the worst performing data type. Text operations are less efficient than other formats such as boolean, number, date, time period, and list. Anytime possible, try to use one of the more efficient types.

Where possible, make use of IF ISBLANK() when joining text, if the strings are empty set to BLANK.

Assuming ‘Shipping Method of Orders’ must be populated in order for ‘Optimize_Stating SKU Code’ to be valid.

We set it Blank first for early exit from the formula, which supports the P (Performance) rule of PLANS.

You can also see another example from Nested Ifs – Model Optimization Team Blog Example no.2 using text in Nested Ifs formula.

Summary

As we can see using text can cause slow performance since it uses a lot of memory in the model. Text concatenation should be done with the fewest cells possible. Much thought should be given before any text is used. We highly encourage you to check out this article for additional information (also mentioned above): Memory usage from text concatenation.

Questions? Leave a comment!

……….

Check out the other articles in the series:

• Calculate once and reference | Model Optimization Team series
• SUM and LOOKUP | Model Optimization Team series
• Nested Ifs | Model Optimization Team series
• RANK | Model Optimization Team series
• Calculate with the fewest cell | Model Optimization Team series
• Convert CUMULATE functions to PREVIOUS | Model Optimization Team series

JaredDolich

@modeloptimizationteam time for me to reread the Planual. As I was reading the first example, concatenating on the smallest list, I thought, wow, that's new and should be in the Planual. Duh, It already is.

Anyway, great post and a terrific way to think about how to optimize the model.

andrewtye

@JaredDolich - as they say everyday is a school day.

And something else we do is have the "_" as a line item in a system module and that's referenced rather than it being in the text field itself. Sure I read somewhere that was better than having it there.

Plus remembering which way is more often true on a boolean can be quite the challenge!