# How to connect Power BI to Epicor Prophet 21 > Source: https://netray.co/integrations/epicor-prophet-21-power-bi > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Integrations ## Short answer Connect Power BI to Prophet 21 through SQL Server, ideally a read replica or a small warehouse, using the on-premises data gateway for scheduled refresh. Model sales, inventory and purchasing from curated views, then add natural-language Q&A so managers can ask questions without building visuals. ## System Epicor Prophet 21 ## Capability power-bi ## Intro Prophet 21 ships with reports and Crystal output, and many distributors also use Epicor's own analytics offerings. Power BI becomes attractive when you want dashboards that blend P21 with other sources, wider access for branch managers and executives, and a modern interface. Because P21 runs on SQL Server, the connection itself is straightforward. The real work is the data model. The usual mistakes are pointing Power BI directly at the production tables and rebuilding business logic in DAX that already exists in P21. Both lead to dashboards that load slowly, load the counter system, or disagree with the numbers people see in P21 reports. A deliberate layer of views, and often a replica or warehouse, avoids most of that. This page walks through the connection options, the on-prem gateway, the dashboards that distributors actually use, and how to add natural-language questions on top. ## Use Cases ### Sales and margin by branch, product group and rep **Description:** Daily sales, gross margin and trend against prior year, with drill from branch to customer to invoice line. This replaces a pile of emailed Excel reports. ### Inventory health and turns **Description:** On-hand value, turns, days of supply, dead stock and below-minimum items by location and supplier, so buyers focus on where capital is stuck. ### Purchasing and supplier performance **Description:** On-time receipt, lead time variance, open PO value and cost change tracking by supplier, supporting negotiations and safety stock decisions. ### Backorders and fill rate **Description:** Line fill rate, backorder aging and lost sales by item and customer, showing where service levels fall short. ### AR aging and collections **Description:** Aging buckets, DSO trend, credit hold exposure and promised payment follow-up for the credit team. ### Rebate and customer profitability **Description:** Customer and product profitability after rebates and freight, where the data is available in P21, to guide pricing and account decisions. ## System Surface SQL Server: the P21 database. Power BI can connect with the SQL Server connector in Import or DirectQuery mode. Table and column names differ by version, so build views and document them. Read replica, log shipping, availability group secondary or a nightly copy: protects the production database from analytical load. Which options you have depends on your SQL Server edition and licensing. A small data warehouse or data mart on SQL Server or another platform, loaded nightly with transformed fact and dimension tables. Best for history, performance and combining sources. On-premises data gateway: a Windows service that lets the Power BI cloud service refresh from an internal SQL Server on a schedule, without opening inbound ports. Epicor reporting and analytics products, and P21 report definitions: reference for the logic users already trust, such as how margin and available stock are calculated. Check what your license includes before duplicating. ## How It Works ### Agree the metric definitions **Detail:** Write down how sales, cost, margin, available stock and backorder are defined, and verify them against known P21 reports. Most disputes about dashboards are disputes about definitions. ### Choose the data path **Detail:** For a small estate, a read replica or off-hours refresh from production may be enough. For multi-year history and many dashboards, build a warehouse with a nightly load. ### Create reporting views **Detail:** Build views for invoice lines, order lines, receipts, inventory snapshots and customers. Use a read-only login and keep logic in SQL where it is shared, not in each report. ### Install and secure the gateway **Detail:** Install the on-premises data gateway on a server that can reach SQL Server, cluster it if refresh is critical, and register data sources with the service account. ### Model in Power BI **Detail:** Use a star schema with date, item, customer, location and supplier dimensions. Prefer Import mode with incremental refresh for large fact tables. ### Add row-level security **Detail:** Restrict branch managers and reps to their data with roles tied to Entra ID groups, and hide cost and margin fields from users who should not see them. ### Enable natural-language Q&A **Detail:** Use Power BI's built-in Q&A or Copilot features where your licensing allows, or put a governed NL layer over the same model so questions run on defined measures. ## Example Flows ### Nightly refresh to branch dashboards **Trigger:** A scheduled refresh runs at 2:00 after the nightly load completes. **Steps:** - The warehouse job copies the day's invoices, receipts and inventory snapshot from P21 views. - The gateway lets the Power BI service refresh the semantic model. - Row-level security filters each branch manager to their location. - A subscription emails the manager a snapshot of sales, margin and backorders. **Result:** Managers start the day with correct, branch-specific numbers and no manual report running. ### Buyer finds dead stock **Trigger:** A purchasing manager opens the inventory health report. **Steps:** - The report filters on items with on-hand value but no sales in a set period. - The buyer groups by supplier and product group to see the largest exposures. - They export the list for return-to-vendor or markdown decisions. **Result:** Capital tied up in slow stock becomes visible by supplier, ready for negotiation. ### Executive asks a question in plain language **Trigger:** A VP types: which branch had the biggest drop in margin last month? **Steps:** - The Q&A feature maps the question to the margin measure and branch dimension. - It compares last month to the prior month and returns a ranked visual. - The VP clicks through to the product groups driving the drop. **Result:** A question that once needed an analyst gets answered in seconds from governed measures. ## On Prem Power BI Desktop and the Report Server products can run on-premises, but the standard Power BI service is cloud-hosted, so a published semantic model stores your imported data in Microsoft's cloud and the gateway bridges refresh. If policy forbids that, options include Power BI Report Server with its own feature limits, DirectQuery through the gateway so data is not stored in the service, or a self-hosted alternative. The natural-language layer can also be private: an open-weight model served locally that writes queries over your warehouse views, leaving the Power BI visuals for display only. Confirm which Power BI editions and features your agreement covers before committing. ## Security - Use a dedicated read-only SQL login and limit it to reporting views, with no direct table access to payroll-like or banking data. - Protect the gateway server, keep it patched, and restrict who can create data sources on it. - Apply row-level and object-level security so cost, margin and customer pricing show only to entitled roles. - Control workspace access and sharing, disable broad link sharing, and use sensitivity labels where available. - Audit who exports data, since exports leave the governed model. ## Options ### Epicor's own reporting and analytics **Fit:** Enough when standard reports and Epicor-supplied dashboards cover your needs and you do not want to run a data model. **Effort:** Low, mostly configuration. ### Power BI on SQL views with a gateway **Fit:** The common route for distributors with a BI-minded analyst and moderate data volume. **Effort:** Two to six weeks for first dashboards, depending on definitions work. ### Warehouse plus Power BI plus a governed AI layer **Fit:** For multi-source reporting and natural-language questions on private data. Netray and other partners deliver this. **Effort:** Several weeks to a few months, depending on scope. ## Pitfalls - DirectQuery against production at month end. Heavy visuals can slow order entry, so use a replica or Import mode. - Recreating P21 logic in DAX with slightly different rules, then arguing about why the dashboard disagrees with the P21 report. - Joining invoice, order and receipt tables without handling multiple lines, returns and unit of measure, which double counts quantities. - A single gateway on a lone server with no monitoring, so refresh fails silently and managers act on stale numbers. - Giving everyone the cost column. Margin data is sensitive in distribution and should be role-limited. ## Faq ### Can Power BI connect directly to the P21 database? **Answer:** Yes, through the SQL Server connector, with a read-only login. Direct production access is best limited to small or off-hours refreshes. For regular use, point at a replica or warehouse so reporting load cannot slow order entry, and use views so logic is defined once. ### Do I need the on-premises data gateway? **Answer:** If your SQL Server is inside your network and you use the Power BI cloud service, yes, for scheduled refresh or DirectQuery. The gateway makes outbound connections only, so you do not open inbound firewall ports. For high availability, install it in a cluster. ### Will numbers match my P21 reports? **Answer:** They will if you replicate the same definitions and filters, and reconcile against known reports before release. Differences usually come from returns, voided lines, unit of measure, date basis (order, ship or invoice date) or branch rules, so test those cases explicitly. ### How do I add natural-language questions? **Answer:** Power BI offers Q&A and Copilot features depending on licensing and settings, which work best on a clean, well-named model. For private data or stricter control, a separate assistant can query governed views and measures using a local model. Either way, define metrics clearly so answers are consistent. ### Is a data warehouse required? **Answer:** Not at the start. A few dashboards on views over a replica often work well. A warehouse pays off with large history, many reports, snapshot needs such as month-end inventory, or data from other systems. Build it when the views start to strain or the questions outgrow P21. ## Search terms epicor prophet 21 power bi, power bi for prophet 21, p21 power bi dashboard, connect power bi to epicor p21, prophet 21 reporting, p21 sql server power bi, power bi gateway sql server erp, distribution dashboards power bi, p21 inventory dashboard, prophet 21 business intelligence --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json