Top tips for modeling in Pigment - Part 1: Getting Started

The purpose of this article is to introduce top tips for model building in Pigment. 🎨

Table of contents

1. Build auditable applications 🔍

There’s nothing worse than going into a model that is completely incoherent. It can transform the most simple changes into an absolute nightmare. It’s better practice to break up long formulas across several Metrics than to have one formula that stretches over dozens of lines. This will help keep your Metrics clean, understandable, and maintainable. 

Blocks should also be clearly labeled and abbreviations should be avoided. For more complicated formulas, you should take advantage of the notes feature and paragraphing. 

As you’re building an application you should frequently ask yourself “Can a modeler easily understand the functionality I’ve just built?” If they can’t, you should aim to take advantage of the techniques above to reduce the complexity.

​​​​​​​

2. Design your application with sustainability in mind🍏 No Hard Coding! 🛑

Consider the future and what might change. Will it always be done in this way? What could change? What are the exceptions?

No Hard Coding!

Hard coding is when you input a fixed data value in the formula instead of making reference to another Metric. It can lead to a number of issues down the line and goes against the auditability and sustainability points above.

Consider the examples below:

Example 1: Staff Bonuses 💰
In a Payroll Planning Application, a modeler writes the following formula to calculate bonuses in a Metric by month. 

'Bonus per Year (EUR)'[by constant: Month.Year][Month.'Month of Year'="December"]

What’s the issue with the formula above?
Since the payment schedule is hard coded to December, end-users won’t be able to change the month bonuses are paid. They also won't be able to do ‘What If’ analysis on the month of bonus payment. If the business wants to change this parameter in the future, a modeler will have to dig through the Metrics in the application to find and edit the formula containing the hard coding. As you can imagine, this can be an onerous process in a complex model that was made by a different modeler.

Example 2: Variable Software Expense forecast 👩‍💻

In an FP&A Application, there’s a standard forecast process for accounts, however, the Variable Software Expense account gets its forecast from a separate Metric. A modeler writes the following formula:

IF( 'A3 Account' = "Variable Software", 'VAR03 Variable Software Forecast', 'VAR 02 Variable Expenses Forecast')

Can you identify the problem with the formula?
The account name has been hard coded. In the future, if the business changes the names in the account hierarchy, the formula would break (e.g. if Variable Software was renamed to Variable Softwares)

Solution 💡

Reference an Input Metric. These can be published on admin or end-user Boards. This would change the Variable Software formula to:

IF( 'A3 Account' = 'ASP06 Variable Software Account', 'VAR03 Variable Software Forecast', 'VAR 02 Variable Expenses Forecast')
 

And the Input Metric [No Dimensions] would look like this:

3. Use a clear and consistent naming convention 📏

It’s generally good practice to have a clear and consistent naming convention. This will signpost functionality in the model, increase auditability and make it easier to write formulas. Consider implementing prefixes to Metrics (for example have a 5 character prefix, 3 letters followed by 2 numbers (e.g. REV01). Alternatively, you could prefix all metrics in a table, for example, your Employee Staff Costs Table could consist of Salary, Bonus and Total Cost. This would be set up as:  EMP_Staff Costs (table), EMP_Salary, EMP_Bonus and EMP_Total Cost. 

Having a consistent naming convention and grouping will allow modelers to anticipate where to find specific functionality when they open a new application.

4. Load data using a unique identifier 🔢

Always aim to load data with a unique identifier. If we build on the “Variance Software” name change example above, if we haven’t loaded the data with a unique identifier, the name change would lead to 2 Variable Software accounts (Variable Software and Variable Softwares). Ideally for each dimension within a transaction list that is subject to change, you should load using their ID.

You should also load data to transaction lists using a unique ID for each row, otherwise you won’t be able to update historical records.

​​​​​​​5. Set switchover period by version 🆚

The native switchover functionality provides a switchover period between actuals and forecast. This does not work for applications that need to vary their switchover date by more than one version.

Consider the example below:

As we only input one switchover date, Budget, Forecast 1 and Live Forecast have to have the same switchover period. 

For Applications with switchover dates that vary by version, we have to set up different functionality.

First, we create an Input Metric by version [Version]. The format of the Metric is dimension - Month. 

Then we create the following system Metric [Version & Month]. This is used in the formula for any Metric down the line that we want to incorporate the switchover logic in.

Formula: IF( Versions = "Actual", true , if(Month.'Start Date' < Month.'Start Date'[by: 'Input: Version Switchover - Good'], true, false))

Finally, in the Metrics where we want to apply the switchover logic we need to do the following:

If('SYS01 Switchover by Version and Month', ‘Source Metric’[select: Versions="Actual"], 'Source Metric')

An example output is shown below.

Do you have any additional tips for modelers? >» Let me know in the comments below.