It is good practise to occasionally review your model and find ways to optimize it. Optimizing your model can lead to improved performance, speed and ease of maintenance. There are many different ways to optimize your model. This article focuses on using insights from Block Explorer to identify your slowest performing and most dense Metrics.

Identify the slowest Metrics in the Application

The first step in optimizing your model is to identify your slowest Metrics.

1. In your Application, open the Blocks dropdown in the sidebar and select All Blocks.
2. In the main pane, select the Metrics tab, bringing up the Insights button to its right. 
3. Select Insights and toggle on the following settings (pictured below): 
    

   

    

• Number of formula executions. This column identifies the Metrics in which formulas have been executed the most times in your Application over the past 15 days. Hover over this value to view the number of executions over the past 24 hours or any trends in this statistic. Formula optimizations or modeling improvements on these Metrics will have the highest impact in the performance of your Application, as the number of executions indicates the number of computations that are occurring on the Metric.
• Average execution time. Use this column to sort and identify the slow Blocks in your model based on the average execution time over the past 15 days. Hover over this value to view the same statistic over the past 24 hours, as well as spotting any unhealthy trends in your Application. For example, an upward arrow indicates that the formula execution time has increased. At this point, you need to review any recent updates in the Metric’s formula that has resulted in reduced performance and slower execution time.
• Last execution time. Use this column to view the latest Metric formula execution time from the past 24 hours. This can help with see the impact of any short term optimizations you might have made on the Metric.
• Maximum execution time. Use this column to identify the slowest Blocks in your model. It gives an insight into the highest risk formulas that were the slowest to execute over the past 15 days. Hover over this value to get the same insights but with a trend over the past 15 days or past 24 hours. 
• Total execution time. Use this column to display the sum total of formula execution time for all formulas executed on the Block over the past 15 days. Hover over this value to get the same insights but with a trend over the past 15 days or past 24 hours. 

Use Performance insights on a specific Metric

When you’ve identified the slowest Metrics in your Application, you can view insights for that Metric specifically:

1. In the sidebar’s Blocks dropdown, select the specific Metric you want to analyze.
2. The Performance insights button appears by the settings icon in the top right. Select it to view insight statistics. 

View performance of Metrics in Tables

You can analyze performance of Metrics appearing in your Tables by Scenario.

1. Select a Table you’d like to analyze from the sidebar’s Blocks dropdown.
2. Select the Performance insights button top right, bringing up the below dialog.

It provides insights divided into two sections, Table refresh time and Metric insights, as follows.

Table refresh time

Tables in Pigment are optimized to display the data from all Metrics that are part of the Table by precomputing all the data from the Metrics. To minimize the time taken to display, a Table’s data is refreshed only when there is a change in the Table configuration (for example if a Metric is added to the Table) and not each time the Table is opened.

In the first section of Performance insights, Pigment displays the following insights on the time it took for the last refresh of the Table’s data:

1. Average Time 
2. Maximum Time
3. Latest Time

Metric insights

The second section provides insights for all the Metrics in that table. You use it as follows:

1. Choose a Scenario from the top dropdown.
2. Select the kind of Metric insights you wish to see by clicking in the box marked By.

It ranks results for your Metrics from highest to lowest for the insight you selected. 

Use this process to review the worst performing Metrics in the Table, so that you can focus on optimizing their performance. For example, you might be interested in checking the ranking of Metrics by Maximum time to see which Metrics in the Table contain formulas that take the longest to execute.

Use Performance Insights in Formula Playground before creating your Metric

When building new Metrics, it is highly recommended to use the Formula Playground to analyze and optimize your formulas before creating the Metric. Access the Performance Insights panel within Formula Playground to test the performance of your formulas before creating the Metric. 

Best practise for improved formula executions

Some recommendations for making Metric formula executions faster:

• If the Metric contains a long formula with nested functions, consider breaking up the formula into Metrics and then referencing those Metrics in the formula. This results in a shorter formula per Metric, which may reduce execution time. 
• When you break up your formulas into multiple Metrics, ensure that you use formula calculations in a Metric once. You can then re-use that Metric by referencing it multiple times if needed. This reduces the number of formula executions and optimizes performance. It also reduces the number of Metrics needed and reduces maintenance.
• Ensure that your formula is performing calculations on a smaller scope of your data. Narrowing this scope as early as possible prevents you from performing unnecessary calculations on data that will be discarded later. 
  • For example, use the FILTER function on specific Items of the Dimension to improve performance. Try to build your formulas to calculate on the smallest possible scope of data for that calculation. 
• The order in which you list functions in a formula can greatly improve performance.  
  • For example, reduce the data size as soon as possible within your formula using IF() or "FILTER:] or
  • Next, do allocation by adding new dimensions using cADD:] or [BY:].

Leverage the “sparse Pigment engine” where possible 

​​​​​​​Metrics perform better in Pigment on sparse versus dense data. While there are instances where you need to have values in every cell, there are instances where you can use Blanks vs 0 or False. Therefore, the goal is to ensure your blocks are as sparse as possible. For more information, see How Pigment Handles Sparsity in Formulas. 

First, identify the most dense Metrics:

1. Open the Blocks dropdown in the sidebar and select All Blocks.
2. In the main pane, select Display top right.
3. Toggle Maximum Size to On.
4. Maximum Size is added as a column header. Select it to sort your Metrics by Maximum Size. 
   This gives you an indication of the theoretical densest Metrics in your application.
5. Select the Performance Insights button top right.
6. Toggle Density to On.
   This shows you whether these Metrics are at risk of becoming your densest Metrics.
7. Select the Dimensions tab in the main pane and select Display top right.
8. Toggle Number of items to On.
   Assess whether you can reduce the number of Dimensions in your Metrics or reduce their size.

Then make dense Metrics more sparse:

• Review functions like IFBLANK,ISBLANK, ISNOTBLANK and see if they are needed, or if the output of the formula is being populated with values that can be replaced with BLANK instead.
• Review any usage of zeroes (0) instead of blanks and see if it is needed.
• Review any usage of false (FALSE) instead of blanks and see if it is needed. In particular for Access Rights, if you are on the current version of Access Rights, use BLANK instead of FALSE . This behaves the same and produces a more sparse Metric. 

Align the structure of your blocks with formulas

Your formula’s output should contain the same Dimensions as your Metric’s structure. Misaligned formulas can result in unexpected results and aren’t performant. Review any Blocks containing formulas that are misaligned with the structure of the Block. Optimize your formulas by explicitly adding or removing those Dimensions in the formula itself. For more information, see Dimension Alignment Indicator. 

Keep your model clean by removing unused blocks

Unused Blocks can result in inefficient models, especially if they are dense. Toggle on the Used Block indicator to check on the usage of Blocks and delete them if unused. You can check the detail of where the Block is being used in the Block’s Settings, under Block Usage. 

Review Blocks that are central to your model to ensure the formulas are optimized

Use the Referenced field to determine which Blocks are most central to your model. This number reflects the number of Blocks referenced in the formula of the Block, so it means that the Block with the highest number is being used by the most Blocks in your application.
 

Quick tips on formula optimizations on these central Blocks:

• Reduce the length of formulas where possible.
• Avoid nested IF statements that are hard to read or maintain and are not performant.
• If you can, perform the calculation on multiple intermediary Blocks rather than a single Block. It’s more performant to split this up.

Export Block Insights from the Block Explorer for further analysis​​​​​​​

If you need to analyze the insights from Block Explorer for further analysis and advanced filtering, sorting and further analysis, you can export the insights displayed in your Pigment Block Explorer panel as a CSV file. Click on the More button at the top right of Block Explorer and Export Block Insights for All Transaction Lists, All Dimension Lists, or All Metrics in your application. The CSV report also contains more information on the columns added or removed due to misaligned formulas.

Learn more

• Work with Block information in Block Explorer
• Display performance measurements with KPI widgets