Key takeaways
- Choose the extraction method by what the source system can support, not by what is fashionable.
- Load full once, then move to deltas with a reliable change marker.
- Schedule around business peaks and month-end close.
- Use a dedicated, least-privilege technical user and log every extraction.
Two teams, one system
The analytics team wants every table, every night, as fast as possible. The SAP basis team wants the system responsive for people posting invoices and shipping goods. Both are right, and a badly designed extraction is how they end up in conflict: a heavy query at the wrong hour slows down order entry, and the first extraction project gets a bad reputation.
Safe extraction is mostly about deciding early what to pull, how, and when, and agreeing on those rules with the SAP owners before writing code.
Pick the method the source supports
There is more than one way to get data out of SAP, and the right choice depends on the product, version, licensing and the skills of the team. Whichever you choose, confirm with SAP or your support partner that it is supported for your release and license, since some direct table-reading approaches are restricted in certain setups.
- Standard extractors and data sources designed for analytics, which usually carry business logic and delta support
- Views and CDS-style semantic layers exposed for reading, where your version provides them
- OData or API endpoints for smaller, business-object-level pulls
- Replication tools that read change logs and stream changes, typically with more setup and licensing considerations
- Direct database reads on SAP Business One's underlying database, where allowed, using read-only access and careful query design
Prefer sources that already understand SAP business logic, such as currency, units, status codes and organizational structure, over raw tables that make you rebuild it.
Full load once, deltas forever
A full load of every table each night is simple and expensive. Use it for the first load and for small reference data. For large transactional tables, move to delta loads that pick up only what changed.
Deltas need a trustworthy change marker: a change-log mechanism, a last-changed timestamp, or a document number sequence. Test the marker with edge cases. Are deletions visible? Do late-posted documents change earlier periods? Does a backdated correction get a new timestamp? Where the answer is uncertain, add a periodic reconciliation, for example a weekly re-pull of a rolling window or a row-count and total comparison against the source.
- Land raw extracts unchanged in a staging area, with the load time and batch id.
- Transform in your own platform, not inside SAP.
- Make each load idempotent, so a rerun after a failure does not duplicate rows.
- Store a watermark for each table and update it only after a successful load.
- Reconcile key totals against a trusted SAP report after each significant change.
Schedule like a good neighbor
Ask the SAP team when the system is busiest: posting peaks, batch jobs, payroll, and above all month-end close. Schedule heavy pulls outside those windows and throttle them. Use smaller packages, sensible page sizes and limits on parallel sessions. Start conservatively and increase only when monitoring shows headroom.
Watch both sides. On the SAP side, track dialog response times and lock waits during your loads. On your side, track duration, row counts and failures. If a load exceeds its window, it should stop cleanly and alert someone, not keep running into business hours.
Access and audit
Create a dedicated technical user for extraction with the narrowest role that works: read access to the specific objects, no dialog logon, no write authorizations. Keep credentials in a secrets manager and rotate them on a schedule. Log each run with who, what, when and how many rows, and give the SAP team visibility into that log.
Also decide early which fields should never leave SAP or must be masked, such as personal data and bank details, and apply that rule at extraction time rather than downstream.
