Connect Power BI to Odoo: Data Visualization & Reporting

Why Power BI + Odoo?

Odoo's built-in reporting covers basic needs, but Power BI provides: interactive dashboards, advanced visualizations, cross-source data blending, scheduled refresh, sharing with stakeholders, and mobile access. Power BI is the world's most popular BI tool with 300M+ users.

Connection Methods

Method 1: Direct PostgreSQL (Recommended)

Prerequisites

• Power BI Desktop (free download)
• PostgreSQL ODBC driver or Power BI PostgreSQL connector
• Odoo PostgreSQL credentials (read-only user recommended)

Step 1: Create Read-Only Database User

# Create a read-only user for Power BI (security best practice)
sudo -u postgres psql

CREATE USER powerbi WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE odoo TO powerbi;
GRANT USAGE ON SCHEMA public TO powerbi;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO powerbi;

Step 2: Allow Remote Connection

# postgresql.conf:
listen_addresses = '*'  # Or specific IP

# pg_hba.conf — add line for Power BI server:
host odoo powerbi POWER_BI_IP/32 md5

# Restart PostgreSQL
systemctl restart postgresql

# Open firewall
ufw allow from POWER_BI_IP to any port 5432

Step 3: Connect from Power BI

1. Power BI Desktop → Get Data → PostgreSQL database
2. Server: your-odoo-server:5432
3. Database: odoo
4. Data Connectivity mode: DirectQuery (real-time) or Import (cached)
5. Username: powerbi / Password: secure_password
6. Select tables to load

Key Odoo Tables for Reporting

Sample DAX Measures

// Total Revenue
Total Revenue = SUM(sale_order_line[price_subtotal])

// Revenue Growth %
Revenue Growth = 
    DIVIDE(
        [Total Revenue] - CALCULATE([Total Revenue], DATEADD('sale_order'[date_order], -1, YEAR)),
        CALCULATE([Total Revenue], DATEADD('sale_order'[date_order], -1, YEAR))
    )

// Average Order Value
Avg Order Value = DIVIDE([Total Revenue], DISTINCTCOUNT(sale_order[id]))

// Overdue Invoices
Overdue Amount = 
    CALCULATE(
        SUM(account_move[amount_residual]),
        account_move[move_type] = "out_invoice",
        account_move[invoice_date_due] < TODAY(),
        account_move[payment_state] <> "paid"
    )

Dashboard Ideas

• Sales Dashboard: Revenue trend, top products, top customers, sales by region, pipeline conversion
• Finance Dashboard: Cash flow, AR aging, AP aging, P&L summary, budget vs actual
• Inventory Dashboard: Stock levels, turnover, dead stock, reorder alerts
• CRM Dashboard: Lead sources, conversion funnel, win/loss analysis, sales forecast
• Executive Dashboard: KPIs, revenue vs target, headcount, customer satisfaction

DirectQuery vs Import

Recommendation: Start with Import mode with daily scheduled refresh. Use DirectQuery only for real-time dashboards with simple queries.

Security Considerations

• Use a read-only PostgreSQL user (never the Odoo admin user)
• Restrict IP access to PostgreSQL (firewall + pg_hba.conf)
• Consider VPN or SSH tunnel for remote connections
• Do not expose PostgreSQL directly to the internet

DeployMonkey + Power BI

DeployMonkey-hosted Odoo instances include PostgreSQL access for Power BI connections. The AI agent helps identify the right tables, suggests DAX measures, and troubleshoots connection issues.