Connecting Odoo to Power BI: what works

For a lot of SMEs, Odoo is the system that holds everything. Sales, purchasing, inventory, sometimes accounting and projects on top. So it makes sense that the question comes up: can we build reporting on this that goes further than the built-in screens.

There are three ways to get that data into Power BI. All three appear to work on the day you set them up. Two of them break afterwards.

Way one: exporting

Someone pulls an export out of Odoo every month and Power BI reads it in. This works, until it doesn't.

It breaks on ordinary things. Someone exports with different columns selected. Someone saves in a different format. Someone is on holiday. And because it is manual work, there is always a gap between the fact and the number, which means you are never really current.

For a one-off analysis an export is fine. For something that has to run every week it is not a solution, it is an agreement that eventually will not be kept.

Way two: straight onto the database

Odoo runs on PostgreSQL, so technically you can connect to it directly. With your own installation on your own server that is not even much trouble.

This works well and it is fast. There are two catches.

The first is that with Odoo Online or Odoo.sh you usually do not get that access. So it is not your decision, it depends on your hosting.

The second one matters more. The Odoo data model was built for the application, not for you. Amounts sit in the currency of the order, not necessarily in yours. There are columns the application uses that do not mean what their name suggests. And an upgrade can change something without anyone telling you, after which your report quietly stops or quietly lies.

So connecting straight to the database works, but only if someone knows that model and keeps an eye on it.

Way three: through the API, into your own layer

This is what I build in practice. You pull the data through the official Odoo interface, you write it into your own database, and Power BI looks at that database.

That looks like a detour. It is not, for three reasons.

You get history. Odoo keeps the current state, not what something looked like three months ago. If you want to see how your inventory or your order book developed, you have to keep that yourself. Your own layer does it automatically.

You are protected against changes. If Odoo changes something, you adjust one collection script instead of every report.

And you can combine. Your footfall, your webshop data, your marketing numbers, your labor cost: none of that lives in Odoo and all of it needs to sit next to what does. Your own layer is the only place where that can happen.

Which of the three I use myself

The third one, always. Pull through the interface, write into your own database, and let it run at a fixed moment.

Concretely, in my case: a script that runs on a schedule through GitHub Actions, with the keys in the repository secrets. It has been running for a while now and it has not produced a single error. That is not luck, it follows from where that setup takes the mistakes out: nobody has to export anything, no file ends up in the wrong place, and when something does go wrong I get a warning instead of old numbers.

The first way, exporting by hand, I only use for a one-off analysis. The second, straight onto the database, is technically fine but makes you dependent on a data model built for the application, one that can shift with an upgrade.

What takes the most time in that third approach is not the technology. It is deciding which of the three revenue figures in Odoo is your revenue figure, and you have that conversation only once.

Where you actually get stuck

The Odoo interface is not the hard part. What costs time is determining exactly what you pull.

For most things Odoo has several places where something lives. A sale sits in the order lines, in the invoicing, and in the accounting, and those three do not give the same answer. That is not wrong, they simply measure different things: what was ordered, what was invoiced, and what was booked.

So before you start you have to decide which of those three is your revenue figure. If you do not choose it explicitly, whoever builds the report chooses for you, and then your dashboard does not match your accounting and nobody knows why.

The same goes for discounts, credit notes, VAT and intercompany. All choices you make once and then write down.

What you need to start

Access with a user that can only read, nothing more. Clarity about which Odoo version and which hosting you have, because that determines your options. And half an hour to agree on what revenue means.

The technical side is then a matter of days, not months. The conversation about definitions is what sets the lead time.

The claim

The question is not how you connect Odoo to Power BI. That is solved. The question is which of the three revenue figures in Odoo is your revenue figure, and you have to answer that one, not your integrator.