Oracle Cloud EPM : How to Export and Import Period mapping in Data management / Data exchange

NEW FEATURE

As we all know that using epm cloud data management we cannot export and import the period mapping, we need to manually add the periods in the Global mapping by referencing the prior period key and the period key and then define the application and source mapping accordingly.

But now the "DATA EXCHANGE" feature in Oracle EPM Cloud FCCS / PBCS / EPBCS has an option to add periods using excel. 

We can export the current mapping and then we can add our required mapping and then import the file back to data exchange which in turn gets updated to the Global mapping in data management.

STEPS

Go to Navigate > Data exchange 

Click on Actions > Period Mapping 

    Here you can import, export, delete period mappings 

Oracle EPM Cloud Data Management : How to define a Sub-String in Import format

 Requirement

        From a source dimension (Ex. Cost center), during the import to data load workbench, we need only the values starting from 5th position, and 6 characters from that. Rest all we need to ignore during import.

Example : CCWER500047TY - this is a cost center member
                    I need only 500047 to be imported to data load workbench

Solution

    In the import format use the expression shown below 

                        substr(UD2, 5, 6)

Oracle EPM Cloud - Data Management : How to use special characters, Prefix/Suffix, replace characters using Multi-Dimensional mapping

Source

Map only specific entities (IND, EUR, JAP)

Cost center* dimension begins with (03****) 

Target

Map to target Cost center dimension - 

1. Add a prefix CC 
2. Members begins with 03**** in source should map to 04**** in target

Example

Source 

Entity : IND, EUR, JAP 

Cost Center : 03MCHJ, 03JIUY, 03KIJY

Target

Cost Center : CC04MCHJ, CC04JIUY, CC04KIJY

SOLUTION

In the Target value define     #FORMAT("CC04##????")

NEW FEATURES IN EPM CLOUD DATA MANAGEMENT - POST RELEASE 20.05

ORACLE FINANCIAL CLOUD SOURCE SYSTEM TYPE NAME CHANGED TO ORACLE ERP CLOUD

In Data Management on the Source System page, the name of the source system type “Oracle Financials Cloud” has been changed to “Oracle ERP Cloud.”  All pre-existing “Oracle Financials Cloud” source system type names now show with the  “Oracle ERP Cloud” source system type name. This is a name change only and you do not need to take any action.

For your reference, the Oracle ERP Cloud source systems include:

• Oracle General Ledger
• Fusion Budgetary Control
• All ERP Cloud Adapters

The file source system type name remains “File.” The Oracle Human Capital Management Cloud source system type name remains “Oracle HCM Cloud.” The NetSuite source system type name remains “NetSuite.”

NEW SYSTEM SETTING FOR MIGRATION OF DATA LOAD MAPPINGS IN DATA MANAGEMENT

A new Map LCM Format profile option in Data Management System Settings enables you to export individual data load mappings by location or combine mappings by all locations for Migration (lifecycle management activities). 

If you select “Individual Data Load Mapping by Location,” maps are exported individually for each location and imported individually for each location. If you have a large number of maps for each location, use this method since it allows loading of maps in smaller sets by location. This method is also useful to migrate maps for certain locations selectively. With this method, existing maps are deleted and replaced by new maps in the snapshot.

If you select “Combine Data Load Mapping for All Locations,” maps are exported in a single artifact for all locations. With this method, maps from the snapshot are merged to the existing maps in the target system. This is the default setting.

The new Map LCM Format setting is available on the Data Management System Settings page as shown below:

REPLACE MODE FOR ALL DATA TYPE WITH SECURITY LOAD METHOD NOW SUPPORTED

When loading data to Planning or Essbase target applications using the All Data Type with Security load method, the Replace mode is now supported for administrator users only. 

In Replace mode, the system first clears any existing data in the application for those referenced in the dataload file. Then the system performs the data load in Merge mode.

LCM MIGRATION OF DATA LOAD MAPPINGS IN DATA MANAGEMENT

In order to improve the performance of export and importing large data load mappings, Data Management now exports the mapping by individual locations.

When importing the mappings to the target environment, the existing mappings are completely replaced with mappings from the source environment. Prior to this update, data load mappings from the source were merged into the target environment.

EPM INTEGRATION AGENT WRITE-BACK

The EPM Integration Agent, which provides connectivity between EPM Cloud and on-premises sources, now provides write-back functionality where data from EPM Cloud can be loaded back to the on-premises database.

To support this feature, a new data export application allows you to export data to the on-premises database. There are two modes in which you can load data to the target table. To use the simple mode, you register the application and specify the table name and include all the columns from the table in the application. The system generates the INSERT statements automatically and loads the data. To use the advanced mode, you provide an INSERT statement with the appropriate source value and any SQL expressions in an INSERT query.

In addition, the agent can execute two event scripts during the write-back execution: BefExport and AftExport. Using a BefExport event, you can perform any action before inserting data to the table or you can override the default insert processing. Using an AftExport event, you can do any post processing cleanup after inserting data to the table.

The write-back feature is supported for agents running in both SYNC and ASYNC modes.

STEPS TO ENABLE

Download the EPMAgent.zip from the Download Agent option on the Actions menu in Data Integration. Unzip the EPMAgent.zip and overwrite your existing agent folder. There is no further action. You don’t need to update the ini file or certificate.

NEW CLEAR REGION OPTION IN DATA INTEGRATION

A new Clear Region option in Data Integration lets you select the slice of data to be cleared before loading data in Replace mode to a Planning module application. In addition, when loading budgets to the Oracle General Ledger, you can optionally clear prior budgets before loading a new budget. This feature can only be used with Oracle Financials Cloud 20C or higher.

You define the clear region by specifying member selection criteria for dimensions such as a static list of members, member function selections, and members derived from the data. By default, the system defines a default clear script based on Entity, Scenario, Version, Year, and Period values included in the data load.

• For Planning, the Period, Year, and Scenario dimensions are derived automatically based on the data and you do not have to specify any filters for these dimensions.

• For Fusion, the Ledger, Scenario, and Accounting Period dimensions are derived automatically based on the data and you do not have to specify any filters for these dimensions.

The Clear Region option is available on the Options page in Data Integration.

Clear Region Tab

The Clear Region option is only available for the Planning module and Oracle Fusion General Ledger (Fusion update 20C or higher required) target applications.

Budgetary control cubes available in Fusion General Ledger are not visible in EPM Cloud Data management

In EPM Cloud Data management, users might have configured the Fusion General Ledger as the source system.

After initialization, users might see the Chart Of Accounts visible under the target application page but not the Budgetary cubes.

CAUSE

The source type of the control budget was not defined correctly.
 

SOLUTION

Budgetary Control Balance cubes and their underlying control budgets are eligible for integration with the Oracle Enterprise Performance Management Cloud when the control budget in Budgetary Control:

• has a "Planning" Source Budget Type.
• does not have Project Portfolio Management keys as a budget segment
• is associated with a Budgetary Control Balances cube

To begin integrating the Budgetary Control with the EPM Cloud, create and register the source system with the source system type "Oracle ERP Cloud".

Select the check-box, Fusion Budgetary Control.
When Budgetary Control is enabled, only Budgetary Control applications, as indicated by an "XCC _" prefix, are imported.

After the source system and connection information are specified, initialize the source system to copy the Budgetary Control information to EPM Cloud as one of many Essbase target applications.

Note : Make sure the source system name does not include any spaces. For example, use FinCloudBC rather than Fin Cloud BC.

To find whether the budgetary cubes are created refer the document below,

Fusion Applications BI Publisher How Can I Use BI Publisher to Run SQL Against the Fusion Applications Database (Doc ID 1910762.1)

After confirming that the cube is visible in ERP, 

• Go to Fusion GL
• From the home page > select Budgetary control - it will lead to the Budgetary  control dashboard
• Select the tasks icon, which will be in the top right corner
• Select Manage Control Budgets
• Select the respective control  budget and click on Edit 
• Set the source budget type to "Hyperion Planning"
• Click Save 
• Then re-initialize the Fusion GL source system in Data management 
• The Budgetary cubes should be visible under Target application page

FCCS - IMPORT METADATA ASSIGNING NEW PLUG ACCOUNT TO ICP ACCOUNT AT SECOND ATTEMPT

FCCS - IMPORT METADATA ASSIGNING NEW PLUG ACCOUNT TO ICP ACCOUNT AT SECOND ATTEMPT 

While importing new plug accounts and tagging the new plug accounts to the existing intercompany accounts, users might get into a situation where the import metadata process fails at the first attempt and gets success in the second attempt...

Users might the below error as well 

ERROR MESSAGE
java.lang.RuntimeException: Attribute value: AC_XXX062 invalid for attribute
dimension: Plug Account
java.lang.RuntimeException: Attribute value: AC_XXX062 invalid for attribute
dimension: Plug Account

SOLUTION

This is the expected behavior with FCCS.

The first pass (first import) is when the accounts that are tagged as plug accounts are created. In the second pass, you will be able to assign those accounts as plug accounts.

How to restrict users from viewing load files from other locations in FDMEE

Create two locations in the names (example) "ForlocationA" and "ForlocationB"
Created two new users "USER A" and "USER B"
In Data Management enable "Location security" under Setup > Security settings
Click on "Maintain User groups" with prefix A and suffix K (anything)

Now new groups "A_ForlocationA_K" and A_ForlocationB_K" will be created

Assign those two groups with either Create Integration, Run Integration or any Intermediate roles.

Then in Shared services, assign USER A as a member of Group "A_ForlocationA_K"
Then in Shared services, assign user B as a member of Group "A_ForlocationB_K"

Then grant both the users USERA and USERB with the roles Create Integration, Run Integration roles

Then login to the workspace with userA - navigate to select Inbox and you will be able to see the folder ForlocationA alone
Then login in to the workspace with userB - navigate to select Inbox and you will be able to see the folder ForlocationB alone

You will not be able to see other locations created by other users when that respective user is logged in,