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

  • 27 June 2022
  • 0 replies

Userlevel 4
Badge +2

Looking to better understand modelling performance in Pigment? We got you covered, here are some performance optimisation tips that all modelers should know.

  1. Calculate at 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 seperate out complicated calculations into multiple metrics. It has benefits on 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. 


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

Calculated items and show value as are amazing on performance and their scope is expanding by the quarter.

  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 will lower spec computers may not even be able to access the page at all.
    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 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 it’s own calendar.

    For example, If I have 4 applications that require 2 years of actuals 1 year of forecast, and I have a long term planning application that needs 10 years of forecast.

    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 eachother using a dimension property.


  1. Not all functions are equal ⚖️

    Certain functions are more efficient than others, for example Filter tends to perform much better than IF. Some functions that tend to be slow include: IF, Cumulate, Previous, Yeartodate, Isblank, isnotblank (Isnotblank densifies the whole metric, try to use Isdefined instead).

    When building, you should consider what's format you want the desired data in, and then consider what's the most efficient formula to get you your result

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

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

    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 most inefficient as IF with multiple conditions tends to perform badly when compared to the alternatives. Option 2 (Filter) operates like an IF with one condition.
Option 3,will create the least amount of 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 subsets, where a subset is an alternative second dimension that is smaller than the original 🤔

    In very select circumstances you might want to use subsets 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. When loading into transactions lists, only include the relevant data and transactions in the import ✅

The higher the volume of transactions the longer Pigment will take to do calculations from those cells.


  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. Avoid assigning values where it is not necessary.

For example,

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

  1. Remove dimensions in formula as soon as possible 🥾

    For example,
    CUMULATE(something[REMOVE: Country]) and 
    CUMULATE(something)[REMOVE: Country] 
    returns the same result, but the first one will be way more efficient

Final Comments ⏬
The above has been some of my top tips for modelling performance. 

Ultimately 'Done is better than perfect' and you won’t always have time to build things in the best possible way, however, you should consider performance implications as you build and seek out alternatives where blocks are noticibly performing poorly.

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

Link to part 1:



0 replies

Be the first to reply!