It is good practice 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.
⚠️ Important
You need to have full Read access rights for the data in the Metric to view Size and Density insights. For all other performance insights, Members need the Configure Blocks permission.
Identify the slowest Metrics in the Application
The first step in optimizing your model is to identify your slowest Metrics.
In your Application, open the Blocks dropdown in the sidebar and select All Blocks.
In the main pane, select the Metrics tab, bringing up the Insights button to its right.
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.
The Total execution time column can be used to identify Metrics that are taking the most ‘computation power’ in your Application. This gives a good indication of those Metrics to focus on to improve the performance of the Application.
The Max Time column is a great way to identify Metrics with the highest risk of having formula timeout errors. These timeout errors result in your Application’s calculations not working. Focus on Metrics with a maximum formula execution time closest to a three-minute limit to reduce this risk.
For optimal insights on your Metric’s formulas, use the values in Max time and Average time together. The maximum formula execution time indicates the slowest possible Metric in the Application over the time period, while observing the average formula execution time gives a sense of the typical execution time for the Metric’s formula on average over the time period.
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:
In the sidebar’s Blocks dropdown, select the specific Metric you want to analyze.
The Performance insights button appears by the settings icon in the top right. Select it to view insight statistics.
If your Metric is part of an iterative calculation, a section appears at the bottom giving calculation times for the iterative configuration. See Iterative Calculations across multiple Blocks using PREVIOUSOF for more information.
View performance of Metrics in Tables
You can analyze performance of Metrics appearing in your Tables by Scenario.
Select a Table you’d like to analyze from the sidebar’s Blocks dropdown.
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:
Average Time
Maximum Time
Latest Time
Metric insights
The second section provides insights for all the Metrics in that table. You use it as follows:
Choose a Scenario from the top dropdown.
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.
The Formula Playground checks for excessive cell usage, showing the below alert when usage exceeds a billion cells:
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.