Understanding Dimensions and Dimension mapping is critical when working with formulas. Dimension misalignment could lead to wrong values or the inability to execute a formula. This article discusses the concept of source to target mapping in Pigment, identifying the impact when Dimensions differ in a formula, and understanding how modifiers work.
What is source to target mapping?
Let’s start with what source and target are in relation to formulas. When writing a formula, source refers to the Metric or List that you are trying to pull data from. Target refers to the Metric or List Property that you are writing the formula in. Both your source and target have their own Dimensions creating their structure. Source to target mapping is aligning the Dimensions of your source into the Dimensions of your target.
Why does it matter?
Dimensions have a huge impact on how your formulas work because they determine how the values are calculated or distributed. If the Dimensions differ from source to target, then modifiers or functions can be used to pull the desired value. If you don’t use modifiers or functions, Pigment often implicitly applies modifiers to correct source to target Metrics with misaligned Dimensions.
For example, if you have a Country Dimension in a source Metric but not in your target Metric, and you reference this data, you get the sum of all the values for each country. That is a similar functionality to how the Remove modifier works.
Let’s look at how modifiers work at a high level and then the impact of Dimension mismatches.
How to adjust Dimensions using modifiers
Modifiers change the Dimensions of source data. They do this without making changes to the actual metric that you are pulling data from. This allows you to use them in a Metric to adjust your data to better fit your needs. There are four different modifiers, BY, ADD, REMOVE, and SELECT. Modifiers are paired with aggregator or allocation methods to define how to adjust the data to fit into the newly defined Dimensions.
ADD
This modifier adds a Dimension and then distributes the data based upon the allocation method that is selected. When working with a formula where your source has fewer Dimensions than the target, this modifier can be used. For example, if your source doesn’t have the Month Dimension but your target does, you could use the ADD modifier to bring in that Dimension and then define how the data should be distributed across the months.
Example
'Data Country x Product'[ADD SPLIT: Month]
REMOVE
This modifier removes a Dimension and then performs an aggregation depending on how it’s defined. For example, if your source Metric has a Product Dimension but your target doesn’t need it, you could use the REMOVE modifier to remove that Dimension and then define how the data should be aggregated in the new Metric.
Example
'Data Country x Product'[REMOVE SUM: Product]
BY
BY is one of the most versatile because it replaces a Dimension with another one. Depending on how the Dimensions are grouped together, it can be used for allocation or aggregation. This modifier allows you to display data by different Dimensions depending on a mapping attribute. A simple example is the Calendar. Months are mapped to quarters, and quarters mapped to years. The BY modifier allows you to take source data at the Month level and aggregate it up into either Quarters or Years. If your source data is in Years, the BY modifier can be used to allocate data to the month or quarter level.
Example
'Data Country x Month'[BY: Month.Quarter]
SELECT
SELECT allows you to remove a Dimension and filter down to one or more Items. For example, if you have a source Metric with the Country Dimension and your target Metric does not have that Dimension, the SELECT modifier allows you to pull data from one or more countries to bring into the target Metric.
Example
'Data Country x Month' [SELECT: Country."United States" OR Country.Region=Region."EMEA"]
ℹ️ Note
When learning how modifiers work, the formula playground is going to be your best friend. Try out each of the modifiers below while in Auto mode.
Select Enter once you input your source Metric to view its current Dimensions.
Enter the Modifier and select Enter to see the Dimensions change.
Adjust the aggregator or allocator to see the data change.
What happens when you don’t use modifiers?
If the Dimensions in your source and target Metrics are different, Pigment adjusts the data depending on how they are different. Check out the examples below.
Fewer Dimensions in the target Metric
Let’s look at an example where our target Metric has fewer Dimensions than the source. Source Metric Country x Month has Dimensions of Month and Country. I have created two different target Metrics. Target Metric Months has just the Month Dimension. Target Metric Country has just the Country Dimension. Both target Metrics are using a simple reference formula to our source Metric. In both cases, because the Dimensions are not the same as the source, the target Metrics sums the values. This has a similar effect to using Remove modifier set to SUM.
More Dimensions in the target Metric
Let’s look at an example where our target Metric has more Dimensions than the source. Source Metric Country has just a Country Dimension. Target Metric Country x Month has the Country Dimension but also has Month. Target Metric Country x Month uses a simple reference formula. The result is that the value of each country is just added consistently to every month. This has a similar effect to using Add Modifier set to constant.
Different Dimensions
Let's look at an example where our target Metric has a different Dimension to the source. Source Metric Country x Month has Dimensions of Month and Country. Target Metric Products x Month has the Month Dimension but instead of Countries, it uses Products. Target Metric Product x Month uses a simple reference formula. The result is that the value of each country is summed every month and then added consistently to each product. This has a similar effect to using Remove modifier set to SUM and Add Modifier set to constant.
🎓 Pigment Academy
To learn more about source to target mapping, check out the Interactive Source to Target Mapping Tool and the Modifiers for Misalignment modules in our Academy.