How to connect Power BI to QAD Adaptive ERP
Short answer
Connect Power BI to QAD by reading from an OpenEdge replica over ODBC through an on-premises data gateway, or from a data warehouse fed by extracts. Model a few core facts first, build manufacturing dashboards, then add natural-language Q&A over the same governed model.
Many QAD sites already have a pile of Excel reports built from browse exports. Power BI replaces the copy-paste with a refreshable model, but the connection is where projects stall: OpenEdge is not a database most BI teams know, and the schema is wide and cryptically named.
The reliable pattern is to avoid pointing dashboards at the transactional database. Replicate or extract the data you need, shape it into a clean model, and let Power BI read that. This also gives you somewhere to apply business rules that live in the application rather than the tables.
This page covers connection options, a starter model, dashboards worth building and how natural-language questions fit in.
What teams use it for
On-time delivery
Compare promised and actual ship dates from sales order lines and shipments, by customer, product line and plant.
Inventory health
Show on-hand value, slow movers, negative balances and days of supply by site and location.
Purchase price and supplier performance
Track price variance, on-time receipts and rejects per vendor over time.
Shop floor efficiency
Compare standard to actual hours and yields from work order operations and labor transactions.
Backlog and capacity view
Combine open orders, planned orders and routing hours to see where work centers will overload.
Financial reconciliation views
Receipts not invoiced and WIP balances for finance, reconciled to the general ledger totals.
QAD Adaptive ERP integration surface
- OpenEdge ODBC or JDBC access to a replica; Power BI typically uses an ODBC driver through a gateway, subject to driver availability and licence for your OpenEdge version.
- On-premises data gateway to bridge the Power BI service to a database inside your network.
- QAD analytics and reporting tools and exports, which may offer a supported path for cloud-hosted customers.
- Data warehouse or lakehouse: a nightly or incremental load into SQL Server, Postgres or similar, the more maintainable option at scale.
- QXtend outbound events or change tables for near-real-time feeds, if hourly refresh is not enough.
How it works
- 1
Decide the source
Replica via ODBC for a quick start, warehouse for scale and cleaner modelling. Cloud-hosted QAD may require approved extracts.
- 2
Install the gateway
Place the on-premises data gateway on a server that can reach the replica, with failover if the dashboards become critical.
- 3
Model core facts and dimensions
Start with sales lines, purchase lines, inventory, work orders, item, customer, supplier, site and calendar.
- 4
Resolve QAD specifics
Handle domains, multi-currency, unit of measure conversions and custom fields in the model layer, not in each report.
- 5
Build the first dashboards
Pick two or three that users already ask for. Validate every number against a QAD report.
- 6
Schedule refresh
Choose refresh times outside peak load, and use incremental refresh on large fact tables.
- 7
Add natural-language Q&A
Use Power BI's built-in Q&A or Copilot features where licensed, or an external assistant querying the same semantic model.
Example flows
Weekly on-time delivery review
Trigger: Operations meeting every Monday.
- The model refreshes overnight from the replica.
- The report shows promised versus shipped date by customer.
- The manager drills into late lines and sees the cause code if captured.
- The team assigns actions in the meeting.
Result: One trusted number replaces several conflicting spreadsheets.
Ask a question of the model
Trigger: A buyer asks which suppliers had the most late receipts last quarter.
- The question is sent to Q&A or an assistant tied to the semantic model.
- It maps to supplier, receipt date and promise date fields.
- A ranked visual is returned with the filter shown.
- The buyer saves the visual to a dashboard.
Result: Self-service answers without waiting for a report developer.
Keeping data on-prem
Keeping QAD data on-prem and using Power BI is possible, but note that the Power BI service itself is cloud-hosted, so the imported model data is stored there. If policy forbids that, consider Power BI Report Server on-prem, or keep the semantic model local and use another BI front end. Either way, the on-premises gateway keeps database credentials inside your network and sends only query results. For natural-language questions on data that must not leave, run an open-weight model locally against the warehouse or semantic layer rather than using a hosted Copilot. Check your licensing for the on-prem report server option before planning around it.
Security checklist
- Use a read-only account on the replica or warehouse and restrict it to the tables the model needs.
- Apply row-level security by site, domain or business unit inside the semantic model.
- Protect the gateway server, keep it patched and restrict who can register data sources.
- Decide whether cost and margin fields belong in shared datasets at all.
- Review workspace sharing so exports do not leak data outside the intended group.
Ways to get it done
Direct ODBC to the replica
Effort: Days to a couple of weeks
Fast proof of concept with a few tables and modest data volumes.
Warehouse plus Power BI
Effort: 1 to 3 months for a starter model
Best for many dashboards, history and clean modelling of QAD data.
Netray natural-language layer on top
Effort: A few weeks on an existing model
When users want to ask questions in plain English, including on-prem with an open-weight model.
Pitfalls
- !Running heavy refresh queries against the production primary database.
- !Importing wide tables with hundreds of unused columns, which slows the model.
- !Ignoring domains and currencies, causing totals that do not match QAD.
- !No row-level security, so everyone sees everything.
- !Building dashboards before agreeing definitions such as what counts as late.
Frequently asked questions
Can Power BI connect to OpenEdge directly?
Usually through an ODBC driver and the on-premises data gateway. Driver choice, licensing and performance depend on your OpenEdge version, so test early with a replica rather than production.
Does it work with QAD Cloud?
Often via approved extracts, QAD analytics offerings or APIs rather than direct database access. Ask QAD which data-access options your agreement includes.
Do I need a data warehouse?
Not for a proof of concept. For many dashboards, history and consistent definitions, a warehouse is easier to maintain than modelling directly on OpenEdge tables.
Can I ask questions in plain English?
Yes. Power BI offers Q&A and Copilot features subject to licence and region, and you can put an external assistant on the same semantic model. Quality depends mostly on how well the model and field names are curated.
Will dashboards match QAD reports?
They will if you replicate the business rules, such as domains, UoM and currency conversion. Reconcile the first dashboards line by line against standard QAD reports before release.
Want Power BI working on your QAD Adaptive 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