Reverse Mapping using Hierarchy-Driven Smartlists

This is my maiden post as a blogger and here goes!

Credit goes to Peter Nitschke and Celvin Kattookaran for inspiring me to writing this post.  I've been involved in the EPM world since 2004 primarily in the areas of Essbase and Planning and I must say, the technology has evolved so much as to what I was used to back in the days.

Today, I would like to share with you one such feature that I have used in a project with my client.  Have you ever need to push data between cubes in PBCS where the source and target cubes maintain different account hierarchy levels?  If so, read on.

There are many ways to pushing data between cubes at differing levels and would require some sort of mapping to be in place.  My go-to method has always been using partitions (with pretty much a hard-coded mapping) when using on-premise version to using Data Maps with Smartlist mapping in PBCS.  My problem is that the account mapping is needed for a just handful of accounts where the source cube is maintained at 1 level higher than the ones in the target cube.  The solution before this was to have a form listing all the source accounts in the rows and a measure linked to a target account smartlist in the column.  Users will then have to manually establish the mapping for all the source accounts (a nominated lower level account for some and the rest will be mapped to itself).  This is not a very good solution as it requires maintenance to this mapping table when new accounts are added to the application.

So, having read this blogpost by Celvin Kattookaran about hierarchy-driven smartlists, it gave us an idea to apply this method to reduce the need to maintain the mapping table.  The post reveals that there is a hidden Alias Table (called SLAliases) automatically created in Essbase to store all the HSP_IDs for every member created in the Essbase app.  This alias table is used as the Entry ID in a hierarchy-driven Smartlist.

The member attached to this smartlist will store the HSP_ID of the selected entry.  For instance, if account A302600 is selected by the user, the member value stored will be 59422.  So, using a function to concatenate "HSP_ID_" with the member value will return us the account member.  In the example below, the "Sender_Account" measure is attached to the above smartlist and will store the HSP_ID of the selected account (e.g. 59422).  This example will return a value of "HSP_ID_59422" and will in turn resolve to the account member "A302600".

Taking Pete's bright idea to reverse the process, I am now able to automatically populate an account mapping table using a hierarchy-driven smartlist.  Here's what I've done to allow the client to manually maintain account mappings that are one level deeper in the target cube for a handful of accounts while having PBCS automatically populate the one-to-one account mapping for the rest of the accounts.

The application was created with the following dimensions.

1. Measure
2. Period
3. Account
4. Entity
5. Scenario
6. Version
7. Year

Create a hierarchy in the Account dimension
This hierarchy should contain all the accounts required to map to the target cube:

Create a Hierarchy-driven Smartlist
Create a smartlist based on an account hierarchy that you have defined in the dimension:

Create a measure to store the account mapping
The first measure is opened for input while the latter is a dynamic-calc member that derives its value from the input measure so that the input value persists in any possible combination.  Both measures are linked to the above smartlist:

Create a form
This form is used to capture the manual account mappings and display the auto-populated mappings for the remaining accounts.  The form is configured to have all the Source Accounts listed in rows while the column contains the 2 measures.  Note the column on the right contains the dynamically-derived account mapping based on the inputs in the first column.  This is to ensure that the values entered persist across all possible combinations, thus, facilitating in the mapping process using a Data Map.

Create a business rule
This rule will populate the HSP_IDs of the accounts to the "COA_Mapping_PL_FinRPT_Entry" measure.

The rule excludes all the accounts where their mappings are manually-maintained.  The remaining accounts are populated with the HSP_ID derived from their aliases in the SLAliases alias table.  As mentioned earlier, the SLAliases table contains the HSP_ID for each and every member that are created in the app.  The table below illustrates what the SLAliases table may contain:

The rule retrieves the Alias for each account from the SLAliases table and strips off the "HSP_ID_" prefix leaving just the numerical values of the alias and will be assigned to the "COA_Mapping_PL_FinRPT_Entry" measure.  Since this measure is linked to the smartlist we have created earlier, the numerical value assigned to this measure will resolve to the relevant entries in the linked smartlist.  The table below illustrates the process:

End Result
The one-to-one account mappings (bottom part of the form - in read only mode) are automatically populated once the above business rule is run.

Hope this post is useful and leave a comment if you like it.  Thanks.