Success Story - peha

Success Story - peha

Dynamic reporting for Odoo.sh via Power BI.

peha Hagmann GmbH

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 supplies, 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.

Learn more

An impressive and flexible reporting by means of Power BI via the connection of Odoo systems is becoming more and more relevant for manaTec GmbH, on the one hand for internal company evaluations and on the other hand due to increased customer inquiries. Above all, 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 Odoo overviews to complex dynamic reports. A not to be neglected advantage 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-premise environments, no database query is available for Odoo.sh and Odoo-Online without any further ado, since 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 rely on the database connection. After an internal evaluation of the possibilities via Power BI Desktop to be able to guarantee such a connection, 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.

The Python script can be used to read and transform data in Power BI using Python code. Python 3 already provides the xmlrpc.client module for remote access as part of the standard library, so that the XML-RPC code for querying the data from Odoo.sh can be defined using the Odoo documentation and then provided as a table in a data frame. 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.

Along the way, 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 description, customizing 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 goal was to enable peha to create independent reports in self-service and, if necessary, to extend 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 the definition of the data sources, the process is similar to that of the connection for the database. The API even facilitates access to calculated fields, which 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, modeling, 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 were successfully implemented with the preparation of Python scripts and mostly via joint workshops, showing 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 reporting, and create immediate added value with the solution for purchasing. The delivered implementation thus serves above all as a basis for future extensions in reporting. These can be implemented 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!