Epicor Power BI Analytics: One Certified Semantic Model Across 14 Companies
Numlytics delivered Epicor Power BI analytics for a 14-company manufacturing and distribution group that could see every plant but never the group. By replacing about 340 company-specific reports with one certified Direct Lake semantic model on Microsoft Fabric, the team gained a consolidated P&L, 62 governed KPIs, row-level security by company and plant, and cut the month-end pack from six working days to two and a half.
The Challenge: Epicor Reporting That Did Not Add Up Across Companies
The group manufactures and distributes engineered components from 14 Epicor Kinetic companies across the US, Canada, Mexico and the UK. Each company had grown its own reporting: Power BI reports built on company-specific BAQs and Excel extracts, roughly 340 reports in all, owned by whoever had built them. Leadership could see each plant, but never the group.
Month-end was where it hurt. Finance spent six working days downloading trial balances, converting five currencies by hand and reconciling intercompany balances in spreadsheets before the board pack could be assembled. Operations leaders asked simple questions, such as which plants were shipping on time or where inventory was ageing, and got three different answers.
- No consolidated view: Each company reported in its own currency, chart of accounts and fiscal calendar. Group revenue and gross margin existed only in a manually built workbook.
- KPIs defined many ways: On-time delivery was measured against order date in one plant, request date in another and promise date in a third. Gross margin included freight in some reports and excluded it in others.
- Slow, fragile Power BI datasets: Import models fed by BAQs hit refresh limits and timed out. Report authors worked around this with ever-smaller extracts, which made the numbers less complete.
- Intercompany noise: Transfers between group companies inflated revenue and cost of sales until eliminated by hand at month-end.
- Inventory without context: On-hand, ageing and turns were available per warehouse but not by part family or plant, so slow-moving stock stayed invisible.
- Access by email, not by role: Plant managers received exports containing other companies’ data because there was no row-level security.
Why Epicor Power BI Analytics Needs a Semantic Model, Not More Connectors
Connecting Power BI straight to Epicor through BAQs or the REST API is fine for a single operational report. It does not produce group analytics, because the logic that makes numbers comparable, such as currency conversion, intercompany elimination, fiscal calendars and the definition of a shipment, ends up repeated inside each report. Good Epicor Power BI analytics starts below Power BI: a governed data model that every report shares.
Numlytics built that model on Microsoft Fabric, on top of an incremental Epicor ingestion layer (the pattern described in our Epicor to Microsoft Fabric data ingestion case study), and exposed it to Power BI through Direct Lake.
Epicor Power BI analytics by Numlytics: one certified semantic model on Fabric with shared dimensions, Direct Lake performance and row-level security by company, plant and role.
The Numlytics Approach: Six Steps to Group-Level Epicor Reporting
- 01KPI Dictionary Before Any Visual
Finance, sales, operations and supply chain leaders agreed one written definition for each KPI: owner, formula, source tables and exclusions. On-time delivery, for example, was fixed as shipped on or before the promise date. The dictionary grew to 62 governed measures and is stored beside the model.
- 02Gold Star Schemas by Subject Area
Epicor data already landed in Fabric was modelled into six Gold subject areas: finance GL, sales and backlog, inventory, production, procurement, and AR/AP. Each uses conformed dimensions for company, plant, date, customer, part, vendor and currency, so any report can slice across areas.
- 03Consolidation Logic Built Once
Currency conversion across five currencies, a common group chart-of-accounts mapping, fiscal-to-calendar alignment and intercompany elimination were built into the Gold layer, so group P&L and balance sheet are calculated, not assembled.
- 04Direct Lake Semantic Model
A single Power BI semantic model reads Gold Delta tables through Direct Lake, avoiding scheduled imports and the refresh limits of the old datasets. Time intelligence, ageing buckets and currency choice are handled with calculation groups instead of duplicated measures.
- 05Row-Level Security by Company, Plant and Role
Security roles map to Entra ID groups. A plant manager sees only their plant, a regional controller sees their companies, and group finance sees everything. Security is defined once in the model and inherited by every report.
- 06Report Suite, Certification and Adoption
The 340 legacy reports were mapped to the new model and consolidated into 22 certified reports in four apps for finance, sales, operations and supply chain. Owners were trained, the model was endorsed in the Fabric workspace, and retired reports were archived with redirects.
Epicor Power BI analytics by Numlytics: consolidated group performance across 14 companies with intercompany eliminated, reported in USD, with row-level security by company and plant.
What Group Leaders Now See in Epicor Power BI Dashboards
The first release gave the executive team a single scorecard. The same model now powers plant-level views for operations and drill-through from group P&L to company, plant and part family.
- Consolidated P&L: Revenue, gross margin and operating profit in USD with intercompany eliminated, drillable from group to company to plant. ★ Replaces a manually built month-end workbook
- On-time delivery: Shipments against promise date by customer, plant and part family, with late-line drill-through to the sales order. ★ One definition for all 14 companies
- Inventory health: On-hand value, turns, ageing buckets and slow-moving stock by plant and part family, with excess and obsolete flags. ★ Exposes tied-up working capital
- Production and WIP: Job status, WIP value, scrap and labour efficiency from Epicor job and labour data, by work centre. ★ Links shop floor to the P&L
- Working capital: AR and AP: DSO, ageing, and open payables by company and vendor, with cash-collection trends. ★ DSO fell by 7 days year on year
- Procurement spend: PO spend, receipt performance and vendor concentration across companies. ⚠ Cross-company vendor visibility for the first time
Before and After: Epicor Reporting Compared
- Group reporting: Spreadsheet assembled from 14 extracts
- Month-end pack: 6 working days
- Reports: ~340 company-specific reports
- KPI definitions: Different by company and report
- Report speed: Import refresh failures, slow visuals
- Currency and intercompany: Manual conversion and elimination
- Access control: Excel exports by email
- Group reporting: Consolidated P&L from one certified model
- Month-end pack: 2.5 working days
- Reports: 22 certified reports in 4 apps
- KPI definitions: 62 governed measures, one definition each
- Report speed: Direct Lake, typically under 3 seconds
- Currency and intercompany: Automated in the Gold layer
- Access control: Row-level security by company, plant and role
The Results
“Our controllers used to spend the first week of every month proving the numbers. Now they spend it explaining them. The board pack is built from the same model the plant managers use daily.”
— Group Financial Controller, Industrial Manufacturing Group (anonymised)