Power BI & Microsoft Fabric for Epicor Prelude

Can Power BI report accurately on Epicor Prelude?

Yes — but not by pointing Power BI at the database. Epicor Prelude is a MultiValue (Pick-model) ERP, and four structural mismatches have to be handled explicitly before any number can be trusted: nested multivalued attributes must be decomposed into separate facts or they inflate revenue through fan-out; I-type virtual dictionary fields are computed at read time and are absent from a raw file dump, so they must be replicated or materialized; decimals are stored as scaled integers whose position lives in the dictionary conversion code; and internal dates are integer day-offsets from December 31, 1967. The working pattern is staged batch extraction into a Fabric or Azure lakehouse through a clustered on-premises data gateway, a conformed star schema at documented grain, and an automated reconciliation harness that ties every fact to an ERP-native control total on every refresh.

Are you getting accurate data from Epicor Prelude?

In most Prelude shops the largest single group of report consumers is the executive team. They are also the group least able to check the number they are handed. A branch manager knows when utilization looks wrong because they can see the yard. A CFO looking at a margin trend has nothing to compare it against except the last version of the same report.

That is why Prelude BI projects rarely fail on visual design. They fail when one number is challenged, cannot be traced back to the ERP record that produced it, and every other number on the page becomes suspect by association. The question worth asking before a single dashboard is built is not whether the reporting looks good — it is whether your C-level team can make accurate decisions from it.

Is your C-level team able to make accurate decisions with Epicor?

Six questions we ask on the first call. If more than two are uncomfortable, the reporting layer is the problem — not the people reading it.

If two of your executives pull the same metric from Prelude on the same morning, do they get the same number?
When a figure is challenged in a board meeting, can someone trace it back to the Prelude record that produced it — the same day?
Does every report page tell you when the data was last successfully refreshed, or does it just tell you what time it is now?
If an overnight refresh fails, do you find out within minutes, or at the Monday leadership meeting?
Can you compare this quarter’s margin to the same quarter last year using the pricing that was actually in force then?
Does anyone reconcile the dashboard to the sales journal and the AR trial balance automatically, or is it done by hand when someone complains?

What inaccurate Prelude reporting looks like from the top

Every row below is a specific, diagnosable defect — not a data-quality generality. Each one has a known cause in the MultiValue model and a known fix.

Executive-visible reporting symptoms in Epicor Prelude, their technical cause in the MultiValue data model, and the business consequence
What the executive sees	Technical cause	Consequence
Two executives quote different revenue for the same month	Multivalue fan-out counted extended price once per lot allocation in one report and not the other	Board packet and sales review disagree; both numbers lose credibility
Margin looks strong until the controller recalculates it	Price-contract dimension overwritten in place, so historical margin is computed against current pricing	Margin history is unauditable and prior-period comparisons are meaningless
Currency figures are off by a factor of 100	MD2 scaled integers pulled raw without applying the dictionary conversion code	Every financial visual is wrong by a constant that nobody spots because it looks plausible
A dashboard field simply is not in the extract	The value is an I-type virtual dictionary item computed at read time, never stored on disk	"The report does not match the screen" — the single most common Prelude BI complaint
Branch managers refuse to accept fleet utilization	Utilization modeled off billing events instead of a daily per-unit snapshot	Units out, unbilled, or on exchange are systematically undercounted
Yesterday’s numbers are on screen today	Overnight refresh failed and the page reports NOW() instead of the extract watermark	Decisions get made on stale data with no visible signal that it is stale

Why Prelude reporting breaks in Power BI

Four structural mismatches between MultiValue and the relational model Power BI expects. Every failed Prelude BI project we have seen traces to at least one of these going unaddressed.

1. Nested attributes create silent fan-out

MultiValue stores multivalued and subvalued attributes inside a single record — a sales order line can carry several lot allocations, ship-to splits, or kit components in one field position. Flattening those into rows multiplies the parent record. Extended price gets counted once per child value. Revenue inflates, nobody notices for a quarter, and trust in the model is gone.

Fix: Extract parent grain and child grain as separate facts. Never let an allocation detail sit on the same row as an order-line amount.

2. Dictionary-computed fields don't exist in the data

A large share of what users see on a Prelude screen is produced by I-type (virtual) dictionary items — expressions evaluated at read time, not values stored on disk. Dump the file, and the field is simply absent. This is the single most common cause of "the extract doesn't match what the screen says."

Fix: Inventory the dictionary before writing a line of ETL. Every I-type feeding a reported number gets either replicated in the transformation layer or materialized at extraction. Both paths get documented and unit-tested.

3. Numbers are scaled integers

MultiValue stores decimals as integers with the decimal position carried in the dictionary conversion code. An MD2 field storing 12345 means 123.45. Pull it raw and every currency figure is off by a factor of one hundred — or a thousand, on MD3 fields. Mixed scaling across files makes the error non-uniform and hard to spot.

Fix: Conversion codes are read from the dictionary and applied programmatically, not hand-mapped per field.

4. Dates and times are integer offsets

MultiValue internal dates count days from December 31, 1967. Times are seconds since midnight. Neither is a date type to Power BI without explicit conversion. Get one of these wrong and fiscal-period reporting is off by decades, not days.

Fix: Conversion is applied in Power Query at load, not in report-level DAX where it silently diverges between visuals.

Power Query conversion patterns

Internal date to a real date:

Date.AddDays(#date(1967, 12, 31), [INTERNAL_DATE])

Internal time to a time value:

#time(0,0,0) + #duration(0, 0, 0, [INTERNAL_TIME])

Naive flattening of a multivalued lot-allocation attribute multiplies the parent order line and counts extended price once per child value. The correct decomposition keeps revenue at order-line grain in one fact and allocations in another.

The extraction layer

DirectQuery against a live MultiValue box is not a production pattern. It puts analytical load on the transactional system distributors run their branches on, and MultiValue ODBC drivers are not built for the query shapes a Power BI visual generates.

Five patterns, in descending order of how often we deploy them:

Staged batch extraction (default)
Scheduled UniBasic extract routines write delimited files at defined grains. Fabric Data Factory or Azure Data Factory lands them in a Lakehouse bronze layer through the on-premises data gateway. Transformation happens in silver. This is the pattern that survives contact with a real distribution business.
ODBC/JDBC to a staging warehouse
Where a licensed driver exists and performs acceptably, pull to Azure SQL or a Fabric Warehouse on a schedule — never straight into a semantic model. The warehouse absorbs the driver's limitations so the model doesn't have to.
Change-data capture via transaction log files
Where Prelude customizations already write audit or transaction-history files, those become the incremental spine. Cheapest path to near-real-time when it exists.
UniObjects / mv.NET service layer
For targeted low-latency lookups — an inventory availability tile, an open-order count. Not for bulk.
Middleware/iPaaS
Justifiable when Prelude integration is already funded for e-commerce or EDI and BI can ride the existing pipe. Rarely worth standing up for BI alone.

Gateway note

MultiValue platforms are almost always on-prem. The on-premises data gateway is a hard dependency — clustered for HA, sized deliberately, and monitored. A single unclustered gateway on someone's workstation is the most common cause of “the reports were down all week.”

The star schema — and reusing what you already have

Most Prelude shops have partial star schema assets already: a SQL Server staging database, a data mart built by someone who left, a set of Access queries that became load-bearing. We don't discard those. The first sprint inventories them, keeps the conformed dimensions that are correct, and rebuilds only the facts where the grain is wrong.

Reusable in almost every case: an existing Date dimension, an existing Customer or Item dimension where surrogate keys are already assigned, and any documented business-rule logic — that last one is often the most valuable artifact in the building.

Typically rebuilt: fact tables where grain drifted, anything that flattened multivalues into the parent row, and any dimension that mutates in place where history is needed.

Conformed dimensions

Conformed dimensions in an Epicor Prelude star schema and their modeling notes
Dimension	Notes
Date	Marked as date table. Fiscal calendar aligned to the distributor's periods, not the Gregorian default.
Customer	SCD Type 2 where credit terms or ship-to hierarchy drive analysis. PII isolated — see governance.
Item / Product	Product hierarchy, vendor line, stocking class.
Branch / Warehouse	Drives most of the security model.
Vendor	—
Salesperson	Type 2 — territory reassignment is the whole point.
Price Contract	Type 2, mandatory. Contract pricing overwritten in place makes margin history unauditable.
Equipment Unit	Rental fleet. Serialized asset grain.
Technician	Service labor.

Fact tables and grain

Fact tables in an Epicor Prelude star schema, the grain of each, and the fact type
Fact	Grain	Type
Sales Order Line	One row per order line	Transaction
Invoice Line	One row per invoice line	Transaction
Inventory Transaction	One row per stock movement	Transaction
Inventory Balance	One row per item / branch / day	Periodic snapshot
AR Aging	One row per open item / period-end	Periodic snapshot
Purchase Order Line	One row per PO line	Transaction
Rental Contract Lifecycle	One row per rental agreement, updated in place	Accumulating snapshot
Rental Billing	One row per billing event	Transaction
Fleet Utilization	One row per unit / day	Periodic snapshot
Service Order Labor	One row per technician time entry	Transaction
Service Order Parts	One row per part issued	Transaction
Lot / Serial Allocation	One row per allocation	Transaction — separate from order line, never joined to it for revenue

The rental modeling point that matters

Rental revenue and fleet utilization are different grains and cannot share a fact. Utilization is a daily periodic snapshot per unit — a unit on rent for 40 days generates 40 rows whether or not it was billed. Revenue is event-driven. Modeling utilization off billing events systematically understates units that are out, unbilled, or on exchange. That single error is why most rental dashboards report utilization numbers the branch managers refuse to believe.

Model discipline

Single-direction relationships. Bidirectional filtering as a last resort with a documented reason.
Measures, not calculated columns. Calculated columns inflate model size and hide logic from lineage.
Import mode with incremental refresh via RangeStart / RangeEnd. Composite models only where a genuine low-latency requirement exists.
Field parameters for branch/entity switching rather than duplicated report pages.

Conformed dimensions shared across Sales, Inventory, Rental, Service, and Procurement subject areas, with the grain labeled on each fact.

Data freshness — engineered, not assumed

Freshness is a contract, not a hope. Every model we ship carries:

A watermark table in the warehouse recording the last successful extract timestamp per source file.
An "as of" indicator on every report page driven by that watermark — not by NOW(), which reports the moment the user opened the page and is actively misleading when a refresh has failed.
A defined freshness SLA per fact Sales and inventory typically nightly. AR at period close. Fleet utilization daily. Availability tiles, where justified, near-real-time through a service-layer call.
Staleness alerting When a watermark exceeds its SLA, the report surfaces a visible banner and an alert fires. Users learn within minutes, not at the Monday meeting.
Refresh failure runbook Gateway restart, driver timeout, extract routine failure, and lock contention each have a documented first response.

Calculation correctness — the reconciliation harness

The reason Prelude BI projects lose executive support is not that the dashboards look wrong. It's that one number is off, someone catches it, and every other number becomes suspect.

We ship an automated reconciliation harness alongside the model:

Control-total tickets Each fact reconciles to an ERP-native control total on every refresh — invoiced sales to the sales journal, AR fact to the AR trial balance, inventory value to the stock valuation report. Variance beyond tolerance halts publication rather than shipping a wrong number.
Row-count and checksum validation Between source extract and landed bronze, catching truncated or partial extracts before they reach silver.
DAX measure regression tests Core measures execute against a frozen test dataset with known expected results. Any change to the model runs the suite first.
Fan-out detection Automated check that fact row counts against source parent-record counts stay within expected ratios. Catches the multivalue explosion class of bug the day it is introduced.
Documented tolerance thresholds Rounding differences between MultiValue scaled arithmetic and DAX are real and expected. Defining acceptable variance up front is what separates a finding from a fire drill.

Fabric architecture

Medallion, deployed on Fabric capacity:

Bronze

Raw Prelude extracts, landed unmodified, retained for replay. No transformation, no filtering. When a number is disputed six months later, bronze is the audit trail.

Silver

Conformed and cleansed. Conversion codes applied, dates converted, multivalues normalized to child tables, dictionary I-type logic replicated and documented. This layer carries the data contracts.

Gold

Dimensional model. Star schemas by subject area — Sales, Inventory, Rental, Service, Procurement — sharing conformed dimensions.

Semantic layer

Direct Lake where the workload supports it, Import where it doesn't. Sensitivity labels applied through Purview. Row-level security by branch and legal entity. Object-level security on any PII-carrying dimension.

Prelude through the on-premises gateway into a Fabric medallion architecture, with the watermark write point and the reconciliation checkpoint that gates promotion to gold.

Deployment pipelines across Dev / Test / Prod with a promotion gate that requires the reconciliation harness to pass.

Where a distributor is not on Fabric capacity, the same architecture deploys on Azure SQL plus Power BI Premium Per User with no loss of pattern integrity — the medallion structure is not Fabric-specific.

Multi-jurisdiction data governance

For US-headquartered distributors with EU or Canadian operations. Where BI models cross borders, architecture decisions become compliance decisions — and they are far cheaper to make before the model is built.

The design principle that resolves most of it: a distribution BI model rarely needs personal data. Sales analysis needs account, territory, and item — not the buyer's name, direct line, or home address. Aggressive minimization at the extraction layer eliminates most obligations rather than managing them.

United States

No omnibus federal privacy law. Obligations come from the state patchwork, which continues to expand.
California is the exception that matters to distributors. The CCPA/CPRA B2B and employee-data exemptions sunset on January 1, 2023. Where every other state law carves out business contacts, California does not. A distributor’s data is overwhelmingly B2B contacts — meaning California obligations attach to exactly the data most other states exempt.
Consequences: access, deletion, and correction rights extend to buyer contacts at customer accounts; service-provider terms are required in the engagement contract; and deletion requests must be executable against the BI model, not just the ERP.
Practical: if a contact is deleted in Prelude on a CCPA request but persists in a Lakehouse bronze layer and three semantic model refreshes, the deletion is incomplete. Retention and purge policy must span the full pipeline. This is a build-time decision.
Most other state regimes exempt B2B and employee data, which narrows scope considerably — but sector overlays still apply where relevant.

European Union

Applies where there's an EU establishment, or goods and services are offered to EU data subjects, or EU behavior is monitored. For a US distributor: an EU subsidiary, EU-based employees, or EU customers.

Lawful basis. Internal business analytics generally rests on legitimate interests, Art. 6(1)(f), which requires a documented balancing assessment. Not consent.
Minimization, Art. 5(1)(c). The strongest argument for pseudonymizing or excluding contact-level data from the model entirely.
Records of processing, Art. 30. The BI pipeline is a processing activity and belongs in the ROPA.
Security, Art. 32. RLS, OLS, sensitivity labels, and encryption at rest and in transit are the technical measures.
DPIA, Art. 35. Triggered by large-scale systematic profiling. Standard distribution analytics usually falls short of the threshold; customer scoring or behavioral segmentation may not.
Transfers, Chapter V. Moving EU-origin data to a US-region Fabric capacity is a restricted transfer. Mechanisms: the EU–US Data Privacy Framework where the importer is certified, or Standard Contractual Clauses with a transfer impact assessment. The DPF remains subject to legal challenge — build so that a transfer mechanism can change without re-architecting.

The architecture answer: Power BI tenant home region is set at tenant creation and is not trivially changed. Fabric capacity region is chosen per capacity. Multi-Geo allows capacity in an EU region under a US-homed tenant — this is usually the correct answer for a US distributor with EU operations. Combine with region-scoped workspaces so EU-origin data never lands in a US-region capacity. Verify current Microsoft EU Data Boundary coverage against the Trust Center before making commitments to a client.

Canada

PIPEDA — Federal, applies to personal information in commercial activity. Meaningful consent, purpose limitation, and mandatory breach reporting to the Office of the Privacy Commissioner where there is real risk of significant harm.
Quebec Law 25 — The sharpest constraint. A privacy impact assessment is required before communicating personal information outside Quebec. Landing Quebec-origin personal data in a US Fabric capacity is exactly that. Also: mandatory designated privacy officer, breach register, and data portability rights in force since September 2024.
Alberta PIPA and BC PIPA — Provincial regimes for private-sector organizations in those provinces, with their own notice obligations around cross-border service providers.
Federal reform status — Bill C-27 and its proposed Consumer Privacy Protection Act did not survive the parliamentary session. Confirm current status before relying on any claim about pending Canadian legislation.

Practical for a Canadian branch: either a Canada-region Fabric capacity for Canadian-origin personal data, or a model design that excludes personal data from Canadian extracts entirely. The second is cheaper, faster, and usually sufficient for distribution analytics.

The governance checklist we run

Data inventory — what personal data exists in Prelude, in which files.
Necessity test — does the BI model actually need any of it. Usually no.
Minimization at extraction, not at the report layer.
Pseudonymization where contact-level grain is genuinely required.
Residency mapping — legal entity to region to capacity to workspace.
RLS by entity, OLS on PII-carrying dimensions.
Sensitivity labels via Purview, inherited downstream to exports.
Retention and purge policy spanning bronze through semantic model.
Documented lineage — required for both Art. 30 and any state access request.
Deletion-request runbook that executes across the full pipeline.

Legal entity to region to Fabric capacity to workspace, showing Multi-Geo placement of an EU capacity under a US-homed tenant.

How the engagement runs

Phase 1 — Assessment (2 weeks, fixed fee).

Dictionary inventory, source file profiling, existing star schema asset audit, jurisdiction and residency mapping, extraction pattern decision. Deliverable: architecture document, reconciliation target list, and a fixed-fee proposal for Phase 2. No hourly exposure.

Phase 2 — Foundation (typically 6–10 weeks).

Extraction layer, bronze/silver, conformed dimensions, first two subject-area stars — usually Sales and Inventory. Reconciliation harness live. Gateway clustered. First production reports.

Phase 3 — Expansion.

Rental, Service, Procurement subject areas. Fabric migration where applicable. Self-service enablement and a governed workspace structure.

Ongoing — Managed analytics.

Refresh monitoring, reconciliation alerting, model change management, quarterly performance review. Fixed monthly.

All phases fixed-fee against defined deliverables.

Why EPC Group

Legacy-source business intelligence is not a general Power BI skill. It requires someone who can read a MultiValue dictionary and someone who can design a Fabric medallion architecture — usually in the same room. That combination is what this practice is built around.

1,500+

Power BI enterprise deployments

500+

Microsoft Fabric projects

11,000+

Enterprise engagements

6

Microsoft Solutions Partner Designations

1,500+ Power BI deployments
500+ Microsoft Fabric projects
11,000+ total engagements since 1997
All six Microsoft Solutions Partner Designations
Original Project Crescent beta team — the program that became Power BI
Four-time bestselling author for Microsoft Press and Sams on the Microsoft data platform
Consecutive G2 Leader quarters

Microsoft Solutions Partner Designations

Data & AI (Azure)
Digital & App Innovation (Azure)
Infrastructure (Azure)
Business Applications
Modern Work
Security

4.4/5 on G2, G2 Leader — seven consecutive quarters. Practice led by Errin O'Connor, Founder & Chief AI Architect and a four-time bestselling author for Microsoft Press and Sams on the Microsoft data platform.

Serving organizations across all industries — Fortune 500, federal agencies, healthcare, financial services, government, manufacturing, energy, education, retail, technology, and global enterprises.

More on the underlying practice: Power BI consulting · Microsoft Fabric consulting · the Project Crescent beta team · choosing a Power BI partner

Epicor Prelude and Power BI — frequently asked questions

Can Power BI connect directly to Epicor Prelude?

Not reliably. Prelude runs on a MultiValue database whose nested attributes, dictionary-computed fields, and scaled-integer numerics are incompatible with the relational model Power BI expects. Production deployments use a staged extraction layer into a warehouse or Fabric Lakehouse, then model from there.

What is UniBasic and why does it matter for reporting?

UniBasic is the BASIC development language used in MultiValue environments — note that the name refers to more than one product, so the specific platform must be confirmed per installation. It matters because a significant share of Prelude business logic and report calculations live in UniBasic routines and dictionary definitions rather than in stored data, and that logic must be replicated in the transformation layer for BI numbers to match the ERP.

Why don't my Power BI numbers match Epicor Prelude?

Four common causes: multivalued attributes flattened into parent rows, inflating totals through fan-out; dictionary I-type virtual fields absent from raw extracts; scaled-integer decimals read without applying conversion codes; and MultiValue internal dates read as integers. All four are addressed at the extraction and transformation layer, not in DAX.

Should we move to Microsoft Fabric or stay on Power BI Premium?

Both support the same medallion architecture. Fabric is the stronger choice where a Lakehouse, Direct Lake semantic models, or unified governance across multiple sources is wanted. Power BI Premium Per User with Azure SQL staging is entirely viable for a single-ERP distributor and is often the lower-cost starting point.

How do we handle GDPR if we're a US distributor with European operations?

Start by minimizing — most distribution analytics needs account and territory data, not personal data, which removes the obligation rather than managing it. Where personal data is genuinely required, use Power BI Multi-Geo to place capacity in an EU region under your existing tenant, scope workspaces by region, and document a transfer mechanism for any data that does cross.

Does California's privacy law apply to our B2B customer contacts?

Yes. The CCPA/CPRA exemptions for business-to-business and employee data expired on January 1, 2023. California is currently the significant exception among state privacy laws — most others still exempt B2B contacts. For a distributor, this means access and deletion rights attach to buyer contacts at customer accounts, and deletion must be executable across the full BI pipeline, not only the ERP.

Can we reuse our existing data warehouse or star schema?

Usually in part. Existing date dimensions, established surrogate keys, and documented business-rule logic are typically worth keeping — that documentation is often the most valuable asset available. Fact tables generally need rebuilding where grain has drifted or where multivalued data was flattened into parent rows.

How do we know the data is current?

Through a watermark table recording last successful extract time per source, surfaced as an "as of" indicator on every report page and backed by staleness alerting when a defined freshness SLA is breached. Reports should never rely on the current timestamp, which shows when the page was opened rather than when the data last loaded.

Key Facts

Power BI & Microsoft Fabric for Epicor Prelude

Can Power BI report accurately on Epicor Prelude?

Are you getting accurate data from Epicor Prelude?

Is your C-level team able to make accurate decisions with Epicor?

What inaccurate Prelude reporting looks like from the top

Why Prelude reporting breaks in Power BI

1. Nested attributes create silent fan-out

2. Dictionary-computed fields don't exist in the data

3. Numbers are scaled integers

4. Dates and times are integer offsets

Power Query conversion patterns

The extraction layer

Staged batch extraction (default)

ODBC/JDBC to a staging warehouse

Change-data capture via transaction log files

UniObjects / mv.NET service layer

Middleware/iPaaS

Gateway note

The star schema — and reusing what you already have

Conformed dimensions

Fact tables and grain

The rental modeling point that matters

Model discipline

Data freshness — engineered, not assumed

Calculation correctness — the reconciliation harness

Fabric architecture

Bronze

Silver

Gold

Semantic layer

Multi-jurisdiction data governance

United States

European Union

Canada

The governance checklist we run

How the engagement runs

Phase 1 — Assessment (2 weeks, fixed fee).

Phase 2 — Foundation (typically 6–10 weeks).

Phase 3 — Expansion.

Ongoing — Managed analytics.

Why EPC Group

Microsoft Solutions Partner Designations

Epicor Prelude and Power BI — frequently asked questions

Can Power BI connect directly to Epicor Prelude?

What is UniBasic and why does it matter for reporting?

Why don't my Power BI numbers match Epicor Prelude?

Should we move to Microsoft Fabric or stay on Power BI Premium?

How do we handle GDPR if we're a US distributor with European operations?

Does California's privacy law apply to our B2B customer contacts?

Can we reuse our existing data warehouse or star schema?

How do we know the data is current?

Make the Prelude numbers match the ERP

Phone

Email

Address

Business Hours

Quick Response Guarantee