How to connect Power BI to Microsoft Dynamics GP
Short answer
Power BI connects to GP through its SQL Server company databases, using curated views and the on-premises data gateway for scheduled refresh in the Power BI service. Import mode with a staging layer is the safest default. Natural-language Q&A can then be added on top of the same model.
Power BI is the most common reporting upgrade for GP shops. SmartList, Excel reports and Report Writer cover operational lists, but cross-module dashboards such as margin by customer or on-time delivery by vendor quickly turn into long Excel files. Because GP data lives in SQL Server, Power BI is a natural fit.
Microsoft has provided GP-oriented Power BI content and SQL reporting views over the years, and some versions include sample reports. Availability and content vary by version and have changed over time, so treat them as a starting point and verify what applies to your install.
The harder part is the data model. GP stores open, work and history documents in separate tables, uses multiple currencies and has account segments that need interpretation. A tidy model, not the connector, determines whether the dashboard numbers match the ledger.
What teams use it for
Open PO and delivery performance
On-time receipt percentage by vendor, average days late and open commitment value, built from PO lines and receipt history.
Gross margin by customer and item class
Sales invoice history joined with cost data shows margin erosion by product family and by customer over time.
Inventory health
On-hand value by site, slow-moving stock and days of supply, with drill from site to item to last transactions.
AR and AP aging
Aging buckets by customer or vendor with trend lines for collections and cash planning, matching the GP aging reports.
Month-end flash report
Revenue, cost and expense by account segment against budget and last year, ready on the first business day.
Microsoft Dynamics GP integration surface
- SQL Server connector in Power BI Desktop pointed at the GP company database, ideally at a replica or reporting copy rather than the production server.
- GP reporting views and SmartList Builder logic, which you can reuse as definitions but should recreate as your own tested views.
- On-premises data gateway installed on a server that can reach SQL Server, used by the Power BI service for scheduled refresh of imported datasets.
- A staging database or data warehouse with fact and dimension tables built nightly from GP, for cleaner modelling and less load on the live system.
- Account framework, segments and currency tables from the General Ledger module, needed to build a correct chart-of-accounts dimension.
How it works
- 1
Decide on the architecture
Small shops can import from reporting views. Larger or busier systems should build a staging database fed nightly, so analysis never competes with order entry.
- 2
Model facts and dimensions
Create fact views for GL, sales, purchasing and inventory movements, combining open and history tables. Add dimensions for customer, vendor, item, site and account.
- 3
Install and secure the gateway
Put the on-premises data gateway on a server with line of sight to SQL Server, use a read-only service account, and add a standby gateway member for resilience.
- 4
Build the semantic model
Import tables, define relationships, measures and a date table. Reconcile totals to GP trial balance and aging reports before publishing.
- 5
Choose refresh and access
Schedule refreshes outside posting peaks, and apply row-level security by company or site using your identity provider groups.
- 6
Add natural-language Q&A
Enable Power BI Q&A or Copilot features if your license allows, add synonyms for GP terms, and consider a separate private assistant for sensitive questions.
Example flows
Nightly refresh into the executive dashboard
Trigger: Scheduled refresh at 2am.
- Staging job extracts changed GP documents into fact tables.
- Gateway runs the dataset refresh in the Power BI service.
- Reconciliation check compares dashboard GL total to GP trial balance.
- Dashboard updates, or an alert goes to the BI owner if totals differ.
Result: Leaders open a dashboard that already agrees with the ledger.
Planner drills into vendor lateness
Trigger: Planner opens the purchasing report and clicks a vendor.
- Report filters PO lines for that vendor.
- It shows lateness trend and top delayed items.
- Planner exports the list to follow up.
Result: Prioritised vendor conversations backed by data.
Keeping data on-prem
Power BI Desktop and the on-premises gateway work with on-premises SQL Server, but the Power BI service itself is cloud hosted. If data must stay entirely in-house, look at Power BI Report Server, which is hosted on your own servers and has its own feature set and licensing, and use direct or scheduled refresh against your SQL Server. Natural-language Q&A in the cloud service sends questions and metadata to Microsoft. For fully private Q&A, point a local open-weight model at the same semantic layer and views and keep the interface on the intranet.
Security checklist
- Use a read-only SQL account and avoid querying the production server during business hours when possible.
- Apply row-level security by company, site or business unit, and test it with realistic user accounts.
- Limit sharing of workspaces and use sensitivity labels for finance content.
- Document the gateway host, credentials owner and patching, as it becomes critical infrastructure.
- Exclude payroll, bank details and similar fields from datasets unless a role requires them.
Ways to get it done
Microsoft-provided GP reports and SQL views
Effort: Days, with limited customisation.
Quick start for standard financial and sales reporting.
Staging database or warehouse with a custom Power BI model
Effort: Several weeks for a first set of dashboards.
Best for manufacturers who need margin, delivery and inventory dashboards that reconcile.
Netray add-on for natural-language questions
Effort: Short build on top of existing views.
When you already have a model and want plain-language Q&A, possibly with a private model.
Pitfalls
- !Querying base tables directly and double counting documents that exist in both work and history tables.
- !Ignoring multi-currency, so totals mix originating and functional amounts.
- !DirectQuery against the live production database causing slow order entry. Prefer import from a replica or staging copy.
- !No reconciliation to the trial balance, so nobody trusts the numbers after the first discrepancy.
- !Gateway on a desktop PC that sleeps. Run it on a managed server.
Frequently asked questions
Does Power BI have a ready connector for Dynamics GP?
Power BI connects to GP through its SQL Server databases rather than a dedicated connector. Microsoft has offered GP-specific content and reporting views at times, so check what exists for your version, then validate the numbers against GP reports.
Import or DirectQuery?
Import is the safer default for most GP environments because it keeps load off the production SQL Server and performs better. DirectQuery suits near-real-time needs but needs well-tuned views and a reporting replica to avoid slowing transaction entry.
Do we need the on-premises data gateway?
Yes if you use the Power BI cloud service with an on-premises SQL Server and want scheduled refresh. The gateway makes an outbound connection, so no inbound firewall port to SQL Server is required, though you should confirm this with your network team.
Can we ask Power BI questions in plain English?
Yes with Q&A or Copilot features, subject to licensing and tenant settings. Results depend on a clean model and synonyms for GP terms. For sensitive data, a private natural-language layer over the same views keeps questions inside your network.
Will this survive a move to Business Central?
Your report designs and measures can carry over, but the source tables and relationships will change. A well-separated staging layer makes the switch easier, since you only repoint the extract and keep the dashboard model.
More for Microsoft Dynamics GP
Want Power BI working on your Microsoft Dynamics GP?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment