More and more companies are relying on the support of the ERP solution Odoo to map their business processes, which is particularly convincing due to its openness and intuitive, modern design. The centralized and digitized storage of data holds a true treasure, which many companies are only gradually becoming aware of in its perfection. After all, the targeted use of the variety of data obtained to identify strengths, weaknesses and trends as information relevant to action can often significantly facilitate the sustainable control of the company's destiny. As an ERP and BI service provider, we have recently received an increasing number of requests for options to output the data available in the system in report form according to individual requirements. Often, the report templates available in the Odoo standard are no longer sufficient for this purpose, especially if the complexity and requirements for the data to be evaluated increase due to customizing and a high degree of specialization of the ERP system.
Above all, Power BI as a reporting tool has aroused the interest of many existing Odoo customers as well as externally supported Odoo customers, since it can be used to quickly achieve initial convincing results with comparatively little effort, which usually awaken the desire for more comprehensive evaluations and make it clear why the use of a BI system, parallel to an ERP system, is still indispensable for serious reporting.
First of all, interested parties often ask themselves how, to what extent and whether the connection of Power BI to Odoo is possible at all. The latter can be answered in the affirmative in any case - in principle, any Odoo system can be connected to Power BI and used as a data source for corresponding reports. The decisive factor for the type and possibilities of the connection is primarily the form of Odoo hosting, i.e. the variant in which the Odoo system is provided.
The aim of this blog post is to clarify a large part of the open questions surrounding the connection of Power BI to Odoo. From the Odoo hosting types as a decisive basis, the definition of the connection options including the properties, the requirements as well as possible advantages and disadvantages up to a recommendation according to the circumstances, we try to cover the relevant topics on this as concisely but completely as possible.
Odoo edition and hosting platform as a basis
Fundamental to the options for connecting an Odoo system to Power BI is the underlying Odoo hosting platform, which is limited by the Odoo version. Odoo is offered as a licensed version in the enterprise variant and as a free open source version in the community variant. Depending on this version, several hosting platforms are available, which roughly define how the database is accessed and how much maintenance is ultimately required for the Odoo system. There are three possible hosting platforms, whereby only the on-premises type is offered in the community variant. In Odoo Enterprise, the Odoo Online and Odoo.sh types are added as possible options.
The differentiation between the versions will not be explained in detail here and can be seen in brief form in the table below, which was taken directly from Odoo and supplemented. The only thing that is essential for the connection is that Odoo Online (SaaS - Software as a Service) and Odoo.sh (PaaS - Platform as a Service) are cloud hostings that do not grant direct external access to the database. In contrast, the on-premises solution, as a local, offline variant of hosting, also offers the option of direct database access.
Power BI itself offers two ways to connect Odoo, which can be implemented in three variants. In addition to direct database access via the in-house PostgreSQL connector or ODBC, API access via the XMLRPC interface is possible. According to the different hosting types, the following connection options therefore result for Power BI depending on the Odoo hosting:
Properties and advantages and disadvantages of the connection options
The three connection methods or data sources presented can be differentiated on the basis of their functional diversity, properties and prerequisites, which we will now discuss separately by topic.
Data retrieval is initially performed for all three data sources via the standard mask in Power BI Desktop, whereby the PostgreSQL connector can be found in the Database area and the other two sources in the Other area, as can be seen in the screenshot below. In order to ensure that the data can be retrieved without complications, a certain amount of configuration work is initially required, which, in addition to the preparations for the initial setup and the provision of the prerequisites, also includes the maintenance work for subsequent adjustments and differs in part significantly for the three data sources.
Common to all data sources is the requirement of a Windows environment as operating system, which is mandatory as a requirement for Power BI Desktop. In addition, different drivers are required for the database connections. The Power Query specific PostgreSQL connector requires an Npgsql driver to ensure the .NET connection to the PostgreSQL database. Accordingly, a generic ODBC driver is required for ODBC, which is inferior in terms of functionality and performance, but can score in terms of compatibility and availability. For the API connection, Python must be preinstalled with the Pandas and Matplotlib packages. Power BI automatically recognizes the Python source directory, so that no further preconfiguration is necessary at this point, nor after installing the corresponding database drivers.
After providing the appropriate prerequisites, the connection is easiest via the PostgreSQL connector, which provides access to the data by specifying the server, database and authentication information via an input mask. The setup via ODBC is marginally more complex due to the transfer of the corresponding information by means of a DSN string (Data Source Name) preconfigured in Windows or alternatively a direct driver connection string, but in principle very similar. The connection via Python is significantly more complex in comparison, as this requires knowledge of the Odoo API, Python as well as the peculiarities of passing data to Power BI from Python scripts. For example, it is necessary to pass all fields queried via API as a dataframe, since Power BI cannot otherwise return data in tabular form. The following screenshot for the connection of the object product.product shows how such an exemplary Python script solution can look like. For each additional table, another script is necessary, which would have to be adapted according to the desired object and based on this example at least for the parameters table and table_columns.
The greater effort of the initial setup via the API subsequently grants the advantage of being able to query a more extensive set of fields directly via the methods from Odoo, since many calculated or linked field values at database level can only be queried indirectly via linking several tables or would even have to be calculated independently. Adjustments in the follow-up or the maintenance of the data sources are similar for all data sources - new fields are simply added in the script or selected in the connector and otherwise deleted or deselected. If necessary, the rights management has to be adjusted, but this has to take place in any case if the configuration is correct and therefore entails the same effort for all sources.
Updating data via Power BI Desktop and Service is supported by all data sources. The only differences at this point are in the installation and configuration of the data gateways, which are particularly important for automated and scheduled updates. For Python script connections, a special so-called on-premises data gateway personal mode is required. Since this can be installed parallel to the normal gateway, via which all other data sources are set up, this circumstance only represents additional work, but no functional restriction.
In addition to the possibility of importing the data into memory, Power BI offers the option of a so-called DirectQuery connection. Here, the data is sent directly as a query to the data source at runtime when a visual is created in the report, without having to store the data temporarily in RAM beforehand. It should be emphasized that the performance in this case depends mainly on the data source, which should therefore provide good interactive query performance. The use of DirectQuery can be useful if the underlying data is changed frequently, a high update frequency is required, very large amounts of data are retrieved or the data is taken from multidimensional sources. As a trade-off, there are modeling limitations, limited data transformation capabilities, and reporting limitations when using DirectQuery, so sensible trade-offs must be made. In any case, the use of DirectQuery offers more flexibility and increases the functional scope of a data source, which at this point in time can only be guaranteed via the PostgreSQL connector.
A further differentiation is possible based on the Power BI Dataflows feature, which was introduced only a few years ago. Dataflows are used to store data from different sources directly in the cloud without the need for extra storage. In Power BI Service, the data is connected, prepared, linked and modeled via a separate workspace using the functionalities of Power Query. As in Power BI Desktop, the queries correspond to their own tables and are stored as entities in the so-called Microsoft Azure Data Layer Gen 2 storage in the cloud without burdening local resources. The transformation logic generated in this way is reusable, can be shared by different datasets and reports in Power BI and, unlike predefined datasets of specific reports, can always be modified or extended afterwards. Currently, only PostgreSQL and ODBC can be used as data sources in Dataflows, so Python scripts are excluded from the additional functionality of being able to be defined directly in the cloud.
Query Folding is a powerful tool in Power BI Desktop that allows queries to be aggregated across multiple well-considered transformation steps, allowing the most complex queries possible to be passed directly to the underlying data source to reduce the load on the Power BI environment. Query folding is only supported for databases as the source system, so Python scripts must be left out of this as well, which conversely increases the load on the Power BI system when using the latter. The following table summarizes the above-mentioned findings in compact form - as can be seen, the PostgreSQL connector offers the greatest range of functions, followed by the ODBC connector and the connection via Python.
In terms of performance, the PostgreSQL connector provided is clearly to be preferred, especially due to the possibility of using DirectQuery for very large data sets and the general performance advantages via the .NET connection. The connection of Odoo via the API is the most inert in comparison due to the lack of query folding and the natural restrictions and should be urgently reconsidered for large data volumes or limited to the most necessary data sets already when retrieving the data. Alternatively, it is possible to regularly transfer the database backups provided in the Odoo cloud via a routine to a local database, via which access can alternatively take place. In order to additionally improve performance at this point, the pre-configuration of meaningful views on the database itself should also be considered in particular, in order to offload as much load as possible from the ETL process and from the Power BI system itself by querying these.
In general, the issue of security is not to be considered problematic in any of the data sources presented. For example, the connection to the database can be SSL-encrypted, although experience has shown that the configuration in the case of the PostgreSQL connector has proven to be more complex and demanding. Furthermore, access is granted via authentication and precise definition of access rights, whether in the PostgreSQL database or Odoo itself for the API connection, and restricted to data required for evaluation via read rights. The prerequisite at this point is above all a prior and well thought-out rights management including configuration at database and Odoo level.
Availability in the sense of flexibility in the application is somewhat limited in the database connection, since Odoo cloud systems cannot be connected natively. Here, the API connection is naturally at an advantage, as it is a possible option for implementation at any time, regardless of hosting. As mentioned, this limitation can alternatively be circumvented in a roundabout way by ensuring database access through a regular database update via the export of the database backups available online.
Weighing up the different properties of the data sources mentioned and assuming the best possible configuration, the evaluation of these can be summarized according to the table below.
Basically, we recommend always considering the PostgreSQL connector as a data source first for the reasons mentioned and only switching to ODBC in case of compatibility or stability problems. If the underlying Odoo system is in the cloud, the only remaining step is to use a dedicated Python script, as long as the data is not additionally kept separately in another local database via a daily routine that would allow an optional database connection.
Are you also interested in evaluating your projects internally or are you looking for a business intelligence solution? Contact us now and we will be happy to assist you as a reliable partner!
Sources: www.odoo.com, www.odoo.com, https://docs.microsoft.com/de-de/power-bi, https://docs.microsoft.com/de-de/power-query