Comment intégrer Power BI avec Odoo (Direct PostgreSQL, 2026)

By the end of this recipe, your Power BI Service will have a published dataset connected directly to your Odoo 19 PostgreSQL database, refreshing every 4 hours, with row-level security so each Odoo company sees only its own data, and a starter semantic model covering Sales, AR, AP, and Inventory. Skill required: PostgreSQL fluency + intermediate Power BI experience. Time required: 4 hours connection + initial model, ongoing time for additional reports. ECOSIRE has built this for clients who outgrew Odoo's native dashboards, and the recipe below is the playbook.

The trap most teams fall into: connecting Power BI directly to the Odoo production database via the application user (odoo) and pulling 200 tables uncontrolled. This pegs the database CPU at 100 percent during refresh, slows every Odoo user, and exposes raw account_move_line to the BI team without record-rule filtering. The recipe below sets up a read-only, replica-based, RLS-enforced connection that scales.

What you will need

• Power BI Pro or Premium: Pro for individual publishing, Premium for shared workspaces and large datasets.
• PostgreSQL access: ideally a streaming read replica of your Odoo production. A dedicated reporting replica avoids contention.
• Power BI Desktop: latest version (Microsoft Store or powerbi.microsoft.com/desktop).
• On-premises Data Gateway (if Odoo is on a private network): install on a Windows machine that can reach both Power BI Service and the PG replica.
• Time: 4 hours initial, 1 to 2 hours per additional report.
• Skill: SQL, basic DAX, understanding of fact/dimension modeling.

Step-by-step

1. Create a read-only PostgreSQL user

Never connect Power BI as the odoo superuser. Create a dedicated user:

CREATE USER bi_reader WITH PASSWORD 'use-a-strong-password';
GRANT CONNECT ON DATABASE production TO bi_reader;
GRANT USAGE ON SCHEMA public TO bi_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO bi_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO bi_reader;

-- Optional: limit which tables can be read (reduce attack surface)
REVOKE SELECT ON ir_attachment FROM bi_reader;  -- attachments contain raw file content
REVOKE SELECT ON res_users FROM bi_reader;       -- password hashes
REVOKE SELECT ON ir_logging FROM bi_reader;

Verification: log in as bi_reader via psql -U bi_reader -h replica-host -d production and confirm you can run SELECT COUNT(*) FROM sale_order but not INSERT.

2. Enable PostgreSQL connectivity from Power BI

Power BI Desktop > Get Data > PostgreSQL database. Provide:

• Server: your-replica-host:5432
• Database: production
• Data Connectivity mode: Import (recommended) or DirectQuery (for very large datasets where import won't fit in memory)

In Authentication, choose Database > username bi_reader + password. Verification: the table list loads showing 200+ Odoo tables.

3. Build dimension and fact tables with views (not direct table imports)

Importing raw Odoo tables results in a star-schema mess. Build views in Postgres that pre-join, pre-filter, and rename columns to BI-friendly names:

-- Create views in a dedicated schema to keep them out of the public schema
CREATE SCHEMA bi;

CREATE OR REPLACE VIEW bi.dim_partner AS
SELECT
    p.id AS partner_key,
    p.name,
    p.email,
    p.phone,
    c.name AS country,
    p.is_company AS is_company,
    p.customer_rank > 0 AS is_customer,
    p.supplier_rank > 0 AS is_vendor,
    p.create_date AS first_seen,
    p.company_id
FROM res_partner p
LEFT JOIN res_country c ON p.country_id = c.id
WHERE p.active = TRUE;

CREATE OR REPLACE VIEW bi.fact_invoice AS
SELECT
    am.id AS invoice_key,
    am.name AS invoice_number,
    am.partner_id AS partner_key,
    am.invoice_date AS invoice_date,
    am.invoice_date_due AS due_date,
    am.amount_total_signed AS total_signed,
    am.amount_residual_signed AS residual_signed,
    am.payment_state,
    am.move_type,
    am.company_id,
    EXTRACT(EPOCH FROM (CURRENT_DATE - am.invoice_date_due)) / 86400 AS days_overdue
FROM account_move am
WHERE am.state = 'posted'
  AND am.move_type IN ('out_invoice', 'out_refund', 'in_invoice', 'in_refund');

GRANT SELECT ON ALL TABLES IN SCHEMA bi TO bi_reader;

In Power BI, only import the views from the bi schema. Verification: the dataset has 5 to 10 clean tables instead of 200 raw ones.

4. Configure Row-Level Security (RLS) for multi-company

If your Odoo has multiple companies, each user should only see their company's data. In Power BI Desktop > Modeling > Manage Roles:

[Company] = LOOKUPVALUE(
    'CompanyAccess'[CompanyId],
    'CompanyAccess'[UserPrincipalName],
    USERPRINCIPALNAME()
)

Maintain a CompanyAccess table mapping each Power BI user (by email) to allowed company IDs. Refresh nightly from your HR system.

Verification: log in to Power BI Service as a user mapped to company A, run a report, and confirm only company A's data shows.

5. Set the data refresh schedule

Publish to Power BI Service. In the dataset settings > Scheduled Refresh, configure:

• Refresh frequency: every 4 hours during business hours.
• Time zone: your business HQ.
• Failure notifications: dataset owner email.
• Gateway: pick the on-premises gateway if Odoo is on a private network.

Verification: trigger a manual refresh; it completes within 5 minutes for typical 5 GB Odoo datasets.

6. Build a Sales-by-Month visual

In Power BI Desktop, create a measure:

Total Sales =
CALCULATE(
    SUM('fact_invoice'[total_signed]),
    'fact_invoice'[move_type] = "out_invoice"
)

Sales LY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('fact_invoice'[invoice_date])
)

Sales Growth % =
DIVIDE([Total Sales] - [Sales LY], [Sales LY])

Drop a clustered column chart with invoice_date.Month on X-axis and [Total Sales], [Sales LY] as values. Add [Sales Growth %] as a tooltip. Verification: the chart matches the Odoo Sales Reporting module within rounding tolerance.

7. Build the AR Aging matrix

Create a calculated column for aging buckets:

Aging Bucket =
SWITCH(
    TRUE(),
    'fact_invoice'[days_overdue] <= 0, "Current",
    'fact_invoice'[days_overdue] <= 30, "1-30 days",
    'fact_invoice'[days_overdue] <= 60, "31-60 days",
    'fact_invoice'[days_overdue] <= 90, "61-90 days",
    "90+ days"
)

Drop a matrix with partner_key.name as rows and Aging Bucket as columns, sum of residual_signed as values. Verification: the totals tie to Odoo's AR aging report.

8. Apply incremental refresh (large datasets)

For datasets over 1 GB or more than 10M rows, configure incremental refresh:

• Define RangeStart and RangeEnd parameters in Power Query.
• Filter fact_invoice to invoice_date >= RangeStart && invoice_date < RangeEnd.
• In Modeling > Incremental Refresh, set "Store rows in last 5 years, refresh rows in last 30 days".

This makes each refresh pull only the last 30 days of changes, reducing refresh time from 30 minutes to under 5.

Verification: the dataset's published refresh history shows incremental refreshes succeeding in under 5 minutes.

Common mistakes

• Connecting as the odoo superuser. Power BI then has write access — risky and unnecessary.
• Importing 200 raw tables. The dataset becomes unusable; users get lost in the schema.
• Skipping RLS in multi-company setups. Sales data leaks across companies.
• Using DirectQuery on Odoo's production. Every report click triggers a SQL query against production, killing application performance.
• Refreshing too often. Every refresh is a heavy table scan. 4 hours is plenty for most ops dashboards; finance can use daily.
• Hardcoding company filters in DAX. Use RLS instead — it's faster and cleaner.

Going further

Build a semantic layer with dbt: instead of raw views, use dbt to define a layered transformation (staging > intermediate > marts) with tests. The mart layer is what Power BI imports.

Add anomaly detection: Power BI's built-in "Anomaly Detection" visual flags unusual sales spikes or drops automatically.

Push back to Odoo via Power Automate: when a Power BI dashboard alerts on a low-margin SO, Power Automate can call Odoo's REST API to flag the SO for manager review.

Embed Power BI in Odoo: use the Power BI Embedded SDK to render dashboards inside the Odoo UI. Most ECOSIRE clients put a "Reports" tab on the form views of key models.

For a fully managed Power BI + Odoo data warehouse including dbt models, custom DAX library, and embedded dashboards, ECOSIRE Power BI services build the entire stack. Pair this with how to integrate Shopify with Odoo for unified e-commerce + ERP analytics.

Frequently Asked Questions

Should I use Import or DirectQuery mode?

Import is faster, supports more DAX, and is the default. Use DirectQuery only when the dataset is too large to fit in memory (over 10 GB) and you accept slower report interaction. Hybrid mode (introduced in 2024) gives you both — recent data is imported, historical is DirectQuery.

What about Odoo Studio's "Spreadsheet" feature?

Odoo Studio Spreadsheet is great for ad-hoc analysis inside Odoo. Power BI is for cross-system reporting (Odoo + Shopify + QuickBooks + HubSpot all in one model) and for sharing with non-Odoo users.

Can I write back to Odoo from Power BI?

Not directly — Power BI is read-only by design. Use Power Automate as the bridge: a Power BI alert triggers a flow that calls Odoo's REST API.

How do I report on custom fields?

Custom fields are regular columns in the Postgres table. As long as the BI view's SELECT picks them up, they appear in Power BI. The convention is to name custom fields x_studio_* or x_* so they're easy to filter.

For a complete Power BI + Odoo enterprise rollout including 30+ pre-built reports, custom DAX libraries, and white-label embedded dashboards, our Power BI implementation team ships fixed-price engagements. Or read how to build a customer cohort retention report for an example of an advanced Odoo data analysis.