Pigment is a business modeling platform based on the concept of multi dimensionality.
You use multi dimensionality every day at work: you plan by periods, by country, by department, by cost nature, etc. Pigment makes it easy to build data & calculations using the dimensions you need to describe your business. Let’s review how to manipulate Metrics of different Dimensions in Pigment.
How should you think about Multi Dimensionality?
I always think about multi dimensionality like this: What are the dimensions of my source and what do I want as a result?
- If they both have the same Dimension there’s nothing to do
(example: Bonus Amount = Annual Salary by Employee x Bonus Rate by Employee)
- If the result I want has more/different Dimensions than the source, then how do I map/allocate it?
(example: Tax Rate by Country → Tax Amount by Employee)
- If they have fewer Dimensions, then how do I sum/remove the one I no longer need?
(Spend by Vendors → Total Spend)
Below, you’ll find a detailed list of real-world use cases and how to manage them in Pigment.
Case 0: No modification required
In this example, both the source and target Metrics have the same Dimensions, Employee & Month. Pigment can bring over the data of the source Metric to the target Metric without any transformation.
Case 1: Summing/Aggregating on Dimension(s)
Naturally, when modeling business processes we aggregate all the time. Think of your general ledger: you are summing every line amount into its own country, cost center and month.
In Pigment, you’ll do that with a BY dimensional modifier, with this formula:
'GL Load'.AmountmBY SUM: 'GL Load'.Country, 'GL Load'.'Cost Center', 'GL Load'.Account, 'GL Load'.Month]
You’ve now met your first Dimension modifier, BY. It’s always used within the square brackets s ] and those are always AFTER a Metric, Property or value.
Refer to Pigment doc where this is explained in detail.
Case 2A: Aggregate to a parent
Now that you have calculated the tax at the employee level, you’d also like to aggregate that amount by Country and Year:
The formula in the Tax by Country Metric is going to be:
'Tax Amount'oBY SUM: Employee.Country, Month.Year]
Note
The SUM is an operator of the BY dimensional modifier. In this case, it is implicit. You could write the formula without it, Pigment would add it automatically.
Case 2B: Allocating/Mapping to another Dimension
Let’s take the following example: you want to calculate the tax by employees depending on their pay:
- you have Metric 1, which is the salary of an employee by employee and by month
- you have Metric 2, which is the tax rate by country
For Metric 3 (which is by employee and by month), what formula will you need to calculate the tax by employees depending on their pay?
Employees are “attached” to countries. And countries exist as a Property of the Employee Dimension.
The Employee Dimension is present in both the source Metric (Metric 1) and our target Metric (Metric 3).
So to calculate tax by employees depending on their pay, you could use the following formula:
'Tax rate by Country'eby: Employee.Country] * Salary
Pigment is going to allocate the tax rate of countries to each employee depending on their Country: this formula tells Pigment how to map Employee & Country. And then it multiplies it with the Salary.
Note
You can do this to add as many Dimensions as you need.
For example, if the tax rate was also by YEAR, your formula would be:
'Tax rate by Country' by: Employee.Country, Month.year] * Salary
Expert Level: What Pigment is actually doing is lBY CONSTANT: Employee.Country].
CONSTANT is an operator of the dimensional modifier. CONSTANT is an allocation operator vs SUM which is an aggregation operator.
Refer to Pigment doc for all operators.
Case 3A: Add a Dimension
Very often you will want to add one Dimension to the data you already have. The example we will use for this scenario will be the switchover date:
- We have a Metric called Actuals defined by account, country, cost center and month Dimensions
- We want to calculate a new Metric by account, country, cost center, month and Versions Dimensions so you can compare Actuals vs Budget vs Forecast
We will need to tell Pigment how to allocate data from the source Metric to the target Metric.
ActualsaADD CONSTANT: Versions]
Pigment is going to add the Dimension Versions to the Metric Actuals and copy the same or constant value to each Versions Dimension, that’s what the CONSTANT does.
(You could also use SPLIT which would divide the value by the number of Versions).
Because we are talking Switchover, the real formula would be:
IF('switchover date'>=Month.'Start Date', ActualsoADD CONSTANT: Versions])
Switchover Date (being a Metric of type date) indicates which period is in the “past”. This effectively adds actuals in the past periods, which is different for each version.
Case 3B: Remove a Dimension
In many situations, you’ll want to remove a Dimension from a Metric. For example below, to plan spend by account, we’ve added a line Dimension (1 to 5) to allow users to provide details. That Dimension isn’t necessary when we roll up the costs into the P&L, so you’ll need to remove it.
How would you do that?
AmountHREMOVE SUM: Lines]
REMOVE is an aggregation modifier, by default it sums all the values of the removed Dimension, so you do not need to write SUM.
For detailed insights on multidimensional modeling and video discussions on typical use cases and common pitfalls of each Pigment modifier by Solution Architect, explore the Write & Test Formulas Learning Path in the Academy.
, in the last example provided, can we say that