# How to connect Power BI to Infor XA > Source: https://netray.co/integrations/infor-xa-power-bi > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Integrations ## Short answer Connect Power BI to Infor XA through the IBM Db2 for i connector or ODBC, using an on-premises data gateway, and preferably through a reporting layer of views or a staging warehouse rather than raw files. Build a small set of manufacturing dashboards, then add natural-language Q&A on top of the same model. ## System Infor XA (on IBM i) ## Capability power-bi ## Intro Most XA sites already have reports: green-screen inquiries, spooled output, perhaps Query/400 or a Crystal or SSRS layer someone built years ago. Power BI is usually the first proposal to modernise that, and it is a good one, since the data sits in Db2 for i and Power BI can read it. The harder questions are performance, naming and who maintains the model. XA's files have short names and encoded values, with business logic in RPG. Pointing Power BI at those files directly gives dashboards that disagree with the screens. The reliable pattern is a reporting layer: views or a staging database that translate codes, apply status filters and reproduce calculations, and a Power BI model built on that. This page covers the connection, the gateway, what to model and which dashboards pay back first. It also covers how to add a question-and-answer layer when users want to ask rather than click. ## Use Cases ### On-time delivery and backlog **Description:** Customer order lines by promise versus ship date, with late backlog by customer and product family, refreshed daily. ### Inventory health and turns **Description:** On-hand value, slow and obsolete stock, and days of supply by warehouse and item class, drawn from the inventory balances and transactions. ### Purchasing and supplier performance **Description:** Supplier on-time and in-full, price variance from standard and open commitments, from PO and receipt records. ### Shop floor and work center load **Description:** Shop order status, scheduled versus actual hours and work center load against capacity. ### Margin and cost variance **Description:** Standard versus actual cost, material and labor variance by order, restricted by role because it contains sensitive data. ### Financial roll-ups next to operations **Description:** Combine ledger summaries with operational drivers so finance sees why a number moved. ## System Surface Power BI Desktop includes an IBM Db2 connector, and Db2 for i can also be reached with the IBM i Access ODBC driver. Driver installation and the supported connection mode vary, so test your version before committing to a design. The on-premises data gateway (standard mode) lets the Power BI service refresh from a server inside your network. The gateway host needs the same driver as Desktop and network access to the IBM i. DirectQuery against Db2 for i is possible but puts interactive load on a production machine; Import mode on a schedule is usually safer, or Import from a staging copy. SQL views, user-defined table functions and materialised query tables in Db2 for i can pre-aggregate data. Journaling can support incremental extracts to a warehouse. Spooled-file reports and Query/400 outputs can be written to tables or files if a legacy report has logic no one wants to rebuild yet. If you run Infor ION or Infor reporting tools, they may provide another data route; check availability for your XA version. ## How It Works ### Pick the first three dashboards **Detail:** Choose by decision value and data availability, such as on-time delivery, inventory health and supplier performance. Define every metric in writing with an owner. ### Build the reporting layer **Detail:** Create views in a dedicated library that decode statuses, convert units and apply the same filters as the XA screens. Reconcile totals with known screen values before touching Power BI. ### Decide between direct and staged **Detail:** For small data and modest users, import from views on a schedule. For heavier history or many users, load nightly into a warehouse such as SQL Server and model there. ### Install the gateway and drivers **Detail:** Set up the on-premises data gateway on a server near the IBM i with the Db2 or ODBC driver, a service account, and a clustered gateway if refreshes are business-critical. ### Build the semantic model **Detail:** Create a star-style model with dimensions for item, customer, supplier, warehouse and date, and measures for the agreed metrics. Add row-level security where roles differ. ### Schedule refresh and monitor **Detail:** Schedule outside the IBM i batch window and after MRP completes, and alert on failed refreshes. ### Add natural-language Q&A **Detail:** Use Power BI's built-in Q&A or Copilot features where your licence supports them, and for questions beyond the model, add an assistant over Db2 for i with a visible query. ## Example Flows ### Daily on-time delivery refresh **Trigger:** Scheduled refresh at 06:30 after overnight MRP and billing **Steps:** - Gateway connects to Db2 for i and reads the shipment and order line views. - Power BI imports the delta since the last refresh into the model. - Measures compute on-time percentage by customer, family and week. - Report subscriptions email the summary to sales and operations leads. **Result:** Managers see yesterday's performance without asking for a report run. ### Slow stock review **Trigger:** Finance asks which inventory has not moved in six months **Steps:** - User opens the inventory health report and filters by last movement date. - The visual lists items by value and age with warehouse breakdown. - User exports the list or asks Q&A for the top twenty by value. **Result:** A reserve discussion backed by data in minutes rather than a two-day spreadsheet effort. ## On Prem Power BI Desktop and the on-premises gateway let you keep Db2 for i inside your network while publishing reports to the Power BI service. If even published datasets cannot leave the site, Power BI Report Server is an on-premises option that reads the same sources, with feature differences from the cloud service that you should verify against current Microsoft documentation. For natural-language questions on-prem, run an open-weight model next to the IBM i that queries the same views, and show the SQL it ran so analysts can check it. The model never trains on the data and no prompt leaves the plant. ## Security - Use a read-only IBM i profile for the gateway data source, with authority limited to the reporting views. - Apply row-level security in the semantic model for plant, customer or cost visibility, and test it with real user accounts. - Keep cost and margin data in a separate model or workspace with tighter membership. - Control who can export or share datasets, since an export is a copy of production data. - Patch and monitor the gateway host, and restrict its network path to the IBM i. ## Options ### Direct import from views with a gateway **Fit:** Modest data volumes and a handful of core dashboards. **Effort:** Days to weeks once views are reconciled with XA screens. ### Staging warehouse feeding Power BI **Fit:** Large history, many users or a need to protect IBM i performance. **Effort:** Weeks, including incremental loading and a documented model. ### Add a Netray private Q&A layer over the same data **Fit:** Users want to ask questions beyond the dashboards, with data kept on-prem. **Effort:** Scoped as an add-on to the reporting views, reusing your definitions. ## Pitfalls - Connecting to raw XA files and rebuilding logic in DAX. Numbers will drift from the screens and trust will go. - DirectQuery against production during business hours, which can slow interactive users on the IBM i. - Skipping the metric definitions. Two people calculating on-time delivery differently ends the project. - Gateway on a laptop or an unmonitored server. Refreshes then fail silently on a Monday. - Ignoring multi-company or facility keys in XA, which silently mix entities in totals. ## Faq ### Does Power BI connect directly to Db2 for i? **Answer:** Yes, through the IBM Db2 connector or the IBM i Access ODBC driver, depending on your setup. You also need the on-premises data gateway to refresh in the Power BI service. Test driver versions on your gateway host early, since this is a common stumbling block. ### Should I use DirectQuery or Import? **Answer:** Import on a schedule is the safer default for XA, because it keeps interactive queries off the production IBM i. DirectQuery suits small, indexed datasets and real-time needs, but test the load at peak hours before rolling out widely. ### Do I need a data warehouse? **Answer:** Not for a few dashboards on modest data. A warehouse helps with long history, many users, cross-system joins or protecting the IBM i. If your reports start to slow the machine, that is the sign to stage the data. ### Can I ask questions in plain English? **Answer:** Power BI offers Q&A and Copilot features depending on licence and tenant settings, which work on the semantic model. For ad hoc questions beyond the model, a separate assistant over Db2 for i can be added, preferably showing the generated query for review. ### Can this run fully on-prem? **Answer:** Partly. Power BI Report Server can host reports inside your network, though it has fewer features than the cloud service. For plants with strict rules, pair it with an on-prem model for questions, and confirm licensing and features with Microsoft. ## Search terms infor xa power bi, power bi for infor xa, power bi ibm i db2, power bi as400 erp, infor xa reporting, infor xa dashboards, power bi on-premises data gateway db2 for i, infor xa bi integration, manufacturing dashboards ibm i --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json