How to connect Power BI to Acumatica
Short answer
The standard path is to expose Generic Inquiries through OData, point Power BI's OData feed connector at them, and use an on-premises data gateway if Acumatica is not publicly reachable. For larger volumes, replicate into a warehouse and model there.
Acumatica ships its own dashboards, pivot tables and Generic Inquiries, and many teams stop there. Power BI enters when you need to blend Acumatica with other sources, build executive views with richer visuals, or share reports with people who do not have an Acumatica login.
The connection is simpler than it looks, because Acumatica treats a Generic Inquiry as an OData feed. The effort goes into designing inquiries that return the right grain, handling refresh and keeping the load off the production system.
This page walks through the connection options, dashboards worth building for manufacturers and distributors, and how to add natural-language questions on top.
What teams use it for
Order book and backlog
Open sales order lines by requested date, customer and product class, with a backlog-to-capacity view for planners.
Inventory health
On-hand value, turns, days of supply and slow or obsolete stock by warehouse, from stock status and transaction history inquiries.
Purchasing performance
On-time receipt rate, price variance and open commitments by vendor and buyer.
Production and job cost
Production order status, material variance and labor against estimate for the Manufacturing Edition.
Finance and AR aging
Aging buckets by customer, DSO trend and cash forecast built from invoices and expected payments.
Multi-entity consolidation
Combine tenants or companies, plus data from other systems like a CRM or MES, in one model.
Acumatica integration surface
- Generic Inquiries (SM208000) with Expose via OData enabled, consumed at an /OData/<tenant>/<inquiry name> URL; Power BI uses its OData feed connector.
- Basic authentication or OAuth for OData depending on configuration and version; many teams use a dedicated reporting user with read-only role.
- The contract-based REST API for entities, though it is less convenient than inquiries for tabular loads.
- Direct SQL Server access on on-prem installs, usually through a read replica, noting that it bypasses Acumatica's role checks and company filtering.
- Acumatica's own data export and reporting tools (Report Designer, pivot tables) for lighter needs, plus options in the Marketplace for prebuilt connectors.
How it works
- 1
Design inquiries at the right grain
One inquiry per subject, such as order lines or stock transactions, with filters like branch and date parameters and only needed columns.
- 2
Enable OData and test
Turn on Expose via OData for each inquiry and test the URL in a browser or Postman with the reporting user.
- 3
Choose the path to Power BI
Direct OData feed for modest volumes. For millions of rows, load into a warehouse or lakehouse on a schedule and let Power BI read that.
- 4
Set up the gateway if needed
If Acumatica is on-prem or behind a firewall, install the on-premises data gateway on a server that can reach it and register the data source.
- 5
Build the model
Create a star schema with date, item, customer and vendor dimensions. Define measures once and reuse them across reports.
- 6
Schedule and monitor refresh
Stagger refreshes off peak hours, use incremental refresh where possible and alert on failures.
Example flows
Daily inventory health dashboard
Trigger: Scheduled refresh at 05:30
- Gateway queries the stock status inquiry over OData.
- Power BI loads on-hand, cost and last movement date.
- Measures compute days of supply and slow-mover flags.
- Dashboard publishes to the planning workspace.
Result: Planners see current stock risks before the morning meeting.
Ask a question in plain English
Trigger: Manager types: which customers have the largest backlog past due?
- Q and A or Copilot, if licensed, reads the semantic model.
- It maps the question to the backlog and customer fields.
- It returns a ranked visual.
- For questions outside the model, a Netray-style assistant queries Acumatica directly.
Result: Faster answers for people who do not build reports.
Keeping data on-prem
With on-prem Acumatica, the usual pattern is an on-premises data gateway inside the network that connects Power BI Service to the OData endpoint, so you open outbound connections only. If reports must stay entirely on-site, Power BI Report Server is an option, with feature differences from the cloud service. For natural-language questions without sending data to a hosted AI, put a local model in front of the same inquiries and a read replica, and keep Power BI for visuals. Make sure the reporting database load does not compete with order entry.
Security checklist
- Use a dedicated reporting user with read-only rights and the minimum branches needed.
- Apply row-level security in the Power BI model for managers who should see only their region.
- Keep credentials in the gateway data source, not in report files.
- Exclude cost, salary and bank fields from inquiries shared with wide audiences.
- Review workspace sharing and export permissions, since downloaded data leaves your control.
Ways to get it done
OData feed from Generic Inquiries
Effort: Days to a few weeks
Fits most mid-market users and modest data volumes with quick setup.
Warehouse replication then Power BI
Effort: 4 to 10 weeks
Fits large histories, multi-source blending and heavy models.
Marketplace connector or partner-built analytics
Effort: Varies
Fits those wanting prebuilt dashboards; check data freshness and field coverage.
Pitfalls
- !Building one huge inquiry that joins everything, which times out and punishes the production database.
- !Refreshing during peak order entry hours.
- !Skipping a date dimension and then struggling with fiscal calendars.
- !Exposing customer-level data to a broad workspace without row-level security.
- !Depending on a single report author's inquiry names without documenting them.
Frequently asked questions
Does Power BI connect directly to Acumatica?
Yes, most often through the OData feed connector pointed at an exposed Generic Inquiry. You can also use the REST API or query a database replica on on-prem installs. Which fits depends on volume, refresh needs and governance.
Do I need an on-premises data gateway?
If Acumatica is hosted on your network and not reachable from the internet, yes. A cloud-hosted instance reachable by Power BI Service may not need one. The gateway must run on a server that can reach the OData endpoint.
Why is my refresh slow?
Typical causes are inquiries returning too many rows or columns, missing filters, and refreshing full history each time. Narrow the grain, add date parameters and use incremental refresh or a warehouse.
Can I add natural-language questions?
Power BI has Q and A and Copilot features, subject to licensing and model quality. For questions that need live ERP data beyond your model, a separate assistant over Acumatica tools can complement it.
Is direct SQL access allowed?
On on-prem installs it is technically possible, and read replicas are used. It bypasses Acumatica's roles and tenant filtering, so apply strict controls. For cloud-hosted Acumatica you generally do not have it.
Want Power BI working on your Acumatica?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment