In this tutorial I will teach you how to connect Odoo and PowerBI to do BI reporting on Odoo data. You will learn how to configure postgreSQL and PowerBI in order to connect them together for real time reporting.
In this tutorial I will work with postgreSQL 9.3, PowerBI Desktop 2.34 and Odoo 10. While this tutorials targets these versions it should work for any Odoo version and postgreSQL 9.3, 9.4, 9.5 and 9.6.

1. Configuring postgreSQL

The first thing that we need to do is to configure postgreSQL. By default postgreSQL does not allow remote connections and if you do configure this it still has to use HTTPS.
As running postgreSQL over SSL is quite advanced and out of scope in this tutorial we’ll configure it without SSL. It is better to run it over SSL as it is more secure so you might want to keep this in the back of your head as a next task.

1.1 Allowing remote connections

Alright, let us start by allowing remote connections to the postgreSQL server. In order to allow this we should edit the ‘pg_hba.conf’ file:

In order to allow remote connections to postgreSQL we should add a line that says postgreSQL that we can connect remote as long as we authenticate. Add the following line at the top of the file:

Your configuration file should now look like this:

So, what exactly does this do? Let me explain it part by part:

• Type ‘host’: This tells postgreSQL that it will allow remote connections from a host.
• Database ‘all’: By saying ‘all’ postgreSQL will allow connecting to any database that exists. If you only want to allow access to one database you can fill in the database name here.
• User ‘all’: This tells postgreSQL that it’ll allow connections to postgreSQL from any postgreSQL user. If you only want access from one specific user you can fill in the user his name here.
• CID-ADDRESS: This part allows you to configure from where connections to postgreSQL will be allowed. In this tutorial I’ve set it to ‘0.0.0.0/0’ which means that connections from anywhere around the world will be accepted. In production this should be narrowed down to the range of your network, for example ‘192.168.0.0/24’. Another option is to limit this from a firewall, this is however out of scope for this tutorial.

So to conclude this line of code: we’re allowing connections to postgreSQL from anywhere in the world as long as they authenticate with an username and password.
Now save the file and close it (ctrl+o to save and ctrl+x to close the file if you work with nano).

1.2 Disabling the SSL from postgreSQL

Now that the postgreSQL server allows connections from anywhere there is a second issue. By default postgreSQL will only allow secure connections that run over SSL, this however requires more configuration so I’ve decided to do this tutorial without SSL. If you do want to run postgreSQL over HTTPS you can find more information here.
Disabling the SSL however is very easy, it is just one setting in a configuration file that you need to set to false.
Edit the postgresql.conf file to disable this:

You can find this setting somewhere along line 80, under the ‘Security and Authentication’ block. There is a line that contains the following code:

Change this from true to false. Your result should look like this:

Now save the file (ctrl+o) and close it (ctrl+x).

1.3 Restarting postgreSQL

Congratulations, you’re done configuring postgreSQL. These changes however require a restart before they become active because otherwise postgreSQL does not know about the changes. Restart the postgreSQL service from the command line:

Restarting the postgreSQL server should return an ‘OK’. If this is not the case you’ve probably made a typo, in this case just go over this tutorial again.
If you get the following output you’re ready to connect with PowerBI:

2. Configuring PowerBI

After the postgreSQL configuration we can connect with it from PowerBI. If you don’t have PowerBI yet you can download it here.

2.1 Settings up the connection

Open up PowerBI Desktop, click on ‘Get Data’ (1) and then click on ‘More’ (2):

A dialog will pop up. Click on ‘Database’ (1), choose for ‘PostgreSQL database’ (2) and then click on ‘Connect’ (3):

After this a new dialog will show where you can fill in the IP of the postgreSQL server (1) and the name of the database (2), next click on ‘OK’ (3). In my example the postgreSQL runs on the IP ‘192.168.118.137’ and I have a database named ‘odoo-test-2’:

Finally a dialog will show up where you can fill in the username and the password. You can choose the credentials from a user that only has access to a specific database or you can use a user who has rights to all databases.
Tip: If you don’t know the credentials you can change the password by running the following commands:

In my example I will connect with the main user ‘postgres’ as this user has access to any database and is the administrator user of postgreSQL:

After you click on ‘Connect’ you’ll see a new dialog show up from which you can finally choose from which tables you’d like to get data and make reports from. You’ll get something like this:

Choose the database tables from which you want to get data and where you want to report on and finally click on ‘Load’. From here on you can start connecting relations and create graphs as you’d like. If you’d like to learn more about creating good and advanced PowerBI reports you can follow the official tutorials here.

2.2 Non-encrypted authentication

If you run the postgreSQL service without SSL (as we’ve configured in this tutorial) PowerBI will give you a warning about it, just click on ‘OK’ to connect:

Tip: If you want to turn off the encryption warnings you can do this from ‘File’ > ‘Options and settings’ > ‘Data source setting’ by clicking on your connection and then clicking on ‘Edit’. After clicking on edit a dialog will show up where you can uncheck ‘Encrypt connections’.

3. Conclusion

Connecting Odoo and PowerBI isn’t the easiest thing to do but it is really worth it. While configuring it takes quite some time it will absolutely be a plus on the longer term. At this time there is no option to create advanced reports and do BI on the data so PowerBI will fill up this gap. After you’ve configured this once and ‘played’ a few hours with PowerBI you’ll notice that it is a very powerful and good reporting tool.
Has this tutorial helped you, do you have any feedback or questions? Post away!