There are a number of factors that can go into formula performance. Using the most performant functions is the easiest to apply and utilize. This article will cover the faster functions plus other tips for more performant formulas and in some cases just making formulas easier to follow/understand.
IF vs IFF and DIV vs DIVF
The key difference is that the functions with the extra “F” perform faster by doing less work. It is recommended to always use the IFF and DIVF functions. A good practice is to replace any IF and DIV functions you find in your formulas with the faster variants as you happen across them.
How each works:
IF: This function will evaluate both the true and false equations, then it will evaluate the test condition for true or false. It returns the correct result and throws out the unneeded result.
IFF: This function first evaluates the condition of true or false. It then evaluates just the needed calculation based and ignores the unneeded. The focused nature of the function makes it more performant.
DIV: This function evaluates the numerator and the denominator. It then checks if the denominator is 0, if yes it throws out all the work and returns 0, else it returns the result.
DIVF: This function evaluates the denominator and checks if the denominator is 0, if yes it returns 0, else it calculates the numerator and completes the division.
Replace nested IFF statements with Switch statement where you can
If you have a big nested IFF statement that checks just one variable for a range of values, a Switch statement is usually easier to read and more performant. This example is an oversimplification of wanting to do something different depending on how long an employee has been with the organization, in this example we will just simply return the number of years.
Less ideal IFF statement:
IFF(trunc(divf((this - ROW.StartDate),365)) = 1, 1, IFF(ttrunc(divf((this - ROW.StartDate),365))=2,2, IFF(trunc(divf((this - ROW.StartDate),365))=3,3, IFF(trunc(divf((this - ROW.StartDate),365))=4,4, …
trunc(divf((this - ROW.StartDate),365)),
Avoid unnecessarily accounts in Modeled sheets
Modeled sheet accounts evaluate for every row in the modeled sheet and many calculations are required, but it is also quite common to find a sheet setup doing extra work. A common example of this could be Total Taxes on a personnel sheet. For example let’s say there is local taxes, community taxes, and government taxes for every employee. Then a Total Taxes account to add the three together. We have no need to see the total taxes for any one person but we do have a GL account for total taxes that we will point to the modeled sheet total taxes.
It would actually be more performant to setup the GL account to do the totaling by making it equal to each of the 3 modeled sheet tax account rather than pointing it to the modeled sheet total taxes account. While not dramatically faster, this is the type of thing that compounds as I may later add another modeled sheet account for Salary plus Taxes…
It is also common to add accounts for testing or during the initial design that are not surfaced anywhere, if you identify accounts like that you should consider removing them (use view dependencies to make sure they are not referenced elsewhere).
Avoid Modeled sheet to Modeled sheet references
This is not a blanket statement for always performing better, but is something to test if you encounter performance under this condition.
An example of this could be one team that plans the Units sold by Product, Customer, and Channel in a modeled sheet. Another team that builds up a production plan by Product in a modeled sheet that references the Units sold from the first sheet. The production plan could involve several rows that need the same Unit plan data. It would actually be more performant in this case to create a cube sheet that has the Product dimension and an account linked to the Unit plan. Then the 2nd modeled sheet would reference the cube sheet instead of the other modeled sheet to improve performance.
Avoid referencing rollups in metric accounts
Formulas should not make reference to a rollup level, for example [Level = Top Level(+)]. The better alternative is to create an assumption account that is set to the value at the top level, then use the assumption account in the metric formula.
Reserve metric accounts for ratios/division
Metric accounts are designed for something divided by something else and recalculate at every level. If all you need is non division math, use a custom account.