The ability to load multi-period data with the Period and/or Year in the rows is not particularly new (since release 16.07 of Planning in the Oracle EPM Cloud*), however it is something that most people seem to forget is possible in Oracle EPM Cloud 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 file.
In the example below, I’ve named the new calendar as HCM_File – this will be referenced in the Data Load Rule later.
Using the Period Key as a reference, map each system period to the format of the Period and Year in the data file. For example, above Apr-19 maps to Period: Apr and Year: 2019 in the file.
As they are manually entered, these mappings would need to be updated manually when new years are added to the system.
Once the new calendar has been created, an extra option will appear on the Data Load Rule for Period Mapping Type, with the following options:
- Default – uses the default mappings on the ‘Global Mapping’ tab
- Explicit – uses the custom ‘Source Mapping’ calendar specified in the Calendar field
To use the custom period mapping calendar, in this example we select the Explicit period mapping type and the new calendar HCM_File, as below:
Important Note! When executing the data load rule, it will still only run for the selecting periods, with the period mappings being picked up from the columns.
For example, consider a file which contains data for Jan, Feb and Mar periods (all year 2019):
- If the rule is executed for Jan-19 to Feb-19, then only Jan and Feb data will be loaded into the system.
- If the rule is executed for Oct-19 then no data will be loaded
- If the rule is executed for Jan-19 to Mar-19, all data will be loaded
As a catch all, executing the period for all periods in the year will ensure all data is loaded.
*Oracle EPM Cloud was formally known by a number of product names including Oracle Planning and Budgeting Cloud Service (PBCS), Oracle Enterprise Planning and Budgeting Cloud Service (EPBCS) and Oracle Financial Consolidation and Close Cloud Service (FCCS)