# How to connect Power BI to SYSPRO > Source: https://netray.co/integrations/syspro-power-bi > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Integrations ## Short answer Connect Power BI to SYSPRO through its SQL Server company database, ideally via a reporting copy or a small data warehouse, using an on-premises data gateway for scheduled refresh. Use e.NET only for values that depend on SYSPRO business logic, and add natural-language Q&A on top of the model. ## System SYSPRO ## Capability power-bi ## Intro SYSPRO runs on SQL Server, which makes Power BI an obvious choice. Most teams get a first report working in an afternoon by pointing Power BI Desktop at a company database. The harder part is making it fast, correct and safe: a sprawl of reports on the live database can slow down order entry, and raw SYSPRO tables do not always give the numbers that appear on screen. The reliable pattern is to separate reporting from transactions. Replicate or copy the tables you need into a reporting database or warehouse, build a clean star-style model with facts for order lines, PO lines, inventory movements and job costs, and let Power BI read that. Business-logic values, such as certain costs or availability, can be recomputed in the model or pulled through e.NET when exact parity matters. Once the model exists, you can add natural-language questions on top, either through Power BI's own Q&A or Copilot features where licensed, or through a separate assistant that queries the same model. ## Use Cases ### On-time delivery and backlog **Description:** Show sales order lines by promise date versus ship date, open backlog by customer and product family, and late lines by cause. ### Inventory health **Description:** Track stock value, turns, slow-moving items and warehouse balances, with drill-through to item movements. ### Purchasing performance **Description:** Report supplier on-time receipt, price variance versus standard and open commitments by buyer. ### Job and WIP costing **Description:** Compare estimated and actual material, labour and overhead by job and product, highlighting variance outliers. ### Cash and receivables view **Description:** Show ageing, promise-to-pay trends and top overdue accounts for the credit team. ### Daily operations wall **Description:** Display shop-floor throughput, scrap and jobs behind schedule on a refreshing page for supervisors. ## System Surface SQL Server company databases: SYSPRO stores each company in its own database, and tables like SorMaster, SorDetail, InvMaster, InvMovements, PorMasterDetail, WipMaster and ArCustomer are common sources. Exact names and columns vary by version, so confirm in your schema. SYSPRO Reporting Services and SYSPRO Analytics offerings: check your licence and release for built-in analytics or Power BI templates before building from scratch. e.NET business objects: useful for values that must match SYSPRO logic exactly, though slower and less suited to bulk loads. Replication, log shipping or scheduled ETL: SQL Server features and tools such as SSIS can copy needed tables into a reporting database on a schedule. On-premises data gateway: required for scheduled refresh or DirectQuery of an on-prem SQL Server from the Power BI service. ## How It Works ### Pick the first five dashboards **Detail:** Interview users and choose reports such as backlog, inventory, purchasing, job variance and receivables, with the decisions each supports. ### Create a reporting layer **Detail:** Replicate or ETL needed tables into a reporting database. Add views that apply company filters, join descriptions and expose clean date keys. ### Build the semantic model **Detail:** In Power BI Desktop create fact and dimension tables, relationships, and measures for on-time percentage, available quantity and variance. ### Validate against SYSPRO **Detail:** Compare totals for sample customers, items and jobs to SYSPRO screens and reports before anyone uses the numbers. ### Install the gateway **Detail:** Deploy the on-premises data gateway on a server that can reach SQL Server, with a service account and monitored uptime. ### Schedule refresh **Detail:** Choose import with scheduled refresh for most data, and DirectQuery only for small, indexed sources where freshness matters. ### Add row-level security and Q&A **Detail:** Define roles by plant, warehouse or salesperson, then tune synonyms and descriptions so natural-language questions return sensible visuals. ## Example Flows ### Nightly refresh of the backlog report **Trigger:** A scheduled ETL job finishes at 2 a.m. **Steps:** - The job copies changed order lines and shipments into the reporting database. - The gateway runs the Power BI dataset refresh. - The model recalculates late lines and backlog by customer. - Subscribers receive a morning summary page. **Result:** Sales managers start the day with current backlog and late lines without touching SYSPRO. ### Natural-language question on inventory **Trigger:** A planner types, 'slow moving stock by warehouse over 180 days'. **Steps:** - The Q&A feature maps terms to model fields and measures. - It builds a visual from the inventory age measure. - The planner pins it to a dashboard for follow-up. **Result:** A fast ad hoc view built on the same governed model. ## On Prem Power BI can use an on-prem SYSPRO without exposing it: install the on-premises data gateway inside your network, give it a read-only SQL login to the reporting database, and let the Power BI service pull refreshed data through an outbound connection. If policy forbids sending data to the cloud service at all, use Power BI Report Server on your own infrastructure, with the understanding that some cloud-only features, including some AI and Copilot features, are not available there. A private natural-language layer using a local model over the same warehouse is an option for those sites. ## Security - Give the gateway and reports a read-only SQL account on the reporting database, not the production SYSPRO database, with access to named tables or views only. - Implement row-level security in the semantic model for plant, warehouse or sales territory, and test it with real user accounts. - Exclude cost, margin and personnel fields from broad datasets, or place them in a separate restricted dataset. - Control workspace membership and sharing, and review who can export data from reports. - Monitor gateway servers and credentials, and patch the gateway on a schedule. ## Options ### SYSPRO's bundled analytics or templates **Fit:** Check your licence for dashboards or connectors that cover standard reporting at low effort. **Effort:** Days to weeks. ### Build the model in-house **Fit:** Good if you have a BI developer who understands SYSPRO tables and wants control over measures. **Effort:** 4 to 12 weeks for a first governed model. ### Partner-built warehouse and dashboards with NL query **Fit:** Good for multi-company or on-prem sites that want a reporting layer, validated measures and a private question-answering option. **Effort:** A pilot of a few dashboards in weeks. ## Pitfalls - Pointing many reports at the live production database slows order entry, so use a reporting copy. - Using raw table sums for cost or available quantity can disagree with SYSPRO screens, so validate measures. - Ignoring multi-company databases leads to hard-coded filters and missing data, so design a company dimension. - Choosing DirectQuery for large tables produces slow visuals and heavy database load. - Forgetting gateway availability means dashboards go stale silently, so alert on refresh failures. ## Faq ### Can Power BI connect directly to SYSPRO? **Answer:** Power BI connects to the SQL Server database that SYSPRO uses, and optionally to data you pull through e.NET. A direct connection to production is fine for a test, but for regular use a reporting copy protects performance and lets you shape cleaner tables. ### Do I need the on-premises data gateway? **Answer:** Yes for scheduled refresh or DirectQuery from the Power BI service to an on-prem SQL Server. The gateway makes an outbound connection, so you do not open inbound ports. Install it on a reliable server and monitor it. ### Why do my Power BI numbers differ from SYSPRO? **Answer:** Usually because SYSPRO applies business logic, status filters, unit conversions or costing rules that raw table sums do not. Compare against a few known records, replicate the logic in measures or views, and use e.NET for values that must match exactly. ### Can users ask questions in plain English? **Answer:** Yes. Power BI Q&A and Copilot features, where licensed and enabled, work on a well-described semantic model. Good field names, synonyms and measures matter. For private or on-prem needs, a separate local-model layer over the same warehouse can answer questions. ### How often should data refresh? **Answer:** Most manufacturing dashboards are fine with nightly or hourly refresh. Shop-floor views may need every few minutes from a reporting copy. Match frequency to the decision being made, and avoid refreshing large tables more often than the business needs. ## Search terms syspro power bi, power bi for syspro, syspro power bi integration, connect power bi to syspro, syspro power bi dashboard, syspro sql server reporting, syspro data warehouse power bi, syspro power bi gateway, syspro analytics dashboards, syspro business intelligence --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json