How to connect Power BI to Global Shop Solutions
Short answer
Connect Power BI to Global Shop Solutions through a read-only ODBC connection or, better, a reporting copy or warehouse refreshed on a schedule. Use the on-premises data gateway for the Power BI service, model jobs, operations and POs as a star schema, and add natural-language Q&A once the model is trustworthy.
Global Shop Solutions has its own reporting, and plenty of shops are served well by it. Power BI enters the picture when leadership wants a single view across ERP data and other sources, such as machine monitoring, quality or CRM, or when they want modern, interactive dashboards on phones and wall screens.
The core decision is where Power BI reads from. Querying the live production database directly is the quickest way to start and the quickest way to annoy your users with slow screens. A scheduled copy into a reporting database protects performance and gives you a place to clean the data.
The other lesson from shops that have done this is that the hard part is the model, not the visuals. Agreeing on what on time delivery means for a job shop with partial shipments is worth more than any chart type.
What teams use it for
On-time delivery
Measure shipped-on-or-before-promise by customer, part family and work center, with a clear rule for partials and customer-approved date changes.
Job profitability
Quoted versus actual hours and material per job, by operation, to expose where estimates miss.
Work center load and WIP
Hours of work queued against capacity by week, and aging of WIP by operation.
Purchasing and supplier performance
Open PO value, late lines and supplier on-time rates, including outside-process vendors.
Quality and scrap
Scrap and rework by part, operation and cause, linked to job cost so the dollar impact is visible.
Global Shop Solutions integration surface
- ODBC: read-only access to the ERP database using the driver for your engine (many on-prem installs use an Actian Zen style database; confirm for your version and hosting).
- Vendor reports and exports: scheduled flat-file outputs can feed Power BI when direct database access is not permitted, such as in hosted environments.
- Vendor web services: where your version provides them, use for smaller, current-state datasets. They are rarely the right tool for bulk history.
- Reporting database or warehouse: a nightly or hourly copy in SQL Server or similar, with cleaned dimensions for customer, part, work center and date.
- Power BI gateway: the on-premises data gateway lets the Power BI service refresh from sources inside your network without opening inbound ports.
How it works
- 1
Pick the source path
Direct ODBC for a prototype, a reporting copy for production. Check licensing and hosting terms before connecting to the live database.
- 2
Build the extraction
Load jobs, operations, labor postings, POs, shipments and inventory into staging tables on a schedule, keeping the ERP keys.
- 3
Model a star schema
Fact tables for operations, shipments and PO lines. Dimensions for part, customer, work center, vendor and date. Define measures such as on-time rate once.
- 4
Install and secure the gateway
Install the on-premises data gateway on a server with LAN access to the reporting database, use a service account, and configure refresh schedules.
- 5
Build the first three dashboards
On-time delivery, work center load and open POs. Review with the people who will use them weekly before adding more.
- 6
Add natural-language Q&A
Once the model is clean, enable Power BI Q&A or Copilot where licensed, and tune synonyms. For on-prem control, add a private model that translates questions to your model's measures.
Example flows
Weekly operations review
Trigger: Monday leadership meeting.
- The reporting copy refreshes overnight from the ERP.
- The dashboard shows late jobs, load by work center and open PO exposure.
- Managers drill from a late job to its operation and blocking PO.
Result: The meeting starts from shared numbers rather than competing spreadsheets.
Ask a question in plain language
Trigger: An owner asks, which customers had the worst on-time rate last quarter?
- Q&A maps the question to the on-time measure and customer dimension.
- It returns a ranked visual.
- The owner drills into the late jobs behind the worst customer.
Result: A quick answer without waiting for an analyst, as long as the measure is well defined.
Keeping data on-prem
Power BI Desktop and the on-premises data gateway let you keep the source database inside your network, but the published semantic model and report live in the Power BI service in the cloud, so aggregated data does reach Microsoft. If that is not acceptable, Power BI Report Server runs fully on-prem, with fewer features. For the natural-language layer, Microsoft's cloud Q&A and Copilot are hosted services; a private alternative is a local open-weight model that turns questions into queries against your reporting database or semantic model, and returns results inside your network. Decide based on contract terms for the data involved.
Security checklist
- Use a read-only account on a reporting copy rather than the production application login.
- Apply row-level security in the semantic model for roles such as sales by territory or plant managers by site.
- Hide cost and margin columns from roles that do not need them.
- Run the gateway under a managed service account and keep it patched.
- Review workspace sharing so exports to Excel do not bypass the model's security.
Ways to get it done
Direct ODBC from Power BI Desktop
Effort: Days. Watch live system load.
Good for a prototype or a very small shop with light queries.
Reporting database plus Power BI
Effort: Several weeks for extraction, model and first dashboards.
Best for most shops that want reliable refresh and clean metrics.
Partner build such as Netray
Effort: A phased project starting with a few weeks.
Good when you also want natural-language Q&A with an on-prem model and a governed model.
Pitfalls
- !Pointing many refreshes at the live database during working hours and slowing the shop floor.
- !Undefined metrics: on-time delivery, utilisation and WIP mean different things to different managers.
- !Ignoring partial shipments and date changes, which distort on-time results.
- !Building dozens of visuals before agreeing on three dashboards people will actually use.
- !Assuming the gateway makes data stay on-prem. The published model still lives in the service.
Frequently asked questions
Can Power BI connect directly to Global Shop Solutions?
Often via ODBC to the underlying database for on-prem installs, but it depends on your version, database engine and hosting agreement. Hosted customers may need to use exports or vendor-provided data access instead. Confirm with the vendor first.
Do we need a data warehouse?
Not strictly, but a reporting copy protects ERP performance and gives you a place to apply consistent business rules. Most shops that scale beyond one or two dashboards end up with one.
What does the on-premises gateway do?
It lets the Power BI service refresh datasets from sources inside your network without exposing them to the internet. It needs a server with access to the database and a service account.
Can we ask questions in plain English?
Yes, with Power BI Q&A or Copilot if you are licensed, or with a private model layered over your semantic model. Results depend on clean measure names and a well-defined model.
Is the vendor reporting enough?
Often yes for operational lists. Power BI adds value when you combine sources, need interactive drill-down or want executives on mobile. Do not build it just to replace reports that already work.
More for Global Shop Solutions
Want Power BI working on your Global Shop Solutions?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment