Insurance analytics build · Power BI on premium, claims and fraud data
For every rupee of premium, how much goes back out? They could not answer it.
A general insurer writing four product lines across four regions, distributed through an agent network. Premium sat in one system, claims in another and fraud flags in a third. KoderXpert joined them into one underwriting model and delivered three Power BI pages, with loss ratio as a first-class metric rather than a quarterly exercise.
Data modelling, DAX and dashboard build by KoderXpert Technologies Pvt. Ltd.
Power BI & data analytics · Ahmedabad & Gandhinagar, India
- Client
- General insurer, name withheld
- Domain
- Underwriting & claims analytics
- Source
- Policy, premium, claims and fraud records
- Platform
- Microsoft Power BI
- Headline result
- Loss ratio on demand, by region, product and agent
01 · Overview
The one division nobody was doing
- A general insurer writing Auto, Health, Life and Travel across four regions, sold through an agent network to a customer base spanning every age band from 18 to 65 and over.
- The core question in insurance is simple: for every rupee of premium taken in, how much goes back out as claims? Answer it by product, by region and by agent and you know exactly where the book is healthy and where it is bleeding. They could not answer it.
- KoderXpert joined policies, premiums, claims and fraud flags into one model on shared region, product and agent dimensions, then built three linked Power BI pages: portfolio performance, claims and fraud, and distribution.
02 · The challenge
Premium was counted. Claims were counted. The two were never divided.
Every problem below has the same root: the records were correct, and no view existed that put the two sides of the ledger next to each other where a decision gets made.
Premium and claims were reported separately
Sales reported what came in. Claims reported what went out. Nobody routinely put the two side by side at the level where a re-pricing decision is actually made.
The cost → two true reports, one missing numberLoss ratio was an exercise, not a metric
The one figure that tells an insurer whether a product line is viable had to be assembled by hand, so it was seen late and rarely by product and region together.
The cost → a line was judged after the damageFraud was tracked, not analysed
Fraud cases were recorded one at a time. Their distribution across regions, products and agents was never surfaced, so the log was evidence but never a lead.
The cost → 418 cases and no patternAgent performance meant premium written
Agents were ranked on what they sold, not on what their book cost to service, so a high-premium, high-loss agent looked like a star and got the next tranche.
The cost → the wrong behaviour got rewardedPremium and claims live at different grains
A policy is one row. A claim is another, and may not exist at all. Dividing one by the other correctly, at every level, is the entire modelling problem.
The cost → every naive join double countsClaim status is not binary
Pending, approved, rejected and no-claim each mean something different to the reserve and to the approval rate, so a single claims total hid four different states.
The cost → the pending reserve was invisiblean insurer that cannot compute loss ratio on demand is underwriting blind
03 · Before vs after
Same records. Two completely different underwriting conversations.
Left is what portfolio reporting looked like before. Right is what three modelled pages replaced it with.
- ✗Premium reported by sales, claims reported by claims, never divided
- ✗Loss ratio assembled by hand, quarterly, rarely by product and region together
- ✗Fraud recorded case by case, with no distribution behind it
- ✗Agents ranked on premium written alone
- ✗Pending claims reserve estimated rather than read
- ✗Every re-pricing argument opened with instinct
- ✓Loss ratio for all 16 region and product cells, on one matrix
- ✓The same ratio sliceable by agent, on demand, not once a quarter
- ✓418 fraud cases mapped across four products and four regions
- ✓Premium, loss ratio and fraud read together in one agent league table
- ✓Pending, approved, rejected and no-claim by count and by value
- ✓Re-pricing conversations that start from arithmetic
drag the orange handle, or use the arrow keys ✨
04 · Goals
What the build had to achieve
Six goals agreed with underwriting before a line of DAX was written, each phrased so it could be checked against a number afterwards.
Loss ratio as a first-class metric. Sliceable by region, product and agent, on demand, rather than compiled by hand once a quarter.
Premium and claims in one model. Written and paid, side by side, at every grain, without double counting the policy row or losing the claim that never happened.
Fraud made analysable. Distribution by region, product line and agent, so a case log becomes an investigation strategy rather than an archive.
Claims pipeline visible. Approval, rejection and pending exposure by status, so the reserve implied by the pending queue is read rather than assumed.
Agents ranked on risk, not just sales. Premium, loss ratio and fraud cases in one league table, so top agent stops meaning top seller.
Know the customer base. Age, gender, payment method and product mix, feeding both pricing and distribution strategy.
05 · What we delivered
Six blocks that turn a policy ledger into an underwriting decision
Each block answers one named failure from section 02, and each was measured on its own terms.
Portfolio KPI strip
Premium written, claims paid, net revenue, total policies, coverage exposure and average customer age. The six numbers that frame every underwriting conversation, on one line.
the board view, one screenYearly premium and claims trend
Five years of premium plotted against claims. The gap between the two lines is the business, and the direction of that gap is the whole story.
the gap is the businessLoss ratio by region and product
The centrepiece. A single matrix showing what every product costs in every region, turning underwriting from opinion into arithmetic.
no commentary requiredClaims pipeline by status
Pending, approved, rejected and no-claim, by both count and value, so the reserve implied by the pending queue is visible rather than assumed.
four states, not one totalFraud by product and region
418 flagged cases mapped across four products and four regions, turning scattered case files into a pattern the investigation team can work.
a case log becomes a leadAgent league table
Every agent ranked on premium written, policies sold, loss ratio and fraud cases. The sales ranking and the risk ranking, in one table.
top agent, honestly definedThe loss ratio matrix
sixteen cells, one of them is the problem
Sixteen region and product combinations, each shaded by what it costs. The worst cell highlights itself when you reach this section. Click any cell to read it, or switch to claim approval rate to see the same book from the claims side. Figures are the build's own.
Premium in, claims out, divided at the level a re-pricing decision is actually made.
One denominator, everywhere06 · Functional deep dive
How each number actually gets made
The part that stays useful after the visuals are restyled. Five measures, five decisions about what a number is allowed to mean.
Loss ratio, computed across two grains
Premium sits on the policy row. Claims sit on their own rows and may not exist at all. Dividing one by the other is the whole problem, because a naive join multiplies the policy by the number of claims against it.
- Premium aggregated at policy grain, claims aggregated separately
- The division performed at the filter context, not row by row
- Guarded so a product with no claims returns blank, not infinity
- Holds by region, by product, by agent, and by all three at once
Claim status treated as four different facts
Pending, approved, rejected and no-claim each mean something different to the reserve. Collapsing them into a claims total destroys exactly the information a claims manager needs.
- Each status carried as its own measure, by count and by value
- Approval rate expressed against decided claims, not against all claims
- The pending queue surfaced as an implied reserve
Fraud given a denominator
418 flagged cases against 20,000 policies is a rate of roughly two per cent. The count alone cannot be compared between a large product line and a small one.
- Fraud cases cross-tabulated against product and region
- Rate expressed against policies in force, not against claims
- Concentration by agent surfaced for the investigation team
One agent table, two rankings
The same agent dimension has to support a sales league table and a risk league table. They are not the same ordering, and the difference between them is the finding.
- Premium written, policies sold, loss ratio and fraud in one row
- Sortable on any of the four without rebuilding the visual
- Loss ratio at agent grain uses the same measure as the matrix
Three pages that lead one question into the next
The portfolio page raises the question, the claims and fraud page locates it in a product and a region, and the distribution page names the agent behind it.
- Shared region, product and agent dimensions across all three pages
- Cross filtering carries a selection through the analysis
- One denominator everywhere, so the three pages cannot disagree
The agent league table, both ways
top on premium, bottom on the book they leave
The same ten agents, sorted three ways. Premium written is the ranking every sales meeting already has. Switch to loss ratio and fraud cases and the order changes, which is the whole point: a league table built on premium alone rewards the behaviour that ruins a book.
Ranked on premium written, the ordering every sales meeting already has.
Sales ranking and risk ranking, one table07 · How we built it
Six stages from ledger to matrix
Definitions first, code second. That order is the reason the numbers survived reconciliation.
Metric definition workshop week one
Agreed with underwriting what loss ratio, net revenue and approval rate actually mean, in writing, before any of them were coded.
Data modelling week two
Policies, premiums, claims and fraud flags unified on shared region, product and agent dimensions, with the grain change documented at every join.
DAX development about ten days
Ratio measures built to slice cleanly at every grain, guarded against divide by zero and unit checked against hand calculations on a sample of products.
Dashboard design one week
Three pages, each led by a KPI strip and anchored on the matrix or table that carries the decision, rather than on the most attractive chart.
Validation four days
Every headline figure reconciled back to the source policy and claims records, each within tolerance or explained in writing.
Deployment, training and tuning final week
Published to the Power BI Service with scheduled refresh, then walked underwriting, claims and distribution through the drill paths and tuned against real usage.
08 · Engineering notes
How you keep an insurance model honest
Written for whoever inherits this model. Each note is a decision that costs nothing on day one and saves a rebuild in month six.
A star schema with one shared set of dimensions
Region, product line, agent and date are conformed dimensions used by every fact table. That is what lets a single selection filter premium, claims and fraud consistently.
- Policy, claim and fraud facts each at their own grain
- Conformed dimensions rather than per-fact copies
- Key columns hidden so nobody sums an identifier
A measure library with rules
Base measures do the arithmetic, display measures do the formatting, and nothing calculates inside a visual where the next analyst cannot find it.
- Loss ratio written once and reused by the matrix, the agent table and the KPI strip
- DIVIDE with a blank fallback, so an empty cell is blank rather than infinity
- Naming convention that sorts base and display measures apart
Access and confidentiality
The client's book is commercially sensitive and the agent table names individuals, so the report was built to be shared without exposing either.
- Row level security by region for distribution managers
- Agent identifiers separated from personal detail
- Aggregate views for anyone outside underwriting
Refresh and reconciliation
Scheduled refresh against the source extracts, with a reconciliation view that compares headline figures to the operational systems every time it runs.
- Premium, claims and policy counts checked on each refresh
- Tolerance thresholds agreed with finance rather than assumed
- Failures surface as a visible variance, not a silent drift
Give fraud a denominator
418 cases is a number, 418 out of what is a lead
20,000 policies, 418 flagged for fraud. Move the slider to change how much of the book you look at and watch the rate hold while the count collapses. A count without a denominator cannot be compared across products or regions, which is why the case log was never an investigation strategy.
The whole book in view: 418 flagged cases against 20,000 policies.
Cross-tabulated against product and region, a case log becomes a lead09 · The impact
Three findings underwriting could not see before
All of it extracted from records the insurer already held, correctly, and had never once been shown together.
- Loss ratio became a standing metric, available by region, product and agent on demand
- Agents are judged on the book they leave behind, not on volume alone
- Fraud gained a denominator, so counts became rates and rates became a strategy
- Re-pricing conversations now start from a matrix rather than from instinct
the answer was in the records the whole time
10 · The stack
What this platform runs on
Six layers, each owned by the client at the end of the engagement.
Premium growth is the easiest number in insurance to improve and the easiest to be destroyed by. An agent can double the book and triple the claims. Loss ratio is the only figure that tells you whether the growth was worth having, so it was built first and everything else was arranged around it.
11 · FAQ
Questions insurers ask before commissioning a build like this
Premium is aggregated at policy grain and claims are aggregated separately, then divided within the filter context rather than row by row. That is what stops a policy with three claims counting its premium three times. The measure is written once and reused by the matrix, the agent table and the KPI strip, so those three can never disagree, and it is guarded so a product with no claims returns blank rather than an error.
It is the normal case and it is the reason the model exists. The two are joined on conformed region, product and agent dimensions rather than on a single system key, so they can arrive from different sources on different schedules. What matters is that the dimensions agree, which is a mapping exercise we do at the start and document as part of the handover.
Yes. The platform reads extracts or a replica; nothing is written back and no change is made to the administration system. That is deliberate, because a reporting layer that requires changes to a core insurance system will not get approved, and should not.
Because a count cannot be compared. 418 cases is meaningless until you know it sits against 20,000 policies, and a product line with 40 cases out of 500 policies is a far worse problem than one with 200 cases out of 12,000. Cross-tabulating the flags against the book is what turns a case log into a prioritised list for the investigation team.
It changes the conversation, which is the point, so it is worth introducing carefully. The table does not replace the premium ranking, it sits beside it. An agent writing a large book at an acceptable loss ratio still ranks well. What changes is that a high-premium, high-loss, high-fraud agent can no longer be read as a top performer, and the next tranche of business gets allocated on the fuller picture.
Pending claims are carried as their own status with both a count and a value, so the reserve they imply is visible rather than folded into a paid-claims total. Approval rate is expressed against decided claims only, because including undecided ones understates it and moves the number every time the queue changes.
Daily suits most insurance reporting, with the schedule set around when the source extracts land. Loss ratio and fraud distribution do not change hour to hour, and a refresh that runs before the morning underwriting meeting is worth more than a live connection that loads the source system.
Yes. The definitions are the deliverable, not the tool. The conformed dimensions, the grain handling and the metric definitions port across; only the implementation changes. Power BI is our default because the DAX measure library and the matrix visual suit this platform, but we have delivered equivalent work on other stacks.
It surfaces immediately, and that is useful. Policies with no agent, claims with no matching policy, products recorded inconsistently between systems: the first draft shows each of those as a visible gap rather than hiding it in an average. We ship a data quality view alongside the three pages so the numbers improve as the source improves.
Six weeks was the shape of this one: definitions and modelling first, then measures, then the three pages, then reconciliation. A single product line with clean extracts can be faster. Multiple legal entities, reinsurance, or a longer history to migrate will extend it, and we scope that against your data rather than against a template.
Underwriting opens the loss ratio matrix, claims opens the pipeline by status, the investigation team opens the fraud distribution and distribution management opens the agent table. That is why it is three pages rather than one dashboard: the four audiences ask different questions and the pages are built around the questions, not around the data model.
Because we build the model as well as the visuals. We work across ERP, finance and operational data for clients in manufacturing, retail, healthcare and financial services, so the grain problems in an insurance book are familiar rather than novel. If you are growing a book you cannot price, see our data analytics services or talk to a consultant and we will scope it against your own data.
Know what you wrote. Do you know what it cost?
Tell us what your policy, claims and agent systems already record and what underwriting keeps asking for. Our data analytics team will map the model, the measures and the pages around those questions, not around a template.
Read next: an HR analytics platform on Odoo, built in Power BI
Insurance analytics build · Power BI on premium, claims and fraud data
For every rupee of premium, how much goes back out? They could not answer it.
A general insurer writing four product lines across four regions, distributed through an agent network. Premium sat in one system, claims in another and fraud flags in a third. KoderXpert joined them into one underwriting model and delivered three Power BI pages, with loss ratio as a first-class metric rather than a quarterly exercise.
Data modelling, DAX and dashboard build by KoderXpert Technologies Pvt. Ltd.
Power BI & data analytics · Ahmedabad & Gandhinagar, India
- Client
- General insurer, name withheld
- Domain
- Underwriting & claims analytics
- Source
- Policy, premium, claims and fraud records
- Platform
- Microsoft Power BI
- Headline result
- Loss ratio on demand, by region, product and agent
01 · Overview
The one division nobody was doing
- A general insurer writing Auto, Health, Life and Travel across four regions, sold through an agent network to a customer base spanning every age band from 18 to 65 and over.
- The core question in insurance is simple: for every rupee of premium taken in, how much goes back out as claims? Answer it by product, by region and by agent and you know exactly where the book is healthy and where it is bleeding. They could not answer it.
- KoderXpert joined policies, premiums, claims and fraud flags into one model on shared region, product and agent dimensions, then built three linked Power BI pages: portfolio performance, claims and fraud, and distribution.
02 · The challenge
Premium was counted. Claims were counted. The two were never divided.
Every problem below has the same root: the records were correct, and no view existed that put the two sides of the ledger next to each other where a decision gets made.
Premium and claims were reported separately
Sales reported what came in. Claims reported what went out. Nobody routinely put the two side by side at the level where a re-pricing decision is actually made.
The cost → two true reports, one missing numberLoss ratio was an exercise, not a metric
The one figure that tells an insurer whether a product line is viable had to be assembled by hand, so it was seen late and rarely by product and region together.
The cost → a line was judged after the damageFraud was tracked, not analysed
Fraud cases were recorded one at a time. Their distribution across regions, products and agents was never surfaced, so the log was evidence but never a lead.
The cost → 418 cases and no patternAgent performance meant premium written
Agents were ranked on what they sold, not on what their book cost to service, so a high-premium, high-loss agent looked like a star and got the next tranche.
The cost → the wrong behaviour got rewardedPremium and claims live at different grains
A policy is one row. A claim is another, and may not exist at all. Dividing one by the other correctly, at every level, is the entire modelling problem.
The cost → every naive join double countsClaim status is not binary
Pending, approved, rejected and no-claim each mean something different to the reserve and to the approval rate, so a single claims total hid four different states.
The cost → the pending reserve was invisiblean insurer that cannot compute loss ratio on demand is underwriting blind
03 · Before vs after
Same records. Two completely different underwriting conversations.
Left is what portfolio reporting looked like before. Right is what three modelled pages replaced it with.
- ✗Premium reported by sales, claims reported by claims, never divided
- ✗Loss ratio assembled by hand, quarterly, rarely by product and region together
- ✗Fraud recorded case by case, with no distribution behind it
- ✗Agents ranked on premium written alone
- ✗Pending claims reserve estimated rather than read
- ✗Every re-pricing argument opened with instinct
- ✓Loss ratio for all 16 region and product cells, on one matrix
- ✓The same ratio sliceable by agent, on demand, not once a quarter
- ✓418 fraud cases mapped across four products and four regions
- ✓Premium, loss ratio and fraud read together in one agent league table
- ✓Pending, approved, rejected and no-claim by count and by value
- ✓Re-pricing conversations that start from arithmetic
drag the orange handle, or use the arrow keys ✨
04 · Goals
What the build had to achieve
Six goals agreed with underwriting before a line of DAX was written, each phrased so it could be checked against a number afterwards.
Loss ratio as a first-class metric. Sliceable by region, product and agent, on demand, rather than compiled by hand once a quarter.
Premium and claims in one model. Written and paid, side by side, at every grain, without double counting the policy row or losing the claim that never happened.
Fraud made analysable. Distribution by region, product line and agent, so a case log becomes an investigation strategy rather than an archive.
Claims pipeline visible. Approval, rejection and pending exposure by status, so the reserve implied by the pending queue is read rather than assumed.
Agents ranked on risk, not just sales. Premium, loss ratio and fraud cases in one league table, so top agent stops meaning top seller.
Know the customer base. Age, gender, payment method and product mix, feeding both pricing and distribution strategy.
05 · What we delivered
Six blocks that turn a policy ledger into an underwriting decision
Each block answers one named failure from section 02, and each was measured on its own terms.
Portfolio KPI strip
Premium written, claims paid, net revenue, total policies, coverage exposure and average customer age. The six numbers that frame every underwriting conversation, on one line.
the board view, one screenYearly premium and claims trend
Five years of premium plotted against claims. The gap between the two lines is the business, and the direction of that gap is the whole story.
the gap is the businessLoss ratio by region and product
The centrepiece. A single matrix showing what every product costs in every region, turning underwriting from opinion into arithmetic.
no commentary requiredClaims pipeline by status
Pending, approved, rejected and no-claim, by both count and value, so the reserve implied by the pending queue is visible rather than assumed.
four states, not one totalFraud by product and region
418 flagged cases mapped across four products and four regions, turning scattered case files into a pattern the investigation team can work.
a case log becomes a leadAgent league table
Every agent ranked on premium written, policies sold, loss ratio and fraud cases. The sales ranking and the risk ranking, in one table.
top agent, honestly definedThe loss ratio matrix
sixteen cells, one of them is the problem
Sixteen region and product combinations, each shaded by what it costs. The worst cell highlights itself when you reach this section. Click any cell to read it, or switch to claim approval rate to see the same book from the claims side. Figures are the build's own.
Premium in, claims out, divided at the level a re-pricing decision is actually made.
One denominator, everywhere06 · Functional deep dive
How each number actually gets made
The part that stays useful after the visuals are restyled. Five measures, five decisions about what a number is allowed to mean.
Loss ratio, computed across two grains
Premium sits on the policy row. Claims sit on their own rows and may not exist at all. Dividing one by the other is the whole problem, because a naive join multiplies the policy by the number of claims against it.
- Premium aggregated at policy grain, claims aggregated separately
- The division performed at the filter context, not row by row
- Guarded so a product with no claims returns blank, not infinity
- Holds by region, by product, by agent, and by all three at once
Claim status treated as four different facts
Pending, approved, rejected and no-claim each mean something different to the reserve. Collapsing them into a claims total destroys exactly the information a claims manager needs.
- Each status carried as its own measure, by count and by value
- Approval rate expressed against decided claims, not against all claims
- The pending queue surfaced as an implied reserve
Fraud given a denominator
418 flagged cases against 20,000 policies is a rate of roughly two per cent. The count alone cannot be compared between a large product line and a small one.
- Fraud cases cross-tabulated against product and region
- Rate expressed against policies in force, not against claims
- Concentration by agent surfaced for the investigation team
One agent table, two rankings
The same agent dimension has to support a sales league table and a risk league table. They are not the same ordering, and the difference between them is the finding.
- Premium written, policies sold, loss ratio and fraud in one row
- Sortable on any of the four without rebuilding the visual
- Loss ratio at agent grain uses the same measure as the matrix
Three pages that lead one question into the next
The portfolio page raises the question, the claims and fraud page locates it in a product and a region, and the distribution page names the agent behind it.
- Shared region, product and agent dimensions across all three pages
- Cross filtering carries a selection through the analysis
- One denominator everywhere, so the three pages cannot disagree
The agent league table, both ways
top on premium, bottom on the book they leave
The same ten agents, sorted three ways. Premium written is the ranking every sales meeting already has. Switch to loss ratio and fraud cases and the order changes, which is the whole point: a league table built on premium alone rewards the behaviour that ruins a book.
Ranked on premium written, the ordering every sales meeting already has.
Sales ranking and risk ranking, one table07 · How we built it
Six stages from ledger to matrix
Definitions first, code second. That order is the reason the numbers survived reconciliation.
Metric definition workshop week one
Agreed with underwriting what loss ratio, net revenue and approval rate actually mean, in writing, before any of them were coded.
Data modelling week two
Policies, premiums, claims and fraud flags unified on shared region, product and agent dimensions, with the grain change documented at every join.
DAX development about ten days
Ratio measures built to slice cleanly at every grain, guarded against divide by zero and unit checked against hand calculations on a sample of products.
Dashboard design one week
Three pages, each led by a KPI strip and anchored on the matrix or table that carries the decision, rather than on the most attractive chart.
Validation four days
Every headline figure reconciled back to the source policy and claims records, each within tolerance or explained in writing.
Deployment, training and tuning final week
Published to the Power BI Service with scheduled refresh, then walked underwriting, claims and distribution through the drill paths and tuned against real usage.
08 · Engineering notes
How you keep an insurance model honest
Written for whoever inherits this model. Each note is a decision that costs nothing on day one and saves a rebuild in month six.
A star schema with one shared set of dimensions
Region, product line, agent and date are conformed dimensions used by every fact table. That is what lets a single selection filter premium, claims and fraud consistently.
- Policy, claim and fraud facts each at their own grain
- Conformed dimensions rather than per-fact copies
- Key columns hidden so nobody sums an identifier
A measure library with rules
Base measures do the arithmetic, display measures do the formatting, and nothing calculates inside a visual where the next analyst cannot find it.
- Loss ratio written once and reused by the matrix, the agent table and the KPI strip
- DIVIDE with a blank fallback, so an empty cell is blank rather than infinity
- Naming convention that sorts base and display measures apart
Access and confidentiality
The client's book is commercially sensitive and the agent table names individuals, so the report was built to be shared without exposing either.
- Row level security by region for distribution managers
- Agent identifiers separated from personal detail
- Aggregate views for anyone outside underwriting
Refresh and reconciliation
Scheduled refresh against the source extracts, with a reconciliation view that compares headline figures to the operational systems every time it runs.
- Premium, claims and policy counts checked on each refresh
- Tolerance thresholds agreed with finance rather than assumed
- Failures surface as a visible variance, not a silent drift
Give fraud a denominator
418 cases is a number, 418 out of what is a lead
20,000 policies, 418 flagged for fraud. Move the slider to change how much of the book you look at and watch the rate hold while the count collapses. A count without a denominator cannot be compared across products or regions, which is why the case log was never an investigation strategy.
The whole book in view: 418 flagged cases against 20,000 policies.
Cross-tabulated against product and region, a case log becomes a lead09 · The impact
Three findings underwriting could not see before
All of it extracted from records the insurer already held, correctly, and had never once been shown together.
- Loss ratio became a standing metric, available by region, product and agent on demand
- Agents are judged on the book they leave behind, not on volume alone
- Fraud gained a denominator, so counts became rates and rates became a strategy
- Re-pricing conversations now start from a matrix rather than from instinct
the answer was in the records the whole time
10 · The stack
What this platform runs on
Six layers, each owned by the client at the end of the engagement.
Premium growth is the easiest number in insurance to improve and the easiest to be destroyed by. An agent can double the book and triple the claims. Loss ratio is the only figure that tells you whether the growth was worth having, so it was built first and everything else was arranged around it.
11 · FAQ
Questions insurers ask before commissioning a build like this
Premium is aggregated at policy grain and claims are aggregated separately, then divided within the filter context rather than row by row. That is what stops a policy with three claims counting its premium three times. The measure is written once and reused by the matrix, the agent table and the KPI strip, so those three can never disagree, and it is guarded so a product with no claims returns blank rather than an error.
It is the normal case and it is the reason the model exists. The two are joined on conformed region, product and agent dimensions rather than on a single system key, so they can arrive from different sources on different schedules. What matters is that the dimensions agree, which is a mapping exercise we do at the start and document as part of the handover.
Yes. The platform reads extracts or a replica; nothing is written back and no change is made to the administration system. That is deliberate, because a reporting layer that requires changes to a core insurance system will not get approved, and should not.
Because a count cannot be compared. 418 cases is meaningless until you know it sits against 20,000 policies, and a product line with 40 cases out of 500 policies is a far worse problem than one with 200 cases out of 12,000. Cross-tabulating the flags against the book is what turns a case log into a prioritised list for the investigation team.
It changes the conversation, which is the point, so it is worth introducing carefully. The table does not replace the premium ranking, it sits beside it. An agent writing a large book at an acceptable loss ratio still ranks well. What changes is that a high-premium, high-loss, high-fraud agent can no longer be read as a top performer, and the next tranche of business gets allocated on the fuller picture.
Pending claims are carried as their own status with both a count and a value, so the reserve they imply is visible rather than folded into a paid-claims total. Approval rate is expressed against decided claims only, because including undecided ones understates it and moves the number every time the queue changes.
Daily suits most insurance reporting, with the schedule set around when the source extracts land. Loss ratio and fraud distribution do not change hour to hour, and a refresh that runs before the morning underwriting meeting is worth more than a live connection that loads the source system.
Yes. The definitions are the deliverable, not the tool. The conformed dimensions, the grain handling and the metric definitions port across; only the implementation changes. Power BI is our default because the DAX measure library and the matrix visual suit this platform, but we have delivered equivalent work on other stacks.
It surfaces immediately, and that is useful. Policies with no agent, claims with no matching policy, products recorded inconsistently between systems: the first draft shows each of those as a visible gap rather than hiding it in an average. We ship a data quality view alongside the three pages so the numbers improve as the source improves.
Six weeks was the shape of this one: definitions and modelling first, then measures, then the three pages, then reconciliation. A single product line with clean extracts can be faster. Multiple legal entities, reinsurance, or a longer history to migrate will extend it, and we scope that against your data rather than against a template.
Underwriting opens the loss ratio matrix, claims opens the pipeline by status, the investigation team opens the fraud distribution and distribution management opens the agent table. That is why it is three pages rather than one dashboard: the four audiences ask different questions and the pages are built around the questions, not around the data model.
Because we build the model as well as the visuals. We work across ERP, finance and operational data for clients in manufacturing, retail, healthcare and financial services, so the grain problems in an insurance book are familiar rather than novel. If you are growing a book you cannot price, see our data analytics services or talk to a consultant and we will scope it against your own data.
Know what you wrote. Do you know what it cost?
Tell us what your policy, claims and agent systems already record and what underwriting keeps asking for. Our data analytics team will map the model, the measures and the pages around those questions, not around a template.
Read next: an HR analytics platform on Odoo, built in Power BI
