# How to connect Power BI to Sage 100 > Source: https://netray.co/integrations/sage-100-power-bi > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Integrations ## Short answer Connect Power BI to Sage 100 by pointing it at SQL Server in Premium, or at the Sage 100 ODBC driver on other editions, and for anything large load the data into a small reporting database first. Use the on-premises data gateway for scheduled refresh, then build dashboards for sales, inventory, purchasing and AR. ## System Sage 100 ## Capability power-bi ## Intro Sage 100 ships with Crystal Reports, Excel-based reporting and in-product inquiries, but many teams want something more visual and shareable. Power BI is a common next step because most companies already have Microsoft 365, and the learning curve for analysts is gentle. The challenge is the data layer. Sage 100's table and column names are terse, the ODBC driver is not built for heavy analytical scans, and the system runs on a local server that Power BI Service cannot reach without a gateway. Getting those three things right matters more than any dashboard design. Once the data is modeled, you can layer on natural-language questions. Power BI has its own Q&A and Copilot features, depending on license and capacity, and a private assistant can sit beside the model if the data must stay in-house. ## Use Cases ### Sales and margin dashboard **Description:** Invoiced sales, cost and margin by customer, item, product line and salesperson, with trend versus prior year. ### Inventory health **Description:** On-hand, days of supply, slow movers and obsolete stock by warehouse, using the item warehouse and transaction history tables. ### Purchasing and vendor performance **Description:** On-time receipt rates, price variance and open PO value by vendor. ### AR aging and collections **Description:** Aging buckets by customer and rep, with the oldest open items highlighted for follow-up. ### Backlog and ship forecast **Description:** Open sales order lines by requested ship date against current availability. ### Production view for Work Order users **Description:** Open work orders, material shortages and completions where the Work Order module is used; table coverage varies by version. ## System Surface SQL Server (Premium): Power BI's SQL Server connector can read tables or, better, views you create. Names such as AR_Customer, AR_InvoiceHistoryHeader, SO_SalesOrderHeader, PO_PurchaseOrderDetail and IM_ItemWarehouse are typical; confirm in your database. Sage 100 ODBC driver: connect through Power BI's ODBC connector on Standard and Advanced editions. Expect slower scans and a need to pull narrow, filtered extracts. Visual Integrator or scheduled exports to CSV or a staging database: a clean way to build a reporting copy with a controlled schedule. On-premises data gateway: required for scheduled refresh to Power BI Service when the source is on your network; install it on a server with access to the data source. Sage Intelligence Reporting or similar add-ons: another route to Excel-based reporting that some teams use alongside or instead of Power BI. ## How It Works ### Decide the data layer **Detail:** Premium shops can model on SQL views. Others should extract nightly to a small SQL Server or similar reporting database to avoid straining ODBC. ### Create friendly views **Detail:** Rename cryptic columns, join headers to details, convert dates, and drop sensitive fields. Power BI should see business terms. ### Build the semantic model **Detail:** Define facts for sales lines, PO lines and inventory snapshots, and dimensions for customer, item, vendor, warehouse and date. ### Install the gateway **Detail:** Put the on-premises data gateway on a server near the source, register the data source, and set a refresh schedule outside peak hours. ### Design dashboards for decisions **Detail:** One page per role: owner, sales manager, buyer, controller. Show exceptions, not just totals. ### Apply row-level security **Detail:** Restrict reps to their customers and hide cost fields from roles that should not see them. ### Add natural-language access **Detail:** Turn on Power BI Q&A or Copilot where licensed, or connect a private assistant to the same model for questions beyond the dashboards. ## Example Flows ### Nightly refresh pipeline **Trigger:** A scheduled job runs at 1 am. **Steps:** - SQL Agent or a script copies required Sage tables or views into the reporting database. - Calculated tables, such as inventory snapshots, are updated. - Gateway refresh of the Power BI dataset is triggered. - Refresh failures send an email to the BI owner. **Result:** Managers open fresh dashboards at 7 am with no load on daytime Sage users. ### Slow mover review **Trigger:** Buyer opens the inventory page before a purchasing meeting. **Steps:** - Filters to items with high on-hand and no sales in 12 months. - Drills into the vendor and last receipt date. - Exports the list to Excel. - Decides on returns, price cuts or write-offs. **Result:** Cash tied up in stagnant stock becomes visible and actionable. ## On Prem If reports must stay on your network, use Power BI Report Server, which is licensed separately from the cloud service and has fewer features, or restrict the cloud dataset to aggregated, non-sensitive data. Keep the reporting database and gateway on premises. For natural-language questions with no cloud exposure, run an open-weight model with vLLM or Ollama internally and let it query the same reporting database through parameterized tools, so staff can ask questions in plain English without any data leaving the network. ## Security - Give Power BI a read-only database login that sees only the views you create. - Use row-level security in the model for rep, region or customer restrictions. - Protect the gateway service account and keep the gateway server patched and monitored. - Limit workspace sharing and sensitivity labels to stop dashboards with cost data circulating widely. - Review what leaves the network if you use cloud Q&A or Copilot features. ## Options ### Crystal Reports and Sage reporting tools **Fit:** Enough if your needs are operational, fixed-format reports. **Effort:** Low, you may already own them. ### Power BI on SQL views with a gateway **Fit:** Best for most Premium shops that want interactive dashboards. **Effort:** Two to six weeks for a first set of dashboards. ### Warehouse plus private AI Q&A with Netray **Fit:** Good when you want plain-English questions answered on premises. **Effort:** Layered on top of an existing reporting database. ## Pitfalls - Pointing Power BI straight at the ODBC driver with large tables, which makes refresh slow and affects users. - Skipping a date table, which breaks time intelligence and prior-year comparisons. - Mixing transaction detail and history tables without understanding which module posts to which. - Not testing that dashboard totals tie to Sage reports before showing them to management. - Forgetting gateway availability; a single gateway server is a point of failure for refresh. ## Faq ### Do I need SQL Server to use Power BI with Sage 100? **Answer:** No, but it helps. Premium uses SQL Server, which Power BI reads natively. Standard and Advanced can use the ODBC driver, but it is slower for large scans, so most teams extract to a small reporting database first. ### Is there a certified Power BI connector for Sage 100? **Answer:** Do not assume one. Some third-party vendors offer connectors or prebuilt content, and availability depends on version. Check with your reseller. The generic SQL Server and ODBC connectors are the reliable fallback. ### Do I need the on-premises data gateway? **Answer:** Yes, if you publish to Power BI Service and the data source sits on your local network. It lets the cloud service refresh datasets without exposing your database to the internet. Power BI Desktop alone can connect directly on the local network. ### Can staff ask questions in plain English? **Answer:** Power BI offers Q&A and Copilot features depending on license and capacity, and quality depends on a clean model with friendly names. For on-premises data, a private assistant can query the same reporting database using open-weight models. ### How often can data refresh? **Answer:** Scheduled refresh is limited by your Power BI license and by how much load the source can take. Many Sage 100 shops refresh nightly or a few times a day. True real-time dashboards usually need a different pattern. ## Search terms sage 100 power bi, power bi for sage 100, connect power bi to sage 100, mas 90 power bi, sage 100 power bi connector, sage 100 dashboards, sage 100 reporting power bi gateway, sage 100 odbc power bi, sage 100 sql server power bi, sage 100 natural language reporting --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json