How to connect Power BI to ECI E2 Shop System
Short answer
Connect Power BI to E2 Shop System through its SQL Server database, preferably via curated views or a small reporting copy, and use an on-premises data gateway if you publish to the Power BI service. Build job, on-time delivery, margin and purchasing dashboards, then add natural-language Q&A on top.
Most E2 shops already live in Excel exports and Crystal reports. Power BI is the natural next step because it reads the same SQL Server data and adds interactive dashboards, filters and sharing. The work is less about Power BI and more about shaping E2 data into something a dashboard can use.
E2 tables are built for transactions, not analysis. Jobs, operations, labor, material and PO data live in separate tables with codes and statuses that need translating. A thin model layer, whether SQL views or a small warehouse, saves you from repeating that logic in every report.
Once the model exists, you can go beyond static pages. Natural-language Q&A over the same model lets a manager ask which customers had late shipments last quarter and get a chart, with the calculation visible.
What teams use it for
On-time delivery by customer
Compare promised and actual ship dates by customer and part family, with a drill-through to the late jobs.
Quote versus actual margin
Show estimated and actual hours and material by job, highlighting operations that routinely overrun.
Work-in-process and backlog
Backlog by work center and due week to expose bottlenecks before they delay shipments.
Purchasing and vendor performance
Vendor on-time receipt, price changes over time and open commitments by buyer.
Shop floor labor and utilisation
Hours posted versus available by work center and employee group, with scrap and rework rates if captured.
Inventory aging and turns
Slow-moving stock, allocated versus free quantity, and material cost tied to open jobs.
ECI E2 Shop System integration surface
- SQL Server direct: Power BI can query the E2 database through the SQL Server connector, using Import or DirectQuery. Table names vary by version, so build on your own schema map.
- Curated views: views that decode statuses and join jobs, operations, labor and materials give analysts stable names and protect them from upgrade changes.
- Reporting copy or warehouse: a nightly copy to a separate database reduces load on the production server and allows history snapshots E2 itself may not keep.
- On-premises data gateway: required for scheduled refresh in the Power BI service when the database sits behind your firewall.
- Excel and Crystal exports: workable for small one-off needs, but they do not scale to shared, refreshed dashboards.
How it works
- 1
Pick the first three dashboards
On-time delivery, backlog and quote versus actual usually pay back first. Define each metric in writing with the exact E2 fields.
- 2
Build the semantic layer
Create SQL views or a small star schema for jobs, operations, customers, parts and dates. Handle status codes, cancelled jobs and partial shipments explicitly.
- 3
Decide on production versus copy
For anything beyond light use, replicate to a reporting database overnight to protect the transactional server.
- 4
Connect Power BI Desktop
Use Import mode for most models. Reserve DirectQuery for cases where near-real-time matters and the server can handle it.
- 5
Publish and schedule refresh
Install the on-premises data gateway on a server that can reach SQL Server, then schedule refresh in the Power BI service.
- 6
Secure with roles
Apply row-level security for sales territories or customer groups and hide cost measures from roles that should not see them.
- 7
Add natural-language Q&A
Use Power BI's built-in Q&A features where available in your licence, or a private assistant over the same model for richer questions.
Example flows
Weekly production meeting
Trigger: Monday morning, after the overnight refresh.
- Dashboard shows jobs due this week by work center.
- Manager filters to jobs with open material shortages.
- Drill-through lists the PO lines and promise dates.
- Buyer follows up on the top three items.
Result: The meeting works from one shared, current view instead of printed lists.
Ask a question in plain language
Trigger: Owner asks which part families lost margin last quarter.
- Question goes to an assistant connected to the semantic model.
- It maps terms to defined measures and groups by part family.
- It returns a table and chart plus the definition of margin used.
Result: Faster answers without waiting for a report writer, and with metric definitions visible.
Keeping data on-prem
Power BI Desktop and the on-premises data gateway let you keep E2 inside your network while publishing reports to the Power BI service, though report data does cache in the cloud service once published. If that is not acceptable, Power BI Report Server is a licensed on-premises option, and open-source tools can also read the same SQL views. For natural-language questions on private data, a local open-weight model serving a read-only semantic layer keeps both data and prompts in-house. Place the gateway and reporting copy on a hardened server with access limited to the SQL login used for reporting.
Security checklist
- Dedicated read-only SQL login for reporting; never reuse an admin account.
- Row-level security and separate workspaces for cost-bearing reports.
- Gateway host patched, restricted, and monitored; credentials stored in the gateway only.
- Review what data is cached in the cloud service when contracts restrict hosting.
- Version-control the SQL views and dataset definitions, and review changes.
Ways to get it done
Excel and Crystal exports into Power BI
Effort: Days; manual refresh and fragile.
Small shops starting out or testing which dashboards matter.
Direct SQL Server model built in-house
Effort: Two to six weeks for three dashboards.
Teams with a SQL-literate analyst who can maintain views and a gateway.
Partner-built model with Netray
Effort: Scoped engagement, often a few weeks.
When you want an E2-aware semantic layer, dashboards and a private natural-language layer.
Pitfalls
- !Reporting directly against production tables during peak shop hours without checking load.
- !Inconsistent metric definitions, such as what counts as on time, between dashboards.
- !Ignoring partial shipments and split lots, which skew delivery metrics.
- !Depending on labor data from a shop floor that does not report consistently.
- !Publishing cost data to a broad workspace by default.
Frequently asked questions
Can Power BI connect directly to E2 Shop System?
Yes in practice, through the SQL Server database that E2 uses. You still need to map tables for your version, and ideally build views. Confirm database access terms with your reseller or ECI.
Do I need a data gateway?
If you publish to the Power BI service and the database is on your internal network, yes, an on-premises data gateway is how scheduled refresh reaches it. Desktop-only use does not need one.
Import or DirectQuery?
Import is faster and lighter on the server and works for most job shop dashboards refreshed daily or hourly. DirectQuery gives fresher data but can load SQL Server and limits some modelling features.
Can I ask questions in plain English?
Power BI offers natural-language features depending on licence and region. For richer questions on private data, a separate assistant over the same semantic model can run on-prem. Test on your own metrics.
Is a warehouse necessary?
Not for a small shop with light use. A reporting copy becomes worthwhile when refreshes slow the production server or you need history that E2 overwrites, such as past due dates.
More for ECI E2 Shop System
Want Power BI working on your ECI E2 Shop System?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessmentTry it here, free
E2 Shop System AI Assistant Sizing and Value Calculator
Estimate peak concurrency, GPU count, index size, hours saved and payback for a private AI assistant on E2 Shop System.
Keep exploring
Free tools
- E2 Shop System AI Assistant Sizing and Value CalculatorEstimate peak concurrency, GPU count, index size, hours saved and payback for a private AI assistant on E2 Shop System.
- E2 Shop System Private AI Layer Blueprint GeneratorFill in your version, database, hosting and use cases to get an architecture plan for a private, on-prem AI assistant on E2 Shop System, ready to share with IT and security.
- JobBOSS to E2 Shop System Migration ChecklistA practical checklist for moving a small shop from JobBOSS to E2 Shop System, from data audit through cutover and first month close. Works in reverse too.
ERP fixes and how-tos
- How to Add a Private AI Layer to E2 Shop System On-PremHow to add a private on-prem AI assistant to E2 Shop System: database views, quote and job data, document retrieval, role scope, sizing and phased rollout.
- How to Create a Quote in E2 Shop SystemHow to build a quote in E2 Shop System, from header setup through routing, material, and outside processing, to converting an accepted quote into a job.
- E2 Shop System Error 5093: Causes and How to TroubleshootE2 Shop System (Shoptech) error 5093: how to isolate whether it is a database, permission, driver or network problem, and the checks to run before calling support.
Guides
- Job Shop ERP Comparison: JobBOSS, E2 Shop System, ProShop and Global ShopJob shop ERP comparison for CNC machine shops: JobBOSS2, E2 Shop System, ProShop, Global Shop Solutions and Epicor Kinetic, and the questions to ask.
- ERP for Metal Fabricators: What the System Must HandleERP for metal fabricators and sheet metal shops: nested material, multi-level BOMs, heat number traceability, outside processing, and shop floor data.
- ERP for Aerospace Build-to-Print Machine Shops: What to RequireWhat an AS9100 build-to-print machine shop needs from ERP: revision control, traveler, lot traceability, outside processing, FAI and certs.
AI on your ERP
- AI for ECI JobBOSS2 and E2 Shop SystemAdd AI to ECI JobBOSS2 or E2 Shop System for faster quoting, tighter scheduling, and shop-floor answers, without sending your job data to the cloud.
- AI for E2 Shop System in Metal Fabrication: Quote History, Material Yield and Delivery RiskPrivate AI for E2 Shop System fabricators: sheet and plate quoting history, nesting yield, welding job costs and delivery risk, answered from your own data.
Integrations
- How to connect Power BI to ECI JobBOSS2Connect Power BI to JobBOSS2: SQL Server views, on-premises data gateway, a reporting database, key job shop dashboards, and adding natural-language Q&A.
- How to add AI agents to ECI E2 Shop SystemHow to add AI agents to ECI E2 Shop System: a read-first tool layer over the E2 SQL database, human approval on writes, and a private on-prem model option.
Software
- ECI E2 Shop SystemIndependent profile of ECI E2 Shop System, a job shop ERP: what it does, core modules, integration options, strengths, limits, alternatives and AI use cases.
- ECI JobBOSS2Independent profile of ECI JobBOSS2, a job shop ERP: what it does, main modules, integration options, strengths, limits, alternatives and where AI can help.