Finally, EPM cloud users can load metadata using data management for EPM cloud service as per oracle update which was released on 30 Nov 2017.
EPM cloud users can now upload metadata for standard dimensions (such as Account and Entity), custom (Such as Product), and Smart List dimensions which only supported for the following services:
- Oracle Enterprise Planning and Budgeting Cloud
- Oracle Planning and Budgeting Cloud
- Oracle Financial Consolidation and Close Cloud
- Oracle Tax Reporting Cloud
In the below table, I compared the features of uploading data management using outline load utility and Data Management.
|Feature||Outline Load Utility||Data Management|
|File Template||Specific and fixed file template||Any format any source|
|Check Errors Step||Check only after the loading||Check before loading|
|Log file||Preview the log file||Preview and export the log file|
|Mapping source to target||No mapping||All mapping types plus mapping scripting|
|Default Values||No default values||Users can set any defaults values|
|The sequence in load many dimension||Not Exist||Users can use the batch definition to load many dimension in one process|
|Automate the process||Not a fully automated process||A fully automated process|
Let’s start with a simple scenario on how to upload metadata to the account dimension using data management.
1- I will use the below file to upload members to the account dimension.
- Column A is the Account code
- Column B is the Account Parent
- Column C is the Account Name
2- The file is CSV (Comma delimited) file without header for the metadata.
3- The metadata will be loaded according to the below rules (Later on, we will see how to configure the setup for them in data management)
- The format of the alias should be “Account code: Account name”.
- To select the correct account type as per the account name or account code.
- Data storage is store for assets and revenue, and it is “never share” for all other account types.
4- In the setup tab of Data Management, navigate to: “Target Application” > “Add Button”> “Dimensions”
5- A new window will be displayed where a registered application and prefix can be selected.
6- Six dimensions automatically will be created (Account, Entity, Scenario, Version, Custom and Smart List) according to the (Prefix + Target Application Name + Dimension Name).
7-In the dimension details, you can find all the dimension properties (Properties that are selected will be available in the import format).
8- For this scenario, I will select the Account type, Data storage and Alias.
9- Now navigate to import format. I created a new one for account dimension, so make sure to select (File type = Delimited All data type and Target = Account Dimension)
10- Create the below Import Format mapping:
11- Create the below location:
12-Now navigate to Data Load Mapping. This will show the mapping per every property which I selected in the target application window.
- Account (Account Code): It will be exported to the target application with the same format which exists at the source file. Also, you could have a file with many records so it’s better to use mapping feature:
- Account Type: It will be set based on the account code, so I will use mapping script (SQL or Python can be used for mapping scripts. For this scenario, I will use SQL)
- Account Alias: I will use the below script to concatenate the account name with the account code to get the Account Alias.
- Data Storage: it depends on account type which I can identify from account code. So I will use like mapping as per the below:
- Parent: I will use like mapping because I want to export it using the same format to the target application.
13- Now, I will navigate to data load rule to create a rule using “Account Dimension” import format.
14-Now, I will execute the data load rule and ignore the period – as we aren’t uploading data 🙂
15-Now, let’ will check the output of the data load rule :
The format of the alias should be “Account code: Account name” ……. Checked!
To select the correct account type as per the account name or account code…. Checked!
Data storage is store for assets and revenue, and it is “never share” for all other accounts….. Checked!
Thank You 🙂