Sage 100 and Microsoft Power BI integration

What moves between them

The flow runs from Sage 100 into Power BI. ml-connector polls Sage 100's BOI interface on a schedule you set - typically AP invoices every 15 minutes, GL accounts daily, and vendors hourly - and pushes the transformed data into Power BI push datasets. Each push refreshes the Power BI tables with the latest balances, invoice status, and vendor master data. The flow is one-direction (Sage 100 to Power BI only) because Power BI is an analytics destination, not a transaction source.

How ml-connector handles it

ml-connector runs as a Windows service on your Sage 100 server (or a server with network access to it). It stores the Windows service account username and password encrypted, and uses those credentials to call the Sage 100 BOI interface through the local COM layer. Because Sage 100 has no webhooks and both systems use different auth models - Sage 100 uses stateless username/password, Power BI uses OAuth2 bearer tokens - ml-connector handles the polling cadence and the token lifecycle. It polls Sage 100 on your schedule, extracts the company code and multi-segment GL account structure, maps the data to Power BI table columns, and POSTs rows to the push dataset endpoint. If a POST fails, ml-connector retries with backoff. Sage 100 uses COM record-locking for concurrent writes, so high-frequency polls include backoff to avoid contention. Power BI tokens are refreshed before they expire, so the push never stalls waiting for a new token. Every transaction is audited so you can trace which records moved and when.

A real-world example

A mid-sized wholesale distributor runs Sage 100 on premises for AP, GL, and inventory. The finance manager currently exports an AP aging report from Sage 100 once a day, uploads it to Excel, and re-keys totals by vendor into a Power BI dashboard for the CFO. With Sage 100 and Power BI connected, the dashboard refreshes every 15 minutes with the latest AP invoices, invoice status, and payment terms. The CFO sees vendor aging in real-time, catches early payment discounts, and the daily manual export step disappears.

What you can do

• •Poll Sage 100's accounts payable, general ledger, and vendor records on a schedule aligned with your posting cycle, encrypted and audited.
• •Push AP invoice data, GL account balances, and vendor master records into Power BI push datasets via OAuth2 so your finance team sees live dashboards.
• •Map Sage 100's multi-segment GL account structure and company codes to Power BI columns so reports are accurate by cost center and account.
• •Run the bridge agent as a Windows service on your Sage 100 server with encrypted credentials, handling COM record-locking and retries automatically.
• •Refresh Power BI datasets on a cadence that syncs with your payables and GL posting without manual intervention or spreadsheet exports.

Questions

Does the bridge agent have to run on the Sage 100 server itself?

The agent must run on a Windows machine with network access to your Sage 100 server and the COM layer. Best practice is to run it on the Sage 100 machine itself to avoid latency and complexity. ml-connector handles the Windows service installation, credentials, and lifecycle.

Which Sage 100 records flow into Power BI and how often?

AP invoices, GL journal entries, GL account balances, and vendor master data are the main flows. AP invoices typically poll every 15 minutes, GL accounts daily, and vendors hourly - you control the schedule. Each poll pushes new and updated rows into Power BI push datasets so your dashboards stay current.

How does ml-connector handle the different auth models between Sage 100 and Power BI?

Sage 100 uses stateless username/password per BOI call; ml-connector encrypts those credentials and submits them on each request. Power BI uses OAuth2 bearer tokens that expire in about one hour. ml-connector handles the token refresh cycle so the push endpoint never stalls waiting for credentials. Every authenticated call is logged for audit.