Nexus53 for ODI
Oracle Data Integrator estates carry years of operating knowledge in knowledge modules, packages, procedures and load plans. We migrate them to Databricks, Snowflake or BigQuery with the repository read as evidence — and with reconciliation, not optimism, deciding when the work is done.
Why move
The drivers are practical — and none of them is that ODI stopped working.
ODI runs on the Java and WebLogic stack, with its own patching, certification and upgrade treadmill. Many estates sit on versions already outside Premier Support, where every change carries risk and every deferral compounds it.
The estate pays twice: Oracle middleware licensing, plus the database compute the transformations actually execute on. Modern platforms run the same workloads natively, priced on consumption.
ODI specialists are leaving the market faster than they are being replaced. Every year the estate becomes harder to change safely — and the knowledge concentrated in a few people becomes the biggest operational risk.
When the organisation consolidates on Databricks, Snowflake or BigQuery, ODI’s agent-and-repository architecture becomes the outlier: separately operated, separately skilled, and outside the platform’s governance and lineage story.
The estate
Anyone quoting an ODI migration by object count has not read the repository. These are the constructs that carry the behaviour — and each one needs an explicit answer in the target.
IKM, LKM, CKM and JKM define how data actually loads — incremental update, SCD2, control append — plus every custom variant written over the years.
The declarative dataflow: joins, filters, expressions, lookups and datastores, resolved through knowledge modules into generated code at run time.
Orchestration steps, and the compiled scenario copies that are what production actually executes — often drifted from their design-time source.
Hand-written multi-step logic: SQL, OS commands, file transfers, Jython — the constructs where business rules hide outside any mapping.
Hierarchical run trees with parallelism, restart behaviour and exception steps that the target orchestration has to reproduce deliberately.
Runtime state, refresh queries and reusable expressions threaded invisibly through mappings, packages and load plans.
Servers, schemas and the dev/test/prod indirection every object resolves through — nothing in ODI names a connection directly.
Where and when everything runs: agents, schedules and the operational calendar the business has shaped around them.
Change capture wired through JKMs, subscriber tables and journal views — a full subsystem, not a checkbox.
By the numbers
construct families carry the behaviour of an ODI estate. Each one needs a designed equivalent in the target.
knowledge-module types — IKM, LKM, CKM, JKM — before a single custom variant is counted.
repository credentials required. The analysis runs on exports your team produces from ODI Studio.
rows of business data leave your platform. Trace53 reads metadata; Proof53 never connects to a database.
Trace53, our repository analyzer, parses the exports into a complete estate model across all 9 construct families — knowledge modules, mappings, packages and scenarios, procedures, load plans, variables and user functions, topology and contexts, agents and schedules, and CDC and journalizing: every object inventoried, fingerprinted into pattern families, scored for complexity and checked for orphaned and dead weight. Knowledge modules are classified as stock, modified or custom — because a customised IKM is a different migration problem from a stock one.
Three things about ODI make this analysis non-negotiable. Every connection detail resolves through odiRef topology calls at run time, so no command can be read — or converted — in isolation. Scenarios are code frozen at compile time, so counting them alongside their design objects double-counts the estate. And rejects flow into E$_ tables that naive migrations silently drop.
The output is an assessment report and decision pack: inventory, pattern families, complexity tiers, orphan audit and a modelled effort range — labelled on its face as modelled from estate structure and confirmed on pilot, because a number whose weakness is not stated is worse than no number.
The method
Six stages on the Nexus53 framework — discovery and assessment on Trace53, generation on Shift53, reconciliation on Proof53 — with findings feeding back into scope and patterns throughout.
02 · Shift53
SQL-dialect converters stop exactly where ODI gets interesting: whole procedures can contain not a single SQL-only step. Every non-SQL construct gets a designed target equivalent — behaviour re-implemented, not syntax transliterated.
| ODI construct | What it does | Our approach | Typical target equivalent |
|---|---|---|---|
| IKM Incremental Update | Upsert by key: update matched rows, insert new ones | Translate to declarative merge semantics with an explicit key | MERGE INTO (Delta / warehouse SQL) |
| IKM Slowly Changing Dimension | Maintains SCD Type 2 history | Generate the SCD2 logic explicitly — effective dates, current flags | MERGE-based SCD2 pattern or dbt snapshots |
| IKM Control Append | Truncate-and-append with flow control | Make the append and its dedupe and controls explicit | INSERT + constraints / Auto Loader |
| Lookup operator | Point-in-time reference join inside a mapping | Declare the join and its grain instead of hiding it | JOIN / broadcast join |
| Table function | Database function call in the middle of a flow | Rewrite as a function, view or expression with the same contract | SQL UDF / Spark UDF / view |
| OS command step | Shell action inside a job | Re-home as an orchestrated, audited task | Workflow or jobs task, notebook step |
| File & FTP tools | File choreography around the data: unload, append, transfer, delete | Replace with native governed writes — the temp-file dance is not carried across | Direct write to object storage |
| Custom KM / Java | Client-specific load behaviour accumulated over years | Reverse-engineer the intent; re-implement behaviour, not syntax | Notebooks / libraries / UDFs |
Commitments
Where automation is not possible, we say so early and propose options — the residue is named, never hidden.
Proof53 writes the reconciliation SQL in plain text, a DBA reads it in full, and your team runs it through the toolstack you already trust — on the Oracle side and on the target. Proof53 ingests the returned numbers, compares the two sides against declared tolerances and assembles the sign-off evidence. No credentials, no egress, no business data leaving the platform.
The checks go beyond row counts: schema and type classes, key uniqueness — the defect an under-specified merge produces while the row count stays right — reject flows, aggregates by population, and idempotency: run the migrated pipeline twice and prove the second run changes nothing, which is exactly where ODI’s control-append and a modern merge diverge.
Two rules keep the evidence honest: the tool never invents a warning — every tolerance and accepted difference is declared by a named owner with a reason and an expiry — and two empty tables can never pass; a run with nothing on either side is invalid, not green.
The target
Databricks, Snowflake or BigQuery; SQL, PySpark or dbt. The choice is made per layer and per team — ingestion, core transformation and marts can land differently — and the output is ordinary code in your repository, whichever way it goes.
MERGE INTO silver.customer AS tgt
USING (
SELECT customer_id, source_system, name, email, updated_at
FROM bronze.customer_extract
WHERE load_date = :run_date
) AS src
ON tgt.customer_id = src.customer_id
AND tgt.source_system = src.source_system
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *Your team exports the repository from ODI Studio; we analyse the exports and return the decision pack: inventory, pattern families, complexity tiers, orphan audit, modelled effort and risk. Ask and we will walk you through the same deliverable over an ODI estate we designed, built and run ourselves, so you know exactly what lands before you commit to anything.
The analysis tooling is deliberately boring to security-review: standard-library only, nothing pip-installed, no repository credentials, running inside your environment. Where AI assists with bespoke logic it runs in your own cloud tenancy, on repository metadata rather than business data, with every result reviewed and reconciled before it ships.
Send us your repository stats or describe the estate you need to move.