Acumatica Power BI Connector — Setup and Best Practices

Every Acumatica client eventually asks for Power BI, and the path from "we want dashboards" to a refresh that doesn't time out is full of small decisions that matter. The core mechanism is simple — Acumatica exposes Generic Inquiries as OData feeds and Power BI consumes OData natively — but I've rescued enough gasping refresh schedules to know the defaults will not carry you past a few hundred thousand rows. Here's the setup I actually use.

The plumbing: GI → OData → Power BI

Acumatica publishes two OData surfaces. The classic one at /odata/<CompanyName> (OData v3) exposes any Generic Inquiry with the Expose via OData checkbox ticked. The newer one at /odatav4/<CompanyName> (OData v4) can additionally expose DACs directly. For Power BI I use GIs on either version — exposing raw DACs sounds attractive until you realise you've just handed report authors tables with no business logic, no joins, and internal status codes.

Setup, end to end:

1. Enable the OData feature on Enable/Disable Features (CS100000).
2. Build your GI, tick Expose via OData on the summary tab, save.
3. In Power BI Desktop: Get Data → OData feed, URL https://instance.example.com/odata/MyCompany, authentication Basic with a dedicated API user.
4. Pick your GIs from the navigator — each exposed GI appears as an entity set.

GET /odata/MyCompany/BI-SalesLines?$filter=LastModifiedDateTime ge
    datetimeoffset'2026-06-01T00:00:00Z'&$select=OrderNbr,InventoryCD,
    ExtPrice,LastModifiedDateTime HTTP/1.1
Host: instance.example.com
Authorization: Basic <base64 user:password>

The API user: dedicated, licensed, least-privilege

Never point Power BI at a human's login. Two reasons beyond hygiene: Acumatica ties licence session counts to users, and a scheduled refresh hammering the same account as a person mid-close will lock someone out at the worst moment. Create a bi.service user with a role restricted to the inquiry screens it needs. Bonus: row-level security applied to that role becomes your data boundary — the OData feed respects it, so a GI over all branches serves only permitted branches to the BI user.

Design GIs for BI, not for humans

The single biggest performance mistake: pointing Power BI at the GIs users browse in the UI. Screen GIs are wide, join everything for display, and format values. A BI feed wants the opposite:

• Flat and narrow. One GI per fact (sales lines, AR balances, stock movements), only the columns the model uses. Every extra joined table multiplies the SQL cost of the full-table scan a refresh performs.
• Keys, not labels. Export CustomerID and build a separate small customer-dimension GI, rather than joining customer name, class, and salesperson onto every fact row. Star schemas are Power BI's native shape anyway.
• No parameters that matter. OData reads a GI without its screen-side parameter values; a GI whose correctness depends on a user-supplied parameter will silently return something else over OData. Filters that must always apply belong on the GI's Conditions tab.

Incremental refresh: the difference between 4 minutes and 4 hours

A full refresh re-reads every GI end to end, and Acumatica pages OData responses server-side, so a multi-million-row inquiry means thousands of sequential page fetches while a SQL connection holds open. The fix is Power BI's incremental refresh against LastModifiedDateTime:

1. Include LastModifiedDateTime in the GI.
2. In Power Query, filter the table between the RangeStart and RangeEnd parameters — and make sure the filter folds into the OData $filter (check with View Native Query; if it doesn't fold, the gateway downloads everything and filters locally, which defeats the purpose).
3. Configure the incremental policy: store 3 years, refresh last 7 days, done.

Gateway, scheduling, and not hurting production

SaaS-hosted Acumatica with a public URL needs no gateway — the Power BI service connects directly. Self-hosted instances behind a firewall need the on-premises data gateway installed somewhere with line of sight to the instance. Either way, schedule refreshes off-peak: an OData read is a real SQL workload on your production database, and I've watched an 8am refresh schedule add two seconds to every sales order save during morning order entry. For heavy analytical loads, the grown-up answer is replicating to a warehouse and pointing Power BI there — the OData feed is the right tool up to a few million rows, not beyond.

Wrapping up

The recipe: OData-exposed GIs designed as narrow facts and small dimensions, a dedicated least-privilege API user, incremental refresh with verified query folding, and off-peak schedules. None of it is exotic, but each piece you skip converts directly into refresh timeouts or a production database that mysteriously crawls at 8am. Set it up properly once and the connector layer disappears from your problem list.