How to connect Power BI to SAP Business One
Short answer
Connect Power BI to a reporting copy of the Business One database (SQL Server or SAP HANA), or to the Service Layer for smaller datasets. Use an on-premises data gateway for scheduled refresh, model a handful of facts such as sales, inventory and production, then add natural-language Q&A on top.
Business One ships with Crystal Reports, Query Manager and, depending on edition, SAP's own analytics options. Many companies still end up in Power BI because their finance team and management already use Microsoft tools and want one place for dashboards across systems.
The connection itself is easy. The hard part is the model: B1 stores documents in header and row tables with many codes, cancelled and closed states, and per-company UDFs. Dashboards built straight on raw tables often show numbers that do not match the B1 reports finance trusts.
This page covers the connection options, the gateway, a sensible set of manufacturing dashboards, and how to reconcile Power BI numbers with B1 before anyone relies on them.
What teams use it for
Sales and margin by customer and item
Built from invoices and credit memos (OINV/INV1, ORIN/RIN1) with cost, showing trend, margin and top movers.
Inventory health
On-hand, committed and ordered by warehouse from OITW, with turnover, slow movers and stockout risk against open sales orders.
Production performance
Production orders (OWOR/WOR1) with planned versus issued quantities, overdue orders and completion rates.
Purchasing and vendor delivery
Promised versus received dates from purchase orders and goods receipts, by vendor and item group.
Receivables ageing
Open invoices and incoming payments with ageing buckets and credit limit comparisons.
Order backlog and on-time delivery
Open sales order lines against due dates and delivery notes, tracking on-time performance by customer.
SAP Business One integration surface
- SQL Server connector: for B1 on Microsoft SQL Server, Power BI can read tables or views directly. A read-only login on a replica or reporting copy is the usual approach.
- SAP HANA connector or ODBC: for B1 on HANA, use the HANA connector with a read-only user, ideally against purpose-built calculation or SQL views.
- Service Layer OData: suitable for small to mid-sized datasets and for respecting B1 business logic, but paging, session handling and refresh duration limit it for large history.
- User query views and saved queries: define clean views once in the database or Service Layer so Power BI reads consistent shapes. Exact options vary by version.
- On-premises data gateway: required for scheduled refresh when the database is on your network and the Power BI service is in the cloud.
- Data warehouse or staging layer: a nightly extract into a separate database that also joins other sources and keeps history.
How it works
- 1
Choose a source
Prefer a reporting copy via log shipping, replication or nightly restore, so dashboards never slow the production company database.
- 2
Create clean views
Build views that join headers and rows, exclude cancelled documents, convert document types and bring in the UDFs that matter. Name them for business use.
- 3
Install and configure the gateway
Put an on-premises data gateway on a server that can reach the database, register it with your tenant and add the data source with a read-only credential.
- 4
Model the data
Create a star schema: facts for sales lines, inventory, production and purchasing; dimensions for items, partners, warehouses and dates. Define measures once.
- 5
Reconcile with B1
Compare totals with trusted B1 reports for the same period and currency. Differences usually come from cancelled documents, credit memos or currency handling.
- 6
Schedule refresh and add Q&A
Set refresh outside posting peaks. Then add natural-language Q&A or a Copilot-style feature if available on your licence, plus an AI layer over the semantic model for questions the dashboards do not answer.
Example flows
Nightly refresh from a reporting copy
Trigger: A scheduled restore completes at 1:00 AM.
- The gateway connects to the reporting database.
- Power BI refreshes the sales, inventory and production models.
- Row counts are compared with the previous run.
- Failures email the BI owner.
Result: Managers open correct figures at 7:00 without touching the live company.
Backlog question in plain language
Trigger: A manager types, "Which customers have late orders over a certain value?"
- The question is passed to a Q&A feature or an AI layer over the semantic model.
- It maps terms to the backlog measures and customer dimension.
- It returns a chart and the underlying table.
- The manager clicks through to the document numbers.
Result: Ad hoc questions no longer need a new report request.
Keeping data on-prem
Power BI Desktop and the cloud service are Microsoft products, so for strict on-prem requirements consider Power BI Report Server, which hosts reports inside your network, or keep the semantic model in Power BI while the data stays behind the gateway. The gateway only opens outbound connections, but your policy may still restrict what summary data enters the cloud service. For natural-language questions on sensitive data, an open-weight model on your own hardware can query the same views through a read-only account and answer without sending rows to a hosted model.
Security checklist
- Use a read-only database account scoped to the reporting views.
- Apply row-level security in Power BI for plant, region or customer restrictions.
- Keep cost and margin out of widely shared reports unless intended.
- Secure the gateway host and restrict who can manage data sources.
- Review workspace sharing and export permissions.
Ways to get it done
Direct connection to a reporting database
Effort: Days to a first dashboard, weeks for a trusted model.
Best default for B1 on SQL Server or HANA when you want speed and flexibility.
Service Layer OData feed
Effort: Low to start, slow at scale.
Acceptable for small data volumes and when you must respect B1 logic closely.
Warehouse plus partner build, including Netray for the Q&A layer
Effort: Scoped project.
Good when you combine B1 with other systems and want natural-language access with an on-prem option.
Pitfalls
- !Reporting off the production database during working hours and slowing postings.
- !Including cancelled or draft documents in sales and stock figures.
- !Mixing local and foreign currency amounts without a clear rule.
- !Skipping reconciliation, so dashboards disagree with finance reports.
- !Letting dashboards expose margin to people who should not see it.
Frequently asked questions
Should I connect to the database or the Service Layer?
For dashboards, a reporting copy of the database is usually faster and simpler. The Service Layer respects business logic and works for modest volumes, but paging and refresh time make it awkward for large history. Many teams use views on the database and keep the Service Layer for writes.
Do I need an on-premises data gateway?
If your B1 database is on your own network and you use the Power BI cloud service with scheduled refresh, yes. The gateway lets the service reach your data without opening inbound ports. Report Server is the alternative if everything must stay inside your network.
Why do my Power BI totals not match B1 reports?
The usual causes are cancelled documents, credit memos treated as sales, drafts being included, currency conversion differences, and date fields such as posting versus document date. Reconcile one month against a trusted B1 report and fix the view logic before adding more visuals.
Can I ask questions in natural language?
Power BI has Q&A and AI features depending on licence and tenant settings, and results depend on how well your model is named and described. For sensitive data or complex questions, an AI layer over the same views with a private model can be added.
Does SAP provide its own dashboards?
Business One includes reporting tools and, depending on edition, analytics options, which may be enough for single-system reporting. Power BI makes sense when you need to combine B1 with other sources or your organisation already standardises on Microsoft tools.
Want Power BI working on your SAP Business One?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment