Pigment is an extremely flexible and powerful tool. It’s very easy to create new metrics, edit existing structures, and amend formulas. This can be a double edged sword as newer modelers break a working model as they are not aware of the impact. of their changes.
This article covers important best practices to ensure you won’t delete information that shouldn’t be deleted, change what shouldn’t be changed, and ultimately avoid unwanted outcomes.
Be aware of things that can be Permanently deleted
Some deletion in Pigment is permanent therefore we need to take extra care when deleting these structures
Deletion of the following is permanent and cannot be reversed:
- Saved Views
- All blocks
- Transaction list and dimension properties
- Transaction list and dimension items
- Boards, and Widgets on boards
Before deleting any of the above, it is important to double-check block usage and the scope of the deletion. If you’re not confident, see if there’s a way to do it in a parallel block or duplicate the block to have a backup of the data for the short term.
Use the Formula Playground as much as possible
Not only to create metrics, but also when you need to modify existing ones.
If you’re not 100% sure of the formula and the impact that your changes could have in your workspace, then you should use the Formula Playground or a duplicated metric.
Duplicate Metrics to work in an isolated environment
It is a good habit to duplicate metrics to work in an isolated environment. This way, you have no risk of impacting the rest of the application if you haven’t followed the right steps or cant figure out the correct dimensionality and formulas. This is particularly important if you are not 100% confident with Pigment just yet, or not 100% sure of “How to” do it.
For example, let’s say you have a P&L bv Month, P&L Category and Department. Let’s say you want to add a new Currency selector to that P&L. Instead of editing existing structures, it’s better to create an alternative path to calculate the P&L at this new level of granularity. This way we can build in an isolated environment, create numerous metrics with the new dimensions, and figure out the correct formulas. Once we are happy the final resultis calculating correctly, we can amend the original metrics.
Consider Block Usages before making changes
When you are making a change to a metric or property, its important to understand other metrics that can be impacted. In Metric’s setting, you’ll see there is Access & Usages. Here you can look at all of the other blocks that use this Metric. You can also open the dependency diagram which will give you a nice visualization of all metrics that are a product of the metric.
Sometimes changing the structure may have no impact on the block you’re working on (e.g. the formula is correct, there is no raw data in it), it will impact the calculations on the block that depend on it.
Let’s say you add the Country dimension to a Revenue metric. If the Gross Margin Metric uses the Revenue, you should adjust the formula or its structure to account for the changes. This can mean changing the formula to do a Revenue [REMOVE SUM: Country] (which will bring the formula back to its original calculation).
Be aware that if you add the dimension to Gross Margin, you may need to do the changes on the blocks that depend on Gross Margin, e.g. EBITDA.
It is important to fully scope out the impact of a change before you have made the change in the model.
Be careful when changing the structure of a Metric
Under Metric settings, there is a tab called Structure. This setting is what controls what dimensions that are part of the Metric. If the metric has data values (input/import or overrides), you will lose your data if the structure is changed.
If you need keep the original data within a metric, please see this article.
Changing the data type in a Property or Metric
Changing the data type will cause you to lose the data (inputs/import & overrides) in a dimension Property or Metric. If block is formula driven, then you will most likely have to amend the formula.
To keep the original data within a dimension property, it is easier to create a new property with the correct data type. In the new property you should write a formula based on the original metric.Once complete, you can delete the formula, and the data will remain (please note, any usages of the original property would need to be re-pointed to the new property).
If you need keep the original data within a metric, please see this article.
An example could be: You have a property type Date and you want to have the Month dimension instead… Changing the property type from Date to Month isn’t the right choice. Instead what you might want to do is:
1. Insert Right -> New Property type Month
2. Click in the new Property and update the Formula with TIMEDIM(List.’Date’ , Month)
If you need to make other data type conversions, these functions can help.
| 
|
Removing metrics from a table removes them from ALL the views
Watch out - If you remove a Metric from a Table it will disappear from all the saved views! You can choose to hide it instead.
Take Snapshots regularly
Snapshots help keep a record of the state of your model was before you made changes, including raw data, calculated values, but also views, boards, metadata and formulas.It is recommended to take a snapshot every month per application, and whenever you finalise a planning Version (e.g. Q2 Reforecast).
There is no downside to taking snapshots.
You can also use it to re-download the data that you previously had in a block to import in your recently-changed application where you might have lost the data.
Be aware they are not reversible so you cannot go back and re-activate a snapshot. It will remain read-only.
Create a specific role for your new Pigmenters
If you don’t want your newly Pigmenters to break anything in Pigment, create a specific Role in Pigment.
With Permissions & Access rights, you can define a role for each user. Here is another guide for more tips on setting up security:
We are constantly evolving our best practices, feel free to add some of your own below!