Source to target dimensionality and when to use modifiers

Understanding dimensionality is critical when working with formulas.  Dimensionality differences could lead to the wrong values or not being able to execute a formula. This article discusses the concept of Source to Target mapping in Pigment, identifying the impact when dimensionality differs 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 will have their own dimensionality, or dimensions used to create their structure.  Source to target mapping is aligning the dimensionality of your source into the dimensionality of your target.

Why does it matter?

Dimensionality has a huge impact in how your formulas work because it determines how the values are calculated or distributed.  If the dimensionality from source to target is different, then modifiers or functions can be used to pull the desired value.  Without using modifiers or functions, Pigment will implicitly apply modifiers to correct source to target metrics that do not have the same dimensionality.  For example, if you have a Country dimension in a source metric but not in your target metric, and you reference this data, you will get the sum of all the values for each country.  That is a similar functionality to how the Remove modifier works. 

Lets look at how modifiers work at a high level and then the impact of dimensionality mismatches. 

How to adjust dimensionality using modifiers

Modifiers change the dimensionality 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 dimensionality. 

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 less dimensions than the target, this modifier can be used.  For example, if your source didn’t have the Month dimension but your target did, 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 will remove a dimension and then perform an aggregation depending on how its defined. For example, if your source metric had a Product dimension but your target didn’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 using the calendar dimensions, 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"]

Try it

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. 

1. Click enter once you enter your source metric to view its current dimensionality. 
2. Enter the Modifier and hit enter to see the dimensionality change
3. Adjust the aggregator or allocator to see the data change.

 

What happens when you don’t use modifiers?

If the dimensionality in your source and target metrics are different, Pigment will adjust the data depending on how they are different. Check out the examples below.

Less Dimensions in Target Metric

Let’s look at an example where our target Metric has less dimensions than the source.   Source Metric Country x Month has its dimensionality set to Months and Countries. 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 the a simple reference formula to our source Metric. In both cases, because the dimensionality isn’t the same as the source, the target metrics will sum the values. This is the same action as if you used Remove modifier set to SUM.

More Dimensions in Target Metric 

Let’s look at an example where our target Metric has more dimensions than the source.   Source Metric Country has its dimensionality of just Countries.  Target Metric Country X Months has the Country dimension but also has Months. 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 is the similar effect of using an Add Modifier set to constant.

Different Dimensions 

Let's look at an example where our target Metric has a different dimension than the source.   Source Metric Country x Month has its dimensionality of Months and Countries.  Target Metric Products X Months 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 is the similar effect of using the Remove modifier set to SUM and Add Modifier set to constant.