Loading multiple periods in one Oracle EPM data load

Most Oracle EPM Cloud data loads follow a simple pattern: one file per period, one period per load. That’s fine when you’re loading a single month’s actuals from the GL. It gets painful the moment the source system hands you one extract covering twelve months, or you need to bulk-load a full year of budget data out of a spreadsheet.

The usual workarounds — splitting the file into one-per-month, or pivoting the periods into columns — are tedious and easy to get wrong. They’re also unnecessary. Data Management, and its successor Data Integration, has supported multi-period loading since the 16.10 release. You add the period and year as columns in the source data, tell the import format where to find them, set up the mappings, and load the lot in one pass.

This post walks through the configuration using FCCS as the target application. The same approach works for Planning, Tax Reporting, and FreeForm.

Data Integration or Data Management?

If you’ve been on EPM Cloud for a while, you’ll know Oracle has been moving configuration tasks out of the older Data Management module and into the newer Data Integration module under Data Exchange. Oracle began announcing this migration in the 23.07 release and has continued moving capabilities across in the releases since (the same announcement reappears in 23.09 and later). On newer environments, period mapping and several other tasks are now handled in Data Integration rather than Data Management.

The multi-period concept works the same way in both. On a newer environment you’ll do the period mapping in Data Integration rather than the classic Data Management screens, but the fields and the logic are identical. The screenshots in this walkthrough deliberately span both interfaces: the import format and data load rule are shown in classic Data Management, and the period mapping in the newer Data Integration screens, so you’ll recognise whichever your environment is on.

Two file layouts

Before the configuration, it’s worth knowing that Oracle supports two different multi-period file layouts.

Periods in rows. Each row holds data for a single period, with Period and Year as columns alongside the dimension members and the amount. A twelve-month file has twelve rows per account and entity combination. This is the layout this post covers, and it’s the most common when the data comes from an ERP extract or a database query. The source file looks like this:

Entity,Account,Period,Year,Currency,Amount
E1,4100,Jan,2025,AUD,150000
E1,4100,Feb,2025,AUD,148000
E1,4100,Mar,2025,AUD,162000
E2,4100,Jan,2025,AUD,95000
E2,4100,Feb,2025,AUD,97000
E2,4100,Mar,2025,AUD,101000

Periods in columns. Each row holds data for several periods, with the period names as column headers and the amounts spread across them. This is the layout you tend to get from a spreadsheet where the months run left to right. The configuration differs: you map the amounts using a Column=start,end expression on the Amount dimension rather than reading a single Period column. We won’t cover that method here; the Oracle docs linked at the end have a walkthrough if that’s the layout you’re dealing with.

Setting it up

1Add Period and Year to the import format

Open your import format in Data Management (Setup → Integration Setup → Import Format). Use the Add → Source Period Row dropdown to add two new source columns. For each one, specify:

• Source Column Name — the name shown in the import format mapping (for example, PERIOD, YEAR).
• Field Number — the column position in the source file. If Entity is column 1, Account is column 2, and Period is column 3, then the field number for PERIOD is 3.

The same dropdown also offers Period Number; you only need Period and Year for this layout. Add one row for Period and one for Year. Once you save, Data Management reads the period and year from each row in the file instead of relying on the POV period you pick at load time.

2Configure period mappings

This is the step that trips people up, because the mappings aren’t where you’d expect. Navigate to Data Exchange → Period Mappings, and work on the Source Mapping tab — not Data Load Mappings. If you go looking under Data Load Mappings, you won’t find anything related to the source period columns you just added.

On the Source Mapping tab, create a new calendar (for example, Calendar1). Then map each combination of period and year from your source file to the matching EPM target period. The Target Period Key column shows the internal period format EPM expects. If your file contains JAN in the Period column and 2024 in the Year column, you map that combination to the target period key 2024-01-31, which resolves to the target period name Jan-24. The Source Period Number column carries the period’s position in your source calendar, which is how out-of-sequence fiscal calendars stay aligned to the right target months.

3Update the data load rule

Go back to your data load rule. With the period mapping calendar saved, a new field appears called Period Mapping Type, with two options:

• Default — uses the standard global mappings on the Global Mapping tab. This is the old behaviour, where the POV period drives everything.
• Explicit — uses the custom source mapping calendar you just created.

Select Explicit, then choose your calendar from the Calendar dropdown.

Running the load, and a gotcha

With the configuration done, you can execute the data load rule. There’s one behaviour to understand first: the rule still respects the start and end period you select at execution time. The period values in your file work within that window rather than overriding it.

So if your file holds Jan, Feb, and Mar, but you execute the rule for Jan to Feb, the March data in the file is skipped without warning. Execute for October when October isn’t in the file, and nothing loads at all.

When you’re loading a file that spans several months, the safe approach is to set the start and end period to cover the full range of periods in the file, or even the whole fiscal year as a catch-all. Data Management matches the rows in the file to the execution range using your source mappings and loads only what matches. That also means you can reuse the same multi-period file with different period ranges whenever you need to reload a subset of months.

Automating with EPM Automate

If you run data loads on a schedule — and for actuals loads you probably should — the runDataRule command in EPM Automate supports multi-period execution natively. You pass the start and end period in the command:

epmautomate runDataRule "My Data Load Rule" Jan-25 Mar-25 REPLACE STORE_DATA inbox/actuals_2025.csv

The period names in the command must match the period mapping names defined in Data Management. It behaves the same as running the rule from the UI: the start and end period set the window, and only rows in the file that fall within that window get loaded.

Once it’s set up, a full year of data loads from a single file in one execution, instead of splitting the file apart or running the same rule twelve times over.

References

• Loading Multiple Periods for Cloud EPM or File-Based Source Systems — Oracle documentation (periods in rows)
• Perform a Multi-Period Load with Period Data in Columns — Oracle documentation (periods in columns)