Power BI

Connecting Power BI to Odoo

How Power BI reads Odoo data: database, API or DataLake, with refresh, permissions and data protection

At a glance

  1. Odoo stores its data in PostgreSQL; Power BI ships with a PostgreSQL connector.

  2. Reports should not run on the production database but on a copy or a separate data store.

  3. For scheduled refreshes from your own network, the Power BI service needs a data gateway.

  4. Odoo's access rules do not apply in Power BI; permissions are set again there.

Simplify-ERP connects Power BI to Odoo: read-only user or replica, data gateway, row-level security and refresh. Power BI can report on Odoo data in three ways: through a read-only user on the PostgreSQL database or a replica of it, through the external Odoo API (JSON-RPC, XML-RPC) with the rights of an Odoo user, or from a separate data store such as the Odoo DataLake, which provides Odoo data apart from the running ERP. For scheduled refreshes from your own network or a private cloud, the Power BI service needs the Microsoft on-premises data gateway. Reading straight from the database bypasses Odoo's record rules, so permissions are set again in Power BI with row-level security, and only the fields a report needs are transferred.

Details

Three ways to the data

  • Read-only access to PostgreSQL

    A dedicated database user with read-only rights, ideally on a replica of the database. Power BI reads the tables directly; this works only where you have access to the database, so not on Odoo Online.

  • The Odoo API

    Odoo's external API (JSON-RPC, XML-RPC) delivers records with an API key and the rights of the user it belongs to. For large data volumes it is slower than database access, but Odoo's rights apply when the data is fetched.

  • A separate data store

    The Odoo DataLake provides Odoo data separately from the running ERP for analytics; BI tools such as Power BI read it through SQL-compatible query engines.

Odoo DataLake

What we set up

We connect Power BI to your Odoo and settle the source, permissions and refresh along the way.

  • Read-only access

    A database user with read-only rights, on the database or a replica; without database access, the Odoo API or the Odoo DataLake as the source.

  • Data gateway

    The Microsoft on-premises data gateway, so the Power BI service reaches the source in your own network or private cloud.

  • Row-level security

    Roles in Power BI that define who sees which company, team or region.

  • Refresh

    Scheduled report refresh at times when little is posted in Odoo.

Refresh

  • Import or DirectQuery

    In import mode Power BI loads the data into its model at set times; with DirectQuery every report queries the source when it opens and loads it accordingly.

  • Data gateway

    If the Odoo database sits in your own network or a private cloud, the Microsoft on-premises data gateway connects the Power BI service to the source. It runs on a Windows machine in that network.

  • Pick the time

    Schedule refreshes for times when little is posted in Odoo, and check after every Odoo version upgrade whether tables and fields still match.

Permissions and data protection

  • Set permissions again

    Reading straight from the database bypasses Odoo's record rules. Visibility per company, team or region is set again in Power BI with row-level security.

  • Only the fields you need

    Transfer only the fields a report needs; personal data such as names and addresses can often be left out or aggregated (data minimisation, Art. 5 GDPR).

  • Settle data processing

    In the Power BI service the data sits in Microsoft's cloud. Check the data processing agreement (Art. 28 GDPR) and your tenant's storage location before personal data is transferred.

Decision rules

  • Count the reports first

    List the reports that are really used and their measures. They decide which tables Power BI needs.

  • Define measures once

    Revenue, margin or open items should mean the same in Odoo and Power BI. Write down the definition of each measure before reports are built.

  • Check Odoo reports first

    Odoo comes with pivot tables, charts and dashboards. Check for each report whether it is enough there before it is rebuilt in Power BI.

Key facts

Data source
PostgreSQL (read-only, ideally a replica), Odoo API or DataLake
Refresh
Import on a schedule or DirectQuery
Own network
On-premises data gateway
Permissions
Row-level security in Power BI

FAQ

Frequently asked questions

Do you set up the connection between Power BI and Odoo?

Yes. We set up read-only access to the database or a replica (or the Odoo API or DataLake as the source), the Microsoft on-premises data gateway, row-level security in Power BI and the scheduled refresh.

Can Power BI connect to Odoo directly?

Yes, if you have access to the database: Odoo stores its data in PostgreSQL, and Power BI ships with a PostgreSQL connector. Use a dedicated read-only user, ideally on a replica. On Odoo Online there is no database access; there the Odoo API remains.

Do Odoo access rights apply in Power BI?

Not when Power BI reads from the database: Odoo's record rules are enforced by the application, not by the tables. Set visibility per company, team or region again with row-level security in Power BI. Through the Odoo API, the rights of the API user apply.

How often are the reports refreshed?

In import mode at the times you schedule in the Power BI service; how many refreshes per day are possible depends on your Power BI licence. With DirectQuery every report queries the source when it opens. For a source in your own network or a private cloud, the on-premises data gateway is needed.

What does GDPR mean for Power BI on Odoo data?

In the Power BI service the data sits in Microsoft's cloud. Transfer only the fields a report needs (data minimisation, Art. 5 GDPR), and check the data processing agreement (Art. 28 GDPR) and your tenant's storage location before personal data is transferred.

What happens to Power BI reports when we move from Dynamics to Odoo?

The reports stay in Power BI, but their data source changes. Odoo has a different data model from Dynamics, so the measures of each report are mapped to Odoo tables and fields again. List the reports that are really used first.

Questions about your project?

Talk your starting point through with us.

Free 30 min -- no obligation