The Complete Guide to Power BI + Odoo Integration

Odoo is one of the most powerful open-source ERP platforms in the world, with over 12 million users and 43 official modules covering everything from sales and inventory to manufacturing and human resources. Power BI is the industry-leading business intelligence platform with over 300 million monthly active users. Yet surprisingly few organizations connect these two systems --- leaving enormous analytical value on the table.

The reason is straightforward: Odoo has its own built-in reporting, and most Power BI consultancies focus on Microsoft Dynamics, SAP, or Salesforce integrations. Very few firms have deep expertise in both platforms. At ECOSIRE, we have built and deployed over 43 Odoo modules and maintain deep Power BI expertise, making the Odoo + Power BI combination one of our core specializations. This guide distills everything we have learned from dozens of real-world integrations.

---

Key Takeaways

• Odoo's PostgreSQL database can be connected directly to Power BI Desktop using the native PostgreSQL connector, giving you full access to every table and field
• The five most valuable Odoo tables for analytics are sale_order, account_move, stock_picking, hr_employee, and mrp_production --- together they cover 80 percent of executive reporting needs
• Incremental refresh in Power BI can reduce Odoo data load times from hours to minutes by only fetching records that changed since the last refresh
• OData endpoints and Odoo's External API provide cloud-friendly alternatives when direct database access is unavailable
• Row-level security in Power BI can mirror Odoo's multi-company access controls, ensuring users only see data from their assigned companies
• Custom SQL queries against Odoo's PostgreSQL database outperform generic table imports by 5-10x because you can filter, join, and aggregate at the database level
• A well-designed Odoo + Power BI deployment replaces dozens of spreadsheet reports with a single governed analytics platform

---

Why Odoo + Power BI Is a Powerful Combination

The Limitations of Odoo's Built-In Reporting

Odoo ships with several reporting tools: pivot views, graph views, and a built-in dashboard. For day-to-day operations, these are adequate. But they fall short for enterprise analytics in several critical ways.

First, Odoo's pivot views cannot combine data from multiple modules in a single visualization. You cannot overlay sales revenue with inventory turnover and manufacturing throughput in one chart. Each module's reporting is siloed.

Second, Odoo lacks time-intelligence functions. Year-over-year comparisons, rolling averages, cumulative totals, and period-to-date calculations require custom development or manual spreadsheet exports.

Third, Odoo has no concept of a governed data model. There are no shared definitions for metrics like "revenue" or "customer lifetime value." Every user creates their own interpretation, leading to conflicting numbers in management meetings.

Fourth, Odoo's visualization capabilities are limited to basic bar charts, line charts, and pie charts. Heat maps, scatter plots, waterfall charts, decomposition trees, and KPI cards are not available.

What Power BI Adds

Power BI addresses every one of these limitations. It connects to Odoo's PostgreSQL database (or API) and creates a unified semantic model across all modules. DAX formulas provide time intelligence, statistical functions, and complex business logic. The visualization library includes over 300 chart types. And Power BI's governance features --- workspaces, row-level security, endorsement, sensitivity labels --- provide enterprise-grade data management.

The combination gives you Odoo's operational excellence for daily work and Power BI's analytical depth for strategic decision-making. Operations teams continue working in Odoo; executives and analysts get Power BI dashboards that update automatically.

---

Connection Methods: Direct Database vs API

There are three primary methods to connect Power BI to Odoo. Each has trade-offs depending on your hosting model and security requirements.

Method 1: Direct PostgreSQL Connection

This is the preferred method for on-premises or self-hosted Odoo deployments. Odoo stores all data in PostgreSQL, and Power BI has a native PostgreSQL connector.

Advantages:

• Fastest query performance (no API overhead)
• Full access to every table and field, including custom modules
• Supports complex SQL queries with joins and aggregations at the database level
• Enables incremental refresh (requires a datetime column)
• No Odoo license or API rate limits

Setup steps:

1. Open Power BI Desktop and select Get Data, then PostgreSQL database
2. Enter your Odoo server hostname and database name (typically the Odoo instance name)
3. Use a read-only database user (never the Odoo admin account)
4. Select Import mode for most scenarios, or DirectQuery for real-time needs
5. Navigate the table list or use a custom SQL query

Connection string parameters:

Creating a read-only user in PostgreSQL:

CREATE ROLE powerbi_readonly WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE odoo_production TO powerbi_readonly;
GRANT USAGE ON SCHEMA public TO powerbi_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO powerbi_readonly;

This approach ensures Power BI can read all current and future tables without any write access to your production database.

Method 2: Odoo External API (XML-RPC / JSON-RPC)

Odoo exposes a full API for reading and writing data. Power BI can consume this through custom connectors or Python scripts.

Advantages:

• Works with Odoo.sh and Odoo Online (no direct database access needed)
• Respects Odoo's access control rules and record rules
• No need to expose the database port externally

Disadvantages:

• Significantly slower than direct database queries (10-100x for large datasets)
• API rate limits may throttle high-volume extracts
• Requires a custom Power Query function or intermediate ETL step
• Pagination adds complexity

For Odoo's JSON-RPC endpoint, a typical Power Query M function would call https://your-odoo.com/jsonrpc with authentication and then paginate through results. This works but becomes impractical for tables with more than 50,000 records.

Method 3: OData Endpoints via Odoo Connector Modules

Several Odoo community modules expose OData feeds that Power BI can consume natively. The OData connector in Power BI supports authentication and pagination out of the box.

When to use this method:

• Odoo Online / Odoo.sh deployments where database access is restricted
• Scenarios requiring Odoo's business logic (computed fields, access rules) in the data
• Smaller datasets (under 100,000 records per entity)

For most enterprise deployments, Method 1 (direct PostgreSQL) is strongly recommended. The performance difference is substantial, and the flexibility of SQL queries allows you to shape data at the source.

---

Essential Odoo Tables for Power BI

Odoo's PostgreSQL database contains hundreds of tables. Understanding the core tables and their relationships is critical for building effective Power BI models. Below are the tables that power 80 percent of executive dashboards.

Sales Module Tables

Key relationships: sale_order.partner_id links to res_partner.id. sale_order_line.product_id links to product_product.id. product_product.product_tmpl_id links to product_template.id.

A typical sales analytics query joins these tables to produce a denormalized fact table:

SELECT
  so.id AS order_id,
  so.name AS order_number,
  so.date_order,
  so.state,
  rp.name AS customer_name,
  rp.country_id,
  rc.name AS country_name,
  sol.product_id,
  pt.name AS product_name,
  pc.name AS product_category,
  sol.product_uom_qty AS quantity,
  sol.price_unit,
  sol.discount,
  sol.price_subtotal AS line_total,
  so.amount_total AS order_total,
  ru.login AS salesperson
FROM sale_order so
JOIN sale_order_line sol ON sol.order_id = so.id
JOIN res_partner rp ON so.partner_id = rp.id
LEFT JOIN res_country rc ON rp.country_id = rc.id
JOIN product_product pp ON sol.product_id = pp.id
JOIN product_template pt ON pp.product_tmpl_id = pt.id
LEFT JOIN product_category pc ON pt.categ_id = pc.id
LEFT JOIN res_users ru ON so.user_id = ru.id
WHERE so.state IN ('sale', 'done')
ORDER BY so.date_order DESC;

Accounting Module Tables

Critical distinction: In Odoo, account_move stores invoices (move_type = 'out_invoice'), vendor bills ('in_invoice'), credit notes ('out_refund', 'in_refund'), and journal entries ('entry'). Always filter by move_type in your Power BI queries.

The payment_state field on account_move tells you whether an invoice is 'not_paid', 'in_payment', 'paid', 'partial', or 'reversed'. This is essential for accounts receivable aging dashboards.

Inventory Module Tables

Real-time inventory: stock_quant always reflects the current state of inventory. For historical inventory analysis, you need to query stock_move with date filters and calculate running balances.

Manufacturing Module Tables

OEE calculation: Overall Equipment Effectiveness can be derived from mrp_workorder records by comparing planned duration against actual duration, analyzing downtime reasons, and tracking quality metrics.

Human Resources Tables

---

Building the Power BI Data Model

Star Schema Design

The most effective data model for Odoo analytics follows a star schema pattern. Fact tables (sales orders, invoices, stock moves, production orders) sit at the center. Dimension tables (products, customers, dates, employees, locations) surround them.

Recommended fact tables:

1. Fact_Sales — from sale_order + sale_order_line (grain: one row per order line)
2. Fact_Invoices — from account_move + account_move_line (grain: one row per journal line)
3. Fact_Inventory — from stock_move (grain: one row per stock movement)
4. Fact_Production — from mrp_production + mrp_workorder (grain: one row per work order)
5. Fact_Attendance — from hr_attendance (grain: one row per clock in/out pair)

Shared dimension tables:

1. Dim_Date — a calendar table generated in Power BI (essential for time intelligence)
2. Dim_Customer — from res_partner (filtered to customer_rank > 0)
3. Dim_Product — from product_product + product_template + product_category
4. Dim_Employee — from hr_employee + hr_department + hr_job
5. Dim_Location — from stock_location + stock_warehouse
6. Dim_Company — from res_company (for multi-company Odoo deployments)

Creating the Date Dimension

Odoo does not have a dedicated date dimension table. You must create one in Power BI using DAX:

Dim_Date =
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & QUARTER([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNumber", MONTH([Date]),
    "WeekNumber", WEEKNUM([Date]),
    "DayOfWeek", FORMAT([Date], "dddd"),
    "FiscalYear", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1),
    "FiscalQuarter", "FQ" & SWITCH(TRUE(),
        MONTH([Date]) >= 10, 3,
        MONTH([Date]) >= 7, 2,
        MONTH([Date]) >= 4, 1,
        4
    ),
    "IsWeekend", IF(WEEKDAY([Date], 2) > 5, TRUE(), FALSE()),
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

Mark this table as a date table in Power BI and create relationships from each fact table's date column to Dim_Date[Date]. Adjust the fiscal year start month to match your organization.

Handling Odoo's Multi-Company Structure

Odoo supports multi-company configurations where a single database serves multiple legal entities. Every transactional table includes a company_id foreign key. In Power BI, create a Dim_Company table from res_company and establish relationships to each fact table.

For row-level security, use Power BI's RLS feature to filter Dim_Company based on the logged-in user's company assignment. This mirrors Odoo's multi-company access controls in the BI layer.

---

Dashboard Recipes: Sales Analytics

Executive Sales Dashboard

This dashboard answers the five questions every CEO asks: How much revenue this month? Are we on track for the quarter? Which products are winning? Which salespeople are performing? Where are our customers?

Measures to create:

Total Revenue = SUM(Fact_Sales[line_total])

Revenue MTD =
TOTALMTD([Total Revenue], Dim_Date[Date])

Revenue QTD =
TOTALQTD([Total Revenue], Dim_Date[Date])

Revenue YTD =
TOTALYTD([Total Revenue], Dim_Date[Date])

Revenue PY =
CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dim_Date[Date]))

Revenue Growth % =
DIVIDE([Total Revenue] - [Revenue PY], [Revenue PY], 0)

Average Order Value =
DIVIDE([Total Revenue], DISTINCTCOUNT(Fact_Sales[order_id]))

Orders Count =
DISTINCTCOUNT(Fact_Sales[order_id])

Visuals layout:

• Row 1: Four KPI cards (Revenue MTD, Revenue QTD, Revenue YTD, Growth %)
• Row 2: Line chart (monthly revenue, current year vs previous year) and bar chart (revenue by product category)
• Row 3: Map visual (revenue by customer country) and table (top 10 salespeople with revenue, order count, average deal size)
• Row 4: Waterfall chart (revenue bridge: new customers vs existing vs lost) and donut chart (revenue by sales channel)

Sales Pipeline Analysis

If you use Odoo CRM alongside the Sales module, connect the crm_lead table to build pipeline dashboards:

Pipeline measures:

Pipeline Value =
SUMX(
    FILTER(Fact_Pipeline, Fact_Pipeline[active] = TRUE()),
    Fact_Pipeline[expected_revenue] * Fact_Pipeline[probability] / 100
)

Win Rate =
DIVIDE(
    CALCULATE(COUNTROWS(Fact_Pipeline), Fact_Pipeline[stage_name] = "Won"),
    CALCULATE(COUNTROWS(Fact_Pipeline),
        OR(Fact_Pipeline[stage_name] = "Won", Fact_Pipeline[stage_name] = "Lost")
    )
)

Average Sales Cycle Days =
AVERAGEX(
    FILTER(Fact_Pipeline, Fact_Pipeline[stage_name] = "Won"),
    DATEDIFF(Fact_Pipeline[create_date], Fact_Pipeline[date_closed], DAY)
)

---

Dashboard Recipes: Inventory and Supply Chain

Inventory Health Dashboard

This dashboard monitors stock levels, turnover rates, and supply chain performance.

Key measures:

Inventory Value =
SUMX(Fact_Inventory_Current, Fact_Inventory_Current[quantity] * RELATED(Dim_Product[standard_price]))

Inventory Turnover =
DIVIDE(
    [COGS Trailing 12 Months],
    [Average Inventory Value]
)

Days of Inventory =
DIVIDE(365, [Inventory Turnover])

Stockout Rate =
DIVIDE(
    CALCULATE(COUNTROWS(Dim_Product), Dim_Product[on_hand_qty] <= 0, Dim_Product[active] = TRUE()),
    CALCULATE(COUNTROWS(Dim_Product), Dim_Product[active] = TRUE())
)

Reorder Point Items =
CALCULATE(
    COUNTROWS(Dim_Product),
    FILTER(Dim_Product, Dim_Product[on_hand_qty] <= Dim_Product[reorder_min])
)

Visuals:

• KPI cards: Total inventory value, turnover ratio, stockout rate, items below reorder point
• Scatter plot: Each product plotted by turnover rate (x-axis) vs margin (y-axis), sized by revenue contribution --- this is the ABC-XYZ analysis visual
• Bar chart: Top 20 products by inventory value (identifies capital tied up in slow-moving stock)
• Table: Items below reorder point with current stock, daily demand, and estimated stockout date

Delivery Performance

From stock_picking, measure on-time delivery:

On-Time Delivery Rate =
DIVIDE(
    CALCULATE(
        COUNTROWS(Fact_Deliveries),
        Fact_Deliveries[date_done] <= Fact_Deliveries[scheduled_date]
    ),
    COUNTROWS(Fact_Deliveries)
)

Average Lead Time Days =
AVERAGEX(
    Fact_Deliveries,
    DATEDIFF(Fact_Deliveries[create_date], Fact_Deliveries[date_done], DAY)
)

---

Dashboard Recipes: Manufacturing

Production Performance Dashboard

For manufacturers running Odoo Manufacturing, the mrp_production and mrp_workorder tables provide rich operational data.

OEE (Overall Equipment Effectiveness) calculation:

Availability =
DIVIDE(
    [Actual Production Time],
    [Planned Production Time]
)

Performance Rate =
DIVIDE(
    [Ideal Cycle Time] * [Total Units Produced],
    [Actual Production Time]
)

Quality Rate =
DIVIDE(
    [Good Units],
    [Total Units Produced]
)

OEE = [Availability] * [Performance Rate] * [Quality Rate]

Visuals:

• Gauge charts: OEE, Availability, Performance, Quality (each with target thresholds: green above 85%, yellow 60-85%, red below 60%)
• Line chart: OEE trend by week, with control limits
• Clustered bar chart: OEE by work center, revealing which machines underperform
• Table: Production orders with planned vs actual duration, variance, and scrap quantity

Work Center Utilization

Utilization Rate =
DIVIDE(
    SUM(Fact_WorkOrders[duration_minutes]),
    [Available Minutes Per Period]
)

Downtime Hours =
DIVIDE(
    [Available Minutes Per Period] - SUM(Fact_WorkOrders[duration_minutes]),
    60
)

This dashboard helps production managers identify bottleneck work centers and optimize scheduling. When combined with Odoo's planning module data, you can build capacity planning models that forecast when you will hit maximum utilization.

---

Dashboard Recipes: HR and Workforce

Workforce Analytics Dashboard

HR dashboards built from Odoo data provide insights that most HRIS systems charge premium prices for.

Headcount and turnover measures:

Active Employees =
CALCULATE(
    COUNTROWS(Dim_Employee),
    Dim_Employee[active] = TRUE()
)

Attrition Rate =
DIVIDE(
    CALCULATE(
        COUNTROWS(Dim_Employee),
        Dim_Employee[departure_date] <> BLANK(),
        YEAR(Dim_Employee[departure_date]) = YEAR(TODAY())
    ),
    [Average Headcount],
    0
)

Average Tenure Years =
AVERAGEX(
    FILTER(Dim_Employee, Dim_Employee[active] = TRUE()),
    DATEDIFF(Dim_Employee[contract_start_date], TODAY(), DAY) / 365.25
)

Cost Per Employee =
DIVIDE(
    SUM(Fact_Payroll[total_cost]),
    [Active Employees]
)

Absence analysis from hr_leave:

Absence Rate =
DIVIDE(
    SUM(Fact_Leaves[number_of_days]),
    [Working Days In Period] * [Active Employees]
)

Bradford Factor =
SUMX(
    Dim_Employee,
    VAR AbsenceSpells = CALCULATE(COUNTROWS(Fact_Leaves), Fact_Leaves[state] = "validate")
    VAR TotalDays = CALCULATE(SUM(Fact_Leaves[number_of_days]), Fact_Leaves[state] = "validate")
    RETURN AbsenceSpells * AbsenceSpells * TotalDays
)

Attendance analysis from hr_attendance:

Average Daily Hours =
AVERAGEX(
    VALUES(Dim_Date[Date]),
    CALCULATE(SUM(Fact_Attendance[worked_hours]))
)

Overtime Hours =
SUMX(
    Fact_Attendance,
    IF(Fact_Attendance[worked_hours] > 8, Fact_Attendance[worked_hours] - 8, 0)
)

---

Incremental Refresh Configuration

For Odoo databases with millions of records, full data refreshes are impractical. Power BI's incremental refresh feature loads only new and changed records, reducing refresh times from hours to minutes.

Prerequisites

• Power BI Pro or Premium license
• Each table must have a reliable datetime column (write_date in Odoo is ideal --- it updates whenever a record is modified)
• The data source must support query folding (PostgreSQL does)

Configuration Steps

Step 1: Create RangeStart and RangeEnd parameters

In Power Query, create two parameters of type DateTime:

• RangeStart: default value = 1/1/2020 12:00:00 AM
• RangeEnd: default value = 12/31/2030 12:00:00 AM

Step 2: Filter tables by the parameters

For each fact table, add a filter step in Power Query:

= Table.SelectRows(Source, each [write_date] >= RangeStart and [write_date] < RangeEnd)

This filter must fold to the database (appear in the generated SQL). Verify by right-clicking the step and selecting "View Native Query."

Step 3: Define the incremental refresh policy

Right-click the table in the model, select Incremental Refresh, and configure:

This configuration stores three years of history but only refreshes the last seven days during each scheduled refresh. Odoo's write_date column automatically updates when any field on a record changes, making it a reliable change-detection column.

Performance Impact

The performance gain is dramatic, especially for manufacturing and inventory datasets that generate high volumes of transactional data.

---

Advanced: Multi-Company and Multi-Currency

Handling Multi-Company Odoo Deployments

Many Odoo Enterprise deployments serve multiple legal entities from a single database. Every transactional record has a company_id field. In Power BI:

1. Create a Dim_Company table from res_company
2. Establish relationships from each fact table's company_id to Dim_Company
3. Add a company slicer to every dashboard page
4. Implement row-level security so each user only sees their company's data

Currency Conversion

Odoo stores amounts in the company's base currency. For multi-currency reporting, join the res_currency_rate table:

SELECT
  so.id,
  so.amount_total AS amount_local,
  so.amount_total / COALESCE(
    (SELECT rate FROM res_currency_rate
     WHERE currency_id = so.currency_id
     AND name <= so.date_order::date
     ORDER BY name DESC LIMIT 1),
    1
  ) AS amount_usd
FROM sale_order so;

Alternatively, maintain a Dim_Currency_Rate table in Power BI with daily exchange rates and use DAX to convert at report time. This approach is more flexible for what-if scenarios (for example, "what would revenue look like at last year's exchange rates?").

---

OData and REST API Integration for Odoo Online

For organizations using Odoo Online or Odoo.sh where direct PostgreSQL access is not available, there are alternative connection methods.

Using Odoo's JSON-RPC API

Odoo exposes a JSON-RPC endpoint at /jsonrpc (or the older XML-RPC at /xmlrpc/2). You can call the search_read method to fetch data:

{
  "jsonrpc": "2.0",
  "method": "call",
  "params": {
    "service": "object",
    "method": "execute_kw",
    "args": [
      "your_database",
      2,
      "your_api_key",
      "sale.order",
      "search_read",
      [[["state", "in", ["sale", "done"]]]],
      {"fields": ["name", "partner_id", "date_order", "amount_total", "state"],
       "limit": 1000, "offset": 0}
    ]
  }
}

In Power BI, you would implement this as a custom Power Query function using Web.Contents with pagination logic. The challenge is performance: each API call returns at most a few thousand records, and you need multiple round-trips for large datasets.

Community OData Modules

Several Odoo community modules add OData endpoints:

• BI Connector for Odoo — exposes configurable OData feeds
• Odoo-Power BI Connector — pre-built data models for common modules

These modules simplify the integration but add a dependency to your Odoo instance. Evaluate whether the convenience outweighs the maintenance burden of a community module.

Hybrid Approach: Scheduled Data Export

A pragmatic middle ground is to schedule a nightly data export from Odoo to a staging database or Azure SQL. An Odoo scheduled action runs a Python script that exports key tables to CSV or pushes data via API to an Azure SQL database. Power BI then connects to the staging database with full query folding support.

This approach works well for organizations that want near-daily data freshness without exposing Odoo's production database to Power BI queries.

---

Real-World KPI Examples

Here are twenty KPIs that ECOSIRE clients frequently build after connecting Odoo to Power BI, organized by department.

Finance KPIs

1. Days Sales Outstanding (DSO) — Average days to collect payment, from account_move (invoice date vs payment date)
2. Gross Margin % — Revenue minus COGS divided by revenue, from sale_order_line (price_subtotal vs product standard_price)
3. Cash Conversion Cycle — DSO + Days Inventory Outstanding - Days Payable Outstanding
4. Budget vs Actual Variance — Requires a budget table (account_budget in Odoo or a manual upload)
5. Revenue per Employee — Total revenue divided by active headcount

Sales KPIs

6. Customer Acquisition Cost — Marketing spend divided by new customers acquired (requires manual marketing cost input)
7. Customer Lifetime Value — Average revenue per customer times average relationship length
8. Sales Cycle Length — Days from opportunity creation to won (crm_lead)
9. Quote-to-Order Conversion Rate — Confirmed orders divided by total quotations
10. Average Discount % — From sale_order_line discount field

Operations KPIs

11. Perfect Order Rate — Orders delivered on time, in full, with correct documentation
12. Inventory Accuracy — Actual count vs system count (from stock_quant adjustments)
13. Supplier Lead Time Reliability — Actual receipt date vs expected date from purchase orders
14. Warehouse Space Utilization — Occupied locations divided by total locations
15. Return Rate — Credit notes / refunds as a percentage of total sales

Manufacturing KPIs

16. First Pass Yield — Units passing quality inspection without rework divided by total units
17. Schedule Adherence — Production orders completed on the planned date
18. Material Waste % — Raw material consumed beyond BOM requirements
19. Work Center Utilization — Actual productive hours vs available hours
20. Mean Time Between Failures (MTBF) — Average operating time between equipment breakdowns

Each of these KPIs requires specific table joins and DAX logic. ECOSIRE's Power BI implementation service includes a standard KPI library with pre-built measures for all twenty.

---

Performance Optimization

Query Folding

Query folding is the single most important performance concept for Odoo + Power BI integrations. When Power Query "folds" a transformation, it translates the step into SQL and executes it on the PostgreSQL server rather than in the Power BI engine.

Steps that fold:

• Table.SelectRows (WHERE clause)
• Table.SelectColumns (SELECT specific columns)
• Table.Sort (ORDER BY)
• Table.Group (GROUP BY)
• Table.Join (JOIN)
• Table.FirstN (LIMIT)

Steps that break folding:

• Table.AddColumn with custom M functions
• Table.Buffer
• Table.Pivot / Table.Unpivot (in most cases)
• Any step referencing a non-foldable prior step

Best practice: Write custom SQL queries instead of relying on Power Query folding. This gives you full control over the SQL sent to PostgreSQL and eliminates folding uncertainty.

Import vs DirectQuery

For most Odoo deployments, Import mode with incremental refresh provides the best balance of performance and freshness. DirectQuery should be reserved for operational dashboards where 30-minute-old data is unacceptable (for example, a live manufacturing floor display).

Composite Models

Power BI Premium supports composite models that combine Import and DirectQuery tables. This is ideal for Odoo integrations where:

• Large historical tables (sales orders, journal entries) use Import mode with incremental refresh
• Small, fast-changing tables (stock_quant for live inventory) use DirectQuery
• The date dimension and other dimensions use Dual storage mode

---

Troubleshooting Common Issues

Connection Errors

"Unable to connect to server" — Verify PostgreSQL is listening on the correct port (default 5432) and that firewall rules allow inbound connections from the Power BI gateway or your desktop IP. Check postgresql.conf for listen_addresses and pg_hba.conf for client authentication rules.

"SSL connection is required" — Add sslmode=require to the connection. For self-signed certificates, you may need to import the CA certificate or set sslmode=allow (not recommended for production).

"Permission denied for table" — The Power BI database user lacks SELECT privileges. Run GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi_readonly; and verify with \dp table_name in psql.

Data Quality Issues

NULL values in critical fields — Odoo allows many fields to be blank. Use COALESCE in SQL queries or handle BLANK() in DAX to avoid calculation errors.

Duplicate records — Odoo's ORM sometimes creates multiple versions of records during editing. Filter by active = true and ensure you are using the correct state field to exclude drafts and cancelled records.

Timezone mismatches — Odoo stores timestamps in UTC. Power BI displays in the local timezone by default. Use AT TIME ZONE in PostgreSQL queries or DateTimeZone.SwitchZone in Power Query to normalize.

Performance Issues

Slow refresh times — Enable incremental refresh. Use custom SQL queries instead of importing entire tables. Filter out inactive records, draft documents, and historical data beyond your analysis window.

Report load times over 10 seconds — Check for complex DAX measures that iterate over large tables (SUMX, FILTER with many rows). Use variables to avoid repeated calculations. Consider pre-aggregating data in SQL views.

Gateway timeouts — Increase the command timeout in the gateway data source configuration. Default is 120 seconds; set to 600 for large Odoo databases.

---

Security Considerations

Database Security

Never connect Power BI to Odoo using the Odoo admin database user. Create a dedicated read-only user as shown earlier. Consider these additional measures:

• Row-level restrictions: Use PostgreSQL CREATE POLICY to limit the read-only user's access if you do not want Power BI to access all tables (for example, excluding hr_payslip)
• Column masking: Create views that exclude sensitive columns (salary, SSN, bank details) and grant Power BI access to views instead of base tables
• Connection encryption: Always use SSL for PostgreSQL connections, especially when the Power BI gateway and Odoo database are on different networks
• Audit logging: Enable PostgreSQL pgaudit to track all queries from the Power BI user

Power BI Security

• Implement row-level security (RLS) in Power BI that mirrors Odoo's multi-company access rules
• Use sensitivity labels for datasets containing financial or HR data
• Restrict workspace access to authorized analysts and consumers
• Disable data export on sensitive reports to prevent data exfiltration

For a deep dive on Power BI security, see our guide on row-level security implementation.

---

Putting It All Together: Implementation Roadmap

Phase 1: Foundation (Week 1-2)

1. Create the read-only PostgreSQL user on the Odoo database
2. Install and configure the on-premises data gateway (if using Power BI Service)
3. Connect Power BI Desktop to the Odoo database
4. Import the five core table groups (sales, accounting, inventory, manufacturing, HR)
5. Build the date dimension and establish relationships

Phase 2: Core Dashboards (Week 3-4)

1. Build the executive sales dashboard (revenue, growth, top products, pipeline)
2. Build the finance dashboard (AR aging, cash flow, budget variance)
3. Build the inventory dashboard (stock levels, turnover, reorder alerts)
4. Configure incremental refresh for all fact tables
5. Publish to Power BI Service and set up scheduled refresh

Phase 3: Advanced Analytics (Week 5-6)

1. Build manufacturing dashboards (OEE, utilization, production scheduling)
2. Build HR dashboards (headcount, attrition, attendance, absence)
3. Implement row-level security for multi-company data isolation
4. Create a mobile-optimized layout for key dashboards
5. Set up data alerts for critical KPIs (stockouts, overdue invoices, production delays)

Phase 4: Governance and Scale (Week 7-8)

1. Establish workspace naming conventions and content certification
2. Train power users on self-service report creation
3. Document the data model and calculation logic
4. Set up usage monitoring to track adoption
5. Plan for additional data sources (marketing platforms, eCommerce, IoT)

ECOSIRE's Power BI + Odoo integration service follows this roadmap and typically delivers the first executive dashboard within two weeks. Our team's dual expertise in Odoo's data model and Power BI's analytical engine ensures that you get accurate, performant, and governed analytics from day one.

---

FAQ

Can I connect Power BI to Odoo Online, or only self-hosted Odoo?

You can connect to both, but the method differs. Self-hosted Odoo gives you direct PostgreSQL access, which is faster and more flexible. Odoo Online and Odoo.sh do not expose the database directly, so you need to use Odoo's JSON-RPC API, a community OData connector module, or a scheduled data export to a staging database. For Odoo Online with large datasets, the staging database approach is recommended because API-based extraction is slow for tables with more than 50,000 records.

How often can Power BI refresh data from Odoo?

With Power BI Pro, you can schedule up to 8 refreshes per day (every 3 hours). With Power BI Premium, you can schedule up to 48 refreshes per day (every 30 minutes). For real-time data, use DirectQuery mode, but be aware that every report interaction will query your Odoo database directly. Incremental refresh reduces the time each refresh takes, making more frequent refreshes practical without overloading the database.

Will Power BI queries slow down our Odoo system?

If you use Import mode (recommended), Power BI queries only run during scheduled refreshes --- typically during off-peak hours. The impact on Odoo performance is minimal. If you use DirectQuery, every report interaction generates live queries against your Odoo database, which can impact performance during business hours. Mitigations include using a read replica, configuring query timeouts, and designing efficient SQL queries that use indexes.

Do I need to know SQL to set up the integration?

Basic SQL knowledge is helpful but not strictly required. Power BI's Power Query interface lets you select tables and apply filters visually. However, for optimal performance and data quality, custom SQL queries are strongly recommended. They allow you to pre-join tables, filter unnecessary records, and shape the data at the database level. If your team lacks SQL expertise, consider engaging a specialist for the initial setup and then maintaining the reports with Power BI's visual tools.

How does ECOSIRE's Odoo + Power BI service differ from generic Power BI consulting?

Most Power BI consulting firms have expertise in Power BI but limited knowledge of Odoo's data model. They spend weeks reverse-engineering table relationships, understanding Odoo-specific conventions (like the dual product_product / product_template structure), and figuring out which fields are meaningful. ECOSIRE has built and deployed over 43 Odoo modules and maintains deep expertise in both platforms. We provide pre-built data models, a standard KPI library with 50-plus measures, and Odoo-specific optimizations like incremental refresh on write_date columns. This dual expertise reduces implementation time by 40-60 percent compared to teams learning Odoo's data model from scratch.