Share

Historical Mappings in Oracle EPM

Problem Description

Ever been asked about historical mappings? Has your client ever enquired about applying an expiry date to a mapping rule? Well, I was asked recently and after thorough analysis, the conclusion was that the Data Management application always maintains one mapping set and it does not have a functionality of a closing date for the mapping rules. Then how do you maintain a mapping table for each month if you are loading data monthly? 

 

Business Needs

At times, organizations must reload data for past months due to various business needs. Again, due to changes in the reporting requirements, mapping tables need to be updated regularly. For example, source account code 10001 was mapped to the target account code A50001 until March 2021. From April 2021, the same source account code 10001 needs to be mapped to the target account code A50002. In this case, it’s not possible to create new locations or new data load rules to track the historical mappings. Then, the question arises how to ensure that if the data for March 2021 is loaded again the source account code 10001 is mapped to the target account code A50001. Some guidance can be taken from the audit logs for the mappings but it’s a practically difficult solution if data needs to be loaded for multiple past months or the changes to the mappings are frequent.

Hence, the need of maintaining the historical mappings or applying the sunset dates to the mapping table arises.

 

Solution

Using the native Oracle EPM toolset, it’s possible to maintain and use historical mapping tables for reloading data for the past periods.

The solution would involve:

  1. Extracting the current mappings and storing these into the current mappings folder
  2. Extracting the mapping each period and storing these into historical mappings folder with the period name used in the name of the file
  3. Based on the period for which data is getting loaded, importing the mapping file from the historical mapping folder using replace option
  4. Once the data is loaded, restoring the mappings using the file for the current mappings in the current mappings folder

The steps mentioned above can be automated using EPM Automate Utility and Batch Scripting.

 

 

Technical Details

In the example, I am loading data for two months Jan 2020 and Sept 2020 using EPM Automate Utility. 

Source Files

mappingsblogimage1

The source file folder has data for two periods: Jan-FY20 and Sep-FY20.

Mappings

For Jan-FY20, the target value is abc.

For Sep-FY20, the target value is pqr.

I have already run a script to extract and store mappings into the historical mappings folder and current mappings folder. This script can be scheduled to run automatically as well.

Data Load

When data is loaded for Jan-FY20, the mapping from the historical folder will be loaded by the automation script before processing the data for Jan-FY20 and hence, the target value will be abc.

 

When data is loaded for Sep-FY20, the mapping from the historical folder will be loaded by the automation script before processing the data for Sep-FY20 and hence, the target value will be pqr.

Please ensure to turn off the Audit Logs for Maps, as this solution will start creating huge logs, which may impact the performance of the application.

Business Benefit

This solution locates and restores the historical mapping tables automatically whenever the data for the prior periods needs to be reloaded, taking away the pain of manually locating and restoring historical mapping rules.

 

Trivia

I have a client who is loading data into the ARC application four times a day every day. During the month-end process, they load data for at least two months simultaneously, and often, they re-load data for all prior months due to various business needs. This solution has automated the data load process end to end applying the historical mapping tables for the respective months. 

FTP utility is scheduled to drop the source files (having the period name in the name of the file) into a designated folder. The data automation solution then reads the names of the files and determines the period for which these files need to be processed. Once it’s determined, using the EPM Automate utility, the solution restores the mapping from the historical period folder for the month for which data needs to be processed. Then the solution transfers the file to the Inbox of Data Management using EPM Automate Utility commands and processes data. Once the data is processed, the solution picks up the current mapping tables from the current mapping tables folder and restores the mappings. Once the data load process is completed, the solution also runs the auto-reconciliations and auto-matching rules.

 

EPM Automate Commands

Here is the list of some EPM Automate commands used in the solution:

*********Deleting existing historical mapping file**************************** 

call epmautomate deleteFile inbox/Mappings_Historical/%LOCATION%_%monthname%-%year%.txt %LOCATION% 

*********Exporting historical mapping file************************************* epmautomate exportMapping ALL inbox/Mappings_Historical/%LOCATION%_%monthname%-%year%.txt %LOCATION%

 

********Deleting existing current mapping file*******************************

call epmautomate deleteFile inbox/Mappings_Current/%LOCATION%_Mappings.txt %LOCATION%

*********Exporting current mapping file*************************************

call epmautomate exportMapping ALL inbox/Mappings_Current/%LOCATION%_Mappings.txt %LOCATION%

*********Importing historical mapping file************************************

call epmautomate importmapping ALL inbox/Mappings_Historical/%LOCATION%_!fn6!.txt  REPLACE FALSE %LOCATION%

*********Importing current mapping file**************************************

call epmautomate importmapping ALL inbox/Mappings_Current/%LOCATION%_Mappings.txt REPLACE FALSE %LOCATION%

Become a member

Newsletter

Receive our newsletter to stay on top of the latest posts.


Dipen Shah

Author

Dipen Shah