Data Engineering · 7 min read
Getting SAP Data Into Power BI Without Breaking the Pipeline
How a metadata-driven Azure Data Factory framework turned a locked-up ERP into a reporting layer the business actually uses.
- Azure Data Factory
- SAP OData
- ADLS Gen2
- Azure SQL
- Power BI
- Medallion Architecture
- Azure Key Vault
- CI/CD
Before
- Reporting meant raising an extract request and waiting
- Numbers were days old by the time they reached a meeting
- Every new table was a development project
- Large pulls failed — or worse, silently returned partial data
- Someone had to remember to run it
After
- Power BI reports refresh daily, unattended
- Analysts query a proper database instead of waiting on exports
- New tables added by configuration, not code
- Loads page through safely and stop when complete
- Runs on a schedule; failures raise an alert automatically
The pipeline, end to end
SAP
ERP system of record, reached through its OData service layer
Azure Data Factory
Parent/child pipelines driven by metadata, with pagination and incremental loads
ADLS Gen2
Staging across bronze, silver and gold layers
Azure SQL
Reporting database, merged and current
Power BI
Downstream reports the business reads
Most businesses running SAP have the same quiet problem. The data is all there — every order, every material movement, every invoice — and almost nobody can get at it.
Reporting means raising a request. Someone with the right access runs an extract, drops a spreadsheet into email, and by the time it reaches the meeting it is already a few days old. Ask a follow-up question and the cycle starts again. Decisions end up being made on the last version of the truth anyone could get hold of, rather than the current one.
This is a case study of fixing exactly that: moving SAP data into a reporting database on a daily schedule, and putting Power BI on top of it. It covers what was built, the four decisions that mattered most, and where the traps are — because most of the cost in a project like this comes from the traps rather than the build.
The problem, stated plainly
The business needed reports. The data lived in SAP. Between those two facts sat a set of very real constraints.
SAP is built to run operations, not to answer analytical questions. It guards its data carefully, it doesn't hand it over in bulk, and querying it heavily during working hours affects the people trying to do their jobs in it. Meanwhile, the reporting requirement wasn't going to stay still — it started with a handful of tables and everyone involved knew more would follow.
That second point shaped the entire design. Building a pipeline for the tables requested on day one is straightforward. Building something that absorbs the next twenty requests without another development cycle each time is a different exercise, and it's where the actual value is.
What was built
A daily pipeline in Azure Data Factory, landing SAP data through Azure Data Lake Storage Gen2 into a SQL database, with Power BI reading from there.
Data moves through a medallion architecture — bronze, silver, gold:
- Bronze holds the data exactly as SAP handed it over, untouched. It's the safety net: if a transformation rule turns out to be wrong three months from now, everything downstream can be rebuilt from here without going back to SAP for the data again.
- Silver is where it becomes trustworthy — typed properly, deduplicated, standardised, with SAP's technical field names translated into something a human recognises.
- Gold is shaped around the questions the business actually asks, so Power BI reports open quickly instead of grinding through joins at query time.
Around that sit four decisions that did more for the outcome than anything else.
Adding a table shouldn't mean building a pipeline
The most common way this kind of project becomes expensive is one pipeline per table. Twenty tables, twenty near-identical pipelines. Every new request is a development ticket, a test cycle and a release. Worse, when something shared needs to change — retry behaviour, logging, how failures are handled — it has to be changed twenty times by hand.
Instead I used a parent–child pipeline pattern driven by metadata.
A configuration file holds the details for each table: which entity to pull from SAP, where it lands in the destination, whether it's a full or incremental load, how the columns map, and where the last run finished. The parent pipeline reads that configuration and works through the list. The child pipeline does the actual work — one pipeline, fully parameterised, handling every table it's given.
The practical consequence: adding a new table is a configuration change, not a development project. No new pipeline, no regression testing of the feeds that already work, no release cycle. What would normally be a multi-week request becomes an afternoon.
This costs more upfront. It's repaid by roughly the third table, and it keeps paying with every one after that.
Working through SAP's front door
Azure Data Factory ships with several SAP connectors, but they weren't a viable route in this environment. The supported path here was SAP's OData service layer, and ADF's OData connector.
That turned out to be the right call for reasons beyond necessity. Going through SAP's own service layer means the integration sits inside SAP's authorisation model rather than around it, and it stays on a path SAP supports — so a system upgrade doesn't silently break the pipeline. Solutions that reach past a system's official interface are quicker to build and a liability for as long as they exist.
The OData route comes with one significant catch, which leads directly to the next decision.
Pagination, or why the pipeline kept breaking
SAP's OData service will not hand over an unlimited number of records in one response. Ask for a large table in a single request and you get a failure, a timeout — or, far more dangerously, a partial response that the pipeline treats as success.
That last failure mode is the one to worry about. The pipeline goes green. The report refreshes. The numbers are incomplete and nobody finds out until someone questions a figure weeks later. By then confidence in the whole platform is damaged.
The fix is pagination. Rather than asking for everything at once, the pipeline requests a manageable batch, lands it, then asks for the next — keeping its place each time, and stopping automatically when a batch comes back short, because a short batch means it has reached the end.
The important detail is that the loop decides for itself when to stop. Nobody knows in advance how many records changed on a given day; it might be fifty, it might be half a million. A fixed number of requests would either truncate on a heavy day or waste calls on a quiet one. A safety ceiling is still enforced so a misbehaving source can never leave it looping indefinitely.
Only move what changed
Even with pagination, reloading entire SAP tables every night is wasteful. Incremental loading means the pipeline only pulls records created or changed since the last successful run.
SAP stamps records with when they were last modified. The pipeline remembers where it finished, asks only for what's moved since, and updates existing rows rather than duplicating them. The watermark advances only after the load completes — so a failure means the next run retries the same window cleanly, rather than resuming into a gap and silently losing data.
The effect is threefold, and all three matter to whoever signs off the invoice:
- Runtime drops sharply — a fraction of the work each night
- Azure cost falls, because Data Factory bills by data movement units consumed
- Load on SAP falls, which matters because it's a live system people are working in
Running it without babysitting it
A pipeline that needs someone to remember to run it isn't automation. Three things make this genuinely hands-off.
Scheduled triggers. The pipeline runs on a schedule. Nobody starts it, and nobody has to remember it exists.
Failure notifications. When a run fails, an alert goes out with the actual error. This matters more than it first appears: the real risk with automation isn't failing, it's failing quietly. A system that silently stops updating while everyone keeps trusting the reports is far more damaging than one that fails loudly at 2am.
CI/CD across Dev, UAT and Production. Nothing is edited directly in the live environment. Changes are built in Dev, validated in UAT, then promoted to Production through a release pipeline — with full change history and a one-step rollback if something goes wrong.
Credentials in Azure Key Vault. Every connection string and password is fetched from the vault at the moment it's needed. Nothing sensitive is ever written into a pipeline definition or a source control repository, so a copy of the code is worthless to anyone who obtains it. Rotating a password happens in the vault, with nothing to rebuild.
The outcome
SAP data lands in the SQL database on a daily schedule. Power BI reads from the gold layer and reports refresh on current data. Analysts write queries against a proper database instead of waiting on extracts, and reporting load sits on the warehouse rather than on the ERP.
The measure of success is deliberately boring: the data is simply correct and current every morning, and nobody has to think about it.
