Power BI - Odoo

Can we use Power BI with Odoo as a data source? Yes, is the answer. Odoo becoming an alternative business management system like Microsoft Dynamics, Salesforce. Odoo have good reporting options too, but still missing of BI is the biggest drawback in Odoo.

First Try

In the beginning, after trying various methods including direct connection to Odoo’s database engine PostgreSQL, we settled with ODBC Connection. It is simple to implement but doesn’t make a big difference from using direct connection. Even we faced the following issues.

1. The need for a pro license for everyone who is working with Power BI desktop to connect with the reports.

2. This method doesn’t work on Mobile or Web, so on roaming or from Home the report can’t be accessed. It became a problem for the users, especially for the Management Team.

3. DB’s security will ruin when we provide the Management Team to work from the place of desire.

4. Primary issue is, we can’t be able to access the relative or function or compute type fields of Odoo. This put lot of pressure on SQL query building.

Solution

First, I enjoy solving the Security issue. Exposing the DB to all is not a good idea. Using of Power BI Gateway is the best solution. So now the issues 2 and 3 is solved. But again, an additional issue added to our list.

5. I need dedicated Server to install in our camps for the Gateway

To solve the issues 1, 4, and 5, I have come up with new out of box solution The Web as the data source.

Advantage of Web as Source

1. No need for proxy like Gateway, direct accessibility. We can save hardware and OS license expenses.

2. No need for Power Bi desktop, deliver thru Web and Mobile app

3. No need for a large count of Pro Licenses.

4. Lot of implementation option.

5. And easily secured.

Implementation

Two ways of implementing the Web Source is, by creating separate Odoo Module, or by using AWS API Gateway + Lambda Function.

Three challenges to fulfil the Web Source

1. Response type should be in CSV format.

2. Query or parameter

3. Security

Odoo Module

Security is the hardest part to implement in Odoo, and it requires SSL. My suggestion is API Key or similar implementation, plus SSL will resolve this problem. Other two point is easy to implement.

AWS API Gateway + Lambda

Security in AWS is state-of-the-art, so no need to worry. But the complex part here is coding in lambda using XMLRPC and implementing API Gateway.

My strong recommendation for Power Bi and Odoo combination is Web as Source delivered thru AWS, or any similar cloud platform.