Business Intelligence on Odoo with Metabase: from raw data to the executive dashboard

Why Odoo's native reporting is not Business Intelligence

Odoo has a reasonably good reporting module for day-to-day operational management: sales for the month, outstanding invoices, stock movements. But when management needs to answer strategic questions —what is my real margin by product family? which customer cohort has the best LTV? in which week of the quarter is 40% of the cashflow concentrated?— Odoo's reporting module falls short by design.

The problem is not Odoo. The problem is confusing an ERP with an analytical platform. Odoo is optimised to record and process transactions. That transactional database (OLTP) is not designed for complex analytical queries that cross dozens of tables, aggregate millions of rows and must respond in less than two seconds. If you run a heavy analytical query directly against Odoo's production PostgreSQL, you are competing for resources with the ERP's active users.

The solution is a two-layer data architecture: Odoo as the transactional source of truth and a read-only replica (or a lightweight data warehouse) as the analytical base, with Metabase as the visualisation and data governance layer. This article describes exactly how to set up that architecture.

General architecture: read-only replica + Metabase + dbt

The architecture we recommend for SMEs and mid-sized companies has three components:

• ▸Read-only PostgreSQL replica (streaming replication from Odoo's primary). Analytical queries go here, never to the primary.
• ▸dbt (data build tool) running on that replica or on a separate analytical schema. dbt transforms Odoo's schema —complex, with dozens of normalised tables— into clean, documented semantic models that analysts can use without knowing Odoo's internal data model.
• ▸Metabase connected to those dbt models. Metabase is open source, deploys in Docker in minutes, and its interface lets anyone in management build their own questions without SQL.

                  ┌──────────────────────────────────────┐
                  │   Odoo (producción)                 │
                  │   PostgreSQL primario (R/W)          │
                  └──────────────┬──────────────────────┘
                                 │ streaming replication
                  ┌──────────────▼──────────────────────┐
                  │   PostgreSQL réplica (solo lectura)  │
                  │   schema: public (tablas Odoo)       │
                  └──────────────┬──────────────────────┘
                                 │
                  ┌──────────────▼──────────────────────┐
                  │   dbt (transforma esquema Odoo)      │
                  │   schema: analytics                  │
                  │   modelos: dim_*, fct_*, mart_*      │
                  └──────────────┬──────────────────────┘
                                 │
                  ┌──────────────▼──────────────────────┐
                  │   Metabase (open source)             │
                  │   Dashboards, alertas, email reports │
                  └──────────────────────────────────────┘

Step 1: Configure the read-only PostgreSQL replica

If you already have Patroni or streaming replication configured (as described in the article on high availability), simply use the replica port (5433 via HAProxy) for Metabase's connections. If you are starting from scratch and want a simple replica just for analytics:

# En el servidor réplica — postgresql.conf
primary_conninfo = 'host=10.0.1.11 port=5432 user=replicator password=<PASSWORD> application_name=analytics_replica'
hot_standby = on
hot_standby_feedback = on   # evita que el primario limpie filas que la réplica todavía necesita
max_standby_streaming_delay = 30s

# Crear el fichero de señal de standby
touch /var/lib/postgresql/16/main/standby.signal

# En el primario — pg_hba.conf, añadir:
host replication replicator 10.0.1.20/32 scram-sha-256

Once the replica is active, create a read-only user for Metabase with access only to the analytical schema:

-- En la réplica, conectado como superusuario:
CREATE ROLE metabase_ro WITH LOGIN PASSWORD '<PASSWORD_SEGURO>';
GRANT CONNECT ON DATABASE odoo_prod TO metabase_ro;
GRANT USAGE ON SCHEMA analytics TO metabase_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO metabase_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO metabase_ro;
-- NO dar acceso al schema public (tablas brutas de Odoo) desde Metabase

Step 2: Install Metabase with Docker Compose

Metabase deploys in minutes. The minimal production configuration uses its own database (PostgreSQL preferably, not H2) to store dashboards, users and saved questions:

# docker-compose.metabase.yml
version: '3.8'
services:
  metabase:
    image: metabase/metabase:v0.50.0
    container_name: metabase
    restart: unless-stopped
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: postgres
      MB_DB_HOST: metabase-db
      MB_DB_PORT: 5432
      MB_DB_DBNAME: metabase
      MB_DB_USER: metabase
      MB_DB_PASS: <METABASE_DB_PASSWORD>
      MB_SITE_URL: https://bi.tuempresa.com
      MB_EMAIL_SMTP_HOST: smtp.tuempresa.com
      MB_EMAIL_SMTP_PORT: 587
      MB_EMAIL_SMTP_USERNAME: alertas@tuempresa.com
      MB_EMAIL_SMTP_PASSWORD: <SMTP_PASSWORD>
      MB_EMAIL_SMTP_SECURITY: starttls
      JAVA_TIMEZONE: Europe/Madrid
    depends_on:
      - metabase-db

  metabase-db:
    image: postgres:16-alpine
    container_name: metabase-db
    restart: unless-stopped
    environment:
      POSTGRES_DB: metabase
      POSTGRES_USER: metabase
      POSTGRES_PASSWORD: <METABASE_DB_PASSWORD>
    volumes:
      - metabase_db_data:/var/lib/postgresql/data

volumes:
  metabase_db_data:

Put Nginx in front of Metabase with SSL (Certbot) before exposing it to the Internet. Metabase on port 3000 should never be directly accessible from outside.

Step 3: Model the Odoo schema with dbt

Odoo's PostgreSQL schema is complex: more than 400 tables with cryptic names (account_move, stock_quant, sale_order_line). dbt lets you create clean views and materialised tables on top of that schema, with documentation, data quality tests and automatic lineage.

Recommended dbt project structure for Odoo

odoo_analytics/
├── models/
│   ├── staging/          # tablas brutas de Odoo, renombradas y tipadas
│   │   ├── stg_sale_orders.sql
│   │   ├── stg_invoices.sql
│   │   ├── stg_products.sql
│   │   └── stg_partners.sql
│   ├── intermediate/     # joins y cálculos intermedios
│   │   ├── int_order_lines_enriched.sql
│   │   └── int_invoice_lines_enriched.sql
│   └── marts/            # modelos finales para Metabase
│       ├── mart_sales.sql
│       ├── mart_margin.sql
│       ├── mart_cashflow.sql
│       └── mart_customer_cohorts.sql
├── tests/
├── macros/
└── dbt_project.yml

Staging model: sales orders

-- models/staging/stg_sale_orders.sql
-- {{ config(materialized='view') }}
SELECT
    so.id                          AS order_id,
    so.name                        AS order_ref,
    so.date_order::date            AS order_date,
    so.state                       AS order_state,
    rp.name                        AS customer_name,
    rp.id                          AS customer_id,
    rc.name                        AS customer_country,
    so.amount_untaxed               AS amount_net,
    so.amount_tax                   AS amount_tax,
    so.amount_total                 AS amount_total,
    so.currency_id,
    su.name                        AS salesperson,
    so.team_id,
    so.company_id
FROM public.sale_order so
JOIN public.res_partner rp ON rp.id = so.partner_id
LEFT JOIN public.res_country rc ON rc.id = rp.country_id
LEFT JOIN public.res_users su ON su.id = so.user_id
WHERE so.state IN ('sale', 'done')

Mart model: margin by product

-- models/marts/mart_margin.sql
-- {{ config(materialized='table') }}
WITH order_lines AS (
    SELECT
        sol.order_id,
        so.order_date,
        so.customer_id,
        so.customer_name,
        sol.product_id,
        pt.name                  AS product_name,
        pc.name                  AS product_category,
        sol.product_uom_qty      AS qty_sold,
        sol.price_unit           AS sale_price,
        sol.price_subtotal       AS revenue,
        COALESCE(pp.standard_price, 0) AS cost_price,
        (sol.price_subtotal - sol.product_uom_qty * COALESCE(pp.standard_price, 0)) AS gross_margin
    FROM public.sale_order_line sol
    JOIN public.sale_order so ON so.id = sol.order_id
    JOIN public.product_product pp ON pp.id = sol.product_id
    JOIN public.product_template pt ON pt.id = pp.product_tmpl_id
    LEFT JOIN public.product_category pc ON pc.id = pt.categ_id
    WHERE so.state IN ('sale', 'done')
        AND sol.product_id IS NOT NULL
)
SELECT
    order_date,
    DATE_TRUNC('month', order_date)  AS month,
    DATE_TRUNC('quarter', order_date) AS quarter,
    product_id,
    product_name,
    product_category,
    customer_id,
    customer_name,
    SUM(qty_sold)                    AS total_qty,
    SUM(revenue)                     AS total_revenue,
    SUM(cost_price * qty_sold)       AS total_cost,
    SUM(gross_margin)                AS total_margin,
    CASE WHEN SUM(revenue) > 0
         THEN ROUND(SUM(gross_margin) / SUM(revenue) * 100, 2)
         ELSE 0
    END                              AS margin_pct
FROM order_lines
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8

Run dbt with a daily cron (or hourly for fresher data):

# crontab -e
0 6 * * * cd /opt/odoo_analytics && dbt run --profiles-dir . --target prod >> /var/log/dbt/run.log 2>&1
30 6 * * * cd /opt/odoo_analytics && dbt test --profiles-dir . --target prod >> /var/log/dbt/test.log 2>&1

Step 4: Executive KPIs that really matter

Once the dbt models are available in Metabase, the executive dashboards that add the most value in the context of an SME running Odoo are:

Dashboard 1: Sales and pipeline

• ▸Sales for the current month vs. the same month last year (% change)
• ▸Year-to-date sales vs. target
• ▸Top 10 customers by revenue in the last 90 days
• ▸Pipeline of open quotations (state draft/sent in sale_order) with weighted probability
• ▸Conversion rate quotation → confirmed order per salesperson

Dashboard 2: Margin and profitability

• ▸Gross margin by product family (%) — monthly heat map
• ▸Evolution of the average margin over the last 12 months
• ▸Products with negative margin or below the threshold (automatic alert)
• ▸Contribution of each category to the total margin (stacked bar)

Dashboard 3: Operating cashflow

-- Cashflow semanal: cobros esperados vs. pagos previstos
SELECT
    DATE_TRUNC('week', am.invoice_date_due) AS week,
    am.move_type,
    SUM(am.amount_residual_signed)          AS pending_amount
FROM public.account_move am
WHERE am.state = 'posted'
  AND am.payment_state IN ('not_paid', 'partial')
  AND am.invoice_date_due BETWEEN CURRENT_DATE AND CURRENT_DATE + 90
GROUP BY 1, 2
ORDER BY 1, 2

Dashboard 4: Customer cohorts

Cohort analysis is perhaps the most underused KPI in SMEs running Odoo. It lets you answer: are the customers we acquired in Q1 2025 still buying after 6 months at the same rate as those from Q1 2024?

-- Cohorte de retención mensual
WITH first_order AS (
    SELECT
        partner_id,
        MIN(DATE_TRUNC('month', date_order)) AS cohort_month
    FROM public.sale_order
    WHERE state IN ('sale', 'done')
    GROUP BY 1
),
orders AS (
    SELECT
        so.partner_id,
        DATE_TRUNC('month', so.date_order) AS order_month
    FROM public.sale_order so
    WHERE so.state IN ('sale', 'done')
    GROUP BY 1, 2
)
SELECT
    fo.cohort_month,
    o.order_month,
    EXTRACT(MONTH FROM AGE(o.order_month, fo.cohort_month)) AS months_since_first,
    COUNT(DISTINCT o.partner_id) AS active_customers
FROM first_order fo
JOIN orders o ON o.partner_id = fo.partner_id
    AND o.order_month >= fo.cohort_month
GROUP BY 1, 2, 3
ORDER BY 1, 3

Step 5: Automatic email reports and alerts

Metabase includes a system of pulses and scheduled reports that lets you send dashboards by email automatically. The recommended configuration for management:

• ▸Monday 08:00: weekly summary of sales, margin and outstanding receivables for the week
• ▸Day 1 of each month 07:00: closing of the previous month with a comparison vs. the previous month and the same month last year
• ▸Real-time alert: if the margin of any order line falls below the defined threshold (configurable per product family)
• ▸Cashflow alert: if the balance of expected receipts in the next 30 days falls below X euros

Metabase alerts are configured from the interface (Alerts section in any saved question) and are sent by email or webhook. For alerts in Telegram or Slack, a Metabase webhook pointing to a Telegram bot is the most direct solution.

Data governance: the element most projects ignore

A BI platform without data governance turns into a chaos of contradictory dashboards in three months. The questions that kill trust in the data are: why does this dashboard say we sold 450k and the sales one says 430k? The answer is always: inconsistent definitions. To avoid it:

• ▸Define the metrics in dbt, not in Metabase: the definition of “confirmed sale” (which sale_order states, whether returns are included, whether it is counted on order date or invoice date) must be in the dbt model, not in each dashboard separately.
• ▸dbt tests for data quality: verify that there are no null order_id, that amounts are always positive, that each invoice points to an existing customer. If the test fails, the model is not materialised and Metabase shows the previous day's data.
• ▸Documentation in dbt: each model and each column has a description in schema.yml. Metabase can import that documentation and show it to the user when hovering over a column.
• ▸Access control in Metabase: management sees all dashboards; salespeople see only their own metrics; logistics does not see margins. Metabase groups and permissions map exactly onto the business roles.

Costs and alternatives

For most Spanish SMEs running Odoo, the stack PG replica + dbt Core + Metabase OSS is enough and the additional infrastructure cost is 30-80€/month (a small VPS for Metabase and dbt). The real investment is the initial modelling time: usually between 15 and 40 hours of technical consulting depending on the complexity of the business.

Frequent mistakes in BI projects on Odoo

• ▸Connecting Metabase directly to the production PostgreSQL: analytical queries compete with Odoo for CPU and connections. Always use a replica or exported data.
• ▸Building dashboards before modelling the data: the result is dashboards that nobody understands or maintains. First the dbt model, then the dashboard.
• ▸Ignoring cost data (standard_price): without costs, margin dashboards are an illusion. Verify that the purchasing team keeps costs updated in Odoo.
• ▸Not scheduling the automatic reports: if the dashboard does not reach management's email, management does not use it. Automation is what turns the project into a habit.

Do you want to set up real Business Intelligence on your Odoo?