Import-Validate for EPM Cloud: Building a Fast Pre-Load Validation Pipeline

If you've spent any time loading data into Oracle EPM Cloud, you've felt this pain: you kick off a Data Integration load, wait for it to run, and only then find out that three rows had a typo'd cost center that doesn't exist in your Entity dimension. Now you're digging through a process log, fixing the source file, and running the whole thing again — hoping nothing else is wrong this time.

This post walks through a small Python pipeline I built to catch that class of problem before it ever reaches Data Integration — in seconds, against your own machine, with zero impact on your EPM environment. I'll explain not just what the code does, but why it's architected the way it is, and why that architecture makes it fast.

Full code is on GitHub: https://github.com/VIKRAM-EPM/vikepmlab/tree/main/import-validate

The problem: validation happens too late, and too slow

Oracle EPM Cloud Data Integration does have its own Import and Validate steps built in — that's not in question. The problem is when and how that validation happens.

Oracle's own documentation describes Data Integration's internal import/validate flow as a multi-step process built around relational staging tables: the file is staged and loaded into a TDATASEG_T table, mapping rules are processed, prior integrations in TDATASEG are cleaned up, the mapping results are copied from TDATASEG_T into TDATASEG, and only then is validation run against that staged data. Oracle's own TDATASEG reference documentation also notes that a large TDATASEG table can slow down query performance during a load.

Put simply: every row of your file gets physically written into a relational table, then copied again to a second table, with validation running as a database operation against that full row-level dataset. That's a reasonable design for Oracle's purposes — it gives you a full audit trail and drill-through access in Workbench — but it also means the cost of validation scales with how many rows you're loading, and you don't find out about problems until you're already several steps into the process.

For a quick "is this file even going to load cleanly?" sanity check, that's a lot of overhead.

The idea: validate distinct values, not every row

Here's the insight the whole pipeline is built around: you don't need to validate every row of your file — you need to validate every unique value.

If your file has 250,000 rows but only 40 distinct cost centers appear in it, then checking whether those 40 values exist in your Entity dimension tells you everything you need to know about every one of those 250,000 rows. There's no reason to touch a database, write staging tables, or process row-by-row.

That's the architecture:

CSV file(s)
    │
    ▼
Import   →  scan the file, extract every UNIQUE value per mapped
             dimension column, with source-file tracking
    │
    ▼
Validate →  check each unique value against EPM (mock sample set,
             or a real EPM Cloud instance via REST API)
    │
    ▼
Report   →  pass/fail summary, with exact values and files that failed
    │
    ▼
Notify   →  (optional) email the same summary

No staging tables. No row-by-row database writes. The amount of work in the Validate step depends on how many unique dimension members your data actually contains — not on file size.

One honest caveat: I'm not claiming a benchmarked "N times faster" number here, and this isn't a replacement for what Data Integration does once a load is actually underway — you still need Data Integration for the audit trail, drill-through, and the actual load itself. Think of this as a fast pre-flight check you run before committing to a full Data Integration run, not a substitute for it.

Step 1: Import — extracting unique values with DuckDB

The Import step uses DuckDB, an in-process analytical database, to scan the CSV directly and pull out distinct values per dimension column.

One design decision worth calling out: rather than a single wide UNPIVOT query across every dimension column at once, the script runs one query per column:

for csv_col, epm_dim in DIMENSION_MAP.items():
    dim_data = _extract_column(con, glob_path, csv_col)
    results[epm_dim] = dim_data

Why not just do it in one scan? On memory-constrained hardware, an UNPIVOT across N columns creates an intermediate result N times wider than the source data — which can blow past available RAM and force heavy disk spilling, actually making it slower despite being "one query" instead of several. Running narrow, single-column queries keeps each query's working set small and predictable, which matters a lot more than query count when you're working with limited memory.

Each per-column query looks like this:

sql = f"""
    SELECT
        CAST({quoted_col} AS VARCHAR) AS member_value,
        filename
    FROM {source}
    WHERE {quoted_col} IS NOT NULL
      AND TRIM(CAST({quoted_col} AS VARCHAR)) <> ''
    GROUP BY member_value, filename
    ORDER BY member_value
"""

A few details doing real work here:

• filename=true + union_by_name=true (set on the DuckDB read source) gives you provenance — which file each value came from — while tolerating files with slightly different column orders or subsets.
• CAST(... AS VARCHAR) forces every value to text, so a typo like '20Z4' in a Year column shows up as a visible bad value instead of silently failing a numeric cast.
• GROUP BY member_value, filename pushes deduplication into DuckDB itself — Python only ever receives the small, already-distinct result set, never the raw rows.
• The blank-filtering WHERE clause matters more than it looks: on the demo dataset I used for this post (more on that below), roughly 10% of rows had a blank Item value. Without this filter, that blank would show up as a "member" to validate — which isn't useful, since it's a missing value problem, not a wrong-value problem. They're different failure modes and worth treating differently.

Step 2: Validate — checking against EPM (mock or live)

The Validate step takes that small set of unique values and checks it against Oracle EPM Cloud's actual dimension hierarchy — one REST API call per dimension, not one per member:

url = (
    f"{EPM_BASE_URL}/applications/{encoded_app}"
    f"/plantypes/{encoded_ptype}"
    f"/dimensions/{encoded_dim}"
    f"?fields=name,children"
)

This hits Oracle's Get Dimension Details endpoint, which returns the entire dimension hierarchy as a nested JSON tree. A small recursive function walks that tree once and collects every member name into a flat Python set:

def _collect_names(node, name_set):
    name = node.get("name")
    if name:
        name_set.add(name)
    for child in node.get("children", []):
        _collect_names(child, name_set)

From there, checking a CSV value against EPM is just a set membership check — no additional API calls needed. Nine dimensions means nine API calls total, regardless of how large your source file is.

Why the repo ships with a mock mode

Not every reader following along has an Oracle EPM Cloud instance handy — and even if you do, you probably don't want to hit it while you're just learning how the pipeline works. So the validator supports two modes, controlled by a single config value:

VALIDATION_MODE = "mock"   # or "live"

In mock mode, validation runs against a small built-in sample member set instead of a real API call — enough to demonstrate both a passing and a failing result with zero setup. Flip it to "live", fill in your EPM connection details, and the exact same code path runs against your real instance instead. No code changes required either way — just the one config value plus your connection details.

Trying it yourself: a public dataset, zero EPM setup required

To make this genuinely runnable by anyone, the demo uses a public Kaggle dataset instead of any real company's data: Retail Store Sales: Dirty for Data Cleaning. It's 12,575 rows of synthetic retail transactions across 8 categories, and — usefully for this demo — it already contains real missing-value messiness you don't have to fake.

The columns that matter for this pipeline:

Transaction ID, Customer ID, Category, Item, Price Per Unit, Quantity,
Total Spent, Payment Method, Location, Transaction Date, Discount Applied

Total Spent, Price Per Unit, and Quantity are numeric fact values — they get excluded from dimension mapping entirely, the same way you'd exclude an Amount column from EPM member validation. The rest map to a simple DIMENSION_MAP:

DIMENSION_MAP = {
    "Category":       "Product_Category",
    "Item":           "SKU",
    "Payment Method": "Tender_Type",
}

Running the pipeline against the unmodified file gives you 8 unique categories, 200 unique SKUs (not 201 — the blank-filtering logic correctly drops the empty Item rows), and 3 payment methods, all validating clean against the mock member set.

I ran this end-to-end myself against the unmodified dataset before writing this post — Import, Validate, and the optional email Notify step all ran successfully, confirming the logic holds up against real (if synthetic) data, not just a hand-built test case.

Seeing it catch something

The dataset ships clean of genuinely invalid values by design, so to see the pipeline actually flag a problem, add one yourself. Copy the CSV and append a row like:

TXN_TEST001,CUST_TEST,Frozen Meals,Item_99_FROZEN,12.0,3.0,36.0,Bitcoin,Online,2024-01-01,False

This breaks all three mapped dimensions at once — Frozen Meals isn't a real category, Item_99_FROZEN doesn't match the real SKU pattern, and Bitcoin isn't a real payment method. Re-run the pipeline against this file and you'll see all three flagged as invalid, each one tagged with the exact filename it came from — the provenance tracking from the Import step doing its job.

Configuration: built to be adapted, not rewritten

Every part of this that's specific to your environment — your data location, your EPM instance, your dimension mapping — lives in one config.py file, organized into numbered sections so it's obvious what you need to edit versus what already has a sane default:

1. Your Data Source — file location, column-to-dimension mapping
2. Your Oracle EPM Cloud Connection — base URL, application, plan type, credentials
3. Validation Mode — "mock" or "live"
4. DuckDB Performance Tuning — memory limit, thread count
5. Output Locations
6. Email Notifications — optional, can be switched off entirely

For dimension mapping specifically, Oracle Planning applications typically include seven standard dimensions — Account, Entity, Scenario, Version, Period, Year, and Currency — on top of whatever custom dimensions your application adds. If you're on a FreeForm/Essbase-based application like the one I originally built this for, there's no fixed "standard" set at all — every dimension is custom by design.

One thing worth being deliberate about: never put your EPM password directly in config.py. It's loaded from an environment variable instead, and if you'd rather not use a plain-text password at all, Oracle's own EPM Automate password encryption is worth a look as an alternative.

Wrapping up

The full pipeline — Import, Validate, and an optional email Notify step — is on GitHub: https://github.com/VIKRAM-EPM/vikepmlab/tree/main/import-validate Clone it, drop in the Kaggle dataset, and you'll have it running in mock mode within a few minutes with no EPM connection at all. When you're ready, point it at your own EPM Cloud instance by filling in one config section and flipping one value.

If you build on this — a different validation mode, a different source format, whatever — I'd genuinely like to hear about it.