Most Microsoft Dynamics GP (Great Plains) users know their data is valuable. The difficulty is getting it out in a form that managers can actually use. SmartLists are excellent for day-to-day lists, and Management Reporter handles the financial statements, but neither gives you interactive dashboards, trends over several years, or a single view across companies.
This is where Power BI comes in. In this article, we will explain how to connect Power BI to Dynamics GP, which tables and views to start with, and how to set things up so your reports stay fast, secure and accurate.
Why Power BI and Dynamics GP Work Well Together
Dynamics GP stores everything in Microsoft SQL Server, and Power BI connects to SQL Server very easily. That combination means you can:
- Build dashboards for sales, receivables, payables, inventory and profitability
- Compare several years of data without running reports one by one
- Combine all your GP companies into one view
- Share reports securely with managers who do not use GP
It also does not consume a GP user license, because Power BI reads the database directly rather than logging in to GP.
Before You Connect: Three Decisions
1. Who will build the reports?
Power BI Desktop is free to download, so anyone can start building reports. However, to share reports with colleagues through the Power BI service, each person publishing or viewing them generally needs a Power BI Pro license, unless your organization has a Premium or Fabric capacity. It is worth checking your Microsoft 365 licensing early, as it affects your budget.
2. How will the data be refreshed?
Dynamics GP normally runs on a server in your own office or data centre. For published reports to refresh automatically, you need the on-premises data gateway. This is a small Microsoft application installed on a server that can reach your GP SQL Server. It securely passes data to the Power BI service on a schedule you set.
3. What access will Power BI have?
We strongly recommend creating a dedicated, read-only SQL login for Power BI. Never use the “sa” account or a Dynamics GP user’s login. A read-only account cannot change GP data, and you can limit it to the databases and views it actually needs.
How to Connect Power BI to Dynamics GP: Step by Step
Here is the standard approach, using Power BI Desktop and SQL Server:
- Open Power BI Desktop and choose Get Data > SQL Server.
- Enter the server name of your GP SQL Server (for example, GPSQL01 or GPSQL01\GP).
- Enter the database name. Each GP company has its own database, usually a short code such as TWO or ABC01. The system database is normally called DYNAMICS.
- Choose the data connectivity mode:
- Import copies the data into Power BI. It is faster and suits most GP reporting.
- DirectQuery queries SQL Server each time a report is opened. Use it only when you truly need near real-time figures, as it adds load to your Dynamics GP server.
- Sign in with the read-only SQL account.
- Select the tables or views you need, and click Transform Data to tidy them in Power Query.
- Build your model and report, then publish to the Power BI service.
- Configure the gateway and set a scheduled refresh, for example every morning before the working day starts.
That is all it takes to get connected. The real skill lies in choosing the right data.
Understanding GP Table Names
Microsoft Dynamics GP table names look cryptic at first, but they follow a pattern you will quickly recognize. The letters tell you the module, and the numbers tell you the type of data.
| Prefix | Module |
|---|---|
| GL | General Ledger |
| RM | Receivables Management |
| PM | Payables Management |
| SOP | Sales Order Processing |
| POP | Purchase Order Processing |
| IV | Inventory |
| SY | System and company setup |
The number range usually indicates what the table holds:
- 00000 range: master records, such as accounts, customers, vendors and items
- 10000 range: work records, which are saved but not yet posted
- 20000 range: open records, which are posted but still active
- 30000 range: history, such as closed years and fully paid documents
This matters a great deal for reporting. If you only read the open table, you will miss history. If you only read history, you will miss this year’s activity. Most reports need both, combined.
Which Tables to Start With
We would emphasize starting small. Pick one business question, connect only the tables needed to answer it, and build from there.
General Ledger (for profit and loss and balance sheet analysis)
| Table | What it holds |
|---|---|
| GL00100 | Account master: account descriptions, types and categories |
| GL00105 | Account index: the full account number string |
| GL00102 | Account categories |
| GL20000 | Posted transactions for open years |
| GL30000 | Posted transactions for historical (closed) years |
| SY40100 | Fiscal period setup |
Combine GL20000 and GL30000 to get the full transaction history.
Receivables (for customer and cash collection reports)
| Table | What it holds |
|---|---|
| RM00101 | Customer master |
| RM20101 | Open receivables documents |
| RM30101 | Historical receivables documents |
Payables (for vendor and spend analysis)
| Table | What it holds |
|---|---|
| PM00200 | Vendor master |
| PM20000 | Open payables documents |
| PM30200 | Historical payables documents |
Sales (for revenue by customer, item and salesperson)
| Table | What it holds |
|---|---|
| SOP30200 | Posted sales document headers |
| SOP30300 | Posted sales document lines |
Inventory (for stock levels and item analysis)
| Table | What it holds |
|---|---|
| IV00101 | Item master |
| IV00102 | Item quantities by site |
A Shortcut: Use GP’s Built-In SQL Views
If joining tables sounds daunting, there is an easier starting point. Microsoft Dynamics GP includes a set of SQL views that sit behind the standard SmartLists. They already join the master, open and history tables together, with readable column names. Useful examples include:
- AccountTransactions: general ledger transactions with account details
- ReceivablesTransactions: customer documents across open and history
- PayablesTransactions: vendor documents across open and history
- SalesTransactions and SalesLineItems: posted sales documents
- Customers, Vendors and Items: master data
For many organizations, these views cover 80% of reporting needs. If you already use SmartLists, the fields will look familiar. Our guide to Dynamics GP for new users explains how SmartLists work.
When the standard views are not enough, your partner can create custom SQL views tailored to your reports. This kind of customization keeps complex logic in one place, so every report uses the same rules.
Practical Tips for Accurate Reports
From our project experience, a few habits make a large difference:
- Filter out unposted and voided documents: Work tables and voided entries can inflate your figures. Check the document status and void fields.
- Watch the document types: In Receivables and Payables, returns and credit notes should usually show as negative amounts. GP stores them as positive values with a document type, so apply the sign in Power Query.
- Trim text fields: GP pads many text fields with trailing spaces. Trimming them avoids failed matches between tables.
- Handle multiple companies: Each company is a separate database. Bring each into Power BI and append them, adding a column for the company name.
- Use a proper date table: Create a calendar table that matches your GP fiscal periods, so monthly and yearly comparisons line up with your accounts.
- Reconcile against GP: Before sharing a dashboard, compare its totals with a GP trial balance or aged trial balance. If the numbers do not match, analyzing the difference early saves embarrassment later.
Security and Organization
Power BI can expose sensitive information, such as salaries, margins and customer balances, to a wide audience. So plan your security from the start:
- Keep payroll and HR data out of general reports.
- Use workspaces in the Power BI service to separate finance, sales and operations content.
- Use row-level security if different managers should only see their own region, department or company.
- Keep the gateway server patched and limit who can manage it.
Power BI Versus Management Reporter
Power BI does not replace Management Reporter for formal financial statements. Management Reporter is still the better tool for printed income statements and balance sheets with precise formatting. Power BI is the better tool for interactive analysis, trends and operational dashboards. Most GP customers benefit from using both.
Looking Ahead to Business Central
Dynamics GP product support ends on 31 December 2029, and many organizations are planning a Dynamics GP to Business Central migration. The good news is that Power BI skills carry across. Dynamics 365 Business Central has a built-in Power BI connector and ready-made reporting apps. Building your GP reports around clear business questions now will make them much easier to rebuild later.
How Nevas Technologies Can Help
Nevas Technologies has worked with Dynamics GP since version 7.0 and regularly helps clients turn GP data into useful dashboards. We can:
- Set up a secure Power BI connection and gateway through our Dynamics GP support services
- Build custom SQL views and dashboards for finance, sales and inventory
- Train your team on GP reporting through Dynamics GP training
- Plan your reporting for the future with our Business Central consultants
Summarizing the Key Points
- Connect using Power BI Desktop and SQL Server, with a read-only login.
- Refresh published reports through the on-premises data gateway.
- Start with GL, RM, PM, SOP and IV tables, or GP’s built-in SQL views.
- Combine open and history tables to see the full picture.
- Reconcile your dashboards against GP before sharing them.
With the right setup, Power BI turns years of GP data into clear, timely insight for your whole organization.
If you would like help connecting Power BI to your GP system, contact Nevas Technologies and our team will be glad to help.
Frequently Asked Questions
Does connecting Power BI use a Dynamics GP user license? No. Power BI reads the SQL database directly, so it does not log in to GP or use a GP license.
Do I need a gateway to use Power BI with GP? Only if you publish reports to the Power BI service and want them to refresh automatically. Power BI Desktop on its own does not need one.
Should I use Import or DirectQuery? Import suits most GP reporting and is faster. Use DirectQuery only when you need near real-time data.
Which GP tables should I start with? Start with the General Ledger tables (GL00100, GL20000 and GL30000) or the Account Transactions view, then add Receivables, Payables and Sales as needed.
