HR analytics build · Odoo HR data on Power BI
24 people. 64 open roles. Somebody should have noticed.
Odoo HR held employees, contracts, job positions, timesheets, skills and leave, all recorded faithfully and never once read as management information. KoderXpert modelled the five domains into one workforce model and delivered four linked Power BI pages that found the gap, the burnout and the liability.
Data modelling, DAX and report build by KoderXpert Technologies Pvt. Ltd.
Odoo Ready Partner & BI development · Ahmedabad & Gandhinagar, India
- Build
- Reference implementation
- Domain
- HR & workforce analytics
- Source
- Odoo HR, PostgreSQL
- Platform
- Microsoft Power BI
- Headline result
- Four pages, zero ERP changes
01 · Overview
The 30-second version
- A growing company ran HR on Odoo: employees, contracts, job positions, skills, timesheets and leave recorded faithfully across ten departments and fourteen job roles.
- Odoo could say how many people there were. It could not say that the workload was landing on one person, that a role had been open for a year, or that untaken leave was a liability nobody had costed.
- KoderXpert, through our data analytics and Power BI services, modelled all five HR domains into one employee centric Power BI model and built four linked report pages: HR executive, workforce, skills and leave. Read only, so nothing inside Odoo changed.
02 · The challenge
HR could count the workforce. It could not diagnose it.
Every problem below came from the same root cause. The records were right, and no view existed that turned them into a question anyone could act on.
Hiring gaps were felt, not measured
Open positions existed as records. Nowhere did anyone see, role by role, how many people a job needs against how many it actually has.
The cost → recruitment priorities argued, not rankedWorkload was assumed to be shared
Timesheet hours sat in the system. Nobody had ever charted them per employee, so nobody knew the distribution was lopsided.
The cost → a resignation nobody saw comingSkills lived in a form nobody read
Every employee had a skills record. There was no view of what the organisation collectively can do, and no view of what it cannot.
The cost → hiring for capability already on the payrollLeave was admin, not a liability
Allocations and absences were processed correctly. The untaken balance on the books was never treated as a number that matters.
The cost → an accrued obligation off the finance radarFive domains, five grains
Employees, contracts, timesheets, skills and leave each sit at a different level. One employee has many contracts, many skills and many leave lines.
The cost → every naive join double counts somebodyEmployee IDs are not people
Employees arrive as foreign keys. Left unjoined, a workload chart reports on numbers no manager recognises and no manager acts on.
The cost → a report nobody could argue with or usethe system knew who worked here, not who was drowning
03 · Before vs after
Drag the handle. Same database, two conversations.
Left is what HR reporting looked like on Odoo alone. Right is what four modelled pages replaced it with.
- ✗Headcount reported monthly, capacity discussed by feel
- ✗Recruitment priorities argued in meetings, never ranked
- ✗Workload reviewed in aggregate, so overload averaged away
- ✗Skills stored per employee, never summed into capability
- ✗Leave balances treated as HR housekeeping
- ✗Every question answered with a fresh spreadsheet export
- ✓Expected against current headcount for all 14 roles, one chart
- ✓A ranked hiring plan that falls out of the gap, not a debate
- ✓Hours per person, with the 854 hour outlier impossible to miss
- ✓86 skills mapped to people, searchable in both directions
- ✓251.5 pending leave days stated as an accrued obligation
- ✓Four linked pages that refresh themselves, no exports
drag the orange handle, or use the arrow keys ✨
04 · Goals
What the build had to achieve
Six goals agreed before a line of DAX was written, each phrased so it could be checked against a number later. Hover any card to tick it off.
Quantify the hiring gap. Expected against current headcount, role by role, so recruitment becomes a ranked list instead of a monthly argument.
Expose workload imbalance. Hours per employee, not hours in aggregate, because the average is what hides the person about to break.
Map the skills the company holds. And, by omission, the ones it does not hold at all, before the next job advert is written.
Turn leave into a liability figure. Allocated, taken, and the pending balance sitting between them, in a number finance recognises.
One executive view. Headcount, cost, retention and openings readable on a single screen, before anyone opens a detailed page.
Deployable to any Odoo HR instance. A template that travels with the standard schema, not a one off report bound to one database.
05 · What we delivered
Six blocks that turn HR records into decisions
Each block was built to answer one named failure from section 02, then measured on its own terms.
HR executive KPI panel
Headcount, active employees, new joiners, open positions, retention rate, departments, jobs, contracts, salary cost and average worked hours. The whole workforce framed before a single chart is read.
the board view, one screenComposition at a glance
Headcount by department, by job role, by category and by gender, plus the growth trend that shows when the organisation actually scaled rather than when it felt busy.
growth has a date nowWorkforce gap analysis
The centrepiece. Expected headcount plotted against current headcount for every role. Where the two bars diverge, the organisation is running a role it has not staffed.
no commentary requiredWorkload distribution by employee
Hours charted per person rather than summed. The moment it is drawn, an imbalance everyone half suspected becomes a fact HR has to answer for.
854 hours has a name attachedSkills inventory and register
Total skills, skill types and high skilled employee counts, with every employee mapped to their skills, proficiency and department, filterable by skill or by person.
capability, finally countedLeave allocation vs utilisation
Allocated leave plotted against leave actually taken, per employee, split by purpose, with utilisation rates. The gap between the bars is the liability.
a finance number in an HR ledgerThe seat board
watch 24 people fill 88 seats, then pick a role
Every seat is a position the business expects to be staffed. The people take their seats on their own the first time you scroll here, and what stays empty is the hiring gap. Pick any role to see its own board. Numbers are the real ones from the build: 88 expected, 24 current, 64 short across fourteen job roles.
64 seats open across the organisation
Handyman is the worst served role: 11 wanted, 1 held06 · Functional deep dive
How is each number actually calculated?
The part that stays useful after the visuals are restyled. Five measures, five decisions about what a number is allowed to mean.
The gap, computed from two tables that never meet
Role demand lives in job positions. Actual headcount lives in employees. Neither table stores the difference, so the gap only exists once you build it.
- Expected headcount read per job position, at role grain
- Current headcount as a distinct count of active employees on that job
- Subtraction done per role, never at company level, so the output is a ranked list
- Experienced Developer wants 20 and holds 11, Handyman wants 11 and holds 1
Workload, charted per person instead of per team
Timesheet lines are joined to the employee dimension before they are aggregated, so the axis carries names rather than foreign keys, and hours are plotted per individual rather than summed.
- Busiest employee carries 854 hours, lightest carries 104
- The average, near 322 hours, looks entirely healthy
- That average is exactly how burnout stays invisible in aggregate reporting
Skills modelled as many to many, not flattened
One employee holds many skills and one skill spans many employees, which Odoo stores as a many2many relation table. Flatten that into one table and every headcount double inflates, quietly, in every visual on the page.
- Bridge table between the employee and skill dimensions
- Distinct counts so a skill filter never multiplies people
- 86 skills across four skill types, filterable by skill or by person
Leave, converted from a balance into an obligation
Leave carries two opposing measures. Allocated is what the business promised, taken is what employees used, and the interesting number is neither.
- 291 days allocated against 39.5 taken leaves 251.5 pending
- Both sides broken out by purpose so policy can be examined, not assumed
- Utilisation rate turns a leave register into a wellbeing signal
Four pages that lead one question into the next
The executive page raises the question, the workforce page locates it in a role, the skills page says whether the capability already exists internally, and the leave page prices the absence.
- Button navigation with the filter context carried across pages
- Cross filtering so a department tap narrows every downstream visual
- Bookmarks for the three views leadership opens most
The average was the disguise
flat bars are a lie, watch them tell the truth
Twenty four employees, 7,726 timesheet hours between them. The aggregate view gives everyone the average and the team looks healthy. Press Per person, or wait for it to play itself, and the same hours redistribute into the shape HR was never shown. Click any bar to read that employee.
Timesheet lines joined to the employee dimension, then plotted per person
Aggregate reporting is how burnout stays invisibleThe bridge, and what happens without it
flatten the table and people start multiplying
Six employees and six skills from the register, wired the way the model wires them. Select a skill to see who holds it, or a person to see what they cover. Then press Flatten the table and watch the same six people become thirteen rows, which is the headcount every naive skills report quietly prints.
Bridged, so a skill filter narrows the people without multiplying them
Select a skill or a person to filter the other side07 · How we built it
Six weeks from schema to a template
Definitions first, code second. The order is the reason the numbers survived reconciliation.
HR schema mapping week one
Traced Odoo's employee, contract, job position, skill and leave tables and the relationships between them, marking every point where the grain changes and a join would start double counting.
Metric definition three days
Agreed what retention, a gap and a pending leave day actually mean, in writing, before a line of DAX existed. Most reporting disputes are definition disputes wearing a chart.
Data modelling week two
Built an employee centric star schema with skills bridged as a proper many to many, key columns hidden, and the date table marked so time intelligence behaves itself.
DAX development about ten days
Gap, workload, leave liability and utilisation measures written once, guarded against divide by zero, and unit checked against hand calculations on a sample of roles.
Four page design and navigation one week
Executive, workforce, skills and leave, each led by its own KPI panel and wired together with buttons so one question leads into the next instead of ending in an export.
Validation and templating final week
Headcount, salary cost and leave balances reconciled back to Odoo's own list views, then the whole thing generalised so it deploys against any Odoo HR instance.
08 · Engineering notes
How do you keep an Odoo BI model fast, correct and safe?
Written for the person who will inherit this model. Everything here is a decision that costs nothing on day one and saves a rebuild in month six.
A read only path out of Odoo
Power BI connects through a dedicated PostgreSQL role with SELECT rights only, scoped to the HR schema. Reporting logic sits in versioned views prefixed kx_bi_ rather than inside the report file.
- No module installed, no field added, nothing writes back
- Column renames handled in one place instead of forty visuals
- Gateway used where the instance is self hosted
A star schema, not a flat extract
Employee is the central dimension. Job position, department, skill, skill type, leave type and date hang off it, with timesheet and leave lines as facts.
- Key columns hidden from the field list so nobody sums an ID
- Single direction relationships except the deliberate bidirectional skills bridge
- Date table marked as a date table, so time intelligence is trustworthy
A measure library with rules
Base measures do the arithmetic, display measures do the formatting, and nothing calculates inside a visual where the next person cannot find it.
DIVIDEwith a blank fallback, so an empty department is blank and not infinity- Expected against current headcount written once, reused by three visuals
- Naming convention that sorts base and display measures apart
Refresh, folding and performance
Query folding is preserved through Power Query so filtering happens in PostgreSQL, not in the mashup engine after the rows have already travelled.
- Incremental refresh window on timesheet lines with a rolling reload for late edits
- Column cardinality checked before publishing
- Scheduled refresh daily, more often where same day workload matters
Validation and access control
Every KPI was reconciled against the equivalent Odoo list view before sign off, each within tolerance or explained in writing.
- Headcount, active contracts, salary cost, allocated and taken leave all tied back
- Row level security holds managers to their own departments
- Salary held in a separate role, so headcount reporting can be shared widely
The leave liability meter
low utilisation is not a saving, it is a bill
291 days were allocated and 39.5 taken, which is 13.6 percent utilisation and 251.5 pending days sitting on the books. Move the slider to see what happens to the liability as people actually take their leave, or press a preset to jump to a policy target. The days figure is real, the projections are arithmetic on the same allocation.
At the real rate, four fifths of the promised leave has never been taken
Allocated minus taken, per employee, split by purpose09 · The impact
Three findings HR could not see before
All of it extracted from data the business already had, recorded correctly, and had never once been shown.
- The hiring plan wrote itself, ranked by gap per role
- Burnout risk became visible before the resignation
- Capability became an asset register, searchable both ways
- Leave became a finance number instead of housekeeping
the answer was in the database the whole time
Four pages, one model
the tour plays once, then the report is yours
A miniature of the delivered platform. It walks through all four pages on its own the first time you reach it, then hands you the buttons. Each page is led by its own KPI panel and answers exactly one question, which is why an executive can start here and end up somewhere useful.
How large is the workforce, and what does it cost?
Read only against Odoo HR, refreshed on a schedule10 · The stack
What does an Odoo to Power BI stack actually need?
Nine layers, each one owned by the client at the end of the engagement, and none of them inside Odoo.
Headcount is a number. Capacity is a decision. Odoo's HR tables were modelled into a single workforce model and surfaced through four linked Power BI pages, and the result is not a headcount report. It is a hiring plan, a burnout warning, a capability register and a leave liability, all extracted from data the business already had and had never once been shown.
11 · FAQ
Questions people ask before commissioning a build like this
Yes. The model and the measures are built against Odoo's standard HR schema, so they transfer to any instance running the same modules. Your data replaces the demonstration data, your role names and departments come through as they are recorded, and the four pages render against your organisation. Where you hold custom fields, we extend the model to include them.
No. The connection is read only through a dedicated database role with SELECT rights, and reporting logic sits in versioned views alongside your schema. No module is installed, no field is added and nothing in the report writes back. That is deliberate: it keeps your ERP clean and your upgrade path clear.
Expected headcount is read from job positions, which hold role demand. Current headcount is a distinct count of active employees on that job. The gap measure subtracts one from the other at role grain rather than company grain, which is why it produces a ranked recruitment list instead of a single unhelpful total. In this build that resolved to 88 expected against 24 current, a gap of 64 across fourteen roles, with Experienced Developer wanting 20 and holding 11, and Handyman wanting 11 and holding 1. Neither Odoo nor any other HRIS stores that difference as a field, because demand and supply live in two tables that are never joined by the application itself. That is exactly why it has to be modelled rather than reported.
Odoo's dashboards are strong at reporting on one model at a time. This platform needs five HR domains at three different grains in one model, a many to many skills relationship that must stay unflattened, and measures that exist nowhere as stored fields. That is modelling work, and a BI layer is the right place for it. It also means finance and operations data can join the same model later without touching the ERP.
Yes. The definitions are the deliverable, not the tool. We deliver Odoo BI layers on Power BI and on Looker Studio, and the same star schema, bridge table and metric definitions port across. Power BI is our default for this platform because the many to many handling and the DAX measure library fit it best, but the choice is yours.
Odoo Online does not expose direct database access, so we connect through the external API or a scheduled replica rather than a live SQL connection. The model, the measures and the pages are identical. Odoo.sh and self hosted instances use the read only database connection directly, which is simpler and refreshes faster. We confirm which path applies on the first call.
Daily suits HR data, which changes slowly, with timesheet facts on an incremental window so only recent periods reload. Where a team wants same day workload visibility the schedule can run several times a day. Live connections are possible but rarely worth the load they place on a production ERP.
Access is read only and least privilege from the first step. Row level security holds managers to their own departments while executives see the organisation, and salary data sits in a separate role so headcount reporting can be shared widely without exposing pay. Distribution runs through your own workspace, inside your tenant, under your identity provider.
That surfaces immediately, and it is useful. Employees with no contract, missing job positions, skills recorded on some people and not others: the first draft shows each of those as a visible blank rather than hiding it inside an average. We ship a data quality view alongside the four pages so HR can clean the records that matter, and the numbers improve as the source improves.
Far less than a customisation would, because nothing inside Odoo was changed. There is no module to port, no inherited view to reconcile and no field to migrate. The report reads standard HR tables, and the parts most likely to move between versions are isolated in versioned database views, so a version change is usually a one file fix rather than a rebuild. The practical risk is a renamed or dropped column, which surfaces as a refresh error rather than as a silently wrong number, and that is the failure mode you want. We test the model against the new version on a staging copy before you upgrade production, as part of our standard Odoo upgrade support, and hand you the list of anything that needs adjusting before the cutover.
For a standard Odoo HR setup, expect two to four weeks. The sequence is schema mapping and metric definition first, then the model, the measures, the four pages, and reconciliation against your own Odoo list views before anyone signs off. Custom fields, multi company structures, or extra domains such as recruitment and payroll extend that, usually by a week each. The single biggest variable is not technical, it is how quickly your team can agree what retention and a pending leave day actually mean, because those definitions have to be settled before the DAX is written. The reference implementation shown here took six weeks, because the reusable template was being written at the same time as the report.
Because we work on both sides of the join. We implement and support Odoo ERP for clients across manufacturing, retail, food, construction and IT, and we build the BI layers on top of it, so we know what the HR tables actually contain rather than guessing from a schema diagram. 134 projects, 53 clients and 50 in house experts sit behind that. If you know your headcount but not your capacity, see our data analytics services or talk to an Odoo consultant and we will scope it against your instance.
You know your headcount. Do you know your capacity?
Tell us what your Odoo instance already records and what leadership keeps asking for. Our data analytics and Power BI team will map the model, the measures and the pages around those questions, not around a template.
Read next: a smart expense management ecosystem in Odoo for IPA Solutions
HR analytics build · Odoo HR data on Power BI
24 people. 64 open roles. Somebody should have noticed.
Odoo HR held employees, contracts, job positions, timesheets, skills and leave, all recorded faithfully and never once read as management information. KoderXpert modelled the five domains into one workforce model and delivered four linked Power BI pages that found the gap, the burnout and the liability.
Data modelling, DAX and report build by KoderXpert Technologies Pvt. Ltd.
Odoo Ready Partner & BI development · Ahmedabad & Gandhinagar, India
- Build
- Reference implementation
- Domain
- HR & workforce analytics
- Source
- Odoo HR, PostgreSQL
- Platform
- Microsoft Power BI
- Headline result
- Four pages, zero ERP changes
01 · Overview
The 30-second version
- A growing company ran HR on Odoo: employees, contracts, job positions, skills, timesheets and leave recorded faithfully across ten departments and fourteen job roles.
- Odoo could say how many people there were. It could not say that the workload was landing on one person, that a role had been open for a year, or that untaken leave was a liability nobody had costed.
- KoderXpert, through our data analytics and Power BI services, modelled all five HR domains into one employee centric Power BI model and built four linked report pages: HR executive, workforce, skills and leave. Read only, so nothing inside Odoo changed.
02 · The challenge
HR could count the workforce. It could not diagnose it.
Every problem below came from the same root cause. The records were right, and no view existed that turned them into a question anyone could act on.
Hiring gaps were felt, not measured
Open positions existed as records. Nowhere did anyone see, role by role, how many people a job needs against how many it actually has.
The cost → recruitment priorities argued, not rankedWorkload was assumed to be shared
Timesheet hours sat in the system. Nobody had ever charted them per employee, so nobody knew the distribution was lopsided.
The cost → a resignation nobody saw comingSkills lived in a form nobody read
Every employee had a skills record. There was no view of what the organisation collectively can do, and no view of what it cannot.
The cost → hiring for capability already on the payrollLeave was admin, not a liability
Allocations and absences were processed correctly. The untaken balance on the books was never treated as a number that matters.
The cost → an accrued obligation off the finance radarFive domains, five grains
Employees, contracts, timesheets, skills and leave each sit at a different level. One employee has many contracts, many skills and many leave lines.
The cost → every naive join double counts somebodyEmployee IDs are not people
Employees arrive as foreign keys. Left unjoined, a workload chart reports on numbers no manager recognises and no manager acts on.
The cost → a report nobody could argue with or usethe system knew who worked here, not who was drowning
03 · Before vs after
Drag the handle. Same database, two conversations.
Left is what HR reporting looked like on Odoo alone. Right is what four modelled pages replaced it with.
- ✗Headcount reported monthly, capacity discussed by feel
- ✗Recruitment priorities argued in meetings, never ranked
- ✗Workload reviewed in aggregate, so overload averaged away
- ✗Skills stored per employee, never summed into capability
- ✗Leave balances treated as HR housekeeping
- ✗Every question answered with a fresh spreadsheet export
- ✓Expected against current headcount for all 14 roles, one chart
- ✓A ranked hiring plan that falls out of the gap, not a debate
- ✓Hours per person, with the 854 hour outlier impossible to miss
- ✓86 skills mapped to people, searchable in both directions
- ✓251.5 pending leave days stated as an accrued obligation
- ✓Four linked pages that refresh themselves, no exports
drag the orange handle, or use the arrow keys ✨
04 · Goals
What the build had to achieve
Six goals agreed before a line of DAX was written, each phrased so it could be checked against a number later. Hover any card to tick it off.
Quantify the hiring gap. Expected against current headcount, role by role, so recruitment becomes a ranked list instead of a monthly argument.
Expose workload imbalance. Hours per employee, not hours in aggregate, because the average is what hides the person about to break.
Map the skills the company holds. And, by omission, the ones it does not hold at all, before the next job advert is written.
Turn leave into a liability figure. Allocated, taken, and the pending balance sitting between them, in a number finance recognises.
One executive view. Headcount, cost, retention and openings readable on a single screen, before anyone opens a detailed page.
Deployable to any Odoo HR instance. A template that travels with the standard schema, not a one off report bound to one database.
05 · What we delivered
Six blocks that turn HR records into decisions
Each block was built to answer one named failure from section 02, then measured on its own terms.
HR executive KPI panel
Headcount, active employees, new joiners, open positions, retention rate, departments, jobs, contracts, salary cost and average worked hours. The whole workforce framed before a single chart is read.
the board view, one screenComposition at a glance
Headcount by department, by job role, by category and by gender, plus the growth trend that shows when the organisation actually scaled rather than when it felt busy.
growth has a date nowWorkforce gap analysis
The centrepiece. Expected headcount plotted against current headcount for every role. Where the two bars diverge, the organisation is running a role it has not staffed.
no commentary requiredWorkload distribution by employee
Hours charted per person rather than summed. The moment it is drawn, an imbalance everyone half suspected becomes a fact HR has to answer for.
854 hours has a name attachedSkills inventory and register
Total skills, skill types and high skilled employee counts, with every employee mapped to their skills, proficiency and department, filterable by skill or by person.
capability, finally countedLeave allocation vs utilisation
Allocated leave plotted against leave actually taken, per employee, split by purpose, with utilisation rates. The gap between the bars is the liability.
a finance number in an HR ledgerThe seat board
watch 24 people fill 88 seats, then pick a role
Every seat is a position the business expects to be staffed. The people take their seats on their own the first time you scroll here, and what stays empty is the hiring gap. Pick any role to see its own board. Numbers are the real ones from the build: 88 expected, 24 current, 64 short across fourteen job roles.
64 seats open across the organisation
Handyman is the worst served role: 11 wanted, 1 held06 · Functional deep dive
How is each number actually calculated?
The part that stays useful after the visuals are restyled. Five measures, five decisions about what a number is allowed to mean.
The gap, computed from two tables that never meet
Role demand lives in job positions. Actual headcount lives in employees. Neither table stores the difference, so the gap only exists once you build it.
- Expected headcount read per job position, at role grain
- Current headcount as a distinct count of active employees on that job
- Subtraction done per role, never at company level, so the output is a ranked list
- Experienced Developer wants 20 and holds 11, Handyman wants 11 and holds 1
Workload, charted per person instead of per team
Timesheet lines are joined to the employee dimension before they are aggregated, so the axis carries names rather than foreign keys, and hours are plotted per individual rather than summed.
- Busiest employee carries 854 hours, lightest carries 104
- The average, near 322 hours, looks entirely healthy
- That average is exactly how burnout stays invisible in aggregate reporting
Skills modelled as many to many, not flattened
One employee holds many skills and one skill spans many employees, which Odoo stores as a many2many relation table. Flatten that into one table and every headcount double inflates, quietly, in every visual on the page.
- Bridge table between the employee and skill dimensions
- Distinct counts so a skill filter never multiplies people
- 86 skills across four skill types, filterable by skill or by person
Leave, converted from a balance into an obligation
Leave carries two opposing measures. Allocated is what the business promised, taken is what employees used, and the interesting number is neither.
- 291 days allocated against 39.5 taken leaves 251.5 pending
- Both sides broken out by purpose so policy can be examined, not assumed
- Utilisation rate turns a leave register into a wellbeing signal
Four pages that lead one question into the next
The executive page raises the question, the workforce page locates it in a role, the skills page says whether the capability already exists internally, and the leave page prices the absence.
- Button navigation with the filter context carried across pages
- Cross filtering so a department tap narrows every downstream visual
- Bookmarks for the three views leadership opens most
The average was the disguise
flat bars are a lie, watch them tell the truth
Twenty four employees, 7,726 timesheet hours between them. The aggregate view gives everyone the average and the team looks healthy. Press Per person, or wait for it to play itself, and the same hours redistribute into the shape HR was never shown. Click any bar to read that employee.
Timesheet lines joined to the employee dimension, then plotted per person
Aggregate reporting is how burnout stays invisibleThe bridge, and what happens without it
flatten the table and people start multiplying
Six employees and six skills from the register, wired the way the model wires them. Select a skill to see who holds it, or a person to see what they cover. Then press Flatten the table and watch the same six people become thirteen rows, which is the headcount every naive skills report quietly prints.
Bridged, so a skill filter narrows the people without multiplying them
Select a skill or a person to filter the other side07 · How we built it
Six weeks from schema to a template
Definitions first, code second. The order is the reason the numbers survived reconciliation.
HR schema mapping week one
Traced Odoo's employee, contract, job position, skill and leave tables and the relationships between them, marking every point where the grain changes and a join would start double counting.
Metric definition three days
Agreed what retention, a gap and a pending leave day actually mean, in writing, before a line of DAX existed. Most reporting disputes are definition disputes wearing a chart.
Data modelling week two
Built an employee centric star schema with skills bridged as a proper many to many, key columns hidden, and the date table marked so time intelligence behaves itself.
DAX development about ten days
Gap, workload, leave liability and utilisation measures written once, guarded against divide by zero, and unit checked against hand calculations on a sample of roles.
Four page design and navigation one week
Executive, workforce, skills and leave, each led by its own KPI panel and wired together with buttons so one question leads into the next instead of ending in an export.
Validation and templating final week
Headcount, salary cost and leave balances reconciled back to Odoo's own list views, then the whole thing generalised so it deploys against any Odoo HR instance.
08 · Engineering notes
How do you keep an Odoo BI model fast, correct and safe?
Written for the person who will inherit this model. Everything here is a decision that costs nothing on day one and saves a rebuild in month six.
A read only path out of Odoo
Power BI connects through a dedicated PostgreSQL role with SELECT rights only, scoped to the HR schema. Reporting logic sits in versioned views prefixed kx_bi_ rather than inside the report file.
- No module installed, no field added, nothing writes back
- Column renames handled in one place instead of forty visuals
- Gateway used where the instance is self hosted
A star schema, not a flat extract
Employee is the central dimension. Job position, department, skill, skill type, leave type and date hang off it, with timesheet and leave lines as facts.
- Key columns hidden from the field list so nobody sums an ID
- Single direction relationships except the deliberate bidirectional skills bridge
- Date table marked as a date table, so time intelligence is trustworthy
A measure library with rules
Base measures do the arithmetic, display measures do the formatting, and nothing calculates inside a visual where the next person cannot find it.
DIVIDEwith a blank fallback, so an empty department is blank and not infinity- Expected against current headcount written once, reused by three visuals
- Naming convention that sorts base and display measures apart
Refresh, folding and performance
Query folding is preserved through Power Query so filtering happens in PostgreSQL, not in the mashup engine after the rows have already travelled.
- Incremental refresh window on timesheet lines with a rolling reload for late edits
- Column cardinality checked before publishing
- Scheduled refresh daily, more often where same day workload matters
Validation and access control
Every KPI was reconciled against the equivalent Odoo list view before sign off, each within tolerance or explained in writing.
- Headcount, active contracts, salary cost, allocated and taken leave all tied back
- Row level security holds managers to their own departments
- Salary held in a separate role, so headcount reporting can be shared widely
The leave liability meter
low utilisation is not a saving, it is a bill
291 days were allocated and 39.5 taken, which is 13.6 percent utilisation and 251.5 pending days sitting on the books. Move the slider to see what happens to the liability as people actually take their leave, or press a preset to jump to a policy target. The days figure is real, the projections are arithmetic on the same allocation.
At the real rate, four fifths of the promised leave has never been taken
Allocated minus taken, per employee, split by purpose09 · The impact
Three findings HR could not see before
All of it extracted from data the business already had, recorded correctly, and had never once been shown.
- The hiring plan wrote itself, ranked by gap per role
- Burnout risk became visible before the resignation
- Capability became an asset register, searchable both ways
- Leave became a finance number instead of housekeeping
the answer was in the database the whole time
Four pages, one model
the tour plays once, then the report is yours
A miniature of the delivered platform. It walks through all four pages on its own the first time you reach it, then hands you the buttons. Each page is led by its own KPI panel and answers exactly one question, which is why an executive can start here and end up somewhere useful.
How large is the workforce, and what does it cost?
Read only against Odoo HR, refreshed on a schedule10 · The stack
What does an Odoo to Power BI stack actually need?
Nine layers, each one owned by the client at the end of the engagement, and none of them inside Odoo.
Headcount is a number. Capacity is a decision. Odoo's HR tables were modelled into a single workforce model and surfaced through four linked Power BI pages, and the result is not a headcount report. It is a hiring plan, a burnout warning, a capability register and a leave liability, all extracted from data the business already had and had never once been shown.
11 · FAQ
Questions people ask before commissioning a build like this
Yes. The model and the measures are built against Odoo's standard HR schema, so they transfer to any instance running the same modules. Your data replaces the demonstration data, your role names and departments come through as they are recorded, and the four pages render against your organisation. Where you hold custom fields, we extend the model to include them.
No. The connection is read only through a dedicated database role with SELECT rights, and reporting logic sits in versioned views alongside your schema. No module is installed, no field is added and nothing in the report writes back. That is deliberate: it keeps your ERP clean and your upgrade path clear.
Expected headcount is read from job positions, which hold role demand. Current headcount is a distinct count of active employees on that job. The gap measure subtracts one from the other at role grain rather than company grain, which is why it produces a ranked recruitment list instead of a single unhelpful total. In this build that resolved to 88 expected against 24 current, a gap of 64 across fourteen roles, with Experienced Developer wanting 20 and holding 11, and Handyman wanting 11 and holding 1. Neither Odoo nor any other HRIS stores that difference as a field, because demand and supply live in two tables that are never joined by the application itself. That is exactly why it has to be modelled rather than reported.
Odoo's dashboards are strong at reporting on one model at a time. This platform needs five HR domains at three different grains in one model, a many to many skills relationship that must stay unflattened, and measures that exist nowhere as stored fields. That is modelling work, and a BI layer is the right place for it. It also means finance and operations data can join the same model later without touching the ERP.
Yes. The definitions are the deliverable, not the tool. We deliver Odoo BI layers on Power BI and on Looker Studio, and the same star schema, bridge table and metric definitions port across. Power BI is our default for this platform because the many to many handling and the DAX measure library fit it best, but the choice is yours.
Odoo Online does not expose direct database access, so we connect through the external API or a scheduled replica rather than a live SQL connection. The model, the measures and the pages are identical. Odoo.sh and self hosted instances use the read only database connection directly, which is simpler and refreshes faster. We confirm which path applies on the first call.
Daily suits HR data, which changes slowly, with timesheet facts on an incremental window so only recent periods reload. Where a team wants same day workload visibility the schedule can run several times a day. Live connections are possible but rarely worth the load they place on a production ERP.
Access is read only and least privilege from the first step. Row level security holds managers to their own departments while executives see the organisation, and salary data sits in a separate role so headcount reporting can be shared widely without exposing pay. Distribution runs through your own workspace, inside your tenant, under your identity provider.
That surfaces immediately, and it is useful. Employees with no contract, missing job positions, skills recorded on some people and not others: the first draft shows each of those as a visible blank rather than hiding it inside an average. We ship a data quality view alongside the four pages so HR can clean the records that matter, and the numbers improve as the source improves.
Far less than a customisation would, because nothing inside Odoo was changed. There is no module to port, no inherited view to reconcile and no field to migrate. The report reads standard HR tables, and the parts most likely to move between versions are isolated in versioned database views, so a version change is usually a one file fix rather than a rebuild. The practical risk is a renamed or dropped column, which surfaces as a refresh error rather than as a silently wrong number, and that is the failure mode you want. We test the model against the new version on a staging copy before you upgrade production, as part of our standard Odoo upgrade support, and hand you the list of anything that needs adjusting before the cutover.
For a standard Odoo HR setup, expect two to four weeks. The sequence is schema mapping and metric definition first, then the model, the measures, the four pages, and reconciliation against your own Odoo list views before anyone signs off. Custom fields, multi company structures, or extra domains such as recruitment and payroll extend that, usually by a week each. The single biggest variable is not technical, it is how quickly your team can agree what retention and a pending leave day actually mean, because those definitions have to be settled before the DAX is written. The reference implementation shown here took six weeks, because the reusable template was being written at the same time as the report.
Because we work on both sides of the join. We implement and support Odoo ERP for clients across manufacturing, retail, food, construction and IT, and we build the BI layers on top of it, so we know what the HR tables actually contain rather than guessing from a schema diagram. 134 projects, 53 clients and 50 in house experts sit behind that. If you know your headcount but not your capacity, see our data analytics services or talk to an Odoo consultant and we will scope it against your instance.
You know your headcount. Do you know your capacity?
Tell us what your Odoo instance already records and what leadership keeps asking for. Our data analytics and Power BI team will map the model, the measures and the pages around those questions, not around a template.
Read next: a smart expense management ecosystem in Odoo for IPA Solutions
