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 assessment