# How to connect Power BI to ECI M1 > Source: https://netray.co/integrations/eci-m1-power-bi > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Integrations ## Short answer Because M1 runs on SQL Server, Power BI can connect to it directly through an on-premises data gateway, but a reporting view layer or small warehouse gives faster, safer and more understandable models. Add natural-language Q&A on top once the model is clean. ## System ECI M1 ## Capability power-bi ## Intro Many M1 sites already have a pile of Crystal or SSRS style reports and Excel exports that someone refreshes by hand each month. Power BI replaces that with scheduled refreshes and shared dashboards, and M1's SQL Server foundation makes the connection straightforward. The hard part is not the connector. It is the data model: M1 tables carry names, keys and status codes that make sense to experienced users but not to a report author or an AI. The work that pays off is a curated layer that turns them into clean facts and dimensions. Once that layer exists, it is also what you want for natural-language questions, so the BI project and the AI project share most of their groundwork. ## Use Cases ### On-time delivery dashboard **Description:** Track shipped lines against promise and request dates by customer, product family and month, with drill-through to late orders. ### Job cost and margin analysis **Description:** Compare estimated versus actual material, labor and overhead by job and part family to refine quoting. ### Shop capacity and WIP **Description:** Show open operations by work center, hours remaining and aging WIP, to spot bottlenecks. ### Purchasing and vendor performance **Description:** Measure vendor on-time receipt, lead time drift and price change on repeat items. ### Inventory health **Description:** Highlight slow-moving and excess stock, negative balances and items without recent usage. ### Quote hit rate **Description:** Track quotes issued, won and lost by customer, part type and estimator. ## System Surface - SQL Server database behind M1, reachable by Power BI through the SQL Server connector, in Import or DirectQuery mode. - On-premises data gateway installed on a server inside your network to let the Power BI service refresh from on-prem SQL Server. - A reporting database or warehouse, populated by nightly ETL or replication, for heavier analytics without touching production. - Existing report logic in Crystal or SSRS style reports, a reliable source of business rules to translate into measures. - Active Directory or Entra ID for Power BI row-level security aligned to M1 roles. ## How It Works ### Choose the data path **Detail:** For a pilot, Import mode from SQL views on a replica is enough. For growth, build a small star-schema warehouse refreshed nightly. ### Build a curated view layer **Detail:** Create fact views (shipments, job costs, receipts, inventory snapshots) and dimension views (customer, item, vendor, work center) with clean names. ### Install the gateway **Detail:** Install the on-premises data gateway on a server with network access to SQL Server, in a high-availability cluster if refresh is business critical. ### Model and measure **Detail:** Define relationships and DAX measures for on-time delivery, margin and WIP, validated against known M1 reports before publishing. ### Secure and publish **Detail:** Apply row-level security, publish to a workspace, set scheduled refresh outside shop transaction peaks, and share through apps. ### Add natural-language Q&A **Detail:** Configure Power BI Q&A or a Copilot-style feature if licensed, and add synonyms for shop vocabulary. For deeper questions, add a text-to-SQL layer over the same curated views. ## Example Flows ### Nightly refresh to morning dashboard **Trigger:** Scheduled refresh at 2 a.m. **Steps:** - Gateway queries the reporting views on the SQL Server replica. - Power BI loads fact and dimension tables. - Data alerts evaluate on-time delivery and late-order counts. - An alert emails the operations manager if on-time delivery drops below target. **Result:** Managers open a current dashboard instead of waiting for a monthly spreadsheet. ### Ask a question in plain language **Trigger:** A sales manager types: which customers had the most late shipments last quarter? **Steps:** - Q&A or an AI layer maps the question to the shipment fact and customer dimension. - It returns a ranked visual with the date filter applied. - The manager drills into the underlying orders. **Result:** Ad hoc questions get answered without waiting for a report author. ## On Prem If the Power BI cloud service is not acceptable, Power BI Report Server is a Microsoft option for hosting reports on your own servers, with feature differences from the cloud service, so check current licensing and capabilities. With either, the data gateway or Report Server connects to SQL Server inside your network, and data never needs to go to an external AI service. For natural-language questions on-prem, serve an open-weight model locally with vLLM or Ollama and point a text-to-SQL or tool layer at the same curated views. That keeps the whole path, from M1 to the answer, on hardware you control. ## Security - Connect with a read-only SQL login limited to the reporting views. - Implement row-level security so managers and customers see only their scope. - Restrict workspace and dataset sharing, and disable export where cost data is sensitive. - Secure the gateway server and its stored credentials, and monitor refresh failures. - Review cloud region and data-residency settings against customer requirements. ## Options ### Direct SQL views with Import mode **Fit:** Fast start for a few dashboards and a small user base. **Effort:** Days to a couple of weeks. ### Reporting database or warehouse with a governed model **Fit:** Best for many users, history snapshots and consistent metrics. **Effort:** Several weeks, plus ongoing ownership. ### Partner-built model with natural-language Q&A **Fit:** When you want BI and conversational questions from one clean layer, on-prem if required. **Effort:** A scoped pilot, often weeks. ## Pitfalls - DirectQuery against production at peak hours, slowing shop transactions. - Rebuilding report logic differently in DAX so numbers disagree with M1 reports. - No inventory or WIP snapshots, so history cannot be charted. - Overlooking multi-site, currency and unit-of-measure conversion. - Gateway on a desktop PC that gets rebooted, silently breaking refresh. ## Faq ### Can Power BI connect directly to ECI M1? **Answer:** M1 data lives in SQL Server, so Power BI can use its SQL Server connector with an on-premises data gateway. Confirm that direct database access is allowed under your ECI licence and support terms. ### Import or DirectQuery? **Answer:** Import is usually better for performance and for keeping load off production. DirectQuery is useful for near-real-time needs on small, indexed views. Many sites use Import with several refreshes a day. ### Do we need a data warehouse? **Answer:** Not to start. A view layer on a replica is enough for a pilot. A warehouse becomes worthwhile for history, many users and consistent definitions across departments. ### Can we ask questions in plain English? **Answer:** Yes, using Power BI's built-in question features if licensed, or a custom text-to-SQL or tool-based assistant over the same curated views. Quality depends heavily on clean names and synonyms. ### Can this stay fully on-prem? **Answer:** Yes, with Power BI Report Server or another on-prem BI tool plus a locally hosted model for natural-language questions. Check the current feature set of Report Server for your needs. ## Search terms eci m1 power bi, power bi for eci m1, eci m1 power bi dashboard, eci m1 reporting, connect power bi to eci m1, eci m1 sql server power bi gateway, job shop erp power bi, eci m1 analytics, eci m1 business intelligence, power bi on-premises data gateway erp --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json