›› Free Data Maturity Scorecard — Discover your analytics score in 3 minutes MY SCORECARD →
›› Free Fabric Capacity Estimator — Model your CU consumption before you upgrade ESTIMATE CAPACITY →
Epicor Microsoft Fabric Data Engineering Manufacturing & Distribution 2026 10 min read

Epicor to Microsoft Fabric Data Ingestion: Replacing 1,940 BAQs with a Metadata-Driven Pipeline

Industry
Manufacturing & Distribution
Challenge
~1,940 BAQs · Slow Extracts
Platform
Epicor Kinetic · Microsoft Fabric
Result
11 h → 38 min Refresh

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.
The inquiry behind this project: “We use BAQs to ingest data from Epicor to Fabric. There are 2,000-odd BAQs developed over time and we are seeing performance implications. We need a proper data ingestion mechanism to build ERP-level analytics on Fabric and Power BI.” This is the situation we see most often with multi-company Epicor estates, and it is a design problem, not a tuning problem.

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 architecture: Epicor ERP to RODB readable replica to Fabric Bronze and Silver with SysRevID watermark, Gold star schemas and Power BI Direct Lake with control tables

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

  1. 01
    BAQ 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.

  2. 02
    Replica 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.

  3. 03
    Control 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.

  4. 04
    SysRevID 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.

  5. 05
    Bronze, 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.

  6. 06
    Parallel 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 dashboard showing 38 minute refresh versus 11 hour BAQ extract, 52 percent lower app-server CPU and BAQ rationalisation across 64 Epicor companies by Numlytics

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

⚠ Before Numlytics
  • 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
✅ After Numlytics
  • 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

38 minFull RefreshDown from 11 hours of nightly BAQ extraction, with an hourly schedule now possible
−52%Peak App-Server CPUDuring the former extract window, once the BAQ pipelines were retired
79%BAQs Retired or ReplacedAbout 1,535 of 1,940 removed or replaced by Silver tables
~2 daysNew Company LiveDown from about 3 weeks, using the control-table framework

“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)
The scaling insight: BAQ ingestion gets slower as an estate grows. A metadata-driven replica-based design gets cheaper per company as it grows, because the 65th company reuses everything built for the first 64.

Technology Stack

Epicor Kinetic
Epicor RODB
SQL Server Always On
Microsoft Fabric
Fabric Data Factory
Fabric Lakehouse (OneLake)
PySpark Notebooks
Delta Parquet
Medallion Architecture
SysRevID Incremental Load
Power BI Direct Lake
On-premises Data Gateway

Frequently Asked Questions

For large multi-company estates, the most reliable pattern is to read from an Epicor readable replica (RODB) rather than the production app server, detect changes with the SysRevID column, and load only changed rows into Delta tables in a Bronze, Silver and Gold lakehouse. A metadata-driven control table defines tables, companies and schedules, so one pipeline serves every company. Where database access is not available, as on some Epicor cloud tenants, the Epicor REST API v2 with incremental filters is the alternative.
BAQs run on the Epicor application server and, when called through REST, return paginated results through the business-object layer. They compete with users for the same compute, reload full tables unless carefully filtered, and multiply as reports are added. With 2,000 BAQs across many companies, extract time and ERP load grow together. BAQs remain the right tool for operational queries inside Epicor, not for bulk analytics ingestion.
Yes. Numlytics audits every BAQ and classifies it as unused, a duplicate, a simple extract, a calculation, or business-object logic. Reports are repointed to Gold models and reconciled per company and period during a parallel run, and a BAQ pipeline is only switched off after business owners sign off the numbers. In this project about 79% of BAQs were retired or replaced and Power BI reports kept working throughout.
Related Case Study Epicor Power BI Analytics: One Certified Semantic Model Across 14 Companies What the Fabric Gold layer built here powers downstream — a Direct Lake semantic model with 62 KPIs, intercompany elimination and row-level security by company and plant. Read the case study →