Success Story - peha Hagmann GmbH
Dynamic reporting for Odoo.sh via Power BI.
The cooperation with manaTec GmbH ran smoothly from the very beginning. It was important for us to have a reproducible report approach with which we can also independently access database fields in our Odoo.sh instance and use them for reports in Power BI. After clarifying our report requirements in detail, an individual offer was created, which was implemented exactly according to our ideas.
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.
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.
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.
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!