Somebody in finance downloads a transaction report from SAP every Monday, pastes it into Excel, and that becomes the basis of the weekly numbers. You want it in Power BI instead, refreshing on its own.
Technically this is a solved problem — Microsoft ships connectors and a developer can have SAP data on screen in an afternoon. The difficulty is that the route chosen in that afternoon determines your latency, your licensing exposure, and how much breaks at the next upgrade. Those bills arrive months later.
Why the Weekly Download Persists
The manual export survives because it works and because the alternative looks like an IT project. It also has costs nobody counts: it happens on one person's schedule, it stops when they are on leave, and every consumer of that spreadsheet is trusting a copy-paste nobody verified.
Automating it is worth doing. Doing it badly means either a reporting layer that falls over at the next SAP change, or a query load on a production system that your Basis team will eventually trace back to you.
Answer Two Questions Before Choosing
These eliminate most of the options on their own.
How fresh does it actually need to be? Not how fresh would be nice. What decision depends on it. Daily is fine for most financial reporting; intraday matters for inventory and production. The cost difference between those answers is substantial, and teams routinely specify the expensive one by reflex.
How much transformation is needed? If reporting needs joins across modules, currency conversion and hierarchy flattening, doing that inside Power BI will be slow and hard to maintain no matter which connector feeds it.
The Four Routes
| Route | Latency | Load on SAP | Survives upgrade? |
|---|---|---|---|
| Direct connector to tables | Live or scheduled | High | Poorly |
| Through SAP BW | Inherits BW schedule | Low | Reasonably |
| Extract to a warehouse | Scheduled, any cadence | Low, off-peak | Well |
| CDS views / OData | Scheduled | Medium | Well |
Direct connector to SAP tables
Quickest to a working report and the usual starting point. Power BI connects through the application server to the underlying tables.
The cost: SAP table structures are not built for analytical queries, so joins across large modules are slow and load heavily on a production system. Pulling directly from a live ERP during business hours is a conversation to have with your Basis team before, not after.
It also binds reports to physical table names, and that logic lives inside a .pbix file rather than anywhere version controlled. Treat this as a prototype route.
Through SAP BW or BW/4HANA
If BW is already in place, the modelling is done: queries expose curated providers with hierarchies, currency conversion and aggregation handled.
The cost: you inherit BW's refresh cadence. If a cube loads overnight, your dashboard is a day behind regardless of anything downstream. You also inherit BW's modelling decisions, and changing them is a BW project rather than a Power BI one.
Extract to a warehouse first
Data is extracted on a schedule into Synapse, SQL Server or Snowflake, transformed there, and Power BI reads the warehouse. This is what I recommend for anything expected to still be running in three years.
Transformation logic lives in version-controlled pipelines rather than inside report files. Query load moves off the ERP. You can join SAP data to sources SAP knows nothing about, which is usually where the interesting analysis is. And when SAP changes, one extraction layer needs fixing rather than every report.
The cost is real engineering effort up front. This is not a two-week project; it is the thing that stops you rebuilding in eighteen months.
CDS views and OData
On S/4HANA, Core Data Services views expose semantically meaningful business entities rather than raw tables, consumable over OData. You get SAP's own definition instead of reverse-engineering one from table joins.
The cost: OData is not built for bulk extraction. Millions of rows through it is slow and fragile, and paging large datasets reliably takes more engineering than expected. Well suited to moderate volumes and incremental loads; poorly suited to full history reloads.
In practice this often pairs with route three — CDS views as the interface, a warehouse as the destination.
The Licensing Question Nobody Raises Early
Extracting SAP data into a system that then serves it to other people can carry licensing consequences, and technical evaluations skip this almost universally. SAP's indirect or digital access provisions concern data consumed through systems other than SAP, and a Power BI environment reading SAP data is exactly that pattern.
I am not going to tell you what your contract says — these agreements vary enormously by vintage, region and negotiation, and a 2012 ECC contract reads nothing like a recent RISE agreement. What I will say is that the question belongs at the start.
Ask your SAP account contact in writing before the architecture is fixed. The cost of asking is one email. The cost of not asking is discovering your position after the business depends on reporting you have already built, when your negotiating leverage is considerably worse.
What Breaks at Upgrade
Each route fails differently when SAP changes, and this is the clearest practical argument for an extraction layer.
Direct table connections break hardest. Structures change between releases, and because the logic sits inside report files you find out when a report fails rather than during testing. The fix is spread across every report that touched those tables.
BW queries are steadier, since the query interface is a contract of sorts, though BW itself gets upgraded and provider changes ripple downstream. CDS views are steadier again, because SAP maintains them as semantic objects — custom views you wrote carry the same risk as any custom code.
A warehouse route concentrates the breakage. When SAP changes, the extraction layer is fixed once and every downstream report keeps reading the same warehouse tables. That containment is worth more than the connector performance differences people usually compare.
Choosing
- Prototype, one module, small volume: direct connector — and plan to replace it.
- Mature BW in place and daily latency acceptable: BW queries.
- Multiple modules, joins to non-SAP data, or a three-year horizon: warehouse.
- S/4HANA, moderate volumes, prefer SAP's own semantics: CDS views, usually into a warehouse.
The mistake to avoid is picking the direct connector because it demos well, then finding at scale that reporting load is affecting order entry. That discovery tends to arrive during month end.
Scoping the First One Sensibly
Do not start by replacing every SAP report at once. Start with the one spreadsheet that causes the most pain, and treat it as a way to learn what your SAP data actually contains.
You will find things that are not in any specification. Master data with trailing spaces that break joins. A company code nobody uses any more but which still carries balances. Document types that finance excludes by convention without that convention being written down anywhere. Every SAP extraction project finds a version of these, and finding them on one report is much cheaper than finding them on twelve.
Run the automated output alongside the manual export for two cycles and reconcile them line by line. When they tie, you have both a working pipeline and something more valuable: documented proof of what the numbers mean, which is what makes the second and third reports quick to build.
Involve whoever currently does the manual export. They hold the undocumented knowledge — which columns matter, which rows they quietly delete, which month behaves oddly. That knowledge is the difference between a pipeline that reconciles and one that is nearly right.
What We Would Do
We map which modules matter and how fresh each genuinely needs to be, raise the licensing question early, then build the extraction and transformation layer with the logic documented and version controlled rather than buried in a report file.
On the manufacturing engagement behind our GM work, rebuilding that layer cut supply chain ETL processing by 85% — not through a faster connector, but by moving the work into a layer designed for it.
Phoenix Solutions connects SAP, Oracle, Dynamics and custom ERPs into governed reporting layers. See ERP integration, the manufacturing case study, or how we work across manufacturing.