Post 09 of 19
Connecting SAP, Oracle, and beyond to a modern analytics stack
SAP, Oracle, Microsoft Dynamics, Unit4 — ERP systems are where the most critical business data lives: orders, inventory, production records, financials, HR. They are also, notoriously, some of the hardest systems to integrate with a modern analytics stack. The data models are complex, the APIs are often legacy or proprietary, and the systems were designed for transaction processing — not analytical querying. Despite this, ERP data integration is the foundation of almost every serious BI engagement we run. Get it wrong, and the rest of the platform inherits the pain: brittle pipelines that break on every ERP patch cycle, dashboards that go stale silently, finance teams that lose trust in the warehouse because last quarter's numbers do not match SAP. Get it right, and the warehouse becomes a genuine reflection of the operational ledger, with the analytical flexibility that the ERP itself cannot offer.
ERP systems were built to process transactions efficiently, not to support analytical workloads. Querying an ERP database directly for reporting purposes can degrade transactional performance — which is why most ERP vendors either do not allow direct database access, or strongly discourage it. The data models are also extraordinarily complex: SAP's data model, for example, has tens of thousands of tables, often with cryptic German-abbreviated field names that have not been renamed since the 1990s. Understanding which tables hold the data you need, how they relate to each other, and which fields contain reliable data requires deep domain expertise that few generalist data engineers possess on day one. Add to this the challenge of handling change data capture (tracking what changed, when, and how) and managing schema changes when ERP upgrades happen, and you have a genuinely difficult technical problem. It is the kind of problem where five days of careful upfront design saves five months of remediation later.
Complex, undocumented data models with thousands of tables
No direct database access in cloud ERP systems (SAP S/4HANA Cloud, Oracle Fusion)
API rate limits and extraction performance constraints
Change data capture complexity — tracking incremental changes reliably
Schema changes on ERP upgrades breaking existing pipelines
Data quality issues in source systems (duplicate records, inconsistent coding)
The modern approach to ERP integration uses a layered architecture: extraction, staging, transformation, and presentation. The extraction layer uses the ERP's official APIs or connectors — BAPI/RFC for SAP, REST APIs for Oracle Fusion, OData for Dynamics — to pull data incrementally. This extracted data lands in a staging area (often a cloud storage bucket or a staging schema in your data warehouse) before any transformation happens. The transformation layer, typically built in dbt, applies business logic: joining tables, defining metrics, handling edge cases, and producing clean analytical models. The presentation layer (Power BI, Tableau) reads from these clean models — never directly from the ERP. This separation of concerns is not architectural purity for its own sake; it is what allows you to upgrade the ERP without breaking dashboards, to rebuild a metric without re-extracting data, and to give analysts a curated environment without exposing them to BSEG or VBAP directly.
Common Mistakes
Querying ERP databases directly for BI reports
Using vendor-provided reporting tools that cannot handle cross-system analytics
Building bespoke extraction scripts that break on ERP upgrades
No staging layer — transforming data in-flight during extraction
Ignoring change data capture — full refreshes at scale are too slow
Best Practices
Use official APIs/connectors — BAPI/RFC, OData, REST
Always stage raw data before transforming
Build transformations in dbt — version-controlled, testable, documented
Implement CDC for high-frequency transaction tables
Test data quality at every layer with automated assertions
SAP is by far the most common ERP system we integrate with. The recommended extraction approach for on-premise SAP is BAPI/RFC calls via Python (using the pyrfc library) or a dedicated connector like SAP's own ABAP data extraction services. For SAP S/4HANA Cloud, the OData APIs are the official integration route. Key tables to understand: BKPF/BSEG for financial documents, VBAK/VBAP for sales orders, MKPF/MSEG for material movements, AUFK/AFKO for production orders. Delta BI's SAP practice has mapped the extraction logic for the most common analytical use cases — financials, inventory, production, procurement — so we are not starting from scratch on every engagement.
Use BAPI/RFC for on-premise SAP; OData APIs for S/4HANA Cloud
Extract financial data via BKPF/BSEG — never query FI tables directly under load
Use SAP's LO extraction cockpit (LBWG) for logistics data with delta queues
Build a SAP-specific staging schema that preserves raw field names for auditability
Test every extraction pipeline against your change management schedule
A successful ERP integration is not just one that extracts data — it is one that extracts it reliably, completely, and with full auditability. We measure integration health with three metrics: completeness (what percentage of expected records arrived in the last extraction run?), freshness (how old is the most recent data in the warehouse?), and accuracy (what percentage of spot-checked records match the source system exactly?). Automated data quality tests in dbt run on every pipeline execution and alert within minutes if something breaks. This is what separates a fragile integration held together with spreadsheets from a production-grade analytics foundation. The audit trail matters too: every reconciliation question — 'why does warehouse revenue for March differ from SAP' — should be answerable in minutes from logged metadata, not days of forensic SQL.
After running integration projects across maritime, logistics, energy, and manufacturing clients, certain failure patterns repeat. Naming them in advance is the single most useful intervention; they are almost always avoidable, but they require organisational decisions, not just technical ones.
Treating the ERP team and the analytics team as separate stakeholders. The integration is a joint product; if the ERP team is not engaged, the analytics team will inevitably ship a model that misrepresents the business logic.
Underestimating master data complexity. Customer hierarchies, material masters, and chart of accounts mappings are where most integration projects discover hidden inconsistencies — usually well after the schedule has been committed.
No rollback plan for ERP upgrades. Every major ERP release will break something in your pipeline; integrations that survive long-term have explicit pre-upgrade regression tests and a rollback path.
Mixing operational and analytical use cases in the same pipeline. The two have different SLAs, different consistency requirements, and different access patterns. Trying to serve both from one extraction tends to please neither.
No domain glossary. The business terms used in dashboards ('open order', 'shipped revenue', 'backlog') need to map precisely to ERP states. Without a glossary, every analyst makes a slightly different mapping and the metrics drift.
A pre-mortem session — sitting down at project kickoff and asking 'how is this going to fail?' — is one of the cheapest insurance policies in BI delivery.
The bottom line
ERP data integration is hard — but it is the foundation of almost every meaningful BI project for industrial and commercial businesses. Done properly, with a staging layer, a transformation framework like dbt, automated quality testing, and a clear separation between operational and analytical concerns, it produces a reliable, auditable, and extensible analytics foundation that can carry an organisation for a decade. Done poorly, it produces a brittle pipeline that breaks at every patch cycle and quietly erodes leadership's trust in the warehouse. The investment difference between the two is not large — it is a matter of architectural discipline applied at the right moment. Delta BI has built ERP integrations across SAP, Oracle, Dynamics, and Unit4 for clients in maritime, logistics, energy, and manufacturing. If you are stuck on this problem, talk to us.
Start with the decision
Tell us about your challenge. We will reply to arrange an assessment and identify a practical route to delivery.
Request a data assessment