SAP Business One and Microsoft Power BI integration

What moves between them

Data flows from SAP Business One into Microsoft Power BI on a polling schedule. ml-connector extracts invoices, purchase orders, journal entries, items, and business partners from SAP Business One, maps them to Power BI table schema, and pushes rows into Power BI push datasets. The sync typically runs daily or on demand, ensuring Power BI reports and dashboards reflect the latest financial and operational data from the ERP. SAP Business One is the source of truth; Power BI is a read-only analytics destination.

How ml-connector handles it

ml-connector stores the SAP Business One Service Layer URL per customer and authenticates by posting credentials to the Service Layer /Login endpoint to obtain a session token. It caches the token and refreshes it before expiry to avoid the -5002 timeout error. For Power BI, ml-connector stores the OAuth 2.0 service principal credentials (client ID and secret) encrypted and exchanges them for a Bearer access token on each session start, refreshing when the token expires. It polls SAP Business One entities using OData queries with $filter on UpdateDate to retrieve only new or changed records since the last sync. Before pushing into Power BI, ml-connector maps SAP Business One field names and data types to Power BI column schema, handles the OData v4 JSON structure, and batches rows for efficient POST operations. Since SAP Business One sessions are tied to server-side state, ml-connector reuses sessions for the entire polling window rather than re-authenticating per request. Power BI push datasets are created with defined schema, and ml-connector validates that incoming rows match the expected columns and datatypes. On SAP Business One on-premise instances with self-signed TLS certificates, ml-connector validates the certificate chain appropriately. Every record flows through the audit trail, capturing source entity, Power BI dataset, row count, and any mapping failures.

A real-world example

A specialty manufacturing company runs SAP Business One at their facility to manage orders, inventory, and accounts payable. The finance team and operations leadership need up-to-the-day visibility into invoice volumes, purchase order spend by vendor, and inventory levels. Before Power BI integration, they exported weekly reports from SAP Business One by hand and built Excel dashboards that were stale by day two. With SAP Business One connected to Power BI, daily overnight syncs populate Power BI datasets with invoices, purchase orders, and inventory. The CFO and operations manager now have live Power BI dashboards showing current spend, aging payables, and stock levels. Month-end close is faster because headcount and cost data are already reconciled.

What you can do

• •Sync invoices, purchase orders, and journal entries from SAP Business One to Power BI on a daily or custom schedule.
• •Transform SAP Business One OData records into Power BI push dataset rows with automatic schema mapping and validation.
• •Authenticate SAP Business One with session tokens and Power BI with OAuth 2.0 service principal credentials, managing token refresh automatically.
• •Poll SAP Business One using OData UpdateDate filters to retrieve only new or modified records, minimizing data transfer and API load.
• •Deliver a full audit trail of every sync, including record counts, field mappings, and any validation or transformation errors.

Questions

What data moves from SAP Business One to Power BI?

Invoices, purchase orders, journal entries, items, business partners, dimensions, and profit centers flow from SAP Business One into Power BI push datasets. The connector extracts data on a polling schedule and maps SAP Business One fields to Power BI columns. Power BI is a read-only analytics destination, so data flows one direction only.

Does SAP Business One's on-premise deployment require special network setup?

Yes. Because SAP Business One runs on each customer's own server, you must provide the Service Layer URL (hostname, port, and path) to the connector. The connector reaches out to that URL over HTTPS. Self-signed TLS certificates are common on on-premise instances and are handled appropriately. If your SAP Business One instance is not exposed to external networks, you may need to deploy ml-connector in your own network or use a gateway.

How does the integration handle Power BI push dataset schema?

The connector defines Power BI push dataset tables with explicit column names and datatypes before pushing rows. Every record from SAP Business One is validated against the expected schema, and any mismatches are logged in the audit trail. If SAP Business One adds new fields, the connector can expand the Power BI schema, but this requires stopping the sync, aligning the schema, and restarting.