Success Story - peha Hagmann GmbH

Success Story - peha Hagmann GmbH

Dynamic reporting for Odoo.sh via Power BI.

peha Hagmann GmbH

peha has been an established packaging specialist and online retailer since 1999, offering a wide range of products for both the B2B and B2C sectors. The focus is on products from the areas of packaging and operating materials, furniture protection and moving supplies, as well as some special products (e.g. anti-slip pads) that have been developed in close cooperation with the customer.

Attractive and flexible reporting by means of Power BI via the connection of Odoo systems is becoming increasingly relevant for manaTec GmbH, both for internal company evaluations and due to increased customer enquiries. Especially because Odoo as an ERP system with its own reporting solutions quickly reaches its limits when it comes to original and more complex requirements. By using Odoo's underlying PostgreSQL database as a data source, Power BI makes it possible to provide any kind of report, from simple extensions of the Odoo overviews to complex dynamic reports. An advantage not to be neglected is that this way no development effort on the Odoo system is required and thus the alignment to the Odoo standard is proactively supported.
Data transfer from Odoo to Power BI (ETL process).
Data transfer from Odoo to Power BI (ETL process).

Recently, this very solution was requested by peha as a new potential BI customer by means of connecting an Odoo.sh platform as a cloud environment. In contrast to the connection of on-premises environments, no database query is available for Odoo.sh and Odoo Online without further ado, as the database access is not released externally and thus cannot be guaranteed. However, Odoo grants an alternative data access point with the external API access through the method and function call via XML-RPC without having to depend on the database connection. After an internal evaluation of the possibilities to guarantee such a connection via Power BI Desktop, peha was able to submit an offer for the implementation of this project via the use of the Power BI data source Python script for data access.

Data access in Power BI by Odoo hosting type.
Data access in Power BI by Odoo hosting type.

Via the Python script, data can be read out and converted in Power BI using Python code. Python 3 already provides the module xmlrpc.client for remote access as part of the standard library, so that with the help of the Odoo documentation the XML-RPC code for querying the data from Odoo.sh can be defined and then provided in a dataframe as a table. The prerequisite is a Python installation on the Power BI system, as well as the installation of the necessary packages that ensure the correct output of the data.

The requirement was to provide an initial report on stock quantities and orders for purchasing. In addition to fields from the Odoo standard, such as the minimum and maximum stock of a product, the supplier or the product name, customising fields such as the safety stock or the average demand were to be queried and supplemented with calculated key figures such as an order proposal for the next order and the last order quantity. In addition to the creation of the report, peha was to be given an insight into Power BI and the underlying approach via accompanying workshops simultaneously with the development of the solution. The aim was to enable peha to create independent reports in self-service and, if necessary, to expand the provided report afterwards. In addition to the actual solution and transfer of knowledge, the created report and other Power BI reports were to be set up for automated updating of data from Odoo.

Apart from the data connection, which requires exact preparation and knowledge of the necessary data models in Odoo and, due to the script language, a somewhat higher expenditure of time in defining the data sources, the process is similar to that of the connection for the database. The API even facilitates access to calculated fields that are not directly accessible via the database and can only be mapped indirectly via additional tables. This results in the usual Power BI steps for data preprocessing, modelling, enrichment and presentation, which culminate in the definition of the Power BI gateway for setting up regular automatic updating of the data.

Power BI workflow after connecting the data from Odoo.
Power BI workflow after connecting the data from Odoo.

All customer requirements could be successfully implemented by preparing the Python scripts and for the most part via joint workshops and show that flexible and dynamic Power BI reporting can also be implemented for Odoo cloud solutions.

By adding the Power BI infrastructure as a reporting solution, peha was able to save development effort on the Odoo system, provide increased dynamics and flexibility in report creation and create immediate added value with the solution for purchasing. The delivered implementation thus also serves as a basis for future extensions in reporting. These can be realised in line with the existing configuration in the BI self-service and, in the case of complex projects, if necessary, also at any time with our advice and support in a now familiar environment.

Are you planning to implement a meaningful and user-friendly reporting from the ERP system Odoo? Then contact us now and we will be happy to advise you on your individual business intelligence solution!