Microsoft Dynamics GP and Microsoft Power BI integration

What moves between them

Records flow one direction, from Dynamics GP into Power BI. ml-connector polls Dynamics GP vendors, payables invoices, purchase orders, GL accounts, GL journal entries, customers, receivables invoices, and inventory items on a schedule you define (typically daily or more frequently for near-real-time reporting). Each record is transformed to match the column schema you define in Power BI push datasets, then POSTed to the push dataset tables. Once data lands in Power BI, you can optionally trigger a dataset refresh to update any dependent semantic models and reports. Deletions and updates in Dynamics GP are handled by polling for changes and replacing or appending rows based on your retention policy.

How ml-connector handles it

ml-connector stores the Dynamics GP Windows Authentication credentials encrypted and impersonates that domain account on each SBA or SOAP call to Dynamics GP. It polls Dynamics GP on your defined schedule with ModifiedDate filters to fetch only changed records, respects the on-premises firewall by connecting through your exposed endpoint with a valid SSL certificate, and handles the fact that SBA endpoints wrap request bodies in a top-level Payload key. For Power BI, ml-connector requests an OAuth2 token from the Microsoft identity platform using the service principal credentials, includes the Bearer token on every API call, and handles token expiry by refreshing before each polling cycle. Because Power BI push datasets have size and latency limitations, ml-connector batches records and retries failed pushes with exponential backoff when the API returns 429 (Too Many Requests). It maps Dynamics GP entity fields to Power BI table columns, handles both unposted and posted GL journal entries by reading GL Journal Master and Detail, and maintains an audit log of every poll, transform, and push so failed records can be replayed. If a Dynamics GP company name changes or the endpoint URL shifts, the configuration is updated without restarting the flow.

A real-world example

A mid-market manufacturer runs Microsoft Dynamics GP on-premises for AP, AR, GL, and inventory. Before the integration, the CFO's team exported month-end data from Dynamics GP manually each closing period, rebuilt vendor aging and GL variance reports in Excel, and always found data discrepancies by the time reports were finalized. With Dynamics GP and Power BI connected, daily polls push the latest vendors, payables invoices, customers, receivables invoices, and GL entries into a Power BI workspace where the finance team has built live dashboards for AP aging, AR collections, and GL reconciliation. Month-end close now starts with AP and AR reports already accurate and current, and the manual export-and-rebuild cycle is eliminated.

What you can do

• •Poll Dynamics GP vendors, payables, receivables, purchase orders, and general ledger on a schedule tied to your accounting close calendar, securely through your on-premises firewall.
• •Transform Dynamics GP records to Power BI push dataset schema and push rows into Power BI for real-time financial and operational dashboards.
• •Bridge Windows Authentication in Dynamics GP with OAuth2 client credentials in Power BI, encrypting both credential sets and managing token expiry automatically.
• •Handle Dynamics GP SBA payload wrapping, ModifiedDate filtering, and both unposted and posted GL journals without custom code.
• •Retry failed Power BI pushes when API throttling occurs, maintain a full audit trail, and replay failed records without data loss.

Questions

Does ml-connector work with both SBA REST and legacy SOAP Web Services in Dynamics GP?

Yes. ml-connector supports both Service Based Architecture (SBA) REST endpoints and legacy SOAP Web Services. During setup, you specify your Dynamics GP endpoint URL and the system version, and ml-connector calls the appropriate interface. SBA is preferred where available because it is simpler to debug, but SOAP Web Services work if that is what your Dynamics GP installation supports.

Does Power BI need to be in a specific license tier or workspace type to receive push data?

Power BI push datasets work in all license tiers (Free, Pro, Premium, Embedded). The service principal that ml-connector uses must be added as Member or Admin on each workspace, and Developer Settings must be enabled in the Power BI Admin portal to allow service principals to use the Power BI API. If these settings are not enabled, all API calls will fail with permission errors.

What happens if Dynamics GP is offline or the firewall endpoint becomes unreachable?

ml-connector retries the poll with exponential backoff. If Dynamics GP remains unreachable after the configured retry limit (typically 3-5 attempts), the flow enters a paused state and sends an alert. Once Dynamics GP is back online, the flow resumes from the last successful poll timestamp, so no data is lost. The audit trail records every failed attempt.