Streamlining Adjustment Period data load from Oracle Cloud ERP to EPM

Typically, EPM Planning applications will have a 12-period year which can present a challenge when loading adjustment period data from ERP. To get around this, Oracle have provided the ability to map ERP adjustment periods to EPM periods in data exchange – usually loaded to the first or last period, depending on if the adjustment is applied at the start or end of the year.

Adjustment period mappings are not automatically set up when initialising the source system, so the first step is to create these in the Source Mapping tab, with mapping type Adjustment:

Enter the name of the Adjustment period and map it to a target EPM period using the period keys are a reference. Note: the format of the adjustment periods may differ to above.

To enable loading adjustment period data, use the Include Adjustment Periods setting on the data load options, with one of the below options:

  • No –  load period value only (no adjustment values)
  • Yes – this setting will load both the period and the adjustment value, according to adjustment period mappings
  • Yes (adjustment only) – only the adjustment period value will be loaded (no periodic data)

 
The rest of this blog will assume that the Yes setting has been selected, meaning that both periodic and adjustment data will be pulled through. For example, when we run a load for Oct-22, the load will pull in Oct-22 period data plus Adj-Oct-22 Adjustment data.

As both sets of data is being loaded to a single period in EPM (Oct FY23) it would be useful to be able to distinguish between which lines are periodic (Oct-22) and which are adjustments (Adj-Oct-22). Luckily, we have found a way to do this with a simple SQL mapping!

This trick uses the GL period name, which is loaded into a hidden attribute in the data load. To find which attribute contains the GL period, go to the workbench in data management (note: at the point of writing this feature isn’t available in data exchange), and click View -> Columns -> Show All.

 

Scroll over to the right to find the attribute which holds the GL period (See Attribute 11 below).

We can now use the attribute 11 to map data, depending on if the data is an adjustment or not.

For example, we can map a Component dimension as “Base” for periodic data and “Adjustment” for adjustment data, using the below SQL mapping:

CASE

  WHEN ATTR11 like 'Adj%' THEN 'Adjustment'

ELSE 'Base'

END

Hope this helps!

Comments

Post a Comment

Popular posts from this blog

Loading multi-period row data files using Data Management

Image
The ability to load multi-period data with the Period and/or Year in the rows is not particularly new (since release 16.10), however it is something that most people seem to forget is possible in Data Management. This really is something worth remembering as it eliminates the need to either split the data files into one file per month or try to pivot the month into the columns. In order to set this up, let’s firstly look at how to add the Period and Year columns to the import format. Add the Period and Year columns using the Add -> Source Period Row dropdown, and input: The Source Column name – e.g. PERIOD The Field Number (file column) – e.g. 3 Any required expressions for that field  As these are Period/Year dimensions, the mappings are not held in the usual Data Load Mappings area. Instead they are configured in the Period Mappings , under the Source Mapping tab. Select the Source System as File to add a new mapping calendar for loading the data ...
Read more

Loading Actuals from Fusion ERP Cloud to PBCS

Image
If you’re looking to set up a data load of Actuals from ERP to PBCS and have heard this is an “Out of the box” integration, then good news! Not only is the OOTB connector included within PBCS Data Management but the integration is simple to setup and to schedule using EPM Automate. This blog will detail how to setup the ERP to PBCS connector in Data Management and some of the common errors found when running the data load. The first thing to know about this integration is that the data source is actually the ASO Essbase cube which sits underneath the Fusion ERP tables, holding the period Actuals values. This ERP Essbase cube is created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system. This cube must be created before the integration can be set up correctly and can be accessed directly via Smart View for reconciliations. Set up the Source System As this is an Oracle built connector, it’s as simple as providing a name for the source system ...
Read more

Executing Smart View Retrievals using VBA

Image
When working with Hyperion systems, Smart View is a key tool used for reporting, as well as inputting data. It provides a valuable link between Hyperion and Excel, which allows users to retrieve data from the database straight into Excel with ease. Smart View functions are available through the Smart View toolbar and can be used with Essbase, HFM, Planning or Cloud connections. Occasionally, it is necessary to perform Smart View functions behind the scenes using VBA code, rather than manually using the Smart View toolbar. For example, a spreadsheet which will refresh all the Essbase retrieval sheets on opening, so that users can view the most up to date data, without having to remember to refresh each of the retrieval sheets themselves. This blog will be cover how to retrieve data using a Smart View refresh function in VBA. For more information about Smart View Excel VBA functions, go to the Oracle Hyperion Smart View Developers Guide .  System Requirements ...
Read more