How to connect Power BI to ProShop ERP
Short answer
Connect Power BI to ProShop by pulling data from its API or scheduled exports into a small staging database, modelling it, and refreshing on a schedule. Because ProShop is typically hosted by the vendor, direct SQL is usually not the path.
Shops on ProShop often outgrow built-in views when they want cross-cutting dashboards: on-time delivery by customer, actual versus quoted hours by part family, scrap and rework trends, or quote win rates. Power BI is a common choice because office staff already know Excel and Microsoft licensing is often in place.
The question is how data gets from ProShop into Power BI. On a self-hosted ERP you would point Power BI at the database. For a vendor-hosted system that is generally not available, so the options are the API, scheduled exports or a vendor-provided reporting feed if your plan includes one.
A little structure pays off. Pulling everything live into Power BI at every refresh is slow and brittle. Landing selected data in a small staging store first gives you history, speed and a place to fix data quality issues once.
What teams use it for
On-time delivery by customer
Compare promised ship dates with actual ship dates by customer, part family and month to find where late deliveries concentrate.
Estimated versus actual hours
Join quotes and completed operations to see where estimates are consistently off, by work center and material.
Scrap, rework and NCR trends
Track nonconformances by cause, part family and operation to focus corrective action.
Work center load and WIP
Show queued hours by work center against capacity so planners see bottlenecks before they bite.
Quote pipeline and win rate
Analyse quotes sent, won and lost by customer and part type to guide where to spend estimating time.
Purchasing and outside process performance
Measure supplier lead time reliability for material and outside processes against promised dates.
ProShop ERP integration surface
- ProShop's API, GraphQL based in current releases with token authentication, can supply the objects needed: work orders, operations, parts, customers, quotes, POs and NCRs. Confirm details for your version.
- Scheduled exports or reports, in spreadsheet or CSV style formats, are a lighter option for a first dashboard and need no code.
- Power BI can call web APIs through Power Query, but GraphQL with paging and tokens is easier to run in a small script or pipeline tool that writes to a staging database.
- A vendor-provided reporting or data feed may exist on some plans. Ask ProShop whether your plan offers one before building your own.
- Custom fields your shop added will appear in the API, so map them deliberately in the model.
How it works
- 1
Pick three dashboards
Start with on-time delivery, hours variance and WIP load. Define each metric in writing before building.
- 2
Choose the data path
Use exports for a quick start, or the API for a durable pipeline. Confirm limits and terms with ProShop.
- 3
Stage the data
Write extracts to a small SQL database or a Fabric lakehouse, keeping history so you can see trends and snapshot WIP.
- 4
Build the model
Create fact tables for operations and work orders, with dimensions for part, customer, work center and date. Handle revisions and time zones explicitly.
- 5
Schedule refresh
Use the Power BI service with a gateway if the staging database is on your network. Refresh overnight and, where needed, a few times in the day.
- 6
Secure the report
Apply row-level security, for example so account managers see only their customers, and keep pricing in a restricted page.
- 7
Add natural-language questions
Use Power BI's built-in Copilot or Q&A where licensed, or add a private assistant that queries the same staging data and shows its query.
Example flows
Nightly refresh pipeline
Trigger: Scheduler at 2:00 each night
- Script calls the ProShop API for work orders and operations changed since last run.
- Rows are upserted into the staging database.
- Power BI service refreshes through the on-premises gateway.
Result: Fresh dashboards by morning without anyone exporting files.
Ask a question of the data
Trigger: Manager types: which customers had the most late jobs last quarter?
- Assistant maps the question to the delivery fact table.
- It generates and shows the query, then runs it read-only.
- It returns a table and a chart with the definition of late.
Result: An answer from governed data, with the logic visible for checking.
Keeping data on-prem
Keep the staging database and the Power BI gateway inside your network so the only external call is from your pipeline to the ProShop API. The on-premises data gateway lets the Power BI service refresh from that database without opening inbound ports. If reports contain controlled or customer-restricted information, consider Power BI Report Server or restricting who can access the service, and check where your tenant stores data. A natural-language layer can run on a private model over the same staging data so questions never leave the boundary.
Security checklist
- API credentials in a secret store, used only by the pipeline.
- Row-level security in Power BI for customer and cost visibility.
- Staging database access limited to the pipeline and the gateway account.
- Sensitivity labels and sharing limits on reports that include pricing.
- Natural-language queries run read-only with logged SQL.
Ways to get it done
Scheduled exports into Excel or Power BI
Effort: Days.
A first dashboard or a small shop with simple needs.
API pipeline into a staging database
Effort: Two to four weeks for a core model.
Teams wanting history, speed and many dashboards; needs scripting skill.
Netray or another partner builds the model and Q&A layer
Effort: Weeks, scoped by metrics.
Shops that want the data model, security and a private natural-language option delivered.
Pitfalls
- !Pulling live from the API on every refresh, which is slow and may hit limits.
- !No agreed metric definitions, so on-time delivery means three things.
- !Ignoring part revisions and re-opened work orders in the model.
- !Overlooking custom fields that hold important data.
- !Sharing a workspace so broadly that pricing and margins become visible.
Frequently asked questions
Can Power BI connect directly to the ProShop database?
For the usual vendor-hosted deployment, direct database access is generally not offered, so plan on the API, exports or a vendor reporting feed. If you have a special arrangement, confirm with ProShop. Even then, a staging layer is often better for speed and history.
Do we need a data warehouse?
Not for a first dashboard. Exports into Power BI are enough to learn what you need. When you want history, many dashboards or daily refreshes, a small SQL database or Fabric lakehouse becomes worth having, and it also feeds any natural-language layer.
Do we need the on-premises gateway?
Only if the source Power BI reads from is inside your network, such as a staging database on a local server. If your pipeline writes to a cloud database, the gateway may not be needed. Choose based on where the staged data lives.
Can staff ask questions in plain English?
Yes, through Power BI features such as Copilot or Q&A where licensed, or through a separate assistant on the staging data. Results depend on how clean your model and metric names are. Require the tool to show the query or measure it used.
How often should it refresh?
Most shops are fine with overnight refresh for performance dashboards and a few intraday refreshes for WIP and load. Real-time is rarely required and costs more. Start nightly, ask users what actually needs fresher data, then adjust within API limits.
Want Power BI working on your ProShop ERP?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment