SAP Business One and Google BigQuery integration

What moves between them

The flow runs one direction, from SAP Business One into Google BigQuery. ml-connector polls the Service Layer on a schedule and reads business partners, invoices, purchase invoices, purchase orders, incoming and outgoing payments, and journal entries, then loads each record type into the matching BigQuery table the customer has defined. Master data such as business partners and items can refresh on a slower cadence while transactional documents like invoices and payments sync more often. SAP B1 stores the internal DocEntry key, which is carried into BigQuery as a stable row identifier. Nothing is written back to SAP Business One, so the ERP stays the system of record and BigQuery is the reporting copy.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the SAP side it posts to the Login endpoint, holds the B1SESSION cookie, and reuses it across requests rather than logging in each time, since the first login costs about five seconds while later calls are near instant. When the Service Layer returns error code -5002 for an expired session it logs in again and replays the failed request. On the BigQuery side it signs a JWT with the service account private key, exchanges it for a Bearer token, and refreshes that token before the one-hour expiry. Reads page through SAP results with $top and $skip and follow the odata.nextLink, and writes use tabledata.insertAll with a per-row insertId so a retry does not create duplicate rows. Each SAP entity is mapped to a target dataset and table with a defined column set, including DocEntry as the row key. Real gotchas are handled directly: SAP B1 is usually on a private network, so the Service Layer port must be reachable or the connector runs on the same network, and on-premise instances often use self-signed TLS certificates that need a configured CA. BigQuery streamed rows can take up to about 90 seconds to appear in tabledata.list, and removing or renaming a column there is destructive, so schema changes are additive. Failed loads retry with backoff and every record carries a full audit trail with error replay.

A real-world example

A regional distribution company with about 150 employees runs SAP Business One for purchasing, inventory, and finance across three warehouses. The finance and operations teams wanted dashboards combining spend, open purchase orders, and aging payables, but the Service Layer returns 20 records a page and is tuned for transactions, not analytics, so every report meant slow exports to spreadsheets that were stale by the next day. With SAP Business One and Google BigQuery connected, invoices, purchase orders, and payments load into BigQuery tables on a schedule, and the analytics team writes SQL and builds dashboards directly on warehouse data. Reporting no longer competes with the ERP for resources, and the numbers refresh on the cadence the business needs instead of by hand.

What you can do

• •Load SAP Business One invoices, purchase invoices, purchase orders, and payments into Google BigQuery tables on a schedule.
• •Sync business partner and item master data so warehouse reports join transactions to vendors, customers, and SKUs.
• •Carry the SAP DocEntry key into BigQuery as a stable row identifier for clean updates and deduplication.
• •Bridge SAP B1 session-cookie login and the BigQuery service account token, refreshing each before it expires.
• •Poll the Service Layer with retries, backoff, and a full audit trail, with error replay on any failed load.

Questions

Which direction does data move between SAP Business One and Google BigQuery?

Data moves one direction, from SAP Business One into Google BigQuery. The connector reads finance and master records from the Service Layer and loads them into warehouse tables. Nothing is written back to SAP, so the ERP stays the system of record and BigQuery holds the reporting copy.

Does this use webhooks or scheduled polling?

It uses scheduled polling. BigQuery sends no webhooks at all, and SAP Business One only supports them on version 10.0 FP 2602 and later with server-side admin setup. The connector polls the Service Layer on a cadence you set, using an UpdateDate filter to pick up new and changed records.

How are duplicate rows avoided when a load is retried?

Each row written to BigQuery carries an insertId, which gives best-effort deduplication within roughly a one-minute window. The connector also carries the SAP DocEntry key into the target table as a stable identifier. Together these keep a retried load from creating duplicate records.