Optimize Formulas with Scoped Calculations

  • 14 June 2024
  • 0 replies
  • 88 views

Userlevel 4
Badge +3

Scoping is a technical optimization that the Pigment engine uses to ensure your model is executing formulas and computing values in the most effective manner. It ensures that your calculation path is streamlined, and it minimizes unnecessary recalculations in your Application. In Pigment, you can profile updates and their calculation paths resulting from specific updates applied to your model. Scoped calculations is one component of information available to you when you profile your Application updates.

Why is scoping beneficial to your model? The best practice for performance management is to reduce unnecessary calculations, which is the goal of scoped calculations. When a formula is scoped, any recalculations in your model will only occur on the Dimension items that are impacted by the formula of the Metric/List property, and not on every cell downstream of the Metric/List property. Only the cells that need to change are calculated, and not every impacted cell. By fully scoping your formulas, you’re ensuring minimal recalculations downstream and reducing the amount of data you’re computing. 

Required Reading

For a comprehensive understanding of performance management in Pigment, we recommend these articles to get you started:

View Scoped Calculations

Scoping is based on each formula computation of a Metric or List property, and are visible in the calculation path of any update that is made. When you profile an update and its respective calculation path from a specific Block, or from your Application History, the Scope column indicates one of the following values:

  • Full. The formula is fully scoped, and the output calculations from this formula will result in minimal recalculations. This is the most efficient level.
  • Partial. The formula is scoped on at least one Dimension, and this same partial scoping can in some cases distribute to subsequent calculations depending on the downstream formulas.
  • None. The formula isn’t scoped and next ones will likely not be scoped as well. Any changes in that are not scoped will result in the model being recalculated entirely.

Tables always have a scope of None, as a Table cannot be referenced in formulas so will not have any output scope and will always be at the end of a calculation path (you can see this by filtering on the path to the Table using the “Filter on the path to this object” option). The scope on Metrics on the Table, but not the Table itself, will impact subsequent computations.

  • N/A. No scoping is applicable.
Scope Column on the Profiling Pane

 

Understanding how scope can be lost during computations

There are instances where a formula scope can be lost. This can happen when: 

  • A formula is assigning values based on a constant value.
  • A Dimension is added to a Metric structure, but wasn’t included in the previous calculation.
  • You use functions that functionally have an output scope of None, for example: 
    • PREVIOUS
    • CUMULATE
    • PRORATA
    • MOVINGAVERAGE
    • MOVINGSUM
    • [BY CONSTANT: ]
    • ADD 

Scoped Calculation Example

Let’s say you have a Metric with the Dimensions City and Month, and the revenue is aggregated per City and per Month. You have a second Metric, based on the above one, but this one has a Country and Month Dimension. In this Metric, you’re calculating revenue by Country and by Month, where you’re using an aggregator to sum the data from city to country.

If you update the revenue for a few cities for the month of March 2022, you only want to perform a recalculation for March 2022. You don’t want to recompute the sum for any other months. Also, if you update data for cities that are only in Germany in your Country Dimension, you only want to perform a recalculation for Germany, and no other countries. 

When a formula is scoped, a calculation is only performed on items that are impacted in the calculation. In the example above, a formula is scoped if the only calculations that occur are:

  • cities in Germany for the City Dimension, and only for the Germany item in Country Dimension
  • the March 2022 item in the Month Dimension

 No other items in the City, Country or Month Dimensions should be recalculated as a result of the scoped formula.


0 replies

Be the first to reply!

Reply