How to manage mapping Data Entry

Center of Excellence

1. Abstract

In many cases, mappings are used to control processes. Mostly, these are mappings between entities that cannot be implemented in the standard relationships (hierarchies) for various reasons (e.g. they are not n:1 relationships, or the hierarchical trees would become too large to be useful from a performance point of view).

Those Mappings are usually maintained through appropriate screens by administrator users. And, there are different ways to implement the required processes to achieve the most effective maintenance screens. There will always be a tradeoff between usability and complexity.

2. Context

The easiest way to populate a mapping cube is to provide a matrix in a DataView where the admin would fIag the combinations to use. That means the least development effort but can become unergonomic for the user if the matrix has many columns and rows.

When we are talking about 1:n relationships—those could be maintained via a DataView with a drop-down on the input field. This means a little bit more development effort, but it can be more comfortable for the user since it can be enhanced with entity members for the input.

In this article, we will look at which solution might be more or less suitable for which use cases.

3. Content

We will be discussing different use cases as examples for the different approaches in order to provide some “real life” illustrations.

3.1 Simple matrix—a 1:1 relationship with just flags

Scenario: In a planning application there is the need to flag months that shall be open for the forecast and the according mapping shall be used to lock data entry on the other months.

Solution: We would need a mapping cube of type integer with the dimension “Month”. This cube will be presented to the administrator in a data entry-enabled DataView where they could flag the “open” months.

You have two options for displaying the DataView—with standard input (integer figures) or with checkboxes to tick the months to be open.

Standard	Checkbox

The standard option would accept any integer value and is, therefore, more flexible (you could, for example, make use of the “Lock & Spread” quite effectively), whereas the second option would only allow for 0 or 1 to be input.

3.2 Defining relationships with drop-downs

The scenario shall be similar to 3.1 but with the enhancement that we are not just flagging open months but rather relating them to a phase of the forecast, like previous, open and future.

The solution for this requires a cube with two dimensions, the month and the phase. The cube again is displayed in a data entry-enabled DataView. The phases could be as follows:

Option 1: if you put the phase in the columns of the DataView, it would look like this:

This would, although it is easy to use, not be coherent with the phase definition, which could cause trouble in understanding. You could, on the other hand, use this cube very well for “select entity based on” if the phase is selected prior.

In order to use this input scheme to populate the “open months” cube you would need a dataflow which we will elaborate on a little later.

Option 2 uses a cube of the same structure as 3.1 but suggested values derived from the entity “FC Phase”.

This will be quite ergonomic for the user.

Since the cube only accepts integer values, you cannot display the corresponding text as well. That would make it necessary to show a legend, too.

3.3 Defining relationships with drop downs using a text cube

3.3.1 Standard DataView with suggested values for drop-down

The solution that offers the most convenience for the user is a text cube for input. Of course, we have to limit the input possibilities, because the administrator should not enter anything that cannot be interpreted by the system. Accordingly, this is also the solution that requires the most effort for development and maintenance.

The text cube “FC Months Option 3” is structured by month only, the data entry is restricted to the three values of the FC phase.

Restriction	Result

This could be used with “Lock & Spread” quite well.

3.3.2 SmartImport with suggested values for drop-down

The SmartImport is a relatively new object in Board’s infrastructure and allows to import large amounts of data either manually or by uploading a file with the application of validation rules. This is a second possibility to input data into the mapping cube. In this example, we will use the same cube as before.

The setup is quite similar to the data entry in the text cube using a standard DataView. It requires you to define rows and columns and the mapping between input data and the data model. We set up a SmartImport with 2 columns and 13 rows (1 row is reserved for the header).

The Mapping looks like this:

The first column is the Month, and the second takes the text for the according phase (this one is mapped to the cube). The suggested values (in this case for the phase and the month) are defined like this:

Month	Phase

You will then be presented with a view that allows you to input data directly or via drop-down.

The disadvantage of this solution is that you have to populate the month column too. The advantage, however, is that you can import really large amounts of data from files.

But be aware that you will need a “check” DataView because any data input into the SmartImport Object will vanish once the save has been completed.

3.3.3 Smart Import object for mapping Entities with a large number of members

There are cases where the Smart Import Object comes in really handy. Consider a situation where you have to map Cost Centres to accounts—hundreds of accounts and hundreds of Cost Centres in your database, each Cost Centre could be mapped to a multiple number of accounts.

Another example would be mapping accounts to certain line items in a P&L structure, or, like in this screenshot, mapping movements to account groups (from a BFC application).

If you would only provide a Data Entry screen to the user for mapping purposes, that screen could become quite overwhelming and hardly manageable with lots of rows and columns. That can even be the case if the number of Cost Centres and/or accounts to map could be reduced by selecting specific groups.

This is certainly the use case par excellence for the Smart Import object. It would even allow for adding new Cost Centres or accounts to the database.

You would design the input screen similarly to the description in section 3.3.2 above, here providing two columns for the entity codes of account and Cost Centre.

The user could add rows to the Smart Import manually or copy & paste them from a spreadsheet or by uploading a file.

You would, however, still need a check screen which could of course be data entry enabled.

3.4 Populating the Locking cube

Keep in mind that any block you want to use for locking cells at the DataView level needs to aggregate to 0 or not 0 when screen selections are applied. Currently, the system does not allow any other value for comparison.

You could, of course, use an algorithm to calculate the corresponding 0/not 0 value but that would have to be applied in each DataView you will design for input and could result in an even higher development and maintenance effort.

The input method described in 3.1 does not require any additional development, the other options however do. We will need to populate the cube “FC Months” with 1 or 0 according to the month being open or not for forecasting.

3.4.1 From dedicated input cube (2 dimensions) to locking cube by dataflow

Reconsider the input matrix with the dedicated cube on Month and FC Phase in Figure 3: Standard input for FC Months:

Source	Target

You will need to translate the FC Phase to a 1 or 0 in the locking cube (target).

Option 1 is using a dataflow triggered by an action on the screen. The steps to be performed are the following:

1. Clearing the cube for the selected time frame (2023), the dataflow inherits the screen selection.
2. Selecting the “Open” phase
3. Selecting the months to be set to open by using a selection based on cube
4. Flow a “1” into the target

Option 2 would be an extract and reload which we’ll explain deeper in the next section for the drop-down input.

3.4.2 From drop-down input on text cube to locking cube by extract/reload

This method allows for a bit more flexibility in comparing text values since it makes use of the ETL Excel-like syntax.

Source	Target

The steps to perform the desired actions are as follows:

1. Extracting the input cube to a file on the disk
2. Reading the input file into the target, looking for “Open” only (use current selection)

Procedure

The procedure triggered by an action on the screen looks quite similar but uses extract/reload steps instead of selections and a dataflow.

Data Reader

The Data Reader checks for the text “Open” in the file and just sets the corresponding months to 1, and any other month to 0.

Since the Smart Import worked with the text cube too we could use the same procedure there, as well.

4. Conclusion

Data entry in collaboration with Dataflows is a capability that sets Board apart from many other vendors. In addition, the screens for data entry can be designed very flexibly. Smart Import objects should rather be used with large amounts of data from files or copying from a spreadsheet because direct data entry can become cumbersome with this object.

Depending on how much flexibility and comfort an implementation should offer, it always makes sense to think about which solution can achieve the best effort/benefit ratio. More effort means more time to be spent implementing and higher costs of the implementation and you would not want to use a sledgehammer to crack a nut.

So, try to keep it as simple as possible but as comfortable as necessary and always keep an eye on maintainability and required documentation.

Center of Excellence

Thanks @Helmut Heimann for the insights on this article!