Dummy entity elements for budgeting purposes

Center of Excellence · December 2023

1. Abstract

In planning processes, sometimes it is necessary to manage new occurrences that don’t exist in real life yet, which means creating provisional elements in the master data that will serve only for planning purposes.

2. Context

Creating provisional elements in master data for planning purposes means creating new codes that will have only planning data, it could be new products, new organizations, new employee IDs … ETC.

When the planning period becomes the current period, actual data that concerns the provisional codes will arrive on the actual real codes, which duplicates the number of new elements.

The challenge here is the management of 3 main elements :

To avoid polluting the master data with too many provisional codes
To avoid exceeding the max item number of elements
To reconcile with actuals for comparison

There are several points to consider before modelling the solution that convers the need to create provisional codes:

Impacted entities: it is important to list all the entities concerned about creating the new elements, because the management will be operated on each entity separately. For instance, focusing on the entity “Department” being the leaf level of the organizational axis. In the planning process, sometimes we need to create new departments with provisional codes, before opening new departments (or not)
Number of new elements per year: the creation of new elements per year must be administered, for this example we consider creating 10 to 20 elements per year.
Number of cubes impacted: the solution requires you to manage each impacted cube individually, it is important to list all the cubes that have planning data on the provisional codes.

3. Content

3.1 Reminder

Not creating provisional codes: The first option is to not create any provisional codes at all, if possible, from a data governance point of view, we should always try to create the correct codes in Board by upload from a valid source and avoid having master data that is specific to Board without any link to the source data. Of course, this is the options that we should push, because sometimes the need to create provisional codes is just to accelerate the process, for example if master data is managed by an MDM tool and injected directly in Board, the entire process of creating new codes should be in that MDM tool and not in Board.
Updating the codes directly in the entity: Board offers the possibility to update codes directly at the entity, this option is not a Best Practice and must be used with extreme caution:
- This operation must be done manually and element by element
- It requires the maintenance mode without any running activity in the application
- It must be done before the new codes are loaded in Board, otherwise it will no longer be possible to do it, this is from a data governance point of view, we must ensure that the codes are manually updated before any loading of new codes

3.2 Transforming and mapping

We have the entity “Department”

A Data Entry Budget cube that is structed by Department, Month, … and other dimensions.

For the budget process, the need is to create new departments, but it must be on provisional codes because the correct codes are not known yet.

After the budget process (year N+1), the demand is to reconcile budget data with actual data.

Solution :

We can create the provisional codes directly in the entity Department.

Then we will need the following elements:

Another Entity called “Department Staging” that is identical to the entity Department
A mapping 1:1 between the provisional codes and the real codes
A mapping 1:1 between the 2 entities where code Department = code Department-Staging

Step 1: Fill the Department-Staging entity

Step 2: Fill the 2 mapping cubes

Cube: Mapping Mirror that has code Department = Code Department-Staging
Cube: Mapping Prov-Real Codes that has a mapping between the provisional and the real codes

Step 3: Create a temporary cube (Temp 1)1 that has the same structure as the data entry cube + Department-Staging

Step 4: Get rid of the “Department” entity. Create a temporary cube (Temp 2) that has the same structure as Temp 1 but without the entity Department

Step 5: Create a temporary cube (Temp 3) that has the same structure as Temp 1. We will add the Department entity using the Prov-Real codes mapping

Step 6: Copy the cube Temp 3 in the initial data entry cube

Step 7: Manage the provisional codes

Now that we have the data on the correct codes, it is mandatory to manage the inactive elements, the goal is to ensure that all the upcoming treatments (data entry, dataflows, selections, … etc.) will be only on the new correct codes. For that matter, depending on the use case, we can use several possibilities to prevent any input on the provisional codes, such as:

Using a parent entity with a flag Active / Inactive
Using the cube visibility features to either lock or hide the provisional codes
Using the select based on cube on the security profile to exclude those elements

Step 8: Deleting the provisional codes

All the previous steps (1 to 7) can be part of a semi-automatic process that a key user can frequently launch.

Deleting the provisional codes cannot be frequent and it requires a maintenance activity, it is possible to organize this activity at the end of the budget process, or before the beginning of the next one for example, the idea is to do it in a period where there isn’t much active users, for more details on to manage that, please refer to the dedicated article “How to Remove Obsolete Entity Members”.

3.3 Create a parent entity that regroups the provisional codes and the real codes

The solution consists of creating a parent entity that contains only the real codes and that regroups the provisional and real code under the real code.

By applying this solution, we must consider the following points:

Ensure that no more input will be done on the provisional code once the real codes are loaded, we can manage that either by locking the provisional codes or excluding them from data entry screens.
Use the parent entity for the reporting purposes only
When using dataflows, make sure to not have data in both provisional and real codes on the same perimeter, otherwise the reporting by the parent entity will be false.

4. Conclusion

Obviously the “Transforming & Mapping” solution is the most complete one which covers the goals mentioned previously :

To avoid polluting the master data with too many provisional codes
To avoid exceeding the max item number of elements
To reconcile with actuals for comparison

The second solution can be considered as a “quick solution” for simple purposes such as reporting only.

Here’s a comparison between the 2 possibilities:

Center of Excellence · December 2023

Thanks @Abdelhadi Babaali for the insights on this article!