How does ml-connector authenticate to both Sage 300 and Microsoft Power BI?

Sage 300 requires HTTP Basic Authentication with an uppercase username and password sent in the Authorization header on every request. ml-connector stores these credentials encrypted in the database. Microsoft Power BI uses OAuth2 client credentials: ml-connector exchanges the service principal client ID and secret for a Bearer token, which is included on every Power BI API call. Tokens expire after one hour and are automatically refreshed.

How often does data sync from Sage 300 to Power BI?

ml-connector pulls from Sage 300 on a schedule you configure (e.g., daily or weekly). Each poll uses OData $filter with timestamps to fetch only changed records, reducing API load and network cost. You can adjust the cadence and filter window to match your business reporting cycle.

Sage 300Microsoft Power BI

Sage 300 and Microsoft Power BI integration

Sage 300 holds your operational data. Microsoft Power BI is where your finance and operations teams see it. Connecting the two puts vendor aging, GL balances, AR collections, purchase activity, and inventory on demand into live dashboards without manual exports. ml-connector reads from Sage 300 on a schedule, transforms the data to match your Power BI schema, and pushes it through the Power BI REST API so reports and stakeholders always work with current numbers.

How Sage 300 works

Sage 300 exposes accounts payable vendors and invoices, accounts receivable customers and receipts, general ledger accounts and journal batches, purchase orders and receipts, order entry orders, and inventory items through REST and OData APIs hosted on a customer's own Windows IIS server. Requests use HTTP Basic Authentication with a base64-encoded uppercase username and password. The API supports OData filtering ($filter with date/time comparisons), ordering, pagination ($skip, $top), and metadata queries, making it well-suited for polling on a fixed cadence. Sage 300 has no webhooks or change-data-capture, so all integration is pull-based.

How Microsoft Power BI works

Microsoft Power BI accepts data through REST APIs that manage workspaces, reports, datasets, dashboards, and push datasets. Authentication uses OAuth2 with a service principal (client credentials), obtained by exchanging client ID and secret at the Microsoft token endpoint. Every API call carries a Bearer token header. Power BI push datasets require the data schema to be defined in advance (table name, columns with data types), and rows are posted as JSON arrays. Power BI is read-only on the outbound side: it does not push events or webhooks to external endpoints, so all integration is unidirectional data ingestion.

What moves between them

Financial and operational records flow from Sage 300 into Microsoft Power BI. AP and AR transactions, GL account balances, PO and order history, and inventory master data are pulled from Sage 300 on a daily or weekly schedule, then posted into corresponding Power BI push datasets. Each dataset holds a table (e.g., APVendors, ARInvoices, GLAccounts) mapped from Sage 300 entity names. Power BI never sends data back to Sage 300.

How ml-connector handles it

ml-connector stores Sage 300 credentials (username, password) encrypted and includes the authorization header on each REST call. On the Sage 300 side, it polls using OData $filter with DocumentDate or LastModified timestamps to retrieve only changed records since the last sync, reducing payload size and API load. For Microsoft Power BI, ml-connector exchanges the stored service principal client ID and secret for a Bearer token, then creates or updates push datasets with the required table schemas. Records are transformed to match those schemas (e.g., amount decimals, date formats, lookup keys) and posted in batches to minimize API calls. Sage 300's HTTP Basic Auth is stateless, so ml-connector retries gracefully on network errors. Power BI tokens expire after an hour, so ml-connector caches and refreshes as needed. Each record is logged with source timestamp and transformation details in the audit trail, so failed pushes can be identified and replayed.

A real-world example

A regional distribution company runs Sage 300 on-premise for AP, AR, GL, and inventory, and uses Power BI for executive dashboards. Finance managers spend hours each week exporting AP aging from Sage 300, AR collections status, and GL variance reports, then loading them into Excel spreadsheets to share with the CFO and regional directors. With Sage 300 and Microsoft Power BI connected, the finance team publishes Power BI dashboards that refresh daily, pulling vendor aging, customer collections, GL account balances, and inventory turns directly from Sage 300. Regional managers log into Power BI to see current AP and AR metrics by company and cost center. Month-end close starts with GL reports already current, and the manual export-and-reload cycle is gone.

What you can do

Poll Sage 300 on a schedule, filtering for new and changed AP invoices, AR receipts, GL journals, and inventory transactions.
Push AP vendor, invoice, and payment data into Power BI tables for aging, cash forecast, and payment analysis dashboards.
Sync AR customer, invoice, and receipt records to Power BI for collections, customer credit, and aging reports.
Map Sage 300 GL account balances and journal details to Power BI tables for variance analysis and close-to-book reconciliation.
Transform Sage 300 data types (uppercase credentials, OData filters, decimal formats) to Power BI API schemas with a full audit trail for each push.

Questions

How does ml-connector authenticate to both Sage 300 and Microsoft Power BI?: Sage 300 requires HTTP Basic Authentication with an uppercase username and password sent in the Authorization header on every request. ml-connector stores these credentials encrypted in the database. Microsoft Power BI uses OAuth2 client credentials: ml-connector exchanges the service principal client ID and secret for a Bearer token, which is included on every Power BI API call. Tokens expire after one hour and are automatically refreshed.
How often does data sync from Sage 300 to Power BI?: ml-connector pulls from Sage 300 on a schedule you configure (e.g., daily or weekly). Each poll uses OData $filter with timestamps to fetch only changed records, reducing API load and network cost. You can adjust the cadence and filter window to match your business reporting cycle.
What Sage 300 entities can sync to Power BI?: ml-connector supports all key Sage 300 modules: AP vendors, invoices, and payments; AR customers, invoices, and receipts; GL accounts and journal batches; purchase orders and receipts; order entry orders; and inventory items. You can publish any combination of these into Power BI push datasets and build dashboards on top of them.

Connect Sage 300 and Microsoft Power BI

Free to use. Add your credentials, ping your real systems, and see if we fit.

Get started