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: monthly sales, outstanding invoices, stock movements. But when management needs to answer strategic questions — what is my actual margin by product family? which customer cohort has the best LTV? in which week of the quarter is 40% of cashflow concentrated? — Odoo's reporting module falls short by design.

The problem is not Odoo. The problem is confusing an ERP with an analytics platform. Odoo is optimised for recording and processing 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 under 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 build 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 the Odoo 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 allows anyone in management to 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 PostgreSQL read-only replica

If you already have Patroni or streaming replication configured (as described in the high-availability article), simply use the replica port (5433 via HAProxy) for Metabase connections. If starting from scratch and you 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 minimum 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 must never be directly accessible from outside.

Step 3: Model the Odoo schema with dbt

Odoo's PostgreSQL schema is complex: over 400 tables with cryptic names (account_move, stock_quant, sale_order_line). dbt allows clean views and materialised tables to be created 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 deliver the most value in the context of an SME with Odoo are:

Dashboard 1: Sales and pipeline

• Current month sales vs. same month last year (% change)
• Year-to-date sales vs. target
• Top 10 customers by revenue in the last 90 days
• Open quote pipeline (status draft/sent in sale_order) with weighted probability
• Quote → confirmed order conversion rate by salesperson

Dashboard 2: Margin and profitability

• Gross margin by product family (%) — monthly heat map
• Trend of average margin over the last 12 months
• Products with negative or below-threshold margin (automatic alert)
• Contribution of each category to 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 underutilised KPI in SMEs using Odoo. It answers: are the customers we acquired in Q1 2025 still buying at 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: Automated email reports and alerts

Metabase includes a pulses and scheduled reports system that allows dashboards to be sent by email automatically. The recommended configuration for management:

• Monday 08:00: weekly summary of sales, margin and outstanding receipts for the week
• 1st of each month 07:00: previous month close with comparison vs. prior month and same month last year
• Real-time alert: if the margin on 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 on any saved question) and sent by email or webhook. For Telegram or Slack alerts, 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 within three months. The questions that destroy trust in data are: why does this dashboard say we sold 450k and the commercial one says 430k? The answer is always: inconsistent definitions. To avoid it:

• Define metrics in dbt, not in Metabase: the definition of 'confirmed sale' (which sale_order states, whether returns are included, whether counted by 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 values, 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: every model and every column has a description in schema.yml. Metabase can import that documentation and display it to the user on column hover.
• 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 business roles.

Costs and alternatives

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

Common 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 up to date in Odoo.
• Not scheduling automatic reports: if the dashboard does not land in management's inbox, management does not use it. Automation is what turns the project into a habit.

Do you want to build real Business Intelligence on your Odoo?

Request a free technical audit