Odoo data marts
A data warehouse and data marts for Odoo, built with Putki: Odoo's sales, invoices, CRM pipeline, projects and timesheets, accounting, purchases and stock, in a dimensional model that keeps history, with dashboards on top and checks on every load. You can try them at odoo.putki.io, on demo data, by signing in with a Google account.
The data marts are not a Putki download yet. This page describes what the demo shows.
Parts
| Part | What it does | Where it runs |
|---|---|---|
| Load | Apache Hop workflows that read Odoo through its JSON-2 API and load staging, the warehouse and the marts | A Putki container, on a schedule or on demand |
| Warehouse | Conformed dimensions that keep history (customers, products, users, employees, projects, dates) and facts per business process | PostgreSQL |
| Data marts | One schema per subject, published as views only | PostgreSQL |
| Dashboards | One Grafana dashboard per data mart, plus data quality | Grafana |
How it works
- Extract. Each load reads only what changed in Odoo since the previous one (
write_date), and finds records deleted in Odoo by comparing ids. A full load reads everything again. - Stage. The records land in a staging database as Odoo has them.
- Warehouse. Dimensions keep a new version when an attribute that matters changes (a customer's country, a product's category or price). Facts point at the version that was valid on their own date, so last year's sales stay with last year's customer details.
- Marts. The data marts are views over the warehouse, in one schema per subject:
sales(with invoices and the CRM pipeline),projects,accounting,purchase,inventoryanddata_quality, and the shared dimensions inconformed. - Check. Every load checks the marts against staging, and the monthly totals against Odoo's own reports (Sales Analysis, Invoice Analysis, Purchase Analysis, timesheet hours, opportunities). A load whose figures do not match fails, and the dashboards keep the last good data.
What you see
| Dashboard | Shows |
|---|---|
| Sales | Quotations and orders, revenue, margin, top customers and products, conversion |
| Invoicing | Invoiced revenue, credit notes, open and overdue receivables, aging |
| Pipeline | Open and weighted pipeline, win rate, lost reasons |
| Projects | Hours logged, open and overdue tasks, milestones, the daily backlog |
| Project profitability | What is sold, invoiced and spent per project |
| Accounting | Profit and loss, balances, cash, payables and their aging |
| Purchase | Purchases, receipts on time, orders still to receive or bill |
| Inventory | Stock on hand and its value, moves, transfers on time |
| Data quality | Problems in Odoo's own data, with a worklist of the records |
Every dashboard has a Company filter. Amounts are untaxed and in the reporting currency unless a panel says otherwise.
Design choices worth knowing
- Views are the interface. Consumers read the views, never the tables behind them, so the tables can change without breaking a dashboard or a report. The data dictionary lists every view and column.
- Several companies and currencies. Every fact belongs to an Odoo company. Amounts come in the
company's own currency (
*_company_ccy), which adds up within one company, and in one reporting currency (*_reporting_ccy), converted the way Odoo converts, which adds up across companies. - An as-of date. Overdue, expired, aging and late are measured against one date
(
conformed.v_as_of). The demo is a snapshot with that date fixed, so its figures do not move. - Quantities in the product's unit. Sales, purchase and invoice lines count in the product's unit of measure, as Odoo's reports do: 2 packs of 6 are 12 units.
The demo data
Odoo 19's demo database, with 18 months of history added through Odoo's own business logic: salespeople and customers in several countries, quotations, orders, invoices and payments, opportunities won and lost, timesheets, purchases and receipts, stock moves, expenses and a second company in euros. It is not a real company. The landing page at odoo.putki.io gives the snapshot's date.
What it does not do
- No live Odoo. The demo is a snapshot; it does not load from Odoo.
- No writes to Odoo. The load only reads, with a read-only Odoo user.
- Links to Odoo records on the Data quality dashboard point at the Odoo the snapshot was loaded from, which is not reachable from the demo.
Where to go next
- Data dictionary: every view and column of the data marts.
- odoo.putki.io: the dashboards, on demo data.