P21 to Power BI, done for you
Prophet 21 already records every order line, receipt, shipment and invoice — it just won't show you margin, turns and fill rate on one page. A senior US-based engineer builds the connection, the model and the scheduled refresh, so the numbers are waiting for you each morning.
Representative dashboard — sample data
Distribution reporting, rebuilt
Wholesale distributors on Epicor's Prophet 21 tend to describe the same problem. The system is doing its job — orders go in, product ships, invoices post — but answering “which customers are actually profitable after freight and rebates?” means running three reports, exporting them, and reconciling the totals in a workbook somebody built years ago.
A Prophet 21 Power BI dashboard replaces that routine with one governed model. We stand up the access route your instance allows, land the records, shape them into a star schema, write the DAX and set scheduled refresh — so sales, gross margin, inventory turns, fill rate and AR aging all resolve from the same set of definitions instead of five different exports.
What we build from P21
Buy a block of prepaid Flex hours and, if you're unhappy within the first 4 hours, we refund you in full — no questions asked. We scope each task and quote the hours before work starts, hours deduct only for work you approve, and your banked hours never expire.
Why distributors hire this out
Nobody stalls on picking a chart type. Distribution BI stalls on getting clean, refreshable data out of the ERP — and on agreeing what “margin” and “on time” mean before anything is measured.
Power BI ships no first-party P21 connector. Prophet 21 keeps its records in Microsoft SQL Server, so we usually read a read-only replica — or the P21 API, or a warehouse your team already stages, whichever route your administrator can actually approve.
Your item master, customer and ship-to hierarchy, salespeople, branches and vendors become proper dimensions. A margin number on the summary page then drills to the customer, the product group and the individual order line underneath it.
Gross margin at the invoice line is easy. Margin after inbound freight, vendor rebates, price exceptions and returns is the number owners actually manage by — so we agree the treatment of each with you before the measure is written.
We land the extract, pull only rows that changed on each run, and refresh on a schedule — with an on-premises gateway where P21 sits on your own network. Nobody's Monday starts with an export, and nothing depends on one person remembering.
One engineer with 20+ years on the Microsoft platform does the extract, the model and the DAX. Prepaid Flex hours never expire, there is no project minimum, and ERP work of exactly this shape is what they are for.
Because the ERP's reporting layer answers one question at a time, for one person. A report returns exactly the rows you asked for; someone still has to trend them, join them to a second report and reconcile the two by hand. The workbook that results quietly becomes the real reporting system — unversioned, owned by one person, and impossible to audit.
The symptoms are consistent across distributors. Two managers quote different fill rates because one measured order fill and the other line fill. Margin looks healthy until inbound freight and rebate accruals are applied, and by then the quarter is closed. Dead stock is discovered when someone walks the warehouse rather than when the turns report says so. A semantic model fixes this at the root: one set of facts and dimensions, one agreed definition of margin and of on time, and visuals that recompute themselves.
The six areas P21 buyers ask for first: sales by customer, rep and branch; gross margin at a grain you can act on; inventory turns and dead stock; order fulfillment and fill rate; AR aging and DSO; and vendor performance. Each becomes a page in one model rather than six disconnected reports.
Every one of them maps to records your Prophet 21 instance already holds. The mapping below is described at a general level — the exact tables or endpoints depend on your version, your modules and how your team has configured them, which is what the demo call is for.
| Dashboard area | P21 data it surfaces | Question it answers |
|---|---|---|
| Sales by customer & rep | Order and invoice headers and lines, with customer, ship-to, salesperson and branch | Where is revenue coming from, and who is growing or slipping? |
| Gross margin | Invoice lines with cost of goods, plus freight, rebate and price-exception treatment you define | Which products, customers and reps actually make money? |
| Inventory turns & dead stock | Item master, on-hand and available quantities by location, receipts and issues | What is turning, what is aging, and where is capital sitting still? |
| Order fulfillment & fill rate | Order lines against ship confirmations, backorders and promise dates | Are we shipping complete, and on the date we gave the customer? |
| AR aging & DSO | Open receivables by customer, terms, due date and payment history | Who owes us, how late is it, and whose behaviour is trending worse? |
| Vendor performance | Purchase orders against receipts, lead times and cost changes | Which suppliers hit their dates and hold their pricing? |
Through whichever supported route your deployment allows: a read-only replica of the SQL Server database, the P21 API, or a reporting warehouse your IT team already stages. We land the records, shape them into a star schema, write the measures and schedule the refresh — so nothing depends on a person remembering to export.
Because Prophet 21 sits on Microsoft SQL Server, a read-only replica is usually the cleanest path: it lets us pull years of order, inventory and receivable history incrementally instead of asking a live production system for it, and Power BI's SQL Server connector documentation (opens in new tab) covers the platform side. Where direct database access is restricted, the API route works for scoped, scheduled pulls. If your team already maintains a warehouse fed from P21, we build on that first — one version of the extract logic beats two. The same database-first approach is described on our SQL Server dashboard development page.
Whatever the route, we don't point Power BI at production and hope. Each refresh moves only changed rows, following Microsoft's incremental refresh guidance (opens in new tab), and where P21 runs on your own network an on-premises data gateway (opens in new tab) carries the connection. We fix breakages out of your banked hours.
It depends on how P21 is deployed, how much history you need, and what your administrator is willing to open up. This is the honest comparison behind the scope estimate you approve before any hours are spent.
| Access path | Best for | Tradeoff |
|---|---|---|
| Read-only SQL Server replica | Most builds; long history and incremental loads without touching production | Needs DBA cooperation, and a gateway when P21 is on your network |
| Prophet 21 API | Sites where direct database access is restricted; scoped, scheduled pulls | Narrower coverage than the database and slower for bulk history |
| Existing reporting warehouse | Distributors already staging P21 data for reporting | Only covers what has been staged; the gaps still need filling |
| Scheduled report export | Small, stable extracts and quick proofs of concept | Stays semi-manual; does not scale to years of transactions |
| Landed copy + star schema | Every production build, on top of any route above | Adds a storage layer — which is the point, not a cost |
Single-branch and multi-branch wholesale distributors running Prophet 21 — industrial, electrical, plumbing, HVAC, fastener, janitorial and similar trade supply businesses — usually with an owner or controller who is tired of rebuilding the same workbook. You buy prepaid Flex hours that never expire: Starter $2,500, Growth $4,600 or Power $8,000, with rates from $100/hour on the largest block.
Most tasks ship in 3–5 business days once you approve the scope estimate; a first full ERP build is larger and gets a clear timeline up front. The usual pattern is a Growth or Power block for the connection, the model and the first pages — that work is front-loaded — then the remaining hours spent on extra views, DAX changes and refresh care. There is no project minimum and no retainer; the mechanics are laid out on how prepaid Flex hours work.
If you want the metric-by-metric view rather than the ERP-specific one, our Power BI dashboards for wholesale distributors page covers fill rate, turns and vendor scorecards across any system. Distributors who also manufacture should read Power BI for manufacturing and logistics, and if you run Epicor's manufacturing ERP alongside P21, the equivalent build is described on our Epicor Power BI dashboard page.
Everything. The Power BI file, the semantic model and DAX, the extraction logic, the refresh schedule and the service account all live in your tenant and your environment. There is no black box, no licence to us and no lock-in — if you never send another request, the dashboards keep running.
We publish to your workspace, document the access route and the records we pull, and walk your team through the model so someone internal can maintain it. Most distributors then keep a few banked hours for what follows: a page for a new branch, a measure that has to track a changed rebate agreement, or a refresh that broke when credentials rotated. That is post-launch support without a retainer — the same hours, spent when you need them.
business days, typical
hours, never expire
own everything
Connection, modeling, DAX and refresh care all draw from the same banked hours. See Flex pricing →
How it works
Tell us how P21 is deployed, which branches and companies are in scope, how you treat freight and rebates, and which fill rate you manage by. We reply with a scope and an hours estimate before any work begins — hours deduct only for builds you approve.
A senior US-based engineer sets up the access route with your administrator, lands the records incrementally, builds the star schema and the DAX, and ties totals back to the P21 reports your team already trusts before anyone sees a chart.
We publish to your workspace, configure the gateway and scheduled refresh, add row-level security so branch managers and reps see their own numbers, document everything and hand it over. You own it outright; we keep the refresh healthy from your banked hours.
Representative perspective
We had a report for everything and a spreadsheet that stitched four of them together. Now margin by customer is on a page that updates overnight, and the branch managers stopped arguing about whose number was right.Controller, multi-branch industrial distributor
Representative perspective
Agreeing one definition of fill rate, and one of margin after freight, was worth more to us than any visual on the page. The dashboard just made the agreement visible every morning.Operations director, wholesale supply business
Tell us how P21 is deployed, which branches are in scope and the reports you need first. We'll confirm the access route, scope the task and quote the hours before any work begins — drawn from prepaid Flex hours that never expire, with no project minimums.
FAQ
No first-party P21 connector ships with Power BI, so we build the pipe ourselves. Prophet 21 stores its records in Microsoft SQL Server, so most builds read from a read-only replica; where direct database access is restricted we use the P21 API or a warehouse your team already stages. We confirm the route before any hours are spent.
Usually a read-only database login scoped to the areas behind the reports you want, or API credentials if that is the approved route. Where P21 runs on your own network we also need an on-premises data gateway so the Power BI service can reach it. The exact list goes in your scope estimate first.
Not the way we build it. We do not point reports at your live production database and hope. We land an extract on a schedule, refresh only the rows that changed, and let Power BI query that modeled copy — so order entry keeps its performance while the dashboards stay current.
That is usually the point. A P21 report answers one question for one person on request; we move the same logic into a modeled dataset that refreshes on a schedule, so margin, fill rate and AR aging are waiting each morning instead of being exported, pasted and emailed by hand.
No. We are an independent, US-based Power BI practice and claim no Epicor or Microsoft partnership. Our engineers are Microsoft-certified, and we build only with documented, supported tooling: the access route your P21 administrator approves, plus Power BI's published refresh, gateway and semantic model features.
Book a free demo
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.
The fastest way to reach us is the form — tell us the task and we'll reply within one business day.