Odoo + Power BI: Complete Analytics Integration Guide

Odoo 19 Enterprise includes solid built-in reporting, but for organizations that need self-service analytics, cross-system data modeling, and enterprise-grade visualization, Power BI is the natural complement. Connecting Odoo's operational data to Power BI's analytical engine unlocks insights that Odoo's native reports can't provide.

This guide covers every aspect of the Odoo-Power BI integration: connection architecture, data modeling best practices, building key business dashboards, DAX measure creation, incremental refresh configuration, and deployment in Microsoft Fabric for enterprise scale.

Key Takeaways

• Three connection methods: PostgreSQL direct, Odoo REST API, and ODBC via Odoo's export
• DirectQuery mode provides real-time data; Import mode provides better performance for large datasets
• Odoo's PostgreSQL schema requires denormalization for efficient Power BI data models
• Incremental refresh reduces load time for large tables (account.move, stock.move)
• Row-Level Security in Power BI mirrors Odoo's company-level access control
• Gateway deployment required for on-premises Odoo; cloud Odoo connects directly
• Microsoft Fabric (Power BI Premium) enables Odoo data in enterprise lakehouse
• Key measures: revenue, gross margin, inventory turnover, receivables aging, OEE

---

Integration Architecture Options

Choose the right connection architecture based on your Odoo deployment and reporting needs.

Option 1: Direct PostgreSQL Connection (Recommended)

Connect Power BI directly to Odoo's PostgreSQL database using the PostgreSQL connector:

Pros:

• Full access to all Odoo tables and raw data
• Best performance for complex joins
• Supports both Import and DirectQuery modes

Cons:

• Requires network access from Power BI Gateway to PostgreSQL
• Changes in Odoo's data model require updating Power BI queries
• Direct database access bypasses Odoo's access control

Option 2: Odoo REST API

Connect via Power BI's Web connector using Odoo's REST API:

Pros:

• Works without network access to PostgreSQL
• Respects Odoo's access rights per user
• No database credentials required

Cons:

• Slower than direct PostgreSQL (one API call per table)
• Rate limiting affects large data pulls
• Difficult to paginate efficiently for large datasets

Option 3: Export to Data Warehouse

ETL Odoo data into a dedicated data warehouse (Azure Synapse, Snowflake, BigQuery):

Pros:

• Maximum performance at scale
• Decouples BI from ERP
• Can integrate multiple source systems

Cons:

• Highest infrastructure cost and complexity
• Data latency depends on ETL schedule (typically 1 hour to 24 hours)
• Requires ETL pipeline maintenance

Recommended architecture for most organizations: Direct PostgreSQL with Power BI Gateway (on-premises) or direct connection (cloud Odoo), Import mode with incremental refresh, scheduled refresh every 1-4 hours.

---

Setting Up the PostgreSQL Connection

Step 1: Network access

For on-premises Odoo:

1. Install the On-Premises Data Gateway on a server with network access to PostgreSQL
2. Configure the gateway with your Microsoft 365 credentials
3. Open PostgreSQL port (5432 or 5433) from the gateway server to the Odoo DB server

For cloud Odoo (AWS, Azure, GCP):

• Configure security group / firewall to allow inbound from Power BI's IP ranges
• Or: use the on-premises gateway on a cloud VM in the same VPC

Step 2: Create a read-only database user

Never connect Power BI with your main Odoo database user. Create a dedicated read-only user:

-- Create read-only user for Power BI
CREATE USER powerbi_reader WITH PASSWORD 'strong_password_here';

-- Grant connection to database
GRANT CONNECT ON DATABASE your_odoo_db TO powerbi_reader;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO powerbi_reader;

-- Grant SELECT on all current and future tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO powerbi_reader;

Step 3: Configure in Power BI Desktop

1. Open Power BI Desktop → Get Data → PostgreSQL Database
2. Enter:
   • Server: your PostgreSQL host (and port if not 5432)
   • Database: your Odoo database name
   • Username: powerbi_reader
   • Password: the read-only user password
3. Select connection mode: Import (recommended) or DirectQuery

---

Key Odoo Tables for Power BI

Understanding Odoo's PostgreSQL schema is essential for building accurate data models.

Financial tables:

Sales tables:

Inventory tables:

HR and Payroll:

---

Power BI Data Model Design

Star schema design for Odoo data:

Convert Odoo's normalized schema to a star schema for optimal Power BI performance:

[Date Table] (dimension)
     ↓
[Sales Fact Table]
     ↓
[Product Dimension] ← [Product Category Dimension]
     ↓
[Customer Dimension] ← [Country Dimension]
     ↓
[Salesperson Dimension]
     ↓
[Company Dimension]

Power Query M code — Sales Fact Table:

let
    Source = PostgreSQL.Database("your-odoo-server:5433", "your_db"),
    SaleOrderLine = Source{[Schema="public", Item="sale_order_line"]}[Data],
    SaleOrder = Source{[Schema="public", Item="sale_order"]}[Data],
    ProductTemplate = Source{[Schema="public", Item="product_template"]}[Data],
    ProductProduct = Source{[Schema="public", Item="product_product"]}[Data],

    // Join order lines with orders
    JoinWithOrder = Table.NestedJoin(
        SaleOrderLine, {"order_id"},
        SaleOrder, {"id"},
        "Order", JoinKind.Inner
    ),

    // Expand order columns needed
    ExpandOrder = Table.ExpandTableColumn(
        JoinWithOrder, "Order",
        {"name", "state", "date_order", "partner_id", "user_id", "company_id"},
        {"order_name", "order_state", "date_order", "partner_id", "user_id", "company_id"}
    ),

    // Filter: confirmed and done orders only
    FilterState = Table.SelectRows(
        ExpandOrder,
        each [order_state] = "sale" or [order_state] = "done"
    ),

    // Select and rename final columns
    SelectColumns = Table.SelectColumns(FilterState, {
        "id", "order_id", "product_id", "date_order", "partner_id",
        "user_id", "company_id", "product_uom_qty", "price_unit",
        "price_subtotal", "price_tax", "price_total"
    }),

    // Change types
    ChangedTypes = Table.TransformColumnTypes(SelectColumns, {
        {"date_order", type datetime},
        {"price_subtotal", type number},
        {"product_uom_qty", type number}
    })
in
    ChangedTypes

---

Essential DAX Measures

Revenue and margin:

// Total Revenue (Net)
Revenue = SUMX(SalesFact, SalesFact[price_subtotal])

// Revenue MTD
Revenue MTD =
CALCULATE([Revenue], DATESMTD(DateTable[Date]))

// Revenue YTD
Revenue YTD =
CALCULATE([Revenue], DATESYTD(DateTable[Date]))

// Revenue vs Prior Period
Revenue vs PY =
VAR CurrentRevenue = [Revenue]
VAR PriorYearRevenue =
    CALCULATE([Revenue], SAMEPERIODLASTYEAR(DateTable[Date]))
RETURN
DIVIDE(CurrentRevenue - PriorYearRevenue, PriorYearRevenue, 0)

// Gross Margin
Gross Margin =
SUMX(SalesFact,
    SalesFact[price_subtotal] -
    (RELATED(ProductDim[standard_price]) * SalesFact[product_uom_qty])
)

// Gross Margin %
Gross Margin % =
DIVIDE([Gross Margin], [Revenue], 0)

Inventory measures:

// Current Stock Value
Stock Value =
SUMX(
    StockQuant,
    StockQuant[quantity] * RELATED(ProductDim[standard_price])
)

// Inventory Turnover (annualized)
Inventory Turnover =
DIVIDE(
    [COGS Annualized],
    [Average Inventory Value],
    0
)

// Days of Inventory Outstanding
DIO =
DIVIDE(365, [Inventory Turnover], 0)

// Stockout % (products with zero stock)
Stockout Rate =
DIVIDE(
    COUNTROWS(FILTER(StockQuant, StockQuant[quantity] <= 0)),
    COUNTROWS(StockQuant),
    0
)

Receivables aging:

// Current (0-30 days)
AR Current =
CALCULATE(
    SUM(ARFact[amount_residual]),
    ARFact[days_overdue] <= 0
)

// 1-30 days overdue
AR 1-30 Days =
CALCULATE(
    SUM(ARFact[amount_residual]),
    ARFact[days_overdue] >= 1 && ARFact[days_overdue] <= 30
)

// Days Sales Outstanding
DSO =
DIVIDE(
    SUM(ARFact[amount_residual]),
    [Revenue] / 365,
    0
)

---

Key Dashboard Pages

1. Executive Dashboard

• Revenue vs budget (gauge chart)
• Revenue trend (line chart, 13 months rolling)
• Gross margin % (KPI card with trend)
• Top 10 customers by revenue (bar chart)
• Top 10 products by revenue (horizontal bar)
• Revenue by region (filled map)

2. Sales Pipeline (CRM)

• Pipeline by stage (funnel chart)
• Weighted pipeline value (KPI)
• Win/loss rate (donut chart)
• Average deal size trend
• Sales rep performance (matrix table)
• Forecast vs actual (line + bar combo)

3. Financial Overview

• P&L summary (table with YTD, YoY)
• Cash position (KPI)
• Receivables aging (stacked bar)
• Payables aging (stacked bar)
• DSO trend (line chart)

4. Inventory Dashboard

• Stock value by category (treemap)
• Inventory turnover by warehouse (bar)
• Slow-moving inventory (table: stock > 90 days)
• Stockout risk items (table: days coverage < 7)
• Reorder point alerts (cards)

5. HR Dashboard

• Headcount by department (bar)
• Attendance vs. scheduled hours (gauge)
• Leave balance utilization (matrix)
• Turnover rate trend (line)

---

Incremental Refresh for Large Tables

Odoo's account_move_line, stock_move, and mail_message tables grow to millions of rows. Incremental refresh prevents full-table reloads on every refresh.

Configure incremental refresh:

1. In Power Query, add parameters RangeStart and RangeEnd (DateTime type)
2. Filter your date column: Table.SelectRows(Source, each [write_date] >= RangeStart and [write_date] < RangeEnd)
3. Right-click the table in the Fields pane → Incremental Refresh
4. Set: store last 12 months, refresh last 3 days

Odoo tables that benefit most from incremental refresh:

• account_move_line: filter by date
• stock_move: filter by date
• sale_order: filter by date_order
• mail_message: filter by date

---

Row-Level Security

Implement Row-Level Security (RLS) in Power BI to mirror Odoo's company-level access control.

// RLS filter: user sees only their assigned companies
[company_id] IN
    CALCULATETABLE(
        VALUES(UserCompanyMapping[company_id]),
        UserCompanyMapping[user_email] = USERPRINCIPALNAME()
    )

Create a UserCompanyMapping table (maintained in Power BI or synced from Odoo) mapping email addresses to authorized company IDs.

---

Frequently Asked Questions

Can I use DirectQuery with Odoo's PostgreSQL database for real-time data?

Yes, but with caveats. DirectQuery on Odoo's PostgreSQL is feasible for dashboards with simple queries. Complex dashboards with many measures will be slow because each visual triggers new SQL queries against your production database. For most use cases, Import mode with 1-hour refresh is the better trade-off between freshness and performance.

How do I handle Odoo's multi-currency data in Power BI?

Odoo stores amounts in both the transaction currency and the company currency. Use the amount_currency field for the original currency and debit/credit (or price_subtotal) for the company currency equivalent. For group-level consolidation in Power BI, use Odoo's company currency amounts and apply a separate currency conversion dimension table for consistent reporting.

What is the performance impact on Odoo's PostgreSQL database when Power BI refreshes?

A full Power BI dataset refresh runs multiple analytical queries simultaneously against PostgreSQL. For large Odoo databases (>50GB), this can consume significant I/O and CPU during the refresh window. Best practices: schedule refreshes during off-peak hours (e.g., 2:00-4:00 AM), use a read replica of PostgreSQL for Power BI queries, and implement incremental refresh to minimize query scope.

Can I connect Power BI to Odoo Community (free version) via PostgreSQL?

Yes. Power BI connects to any PostgreSQL database regardless of which application manages it. Odoo Community's PostgreSQL schema is nearly identical to Enterprise (minus some Enterprise-only tables). The connection method is the same; just ensure the read-only database user has access to the Community database.

How do I keep my Power BI data model in sync when Odoo is upgraded to a new version?

Odoo version upgrades can rename or restructure database tables, especially for modules that underwent significant refactoring. After any Odoo upgrade: run a comparison of table schemas between old and new versions, update Power Query queries to reference renamed columns, and validate all DAX measures against the new schema. Build a schema change check into your migration runbook.

---

Next Steps

Building a production-grade Odoo + Power BI integration requires data modeling expertise, PostgreSQL knowledge, and deep understanding of Odoo's schema. Done correctly, it delivers a unified analytics platform that transforms how your leadership team makes decisions.

ECOSIRE delivers end-to-end Odoo + Power BI analytics solutions — from database architecture and data modeling through dashboard design, DAX development, and deployment. Our team bridges Odoo expertise and Power BI specialization.

Talk to ECOSIRE About Your Odoo Analytics Integration →

Explore ECOSIRE's Power BI Services →

Share your reporting requirements and we'll design a Power BI architecture that gives your leadership team real-time visibility into every dimension of your Odoo operations.