ETL design — Extract, Transform, Load — is the technical discipline of specifying, building, and validating the processes that extract data from source systems, apply transformation logic, and load the result into target systems. In ERP and EPM implementations, ETL design translates the data mapping document into executable processes that run on a schedule, handle errors consistently, and produce a reconcilable output — the actuals data in the EPM, the migrated master data in the ERP, or the reporting dataset in the BI platform. ETL is not a technology; it is a set of designed, tested, and operated processes that may use any of several tooling options depending on the source and target system combination.
ETL Design Components
A complete ETL design for an ERP-to-EPM data flow covers:
- Extract design: The query or API call that retrieves data from the source system — including the filtering logic (which entities, which periods, which account ranges), the scheduling mechanism, and the handling of incremental versus full extracts
- Transformation design: The sequence of transformation steps — dimension value mapping, currency conversion, aggregation, filtering — and the tool layer at which each transformation occurs (source extract, middleware, or target system)
- Load design: The load mode (replace, accumulate, or merge), the validation steps performed before data is committed to the target, and the rollback procedure if validation fails
- Scheduling and dependency management: The trigger for each ETL run, the dependencies between ETL jobs (the GL extract must complete before the FDMEE load begins), and the handling of schedule failures
- Error handling and alerting: The specific error categories the ETL must handle, the action for each (reject record and continue, reject batch and alert, retry after interval), and the alerting mechanism for errors requiring human intervention
- Reconciliation output: The reconciliation record produced by each ETL run — source record count, loaded record count, rejected record count, and total value comparison — used to confirm the completeness of each load
Common Gaps and Failure Modes
The specific failure that causes recurring close-cycle incidents is ETL processes built without a defined reconciliation output. When the FDMEE data load to Oracle FCCS completes, it shows a status of “successful” — but successful means the process ran without a system error, not that every record was loaded correctly. Without a reconciliation that compares source trial balance totals to loaded EPM actuals at the account-entity-period level, the finance team has no confirmation that the EPM actuals match the ERP. They discover the mismatch when the consolidation output does not agree with the ERP trial balance — three days into the close cycle, when reconstructing the cause requires reviewing ETL logs that were not designed to support reconciliation investigation.
How Loop Wise Solutions Produces This
Loop Wise Solutions designs ETL processes with a mandatory reconciliation output as a standard requirement — not an optional enhancement. Every data load produces a reconciliation record that is reviewed by the finance team before the close process proceeds. We design the error handling logic before build begins, not after the first testing failure reveals that there is no error handling. ETL processes built without a reconciliation output and without defined error handling are not accepted as build-complete in our delivery standard.
