Claims Analytics Dashboard

Business Problem

The claims operations team had no centralized visibility into claim lifecycles. Each adjuster maintained their own Excel tracker, leading to missed SLAs, duplicate effort, and $1.8M in undetected cost overruns discovered only at quarter-end financial reconciliation.

Leadership needed a single source of truth for claim frequency, severity, processing time, and adjuster workload all in near real-time.

Solution Overview

Wrote optimized T-SQL queries joining 6 tables: Claims, Policy, Adjuster, Coverage, Payment, Region building a star schema in SQL Server
Designed a 5-page Power BI report: Executive Summary, Claim Lifecycle, Adjuster Performance, Regional Heatmap, SLA Tracker
Implemented DAX measures for rolling 13-month trends, YoY comparisons, and dynamic benchmark lines
Set up incremental refresh on the Power BI dataset (24M row fact table) refresh time reduced from 45 min to 6 min
Automated SSRS snapshot for weekly PDF distribution to leadership via email subscription
Conducted stakeholder walkthroughs and trained 15 end-users across 3 departments

Process Walkthrough

Requirements Gathering

Ran 6 stakeholder sessions with claims managers, adjusters, and finance. Documented 23 KPIs, 8 drill-through paths, and 4 benchmark targets in a BRD.

Data Extraction & Modelling

Profiled source tables (300K+ claim records). Wrote T-SQL ETL logic handled NULL imputation, date spine generation for SLA calculations, and surrogate key management.

DAX Measures & Calculations

Built 40+ DAX measures: Claim Frequency Rate, Avg Days to Close, SLA Breach %, Cost per Claim, YoY Change %, Moving 13-Month Average.

Dashboard Design & QA

Designed all 5 pages in Power BI Desktop, applied row-level security by region. Ran automated data quality checks comparing Power BI totals vs SQL Server aggregates.

Deployment & Training

Published to Power BI Service, configured scheduled refresh, set up SSRS weekly snapshot. Delivered training across 3 sessions; created a user guide with FAQs.

Project Data Visualizations

Monthly Claims Volume (Last 12 Months)

Average Processing Time (Days) – Before vs After

Claim Status Breakdown

SLA Compliance Rate (%) by Month

Code Samples

        -- Claims Star Schema – Fact + Dimension joins
CREATE VIEW vw_ClaimsFact AS
SELECT
    c.ClaimID,
    c.PolicyID,
    c.ClaimDate,
    c.ClosedDate,
    DATEDIFF(day, c.ClaimDate, c.ClosedDate)   AS DaysToClose,
    CASE
        WHEN DATEDIFF(day, c.ClaimDate, c.ClosedDate) <= 30
        THEN 'Within SLA'
        ELSE 'SLA Breach'
    END                                             AS SLAStatus,
    c.ClaimAmount,
    c.PaidAmount,
    c.ClaimStatus,
    a.AdjusterName,
    a.Region,
    p.ProductLine,
    p.CoverageType,
    -- Rolling 13-month flag
    CASE
        WHEN c.ClaimDate >= DATEADD(month, -13, GETDATE())
        THEN 1 ELSE 0
    END                                             AS IsRolling13M
FROM       dbo.Claims      c
INNER JOIN dbo.Adjusters   a  ON c.AdjusterID  = a.AdjusterID
INNER JOIN dbo.Policies    p  ON c.PolicyID    = p.PolicyID
INNER JOIN dbo.Coverage    cv ON p.CoverageID  = cv.CoverageID
WHERE  c.ClaimDate >= '2022-01-01'
  AND  c.IsDeleted    = 0;
      

        -- Claim Frequency Rate (per 1000 policies)
Claim Frequency Rate =
DIVIDE(
    COUNTROWS( Claims ),
    CALCULATE( DISTINCTCOUNT( Policies[PolicyID] ) ),
    0
) * 1000

-- SLA Breach %
SLA Breach % =
DIVIDE(
    CALCULATE( COUNTROWS( Claims ), Claims[SLAStatus] = "SLA Breach" ),
    COUNTROWS( Claims ),
    0
)

-- YoY Claims Change %
YoY Claims Change % =
VAR CurrentYear =
    CALCULATE( COUNTROWS( Claims ), DATESYTD( DateTable[Date] ) )
VAR PriorYear =
    CALCULATE( COUNTROWS( Claims ),
        DATESYTD( SAMEPERIODLASTYEAR( DateTable[Date] ) ) )
RETURN
    DIVIDE( CurrentYear - PriorYear, PriorYear, 0 )

-- Rolling 13-Month Avg Days to Close
Avg Days to Close (13M) =
CALCULATE(
    AVERAGE( Claims[DaysToClose] ),
    DATESINPERIOD( DateTable[Date], LASTDATE( DateTable[Date] ), -13, MONTH )
)
      

        // Power Query M SLA status column & date spine
let
    Source = Sql.Database("SQLSRV01", "ClaimsDB"),
    ClaimsTable = Source{[Schema="dbo",Item="vw_ClaimsFact"]}[Data],

    // Add fiscal month column
    AddFiscalMonth = Table.AddColumn(ClaimsTable, "FiscalMonth",
        each if Date.Month([ClaimDate]) >= 4
             then Date.Month([ClaimDate]) - 3
             else Date.Month([ClaimDate]) + 9,
        Int64.Type),

    // Rename for readability
    Renamed = Table.RenameColumns(AddFiscalMonth,{
        {"DaysToClose", "Processing Days"},
        {"ClaimAmount", "Incurred Amount"}
    }),

    SetTypes = Table.TransformColumnTypes(Renamed, {
        {"ClaimDate",  type date},
        {"ClosedDate", type date},
        {"Incurred Amount", Currency.Type}
    })
in
    SetTypes
      

Sample Output SLA Performance by Region

Region	Total Claims	Within SLA	SLA Breach	Breach %	Avg Processing Days	Status
North	62,400	60,110	2,290	3.7%	14.2	On Target
South	58,700	54,100	4,600	7.8%	18.6	Monitor
East	71,200	60,520	10,680	15.0%	26.1	Breach
West	55,900	54,100	1,800	3.2%	13.8	On Target
Central	52,100	46,300	5,800	11.1%	22.4	Breach

Insurance Claims Analytics Dashboard