# How to connect Power BI to Epicor Vantage and Epicor 9 > Source: https://netray.co/integrations/epicor-vantage-power-bi > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Integrations ## Short answer Power BI reaches Epicor 9 most reliably through the Progress OpenEdge ODBC driver via an on-premises data gateway, or better through a staged data warehouse refreshed nightly. Build a small set of curated tables for jobs, inventory, purchasing and sales, then add natural-language Q&A on top of the same model. ## System Epicor Vantage / Epicor 9 ## Capability power-bi ## Intro Epicor 9 ships with reporting tools, and many sites also lean on Crystal or SSRS reports. Power BI is attractive because it brings modern visuals, mobile access and easy sharing. The catch is that the data sits in a Progress OpenEdge database, not SQL Server, and that shapes how you connect. Direct ODBC connections to Progress work, but performance can suffer with wide joins, and long refreshes can annoy the transactional users. Most mature setups land on a replica or warehouse, where data is reshaped once and Power BI reads clean tables. The other opportunity is Q and A. Once you have a well-modelled dataset, adding natural-language questions on top lets managers ask about late jobs or margin without waiting for a new report. ## Use Cases ### On-time delivery and backlog **Description:** OrderRel promise dates against shipment dates, with a backlog by customer and by week, drill-through to the specific lines. ### Job cost versus quote **Description:** Estimated versus actual labor, material and burden by job and operation, grouped by part family to feed the next quote. ### Inventory health **Description:** On-hand by warehouse and bin, slow and obsolete stock from PartTran activity, and coverage against upcoming demand. ### Purchasing performance **Description:** Supplier on-time receipt, price variance against last purchase and open commitment by due week. ### Shop floor efficiency **Description:** Labor hours booked versus standard by work center and shift, with scrap and rework trends from reported quantities. ## System Surface - Progress OpenEdge ODBC (DataDirect-style) driver: install the 64-bit driver on the gateway machine and match it to your OpenEdge version. Test connection and character set handling early. - On-premises data gateway from Microsoft: allows the Power BI service to refresh from a database inside your network without opening inbound ports. - A staging database such as SQL Server fed by nightly extract jobs (SSIS, scripts or an ETL tool), which converts Progress data into a star schema Power BI handles well. - BAQ results exported or exposed as query sources, useful when a business-approved query already encodes the logic you want. - Epicor 9 reporting tools (SSRS, Crystal and BAQ-based reports) which can remain for operational output while Power BI handles analysis. ## How It Works ### Decide direct, replica or warehouse **Detail:** Direct ODBC is fine for a pilot with a few small tables. For production, use a replica or warehouse so refreshes do not compete with users. ### Install and test the ODBC driver **Detail:** On the gateway or ETL server, configure a DSN with a read-only login and test sample queries, including date and decimal handling. ### Model the facts **Detail:** Create fact tables for order lines, job operations, inventory transactions and PO receipts. Add dimensions for part, customer, vendor, warehouse and calendar. ### Encode business rules once **Detail:** Put definitions like 'late', 'open' and 'standard cost' in the model or staging layer, not in each report, and document them. ### Build four starter dashboards **Detail:** Delivery, job cost, inventory and purchasing. Keep each page focused on a decision and on a role. ### Schedule refresh and secure access **Detail:** Refresh nightly or a few times a day. Apply row-level security by plant or sales region if needed. ### Add natural-language Q&A **Detail:** Use Power BI's Q and A features on a tidy semantic model with synonyms, or put a private NL query layer on the warehouse for sensitive data. ## Example Flows ### Nightly warehouse refresh to morning dashboard **Trigger:** 2:00 AM scheduled extract **Steps:** - ETL job reads incremental changes from Progress tables via ODBC. - It loads facts and dimensions into the warehouse. - The gateway triggers the Power BI dataset refresh. - Managers open the delivery dashboard at 7:00 AM. **Result:** Fresh numbers without anyone running exports. ### Ask a question on the dataset **Trigger:** Manager types, 'Which customers have the most late lines this month?' **Steps:** - Q and A maps terms to the model fields. - It filters on late flag and current month. - It returns a ranked chart. - The manager drills into the order lines. **Result:** A new view appears in seconds without a report request. ## On Prem Power BI Desktop and Report Server can run entirely inside your network if you cannot use the cloud service. Power BI Report Server lets you host reports on-prem against the same ODBC source or warehouse, with a subset of cloud features. Natural-language Q and A in the cloud service is not available the same way there, so many on-prem customers add a private query layer using an open-weight model against the warehouse. That keeps costs, customers and part data inside the plant while still giving users an ask-a-question experience. ## Security - Use a read-only account for ODBC and for the gateway data source. - Apply row-level security by plant, sales region or customer in the Power BI model. - Keep cost and margin tables in a separate dataset or role-restricted so wider audiences see only what they need. - Control workspace membership and sharing; dashboards often travel by email forward. - Protect the gateway machine like any server with database credentials. ## Options ### Direct ODBC through the gateway **Fit:** Pilot or small datasets where refresh load is acceptable. **Effort:** Days to a couple of weeks. ### Warehouse plus Power BI built in-house **Fit:** Best for long-term reporting if you have BI and ETL skills. **Effort:** Several weeks to months depending on scope and data cleanup. ### Netray-assisted model plus private Q and A **Fit:** When you want curated manufacturing models and natural-language questions without sending data to a cloud AI. **Effort:** A scoped first dashboard set is typically weeks. ## Pitfalls - Connecting dashboards straight to production tables and then slowing the shop during refresh. - Mismatched 32-bit and 64-bit ODBC drivers, which cause confusing gateway failures. - Rebuilding the same definitions of 'late' or 'cost' in every report so numbers disagree. - Ignoring UD fields, so key custom attributes never reach the dashboards. - Loading every table, creating a model nobody understands. ## Faq ### Can Power BI connect directly to Epicor 9? **Answer:** Indirectly. Epicor 9 stores data in a Progress OpenEdge database, so Power BI connects through an ODBC driver, usually via an on-premises data gateway. For anything beyond a pilot, a replica or staged warehouse is a more reliable pattern. ### Do I need a data warehouse? **Answer:** Not for a first dashboard, but it pays off quickly. A warehouse lets you reshape Progress tables into a clean model, keep history, join other systems and avoid refresh load on the production database. ### Which Epicor tables matter most? **Answer:** Typically order, job, part, inventory transaction and purchasing tables, for example OrderHed, OrderDtl, OrderRel, JobHead, JobOper, Part, PartTran, POHeader and PODetail. Names can differ slightly by version, so confirm in your schema. ### Can I keep it fully on-prem? **Answer:** Yes. Use Power BI Report Server or another on-prem reporting tool against a warehouse inside your network, and add a private natural-language layer with an open model if you want question answering without cloud services. ### How is this different from Epicor BAQs and SSRS? **Answer:** BAQs and SSRS suit operational lists and printed documents. Power BI adds interactive analysis, trends and sharing. Many sites keep both, using BAQs as vetted source definitions for the model. ## Search terms epicor vantage power bi, epicor 9 power bi, power bi epicor 9 odbc, power bi progress openedge, epicor 9 dashboards, epicor power bi gateway, connect power bi to epicor 9, epicor 9 reporting power bi, epicor 9 data warehouse, power bi manufacturing epicor --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json