Clear target data in Oracle EPM - ERP integrations using a Data Exchange Clear Region

Use a Clear Region to ensure that the target intersection for loading is empty before running a data integration via Data Exchange or Data Management. Not only can a Clear Region wipe data from Oracle Planning Cloud, the functionality can be applied to Oracle General Ledger too! This allows users to clear and reload data in the GL simply by launching the Data Load Rule from Planning.

Use Cases

Daily refresh of Actuals from Oracle GL
Saves from scheduling a clear data Business Rule ahead of the daily data load!
Pushing / Pulling data between Planning & GL to overwrite a 'Latest Forecast' Scenario member to be used as a default for reporting tools.
Pushing / Pulling data between Planning & GL in scenarios where only a subset of data requires clearing.
The standard Replace Export Mode (more on this below) would clear all account members (e.g. FTE data) regardless of the source data.

What you need to know

General

The Clear Region is attached to a Data Load Rule and can only be configured within Data Exchange - Data Integration.
The Clear Region is only applied when using the Export Mode: Replace
Planning to automate using EPM Automate or the Batch Scheduler?
This is not an issue! Once the Clear Region is configured in Data Exchange, provided the Export Mode Replace is set, the clear will still be applied when the rule is automated.

Pushing Data to Oracle GL

Export Mode Replace is not available when running via Data Management and therefore your Clear Region will not be applied. Instead, create a Batch, for which you can select the Export Mode, and then run the batch!
There is a limit in Oracle GL on the number of members that can be cleared via a data load. The Member Functions such as ILvl0Descenants are not used. Instead the Parent is defined and the load creates a flat list of lowest level members beneath the parent for clearing. This can cause the limit to be reached. Split the load into multiple load rules to avoid this - not so sleek but unavoidable at times!

How to configure a Clear Region

Create a Data Load Rule
Navigate to Data Exchange - Data Integration and click to edit the Integration
Select the Options tab
Within Options, select the Clear Region tab
Click the + to add a dimension for clearing or use the drop down arrow to change a dimension
When Oracle Planning Cloud is the target, you can use Member Functions in the Filter Conditions, such as @ILvl0Descendants(Total Entity).
When Oracle General Ledger is the target, you can select the Parent for which to clear all lowest level members beneath, but you cannot use Member Functions.
If preferred for a dimension, you can use Derive From Data to only clear any members that are featured in the dataset. Leave the Filter Condition empty for these dimensions.
Some of the Clear Region will be automatically defined from the dataset regardless of the definition selected here.

For Planning targets, the Period, Year, and Scenario are defined from the dataset
For Oracle GL targets, the Ledger, Scenario, and Accounting Period are defined by the dataset

Click Save and Close
Run the integration using Export Mode: Replace

For Oracle GL Targets Data Management will offer Replace as an export option - instead create a Batch, and configure the Export Mode to Replace using the Batch Options.
Optionally: Automate via Batch Scheduler or EPM Automate and the Clear Region will be applied.

View the Log to see more detail on the Clear Region applied

What is different when using Export Mode Replace without a Clear Region?

Replace data uses the Point of View and contents of the dataset to clear data for the Year, Period, Scenario, Version, and Entity dimensions. However it clears for all accounts, and all other custom dimension members.

What if your data has moved from one entity to a different entity and no longer featured in the subsequent dataset? Using Replace, without a set Clear Region, the original entity will remain uncleared and duplication would occur.

Clear regions allow users to be more specific in defining the region that is cleared.

Supporting Information from Oracle Documentation

Can only be used with Oracle Financials Cloud 20C or higher
For the Oracle Financials Cloud, you cannot specify member functions. Can select parent member and system clear data for all lowest level children automatically.
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. If you specify member for these dimensions, they are ignored.
For Oracle General Ledger, 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. If you specify member for these dimensions, they are ignored.

Transfer of Smart List Values to Members in Calculation Manager

This blog features instructions for retrieving and transforming a Smart List entry (stored against a dimension member) into a member name to be used within a calculation script. This member name is in a valid format for use with cross dimensional operators. This functionality is particularly useful for Staff Costing models. It can be used to retrieve a grade or payscale point from a staff member and lookup its relevant base salary. I came across this trick when writing a calculation script that would pass through each employee in a Staff Cost model and retrieve their pay scale point – a Smart List value – and then transform this into a member in order to retrieve the corresponding base pay associated to that pay scale point. Pay scale points are members in my ‘Employee’ dimension. Upon configuration of the pay scale Smart List, I selected ‘Create from Members’. This is compulsory in order for the trick to work! I have attached this Smart List to a member in the Metric...

EPM Planning

Search This Blog