------------------------------------------------------------------------------------------------------------------------
*** Update (April 2023) ***
I wanted to add an update to this post, with some further learnings from recently implementing this functionality.
Please remember, the clear regions do not clear multiple scenarios in ERP at once. In a recent example, we had EPM Versions being mapped to ERP Scenarios. The push to ERP definition included three versions in the Source Filter definition. This mapped to three scenarios in ERP. The push integration ran successfully, however the clear did not take place. (It is easy to see in the log, if the clear happens you can see a line for the clear string.) We overcame this by using business rules (REST API / Groovy - see my blog post for details) to launch the integration and override the source filter with one version at a time.
On a slightly adjacent note, if you'd like to run a single data load rule for multiple scenarios in sequence, the new EPM Automate command runIntegration to select which Scenario should be defined for the Source Filter at runtime (this acts like an override at run time only, leaving the original source filter definition intact). The original EPM Automate command runDataLoadRule will not allow Source Filters to be selected. See my code below:
call %EPMAUTOMATE% runIntegration %RULE_NAME5% importMode=REPLACE exportMode=REPLACE periodName=%START_PERIOD%%END_PERIOD% "Version"=%VERSION1%Where "Version"=%VERSION1% is overriding the Source Filter.
------------------------------------------------------------------------------------------------------------------------
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.
Comentários