Eliminating Unnecessary Zero Values

Zeros can actually be stored and are different from NULL values. While occasionally useful, unnecessary zero values can inflate data model versions and hinder efficiency. Removing these zero values, reduces the planning model's overall size, making it easier to manage and making it efficient in terms of storage and processing resources.

Let's start with three common scenarios where you might find unnecessary zeros in your planning model and recommendations for eliminating them.

1. Where there is a large number of sparsely booked measures.

   Recommendation: Consider redesigning the model and store values in more generic measures or booking on additional dimension members.

2. Where there are advanced formulas for which you have to reset some values to zero prior to the new calculation to take place.

   Recommendation: Consider assigning a NULL value to the data region you wish to erase, and not assigning zeros to it.

3. Where data actions have unintentionally caused overly large versions by adding unnecessary zero values to inefficient data. Pruning these zero values can help to streamline the model and improve performance.

   Recommendation: Prune out the superfluous zero values.

From time to time, you may delete zeros from the planning database by using native system functions in the modeler to delete facts. In this lesson, we'll show you how to do this using a data action for the following scenarios:

1. Delete zero value records from a public version.
2. Delete values from a specific account.
3. Delete zero value records from an account for a specific time period.

In this story, you have a version that has a lot of zero values on leaf accounts and time dimension members.

Scenario 1: You want to delete zero value records from a public version, so you create a data action with an advanced formulas step. It includes the following code:

123
IF RESULTLOOKUP() = 0 THEN // Condition to check data is valid or not
DELETE() // once deleted cannot revert back if version is published
ENDIF

Result: When you run the data action, you can see that all zeros have been removed.

Scenario 2: You want to delete zero value records from a specific account, so you create a data action with an advanced formulas step to delete zero values for Account 10. It includes the following code:

12345
MEMBERSET[d/Account]="10" // Define scope, if required
 
IF RESULTLOOKUP() = 0 THEN // Condition to check data is valid or not
    DELETE() // once deleted cannot revert back if version is published
ENDIF

Result: When you run the data action, you can see that the zeros are only removed for Account 10.

Scenario 3: You want to delete zero value records from an account for a specific time period, so you create a data action with an advanced formulas step to delete zero values for Account 10 for April, May, and June 2025. It includes the following code:

123456
MEMBERSET[d/Account]="10" // Define scope, if required
MEMBERSET [d/Date] = ("202504", "202505", "202506")
 
IF RESULTLOOKUP() = 0 THEN // Condition to check data is valid or not
    DELETE() // once deleted cannot revert back if version is published
ENDIF

Result: When you run the data action, you can see that the zeros are only removed for Account 10 for April, May, and June 2025.