Abacus and Power BI: Efficient analyses through intelligent data integration

Power BI and Abacus: Efficient analyses through intelligent data integration

It is essential for small and medium-sized enterprises (SMEs) to analyse their business data efficiently in order to make informed decisions. A popular combination for this task is the use of Power BI in conjunction with Abacus, a leading ERP solution in Switzerland. In this article, you will find out what methods exist for data integration, why the OData interface is particularly promising and what advantages a data warehouse (DWH) offers.

Methods for integrating data from Abacus into Power BI

1. Direct access to the Abacus database with ODBC and Power BI Gateway

This method allows you to access the Abacus database directly via the Power BI Gateway. Detailed instructions can be found in this article from Digicomp and in the article from upGreat.

Example: Imagine you want to retrieve monthly sales directly from your Abacus database. With a configured ODBC connection, you can create an SQL query in Power BI that extracts the sales figures from the corresponding tables. For example:

SELECT BELNR, DNR, FBELDAT
FROM DEBI_DBL
WHERE INTERCO = 1
GROUP BY DB

This visualises the data in real time and provides an insight into the monthly earnings.

But yes, you have to be very familiar with Abacus to be able to write the SQLs efficiently.

2. Export via AbaConnect

With AbaConnect you can export data from Abacus and then import it into Power BI. This method offers flexibility, but requires manual processes or additional automation solutions. You can find detailed documentation directly from Abacus.

Example: A company exports weekly accounts receivable lists in XML format via AbaConnect. These files are then automatically loaded into Power BI to graphically display open receivables by company size.

3. Integration via OData REST API

Using the OData REST API is our preferred method. It offers some decisive advantages:

Close to the application logic: no need to know cryptic table names.
Compatibility with AbaWeb: The method also works for cloud-based Abacus installations.
Flexible and easy access: The data can be queried in a targeted and efficient manner.

However, there are also challenges: Authentication is not trivial and it is impractical to download all data for every query.

Example: You can use the OData REST API to query specific sales figures for a specific financial year. Use the following endpoint and filter:

https://entity-api1-5.demo.abacus.ch/api/entity/v1/mandants/7777/CustomerInvoices?$select=InvoiceCode&$select=CustomerId&$select=InvoiceDate&$select=Amount&$select=AmountOpen&$expand=Positions&$orderby=Id&$top=5

You can even try out queries using the Abacus API's Swagger documentation:

Test the Abacus API with the online swagger documentation.

This approach ensures that you get exactly the data you need without having to load the entire dataset. This is ideal for ad-hoc reports. But for recurring reports - e.g. month-end reporting - it is more practical to do the filtering and aggregations in Power BI, i.e. in DAX.

Why a data warehouse is the better choice

To solve these challenges, we at Power Partners have developed a special Abacus DWH. This solution joins our various DWH solutions (Business Central, Odoo, SAP, DATEV, etc.) and offers numerous advantages:

Efficient data access: The data is automatically loaded into a data warehouse in the background.
Simple integration with Power BI: Data can be accessed quickly and easily.
Cost efficiency: Power BI users do not need a separate Abacus licence.
High security: Access can be controlled granularly for individual users or groups.
Cross-client analyses: Ideal for group controlling and similar use cases.

Additional advantages over the Abacus DataAnalyzer

Although the Abacus DataAnalyzer offers a possible solution for data analysis, our Abacus DWH has decisive advantages:

Integration of external data sources: You can include not only Abacus data, but also information from CRM systems, web shops, Excel files or historical pre-system data.Example: a company wants to combine sales figures from the web shop with accounting data from Abacus to obtain a comprehensive picture of customer performance. The Abacus DWH makes this effortless.
Use modern BI tools: Work with best-of-breed platforms such as Power BI, Tableau or Qlik and benefit from their outstanding user-friendliness.
Reduce lock-in: By using an independent DWH, you minimise your dependency on Abacus.

Conclusion: Your BI solution with Abacus and Power BI

The intelligent integration of Abacus data in Power BI via a data warehouse offers you maximum flexibility, cost efficiency and future-proofing. Don't let technical hurdles or limited solutions slow you down - rely on a modern and scalable architecture.

Would you like to find out more? Contact us at [email protected].