Top Tips for modeling in Pigment - Part 2: Performance Optimisation

  • 27 June 2022
  • 1 reply

Userlevel 5
Badge +4

Looking to better understand performance best practices in Pigment? We got you covered. Here are some performance optimization tips that all modelers should know.

  1. Calculate the minimum number of dimensions necessary 🎯

When you're building a calculation, you should consider building at the minimum no. of dimensions necessary.

For example, if you're building a Profit and Loss report and you need to see it by Department and Function, but Department is also a property of function, then we don't need to dimension the metrics by BOTH department and function.


  1. Avoid doing too much within one Metric 😬

It’s good to separate out complicated calculations into multiple metrics. This will benefit application auditability, sustainability and performance. The more calculations you're doing within one metric, the longer it will take to calculate, the harder it will be to read, and the increased chance of that metric timing out in future. 

A good guideline is that, if you have to copy and paste part of a formula more than once, it may be worth its own metric.

  1. Use Calculated Items or ‘Show Value As’ instead of creating additional metrics where possible 🙌🏽

Of course, don’t forget about Calculated Items and the Show Value As functionality! These can take the place of superfluous metrics and are amazing for performance.

  1. Avoid using large sparse views in dashboards 📣

Avoid having views with a large number of rows and columns. Large views take longer to load, and those with lower spec computers may not even be able to see the full view.

If you need users to input or view large amounts of data, then make use of page selectors and available items in page options.

For example, instead of including 10,000 projects in rows, why not:
- Have one project at a time in the view. You can do this by adding projects into pages and rows, then changing page options to be single selector.
- Have only the projects that relate to a single country in the view. You can do this by adding Projects > Country dimension to pages and then changing page options to be a single selector.
- Filter out blank cells and irrelevant data


  1. Use multiple calendar dimensions when the time range is significantly different by use case 📆

In general, it’s best practice to have all your applications running from a central calendar within the data hub, however, there are some exceptions. If you have an application that has a significantly different time range than the rest of your workspace, it's probably worth having it on its own calendar.

For example, If I have 4 applications that require 2 years of actuals and 1 year of forecast, and I have a long term planning application that needs 10 years of forecast, then instead of having a 12 year calendar in the data hub I could have a 3 year calendar in the data hub, and a 12 year calendar specific to long term planning. I then would need to map the two calendars to each other using a dimension property.


  1. Not all functions are equal ⚖️

Certain functions are more efficient than others. For example, Filter tends to compute much faster than IF. Some functions that tend to be less efficient include: IF, Cumulate, Previous, Yeartodate, Isblank, and Isnotblank (Isnotblank densifies the whole metric, so try to use Isdefined instead where applicable).

When building, you should consider what format you want the desired data in, and then consider what the most efficient formula is to achieve that result

Example - I want to calculate Revenue for the Country France.

Option 1 - Output Metric by Country and Month
IF(Country."France", Revenue France , BLANK)

Option 2 - Output Metric by Country and Month
Revenue[Filter: Country.”France"]

Option 3 - Output Metric by Month
Revenue[select: Country."France"]

All three calculations will return the same value, with Option 3 removing the Country dimension.


Option 1 will be the least efficient, as IF with multiple conditions tends not to perform as well as the alternatives. Option 2 (Filter) operates like an IF with one condition.

Option 3 will create the fewest cells overall, so it could be the best solution, but it would depend on whether the target metric would need to be dimensioned by country. If you want to keep the country dimension, Option 2 would be the best solution.


  1. Consider using smaller versions of lists, where items are in an alternative second dimension that is smaller than the original 🤔

In very select circumstances, you might want to use these lists in order to reduce the number of cells certain blocks contain.

An example would be in a headcount planning application. In reality your staff costs will probably only be relevant to a handful of accounts, so instead of dimensioning all your outputs in the headcount planning application by the full dimension of accounts, you could dimension it by a new dimension called "Headcount accounts" which would map to your central accounts list. 

  1. Troubleshoot by checking formula calculation times 🔎

Pigment formulas timeout after 3 minutes (resulting in an application error) . When you write a formula, you should always check to see how long it takes to calculate.

It's also worth writing formulas with the future in mind, for example, if your formula takes 2 minutes to calculate, then it's worth looking at the alternatives, because when you add more data such as additional years or dimension items, there's a good chance that formula will time out.

Personally, if a formula takes more than 10 seconds to calculate I will investigate alternatives.

  1. Exclude irrelevant data from transaction list imports ✅

When loading into transactions lists, only include the relevant data and transactions in the import. The higher the volume of transactions, the longer calculations from those cells will take.

  1. BLANK Beats 0 / FALSE 🥊

Blanks are not counted when we run calculations as they are not populated with data. This is not the case for 0s and FALSE booleans, so avoid assigning values where it is not necessary.

For example,

IF( Condition A, Metric) has better performance than
IF( Condition A, Metric, False)

  1. The order of your modifiers matter

​​​​​You want to perform calculations and transformation on the minimum number of cells possible. In general it’s better to use your modifiers in the following order to minimise the data that pigment has to compute:

FILTER / SELECT → BY (Aggregation) → REMOVE → BY (Allocation) → ADD

For example,

Example 1
Calculating a Revenue Target for EMEA Sales Reps, based on the Actual Revenue last year. Target will be split by Product Category.

[FILTER: City.Region = Region.”EMEA”] [SELECT: Month - 12] [BY: City.Country] [REMOVE: Legal Entity] [BY: Sales Reps.Country] [Add: Product Category]
* Assumption

Would be more efficient than

[Add: Product Category] [FILTER: City.Region = Region.”EMEA”] [SELECT: Month - 12] [BY: City.Country] [BY: Sales Reps.Country] [REMOVE: Legal Entity]
* Assumption

Example 2

CUMULATE(something[REMOVE: Country]) and 
CUMULATE(something)[REMOVE: Country] 
returns the same result, but the first one will be much more efficent as we remove Country before executing the cumulate (Cumulate calculating on many less cells)

  1. Calculate once reference twice 🤔

This is specifically important if you have a heavy calculation.

For example, it is better to do:

Metric 1: Cumulate( Revenue, Month, Month.Year)
Metric 2:  (Metric 1 * ‘FX Conversion’ ) - (Metric 1 * FX)[Select: Month -1]

Metric 1: 
(Cumulate( Revenue, Month, Month.Year) * ‘FX Conversion’ ) - (Cumulate( Revenue, Month, Month.Year)* FX)[Select: Month -1]

This is because in the second example we are calculating Cumulate( Revenue, Month, Month.Year) twice. It’s better to calculate this once in a separate metric, than reference that metric twice in the formula.


  1. Review the dependency diagram for redundancies

When you have dozens or even hundreds of blocks in your dependency diagram, it can be difficult to spot redundancies. However, if you add back a metric that was removed a few blocks earlier in the dependency tree, it’s worth investigating to see if you can streamline this for better computation speeds.


  1. Create required scenarios, metrics and more ahead of busy periods

During busy periods like important planning cycles, there will be significantly more activity in your workspace. If you’re also creating new scenarios and new metrics at the same time, it could create a slowdown. By defining these requirements outside the busy period, you can make sure each user gets optimal performance whilst using Pigment.

   15. Minimise the number of properties with formulas inside your transaction lists.

Transactions lists can only calculate one property at a time. If an action is performed that triggers a re-calculation of the full transaction list (like an import), then every property must calculate seqentially. Large transaction lists can take 10 minutes + to fully update (Still very fast, but, something to be aware of if you complete lots of transaction list recalculations)

Final Comments ⏬

Ultimately, done is better than perfect, and you won’t always have time to build things in the best possible way. However, you should try to consider performance implications as you build and seek out alternatives for even faster performance.

Do you have any performance recommendations for the community? Let us know in the comments below

Link to part 1:

1 reply

Userlevel 1

Awesome to review before starting a major build or learning Pigment for the first time. It’s important to develop good habits early on!  👏🏻