Epicor to Microsoft Fabric Data Ingestion: Replacing 1,940 BAQs with a Metadata-Driven Pipeline
Numlytics designed Epicor to Microsoft Fabric data ingestion for an industrial manufacturing and distribution group whose analytics depended on about 1,940 Business Activity Queries (BAQs) across 64 Epicor companies. The BAQ-driven extracts took 11 hours a night, loaded the Epicor application server that plants relied on, and produced inconsistent KPIs. A metadata-driven, replica-based Fabric architecture cut the full refresh to 38 minutes, retired or replaced 79% of the BAQs, and made onboarding a new company a two-day task.
The Challenge: 1,940 BAQs and an ERP Slowed by Its Own Reporting
The group ran Epicor Kinetic across 64 legal entities in North America and Europe, processing roughly 9 million transactions a year. Over several years the analytics team had pulled Epicor data into Microsoft Fabric the fastest way available: write a Business Activity Query (BAQ) in Epicor, publish it, and call it from a Fabric pipeline through the REST API. Every new report added another BAQ. By the time Numlytics was engaged there were about 1,940 of them.
The approach had stopped scaling. Each BAQ call is executed by the Epicor application server, which is the same compute the plants and finance teams use for order entry, shop-floor reporting and invoicing. Extracts and users were competing for the same resources, and the Epicor administrators were being asked to throttle analytics to protect the ERP.
- Nightly extract of 11 hours: Paginated BAQ calls ran from 8 pm and regularly overran into the morning. Finance opened reports on yesterday’s data, and sometimes the day before.
- Peak app-server pressure: Long-running BAQs pushed application-server CPU close to saturation during the extract window, and plant managers reported slow screens.
- Logic duplicated everywhere: Gross margin, backlog and on-time delivery were coded differently across hundreds of BAQs, so the same KPI returned different numbers by company.
- Full reloads, not changes: Most BAQs reloaded entire tables every night because there was no reliable way to ask for only the rows that had changed.
- Every acquisition was a project: Onboarding a new Epicor company took about three weeks of copying BAQs, editing company filters and building new pipelines.
- No owner, no inventory: Nobody could say which BAQs were still used. Retiring any of them felt risky, so the estate only ever grew.
Why BAQ-Based Epicor Data Ingestion Fails at Scale
BAQs are excellent for what they were designed for: operational queries and dashboards inside Epicor, written by people who know the business. They are not a bulk data-movement mechanism. When a BAQ is called through the Epicor REST API, the query runs through the business-object layer on the app server, results are paginated, and every call carries authentication and serialisation overhead. Multiply that by 64 companies and 1,940 queries and the extract becomes a workload the ERP was never sized for.
A proper Epicor to Microsoft Fabric data ingestion design separates three concerns that BAQs mix together: where data is read from (not the production app server), how change is detected (not full reloads), and where business logic lives (once, in the lakehouse, not in a thousand queries).
Epicor to Microsoft Fabric Data Ingestion: Options We Evaluated
- Tuned BAQs over REST: Rewrite the heaviest queries, add paging and parallelism, extend the window. Quick, but still runs on the app server and still reloads full tables. ⚠ Delays the problem, does not remove it
- Epicor REST API v2 incremental: OData v4 calls with filters on changed rows. Useful for logic that only business objects can compute, and for Epicor cloud tenants without database access. ★ Kept for about 2% of BAQs
- Readable replica (RODB) + Fabric: Read tables from an Always On readable secondary, or a log-shipped copy, using a SysRevID watermark. Zero load on the Epicor app server. ★ Chosen as the primary pattern
- Fabric mirroring / replication: Continuous replication of SQL Server tables into OneLake. Strong for a small set of very high-volume tables. ★ Retained as an option for hot tables
Where a client runs Epicor Kinetic in a vendor-hosted cloud tenant without database access, the REST v2 route with incremental filters becomes the primary pattern, with the same medallion layers downstream. In this project the group controlled its SQL Server estate, so the replica route was available and was the clear winner on load, speed and cost.
Epicor to Microsoft Fabric data ingestion by Numlytics: a metadata-driven architecture that reads the Epicor replica, loads only changed rows into a medallion lakehouse and serves Power BI through Direct Lake.
The Numlytics Approach: Metadata-Driven Epicor Ingestion in Six Steps
- 01BAQ Inventory and Usage Audit
Numlytics exported every BAQ, its owner, the tables it touched and 12 months of execution history from Epicor. Each one was classified as unused, a duplicate, a simple table extract, a calculation-heavy report or logic that depends on business objects. This single spreadsheet became the programme’s backlog and its audit trail.
- 02Replica Access and Reader Security
A readable secondary of the Epicor SQL Server databases was set up with a read-only, least-privilege login. Fabric reaches it through an on-premises data gateway. Production Epicor was never queried by the new platform.
- 03Control Tables, Not Hard-Coded Pipelines
One metadata layer defines which Epicor tables are loaded, for which companies, as full or incremental, how much history, and on what schedule. A single Fabric Data Factory pipeline reads those rows and fans out to parameterised Spark notebooks. Adding a company or table is a row in a control table.
- 04SysRevID Incremental Loads with Delete Detection
Epicor tables carry a SysRevID column that increases when a row changes. Each load stores a watermark, pulls only rows above it and merges them into Delta tables. Because source deletes are not visible to a watermark, a scheduled key comparison flags and soft-deletes removed rows.
- 05Bronze, Silver and Gold Medallion Layers
Bronze keeps the raw Epicor rows. Silver holds cleaned, company-keyed Delta tables, about 210 in total, that replaced the simple BAQs. Gold holds 28 star-schema models for finance, sales, inventory, production, procurement and AR/AP, where the shared business logic is written once.
- 06Parallel Run, Reconciliation, Retirement
For two close cycles the old BAQ pipelines and the new Fabric pipelines ran side by side. Totals were reconciled per company and period, and business owners signed off before any BAQ pipeline was switched off.
Epicor to Microsoft Fabric data ingestion by Numlytics: pipeline health, refresh time, app-server load and BAQ retirement tracking across 64 Epicor companies.
What Happened to the 1,940 BAQs
The audit showed that most of the estate did not need to be rebuilt. Only about one BAQ in five needed its logic reimplemented, and that logic is now written once in Gold instead of dozens of times.
- ~795 retired (41%): Unused for 90+ days or duplicates of another BAQ. Removed after owner sign-off. ★ Zero load, zero maintenance
- ~740 replaced by Silver (38%): Plain table extracts with no logic. Now served by about 210 Silver tables loaded incrementally. ★ Replica read, changed rows only
- ~370 rebuilt in Gold (19%): Joins and calculations rebuilt once in 28 Gold models and reconciled company by company. ★ One definition per KPI
- ~35 kept on REST (2%): Logic that only Epicor business objects calculate stays on the API, called incrementally. ⚠ Deliberate exception, not the default
Before and After: Epicor Data Ingestion Compared
- Source of extracts: Epicor application server, shared with ~1,100 users
- Nightly refresh: 11 hours, overran regularly
- Load pattern: Full reloads through paginated BAQ calls
- Number of ingestion objects: ~1,940 BAQs and ~1,940 pipeline activities
- KPI definitions: Dozens per KPI, different by company
- Onboarding a new company: ~3 weeks of BAQ and pipeline work
- Month-end reporting pack: 6 days to assemble
- Source of extracts: Epicor RODB readable replica, no app-server calls
- Nightly refresh: 38 minutes incremental, can run hourly
- Load pattern: SysRevID watermark, changed rows only
- Number of ingestion objects: 1 metadata-driven pipeline and control tables
- KPI definitions: 1 certified definition per KPI in Gold
- Onboarding a new company: ~2 days, one control-table row and validation
- Month-end reporting pack: 2 days, from reconciled Gold data
The Results
“We thought we had a Fabric problem. We actually had an ingestion design problem. Once reporting stopped running on the ERP, the plants noticed it was faster, and finance finally had numbers that agreed with each other.”
— Head of Data and Analytics, Industrial Distribution Group (anonymised)