Extracting SAP data for analytics

Every extraction option sits on one line. At one end you read the underlying tables directly, which is quick to build, needs no cooperation from the SAP team, and gives you data with no business meaning attached. At the other end you consume views SAP has released for extraction, which takes longer to set up, requires the SAP team, and gives you data that means something and keeps meaning it after an upgrade.

Almost every regrettable decision in this area comes from picking the first end because the second end needed a conversation with a team that was busy.

The arithmetic is worth stating. A direct table extract might take three days to build. It will then need reworking at some proportion of upgrades, each rework costing a few days plus the incident that revealed it. Over five years and ten releases, the cheap option is not cheap, and the cost lands as unplanned interruptions rather than as project work, which is the worst way for cost to arrive.

The third row is the right default for most analytics work, and it is worth understanding why rather than taking it on trust. SAP publishes views that are explicitly released and marked as extraction-enabled, and both the replication tooling and the data platform tooling consume the same mechanism. SAP describes the pattern for its replication server as connecting to a source system to transfer data from C1-released and extraction-enabled CDS views residing in the source, adding a subscription to an existing extraction process or creating a new one.

The equivalent path for its data platform works the same way, described as a component that allows multiple consumers to subscribe to a CDS view, including options for delta recording and delta consumption.

The word to notice in both is subscription. Multiple consumers share one extraction process on the source. That is the performance argument for doing it properly: five teams reading the same view cost the source system roughly one extraction, whereas five teams writing their own table queries cost it five.

The fifth row is worth checking before building anything. If your warehouse vendor ships a supported connector, using it transfers the maintenance burden to the vendor. Google documents a connector for replicating SAP data into BigQuery, with an explicit planning guide covering sizing and prerequisites, and Microsoft documents an SAP change data capture connector that reads through the operational data provisioning framework.

The first version of every extract is a full load, because it is simple and it works. It stays acceptable until the table gets large, at which point the nightly job starts overlapping with the business day and somebody asks why the system is slow at 7am.

The transition to delta is not a tuning exercise, it is a design change, and it is much cheaper to do at the start than after a year of downstream logic has been built assuming a full refresh.

The specific thing to get right is deletions. A delta feed that captures inserts and updates but not deletions produces a warehouse where cancelled records live forever. Nobody notices for months, because the count only ever goes up and nothing looks broken. Then somebody reconciles a total against the source and the numbers do not match, and by then there is no way to know when the divergence started.

So ask the question explicitly during design: how does a deletion in the source become a deletion in the target. If there is no answer, you do not have a delta design, you have an append log wearing one.

Somebody will ask for real time. Almost nobody needs it, and the cost difference between daily and near-real-time is large enough that it deserves an actual conversation rather than a default.

Make it concrete with a question about consequence: what decision changes if this data is four hours old instead of four minutes. For a finance report closing monthly, nothing. For a warehouse allocating stock against live orders, everything. Those two answers should not produce the same architecture and frequently do, because nobody asked.

The costs that scale with latency are not just infrastructure. A daily batch that fails can be rerun in the morning by whoever notices. A continuous stream that fails needs someone on call, needs alerting that distinguishes a blip from an outage, and needs a documented catch-up procedure. That is an operating model, not a pipeline.

Write the answer into the requirement as a number with a reason attached. Four hours because the planning meeting is at 9am is a requirement. Real time because the executive said real time is a budget line waiting to happen.

Land the extracted data as close to its source shape as you reasonably can, then model in the warehouse. The temptation is to transform during extraction because it looks efficient, and it creates a system where the business logic lives in an integration tool that no analyst can read or change.

The separation is worth defending. Extraction moves data and does not interpret it. Modelling interprets and does not move. When those two jobs are mixed, every model change becomes an integration change, which means a different team, a different release process and a two-week turnaround for what should be an afternoon.

The modelling itself is ordinary once the data is in. Star schemas, a conformed date dimension, explicit grain per fact table. That is the same discipline any analytical model needs, and SAP data is not special in this respect, whatever its source complexity suggests.

The pattern also holds if your target is a query engine rather than a semantic model: land raw, transform in the warehouse, keep the load idempotent so a rerun is safe. The property that matters is that any load can be repeated without duplicating anything, because at some point it will be repeated at 2am by someone tired.

This is the part that is missing from most plans and it is the part that sets the elapsed time. An extraction project needs a service account, an authorisation grant, a security review, a sizing sign-off from whoever runs the SAP system, and usually a network change. None of those are your team's work and all of them are on your critical path.

The SAP basis or operations team in particular has a legitimate interest in saying no. An extraction process consumes resource on a production system, and they have been burned before by a reporting query that took down a business process. Arriving with a sizing estimate and a proposed schedule window makes that conversation short. Arriving with a request for full read access makes it long.

Budget elapsed time for it separately from effort. Three days of pipeline work inside six weeks of approvals is a normal shape for a first extract, and reporting only the three days is how a plan acquires a slip nobody can attribute. That distinction between effort and elapsed time is the same one that makes an integration estimate honest.

An enterprise transit technology provider running a platform migration across 154 SAP HANA data sources found exactly this: the conversions were estimable and the inventory, ownership and access work was where the schedule actually went. If you are standing up an analytics estate on top of an ERP, the sequencing and the vendor coordination is the work, and it is a delivery problem before it is a technical one. The companion piece for HR data, which has its own extraction constraints, is the workforce reporting version of this problem.