Insights and scalable growth with a data warehouse on an in-house server
Zuiver
SAP Business One Power BI dashboard
We bring data from your SAP Business One companies into a single Power BI model via the Service Layer or the database. We helped Zuiver with its first data warehouse.
Zuiver set up its first data warehouse with us, bringing together different sources
“DSL helped us set up our first data warehouse. They took us through the process and guided us step by step […] For us, they’ve been a fantastic partner, and we’d recommend them to other SMEs.” Richard Aznar Webster, CEO, Zuiver Interior Group
Why does getting an overview of all our SAP Business One companies take so much manual work?
In SAP Business One, you log in to each company database separately. If you have multiple companies, you run the same query for each one and combine the results in Excel. A Power BI model reads data from all the companies and displays it by company or together.
Service Layer or database, then one model for all your companies
Via Service Layer
SAP Business One’s REST API is based on OData. We log in to each company database and retrieve only the fields we need.
Via the database
On SQL Server or SAP HANA, we use a read-only account, or we make custom views available through Service Layer.
One schema for all companies
We assign a company code to general ledger, sales, purchasing and inventory data, so every report shows the same measures for each company or for all companies combined.
Start small, then grow
As we did when helping Zuiver build its first data warehouse, we start with the reports that currently require the most manual work.
Get it right for one company first, then move on to the others
-
Map the version and companies
SQL Server or HANA, which company databases are in use, which reports currently circulate and who is allowed to see which figures.
-
Choose the approach and build the model
Service Layer or database, then the model for one company, checked against the reports in SAP Business One.
-
Roll out and manage
The other companies follow through the same loading step. The gateway, refresh schedule and access rights go live with a designated owner.
Is reporting in SAP Business One enough, or would a model of our own be worthwhile for our company?
A good fit if
You work with multiple companies or locations in SAP Business One, or combine it with a webshop or another data source, and the standard reports do not give you an overview of them.
Not a good fit if
You have one company and the reporting in SAP Business One already answers your questions. A model of your own would then take more maintenance than it is worth.
What determines the scope
The number of company databases, whether you use SQL Server or HANA, how many user-defined fields and customisations you have, which sources you need alongside SAP Business One, and who will manage the model.
Can Power BI read from our database?
Technically, it can with a read-only account, but agree this with your SAP partner first. Through Service Layer, SAP Business One authorisations apply, including to views and SQL queries.
Service Layer, the database or a data warehouse for your SAP B1 reporting?
Service Layer
Choose this if SAP Business One permissions need to apply. Our advice: prepare the data in views and reuse the session.
The database
Choose this if SAP Business One runs on your own servers and you can access SQL Server or SAP HANA with a read-only account. Power BI has a connector for both.
A data warehouse
Choose this if SAP Business One is one of several data sources or if you want to retain historical data for longer. See having a data warehouse built.
We’re increasingly helping SMEs take their first step towards a single source for their figures
Data Science Lab has worked with data and AI since 2016 and is a Microsoft Partner for Fabric. For Zuiver, we built a data warehouse that brings data sources together; for Ammega, we built Power BI dashboards and a Power BI app.
We’ll start by discussing your SAP Business One version and your companies
Stef Stoelwinder
Business DevelopmentStef is your first point of contact. In our first conversation, we’ll discuss your version, your company databases and the reports that currently require the most manual work.
E-mail info@datasciencelab.nl
Phone +31 (0)20 3636 724
How many SAP Business One companies do you want to see combined figures for?
Describe your version (SQL Server or HANA), your companies and the reports you currently produce. We’ll reply by email or suggest an initial call.
Message sent
Thanks for your message. We’ll be in touch as soon as possible.
Frequently asked questions
Through Service Layer, SAP Business One’s REST API based on OData and JSON, or directly from the database. SAP Business One runs on SAP HANA or Microsoft SQL Server, on-premises or in the cloud. We load the data and build the model on top of it.
We explain how a management question becomes a dashboard on our Power BI dashboards page.
A Service Layer session starts by logging in to one company database: the request contains the CompanyDB, username and password. The data-loading step therefore logs in to each company, retrieves its data and combines it with a company code.
SAP recommends reusing sessions rather than logging in each time, and retrieving only the data you need.
On SQL Server, you create a view in the dbo schema with a name ending in B1SLQuery and make it available through Service Layer. On SAP HANA, you use calculation views with Query at the end of the name, such as the built-in BalanceSheetQuery.
In both cases, you grant users separate access to the view in SAP Business One.
The difference is in how you connect to the database: for SQL Server, you use Power BI’s SQL Server connector; for HANA, you use the SAP HANA connector. The latter requires an SAP HANA ODBC driver on the connecting computer and supports both import and DirectQuery.
If SAP Business One runs on your own servers, an on-premises data gateway connects it to the Power BI service.
Typically, they track revenue and margin by customer, product and branch, outstanding receivables and payables, stock by warehouse, and budget versus actuals. You can view all of these by company or combined, from a single model.
Read our financial dashboard in Power BI page to see which metrics help you manage performance.
Service Layer applies the user’s SAP Business One permissions, so use a separate account for data loading with only the permissions it needs. In Power BI, row-level security ensures that a branch manager sees only their own company’s figures.
We set up workspaces and apps for each audience as described on our Power BI consultant page.
Yes. Only the way you retrieve the data changes; the model and definitions stay the same. See our pages on Dynamics NAV, SAP S/4HANA and Exact Online in Power BI.
If your organisation moves to another ERP system later, it is mainly the data-loading step that changes.
If you have just one company and SAP Business One’s reporting meets your needs. Or if products, customers and general ledger accounts are set up differently across companies: then the starting point is data quality, not Power BI.
If a few fixed queries are enough, we’ll tell you that too.