Skip to main content

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.

Labels:

Comments

Post a Comment

Popular posts from this blog

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 dim
5 comments
Read more

Connecting to multiple Oracle Cloud Environments

Connect to multiple EPBCS environments via Smart View Shared Connections XML Switching environments, changing domains, logging in, logging out… Why not save valuable time by creating a Smart View environments short-cut? Here’s how! First, gather the following details for each environment that you would like to add to your drop-down list: 1. Environment URL (PBCS/EPBCS) :  Recently provisioned environments: https:// epm-exampleDoM .epm. exampleDC. oraclecloud.com/HyperionPlanning/SmartView Environments provisioned before 2020: (If unsure, check your web URL - does it start with planning or epm, match URL) https://planning- exampleDoM .pbcs. exampleDC .oraclecloud.com/HyperionPlanning/SmartView                (where Domain name = exampleDoM and Data centre = exampleDC) 2. Chosen Shortcut Name : ie. Finance PBCS Production Tip 1: Other Oracle Provider Types I have only included the PBCS connection as an example here. See the Oracle Documentation for e
1 comment
Read more

Smart Lists as Run Time Prompts

How to Create a Drop-Down Box Smart List Prompt Image 1: Business rule containing Smart List Run Time Prompt Smart Lists are very powerful tools within Planning Budgeting Cloud Service (PBCS) software. I have recently created a PBCS Forecasting system that utilises Smart Lists to drive the Forecast Process; allowing users to populate the forecast using a customised forecast method selection tool.  The Forecast Process is as follows: 1.        An admin will load in the latest month of Actual data and open the new Forecast for use; 2.        A business rule will be launched to pre-populate the whole Forecast with a single Forecast Method of choice; ·        For example - The forecast method ‘Average’ is chosen and so ‘Forecast 3+9’ becomes populated with 3 months’ worth of Actual data and the remaining 9 months are populated with the average of the first 3 months’ Actual data, for the whole forecast. 3.        Users then review the forecast and apply the following actio
4 comments
Read more