Insights and scalable growth with a data warehouse on an in-house server
Zuiver
Exact Online Power BI dashboard
We bring all your Exact Online accounts together in one Power BI model with consistent definitions, just as we brought data sources together for Zuiver.
We built a data warehouse for Zuiver that brings together multiple sources
“DSL helped us set up our first data warehouse. They guided us through the process […] and worked with us to identify the information we need to make better decisions. […] I would recommend them to other SMEs.” Richard Aznar Webster, CEO of Zuiver Interior Group
Why don’t the revenue figures in our Power BI match Exact Online?
Every Monday, the financial controller exports a report for each entity from Exact Online, pastes everything into Excel and manually adjusts the intercompany entries. A model that pulls in the figures for each entity makes them available every morning.
An Exact Online dashboard starts with the API and one model for all your accounts
All accounts in one model
Each account enters the model through the Exact Online REST API with a fixed code. You can then view the figures by company or on a consolidated basis.
Only what has changed
With each refresh, Exact’s sync endpoints return only new or changed entries. This keeps data loading efficient, even when there are many accounting entries.
Consistent definitions
We define revenue, margin, outstanding receivables and budget versus actuals once as measures, so every report shows the same figures.
A data warehouse underneath
If you add more sources, we put Exact Online in a data warehouse alongside your other systems, as we did when we brought different sources together for Zuiver.
Map each set of accounts first, then report the figures to management
-
Mapping the accounts
Which sets of accounts and charts of accounts exist, which Excel reports are currently circulating, and which figures management wants to see.
-
Model and first report
We connect the API, build the model and check the first report against Exact Online with the controller.
-
Refreshing and managing
Data refreshes, access permissions and an owner for each report go live. The other reports then follow, using the same model.
Do you need your own Power BI model, or is what Exact Online shows you enough?
A good fit if
You work with multiple sets of accounts or use sources beyond Exact Online, and you want consolidated figures ready every morning without exports or manual work in Excel.
Not a good fit if
You have one set of accounts and Exact Online’s standard reports give you what you need. In that case, maintaining your own model takes more effort than it’s worth.
What determines the scope
The number of sets of accounts, whether their charts of accounts match, which other sources you need to include, how much historical data you want to see and whether your own team will take over management.
What if we switch software later?
The model stays in place. We keep the process of retrieving data from Exact Online separate from the model, so switching software mainly affects the data-loading step and your reports keep working.
The Exact connector, the REST API or a data warehouse?
The Exact connector
Choose this if you have Exact Online Premium and mainly want to use Exact’s Power BI templates. To refresh the data, you’ll need a gateway with the ODBC driver.
The REST API
Choose this if you want to combine multiple accounts and define the model yourself. We recommend it for groups that make decisions based on consolidated figures.
A data warehouse
Choose this if Exact Online is one of several sources, alongside a webshop or CRM, for example. Read more about building a data warehouse.
We build Power BI reporting and the data foundation behind it
Data Science Lab has worked with data and AI since 2016 and is a Microsoft Partner for Fabric. We built a data warehouse for Zuiver, and Power BI dashboards and a Power BI app for Ammega.
Your Exact Online accounts are the starting point for our first conversation
Stef Stoelwinder
Business DevelopmentStef is your first point of contact. In our first conversation, we’ll discuss your accounts, your current reports and what Power BI needs to show.
E-mail info@datasciencelab.nl
Phone +31 (0)20 3636 724
How many sets of accounts do you want to see in one Exact Online overview?
Tell us how many sets of accounts you have and how you currently report. We’ll reply by email or arrange an initial conversation.
Message sent
Thanks for your message. We’ll be in touch as soon as possible.
Frequently asked questions
There are two routes. Exact provides its own Power Query connector, Exact Online Premium, which is in preview. It requires a Premium subscription, the ODBC driver, an IP address on the Power BI allowlist in Exact, and an on-premises data gateway for refreshes.
The second route is the Exact Online REST API, which you configure yourself. To see how we build a dashboard around the decision it needs to support, read about Power BI dashboards.
Almost every API call requires an account, called a division in the API. The divisions endpoint returns the accounts the user who granted permission can access; an accountant can also see their clients’ accounts.
In the model, each account gets a fixed code. We map general ledger accounts to a single shared chart of accounts, and you eliminate intercompany transactions using a fixed rule in the model, not in Excel.
Use the sync endpoints, which Exact recommends wherever possible. Each record has a Timestamp that changes when the record is updated. You store the highest value and, next time, request only what came after it.
Watch out for migrations: if Exact moves an account to another database, its Timestamps start again and you need to reload that account in full. The DivisionMoveDate field shows whether this has happened.
The Exact Online API provides, among other things, financial data (general ledger, journal entries, accounts receivable ageing and balances), budgets, payments and bank transactions, projects with hours and costs, sales invoices, inventory and payroll data. What you can retrieve depends on the modules you use.
For the management information this typically provides, such as margins, cash flow and budget versus actuals, read about the financial dashboard in Power BI.
On the Exact side, the access rights in Exact Online of the user who authorises the connection determine which accounts the API returns. Use a dedicated account for this, not a colleague’s personal account.
In Power BI, you handle the rest: row-level security by account or department, and an app for each audience. To see how we set this up, read about our Power BI consultant.
When charts of accounts differ slightly between accounts, so the same revenue appears under different general ledger accounts. When refreshes run on one colleague’s laptop. And when every load retrieves the entire dataset again.
Exact documents separate limits for its bulk and sync endpoints. So plan refreshes around what has changed, not the entire history.
Yes, the approach is the same, but each system has its own way of making data accessible. Read more about a Twinfield Power BI dashboard, an AFAS SB Power BI dashboard and an AFAS Payroll Power BI dashboard.
If your group uses several systems, you can bring them together in one model with one chart of accounts.
If you have one account, Exact Online’s standard reports are sufficient and no one needs more. Or if the accounting records themselves are messy: Power BI would only address the consequences, so it is better to start with data quality.
If a simpler solution, such as Exact’s templates, is a better fit, we will say so.