ERP & SAP Business One

SAP Business One Power BI: Management Reports That Hold Up

Asem Alkamel 11 min read
In this article
  1. SAP Business One Power BI connection options
  2. Where Crystal Reports and SAP queries still win
  3. Set up a read-only reporting path
  4. Build the data model with views and a star schema
  5. Refresh: gateway, schedule and incremental refresh
  6. Which KPIs to build for each department
  7. Executive dashboards for the CEO and the Board
  8. Mistakes that make dashboards untrustworthy or slow
  9. Key takeaways
  10. Frequently asked questions
  11. Can Power BI connect directly to SAP Business One?
  12. Should I use Import or DirectQuery with SAP Business One?
  13. How do I keep Power BI from slowing down SAP Business One?
  14. What is an on-premises data gateway and do I need one?
SAP Business One Power BI: Management Reports That Hold Up

SAP Business One Power BI reporting works through the database underneath SAP B1: Power BI’s SQL Server connector when B1 runs on Microsoft SQL Server, or its SAP HANA connector when B1 runs on HANA. There is no native “SAP Business One” connector, so the reliable setup is a read-only reporting user, a set of SQL views that shape B1’s tables into a clean model, and a scheduled refresh through an on-premises data gateway. This article walks through that setup and what to put on the dashboards.

I write the SQL queries and Crystal Reports used inside SAP Business One for production, sales, finance and HR at a feed manufacturer, and I built the executive dashboards the CEO and Board use. The hard part was never the visuals. It was deciding what each number means, making sure the figure on the dashboard equals the figure in the ledger, and keeping the reporting workload from slowing down the people posting transactions. Those three concerns shape everything below.

SAP Business One Power BI connection options

The connection options, from most common to least:

Option When it applies Import / DirectQuery Notes
SQL Server connector SAP B1 on Microsoft SQL Server Both Standard Power BI connector; query against views, not raw tables; needs a gateway for scheduled refresh from the cloud service
SAP HANA database connector SAP B1, version for SAP HANA Both Requires the SAP HANA ODBC client on the machine running Power BI Desktop or the gateway; supports SQL statements and HANA calculation views
Service Layer (OData) Either platform, for API-style access Import Useful for small, specific datasets; slower and more complex than database views for analytical volumes
Third-party connectors Either platform Depends on product Convenience layer over the same data; weigh cost and support against views you own

Decision rule: if you can reach the database, use the database connector for your platform and build views. Use Import mode for management reporting; DirectQuery sends every dashboard click to the ERP database, which is exactly the load you want to keep away from the transaction system. The one exception is a small near-real-time operational page, which is better served by a dedicated view with a narrow filter than by making the whole model DirectQuery.

Where Crystal Reports and SAP queries still win

Power BI does not replace the reporting built into SAP Business One; it sits beside it. The split I use:

  • Query Generator and Query Manager (Tools, Queries) for operational lists users run inside B1 several times a day: open orders by customer, items below minimum stock, unposted documents. They are fast to build and run in the user’s context with B1 authorizations.
  • Crystal Reports for anything printed or sent as a document with a layout: invoices, delivery notes, production orders, statements, and formal month-end reports with headers, subtotals and signatures.
  • Power BI for trends, comparisons and cross-department views: sales by region over 24 months, production output against plan, overdue receivables by salesperson, cost per ton by line. Anything a manager wants to filter, drill and read on a phone.

The SQL behind a Crystal report and the SQL behind a Power BI view should share the same definitions. When “net sales” is defined once in a view and both tools read it, the CEO never sees two different figures for the same month.

Set up a read-only reporting path

Before building a single visual:

  1. Create a dedicated database login for reporting with read-only permissions on the company database, and nothing else. Power BI never writes to SAP B1 tables, and no report should ever use the SAP administrator credentials.
  2. Grant access to views, not tables. Put the reporting views in their own schema and grant the reporting login SELECT on that schema only. This lets you change the underlying query without touching every report, and it hides columns such as cost prices or salaries from reports that should not see them.
  3. Keep the reporting workload off the live server where possible. Options range from scheduling refreshes outside working hours, to a database replica or a nightly copy used only for reporting. The right choice depends on size and budget; the principle is that a Board dashboard refresh should never make an invoice take longer to post.
  4. Install the on-premises data gateway on a server that can reach the database, in standard mode so the connection is centrally managed, and register the data source with the reporting login’s credentials.

Document the login, the schema and the gateway in the IT runbook; reporting plumbing is usually built by one person and forgotten until it breaks.

Build the data model with views and a star schema

SAP Business One’s tables are normalized for transactions, not for analysis. Pointing Power BI at the raw document tables (invoice headers and lines, item master, business partner master) produces slow, fragile reports. The fix is a small set of views that produce a star schema: fact views with measures and keys, dimension views with descriptive attributes.

A simplified sales fact view on SQL Server looks like this (the real one carries more columns and handles credit memos as negative rows):

CREATE VIEW rpt.v_FactSales AS
SELECT
    T0.DocEntry          AS InvoiceKey,
    T0.DocDate           AS InvoiceDate,
    T0.CardCode          AS CustomerKey,
    T1.ItemCode          AS ItemKey,
    T1.WhsCode           AS WarehouseKey,
    T0.SlpCode           AS SalespersonKey,
    T1.Quantity          AS Qty,
    T1.LineTotal         AS NetAmount,
    T1.GrssProfit        AS GrossProfit
FROM OINV T0
JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
WHERE T0.CANCELED = 'N';

Model rules that have saved me rework:

  • One fact view per business event: sales invoices, purchase invoices, production receipts, stock movements, receivables. Do not build one giant “everything” view.
  • Dimension views for items, business partners, warehouses, salespeople, accounts and a date table. Load the date table in Power BI and mark it as such so time intelligence works.
  • Do the heavy joins and business rules in SQL, where the database engine is good at them, and keep Power Query transformations light so queries fold back to the source.
  • Define each measure once in DAX, with a plain-language definition in the model description (“Net sales: invoice line totals excluding VAT, less credit memos, cancelled documents excluded”). That definition is what you show the CFO when a number is questioned.
  • Handle cancellations, credit memos and returns explicitly. The most common cause of a dashboard that disagrees with the ledger is a cancelled invoice counted once.

Refresh: gateway, schedule and incremental refresh

For Import mode, the Power BI service refreshes the model on a schedule through the on-premises data gateway, which uses outbound connections only, so no inbound port needs to be opened to the database server. Points that matter in practice:

  • Schedule for the business. A daily refresh before the morning management review is enough for most executive dashboards; hourly is reasonable for a narrow production page.
  • Use incremental refresh for large fact tables. Power BI partitions a table by date using two reserved Power Query parameters, RangeStart and RangeEnd, and refreshes only recent partitions. It is available on Power BI Pro as well as Premium. The filter must fold to the source query, which is one more reason to keep transformations in SQL views.
  • Monitor refresh duration and failures. Microsoft documents a two-hour refresh limit for Pro models; route failure notifications to the helpdesk queue, because a dashboard that silently shows last Tuesday’s data is worse than one that is visibly broken.

Microsoft’s own guidance on gateway types and setup is in the on-premises data gateway documentation for Power BI.

Which KPIs to build for each department

The department reports I maintain in SAP Business One, and the dashboard pages built from the same views, cover roughly the following. Start with fewer and add on request; every measure you publish is one you must defend.

Department Core measures Typical breakdowns Refresh
Sales Net sales, gross margin, order backlog, average days to deliver, overdue receivables Customer, region, salesperson, item group, month Daily
Production Output in tons, plan attainment, yield and scrap, downtime hours, cost per ton Line, shift, product, day Hourly to daily
Procurement and inventory Stock value and days of cover, purchase price variance, supplier on-time delivery, slow-moving stock Warehouse, item group, supplier Daily
Finance Revenue, gross and net profit, cash position, AR and AP aging, budget vs actual by cost center Entity, cost center, month, account group Daily, with month-end snapshot
HR Headcount, attrition, overtime hours, payroll cost by cost center, Saudization-related headcount views Department, nationality group, month Monthly

On a plant where SAP B1 is integrated with an MES, production measures come straight from the synced production data rather than from manual entry, which is what makes an hourly production page trustworthy. How that integration was set up is described in the SAP Business One manufacturing case study, and the division of responsibilities between the two systems in MES vs ERP on the factory floor.

Executive dashboards for the CEO and the Board

The dashboards our CEO and Board use are deliberately small. What I learned building them:

  1. One page, eight to twelve numbers, each with a comparison. A figure without “versus last month” or “versus plan” generates a question, not a decision.
  2. Every number ties to the ledger or to a signed operational report. Before publishing, I reconcile the dashboard’s monthly revenue, margin and receivables to the finance close. The first time a director finds a mismatch is the last time the dashboard is trusted.
  3. Definitions on the page. A tooltip or a definitions panel that states how each measure is calculated. It ends the “my number is different” conversations.
  4. Month-end snapshots. Open receivables and stock values change daily; the Board wants the figure as it stood at month end. Store snapshot tables rather than relying on live views for historical comparisons.
  5. Row-level security and a mobile layout. A sales manager sees her region, the CEO sees everything, and directors read on phones between meetings.

Mistakes that make dashboards untrustworthy or slow

  • Connecting Power BI to raw B1 tables. Slow refreshes, broken relationships and reports that break at the next patch. Use views.
  • Using DirectQuery for everything. The ERP database becomes the dashboard’s query engine, and users posting transactions pay for every filter click.
  • Reporting with admin credentials. A security finding waiting to happen, and an audit failure.
  • Defining measures in each report separately. Three reports, three definitions of margin, three arguments.
  • Ignoring cancellations, credit memos and currency. The classic reasons the dashboard and the ledger differ.
  • Building for the builder. Forty visuals on one page impress developers and lose executives.
  • No owner after go-live. Reports need an owner for definitions, access and refresh failures, like any other system.

Key takeaways

  • Connect through the database (SQL Server or SAP HANA connector), in Import mode, with a read-only reporting login limited to views.
  • Shape B1’s tables into a star schema in SQL views; keep Power Query light so queries fold.
  • Define each measure once, in writing, and reconcile the dashboard to the ledger monthly.
  • Refresh through the on-premises data gateway on a business-driven schedule, with incremental refresh on large fact tables.
  • Keep Crystal Reports and SAP queries for documents and operational lists; use Power BI for trends and cross-department views.
  • Executive pages: few numbers, each with a comparison, definitions visible, month-end snapshots, mobile-ready.

Frequently asked questions

Can Power BI connect directly to SAP Business One?

Power BI has no dedicated SAP Business One connector. It connects to the database SAP B1 runs on: the SQL Server connector for B1 on Microsoft SQL Server, or the SAP HANA database connector for B1 on HANA, which needs the HANA ODBC client installed. Both support Import and DirectQuery. Third-party connectors and the Service Layer OData interface are alternatives for specific cases.

Should I use Import or DirectQuery with SAP Business One?

Import for management and executive reporting: it is faster for users and keeps query load away from the ERP database. DirectQuery sends every interaction to the database and should be limited to small, narrow operational views where near-real-time data matters. Combine Import with incremental refresh for large fact tables rather than switching the whole model to DirectQuery.

How do I keep Power BI from slowing down SAP Business One?

Use Import mode with scheduled refreshes outside peak hours, query a small set of SQL views rather than raw tables, apply incremental refresh to large tables, and consider a replica or nightly copy of the database for reporting. Give the reporting login read-only rights to the views only, and monitor refresh duration so growth is noticed early.

What is an on-premises data gateway and do I need one?

The gateway is Windows software installed inside your network that lets the Power BI cloud service refresh or query data sources it cannot reach directly, such as an on-premises SAP B1 database. It uses outbound connections, so no inbound firewall ports are needed. You need it for scheduled refresh of any on-premises source; standard mode is the right choice for shared, centrally managed connections.

If you run SAP Business One and want a reporting layer that leadership trusts, from the SQL views to the Board page, that work sits under my digital transformation service, and it follows naturally after a SAP Business One implementation has stabilized. You can contact me to talk through what your dashboards should show.

Cart 0 Checkout