Multidimensional Mapping is a tool that allows administrators to map data to an intersection by referencing dimensions other than the one being mapped. This can be helpful when administrators face more stringent data load requirements. This could be due to a GL string identifier in the source system, or the need to abide by certain requirements for system calcs to run properly. In this example, I will be focusing on the GL string scenario, but the concept can be applied in a variety of use cases.
Let’s begin by taking a look at an example involving a GL string that is composed of an entity member, followed by an account member in the following form XXXX.XXXXX. Once again, in your case the GL string may use different dimensions, or you might be taking a variation of this issue, but the same rules apply.
Before we go any further, it is important to highlight that this is meant to be used as needed, but best practice would be to clean up the hierarchy on the front end. A cluttered hierarchy is something that will add another layer of unnecessary complexity, and thereby decrease overall performance. This solution only allows the administrator to circumnavigate the issue and map the data to the appropriate dimensions. It is still highly recommended you clean up your hierarchies, and minimize the number of multidimensional mappings you will need to set up.
Also, remember that the accounts you are mapping will need to have already been created in the outline. These are likely not accounts that were brought over in the initial metadata import. It is more than likely that you will need to create these members, and you will also need to determine the nomenclature that will be used, as these will not look like the source system’s GL String.
The first step will be to manipulate our GL string into the various dimensions of which is it composed. In our example, this is the entity and account dimensions. To do so, we will leverage the Text to Columns function found in the Data tab in Excel. First, let’s highlight the column we want to separate into the various dimensions, and select Text to Columns.
A small pop up screen should appear. You can either set it to a fixed width or delimited. If the GL string is always the same length, fixed width can be an effective method of identifying where our entity dimension ends, and where the account dimension begins. In most cases, GL strings will use some method of delimitation, so we will showcase this in our example.
After delimited has been selected, the following screen allows us to determine what character is being used as the separator. In our case, we are using a period. We can select multiple characters (even tabs and spaces) as separators, and can treat consecutive delimiters as one singular delimitation. For our purposes, we can check the other box, and enter the period value.
Finally, the last screen allows us to determine the data format of the new columns we are creating. It is recommended that you select text to avoid the loss of trailing zeros, or any other changes to our values as we manipulate them. Once you have completed this and your GL string has been divided across your various columns, you can copy and paste them separately in their respective columns in the flat file you are trying to load.
Next, we will go to Data Management, and select Data Load Mappings:
From here we select the tab marked Multi Dimension:
Next, we select Add, or the green plus sign to add a new mapping. We should be taken to a screen that looks like this:
From here we can input a rule name. For now I will just use the name of the member we are mapping it to in order to guarantee uniqueness. In such a case my rule name and target value would be one and the same. For this example, the GL String 3030.24125 will be mapped to A_24125_REV. In other words, when the account 24125 intersects with the department 3030, the value should be mapped to a member by the name of A_24125_REV. We could then go back and map other departments to other accounts.
In any case, once A_24125_REV has been input into the rule name and target value, select the green plus sign to add a dimension. Once it has populated select the account dimension from the drop-down menu.
Next, we input the value of the source account, or in this case 24125 (remember that the GL String has been broken into two columns to allow for this).
Next, we select the green plus sign and select the other dimension in the GL String. In our case, it would be entity, and for value we would simply input the source value for departments. For this example, the value field would be 3030.
Once you are done, select okay to add the mapping. You can go back and edit the mapping using the pencil sign just like any other mapping, and you can repeat the process for all the accounts that suffer from this issue. Otherwise, the rest of the process is the same. You would load the data the same way, and run the same business rules you would have otherwise run.
Multidimensional Mappings is an effective tool in mitigating the added complexity incurred by GL strings, as well as the more stringent data load requirements that usually accompany them. By using this technique, administrators can map data from their source systems to their environment with relative ease and simplicity. This is despite the, often drastic, differences between the source and target hierarchies. Thanks to the flexibility provided by this technique, administrators can utilize this regardless of which two dimensions are being referenced in their own GL strings, or in any situation where the administrator needs to map data across two or more dimensions.
Blog post by Ali Khaled of Key Performance Ideas.
Posted on Wed, July 25, 2018
by Anne Stein