Enterprise KPI Dashboard — Harish Thiyagharajan Selvi

// sla_performance

SLA Compliance Dashboard

Monthly SLA Compliance Trend

Target: 95% · Warning: 90% · Source: kpi.fact_incidents

SLA & MTTR by Priority

Resolution SLA compliance % per priority tier · 40,000 tickets · 2 years

// team_workload

Team Workload Distribution

Ticket Volume by Team

3 teams handle 60% of all volume — invisible before this dashboard

Repeat Incident Rate by Team

Target: <12% · Proactive brief identified root causes → 18% reduction

// query_optimisation

How We Got 40% Faster

The original Power BI dashboards queried ServiceNow via ODBC directly. Functions on indexed columns forced full table scans. Two rewrites delivered the majority of the speed gain.

❌ Before — 8–12s

✅ After — <400ms

-- ❌ Function on index → full scan every time
WHERE MONTH(created_date) = 3
  AND YEAR(created_date) = 2025

-- ✅ Sargable range → index seek
WHERE created_date >= '2025-03-01'
  AND created_date  <  '2025-04-01'

-- ❌ Correlated subquery: O(n²)
SELECT (SELECT AVG(resolution_time_hours)
        FROM fact_incidents f2
        WHERE f2.team_id = f.team_id)

-- ✅ CTE: single aggregation pass
WITH team_avg AS (
  SELECT team_id, AVG(resolution_time_hours) avg_hrs
  FROM fact_incidents GROUP BY team_id
)
SELECT f.*, ta.avg_hrs
FROM fact_incidents f JOIN team_avg ta
  ON f.team_id = ta.team_id
        

Staging layer eliminated live ODBC queries

Raw ServiceNow data lands in SQL Server staging nightly via Azure Data Factory. Power BI hits indexed, typed SQL tables — not a live ODBC connector with implicit string→datetime casts on every refresh.

Covering index on month_year + priority

Most Power BI queries filter by month and priority, then aggregate SLA metrics. A single covering index with those as keys and SLA/MTTR columns as includes eliminated all key lookups on the most common access pattern.

Incremental refresh: 18 min → 4 min nightly

Configured Power BI incremental refresh with RangeStart/RangeEnd parameters. Only the last 60 days re-process nightly; historical partitions stay cached. This alone cut the nightly refresh by 78%.

// data_architecture

Architecture & Data Model

1

5 source systems → staging schemaServiceNow (incidents/requests), Workday (agents), SharePoint (SLA rules), and 2 SQL Server DBs land nightly into the staging schema via Azure Data Factory pipelines.

2

Staging → kpi schema (nightly rebuild)02_build_kpi_layer.sql runs after every ADF load — TRUNCATE + full INSERT from staging into the clean kpi schema with all joins pre-resolved and date dimension populated.

3

Star schema in Power BIdim_date → fact_incidents ← dim_teams ← dim_agents. No bidirectional filters — disabled after they caused filter leakage between the incidents and service requests fact tables.

4

DAX measures library (15 measures)SLA Compliance Rate, MTTR, Repeat Incident %, Team Utilisation %, MoM comparisons, rolling 4-week averages — all centrally defined, reused across 6 dashboard pages.

5

Automated weekly reconciliationPython script compares staging vs kpi for status/SLA/date mismatches. Replaced 3 hours/analyst/week of manual Excel VLOOKUP reconciliation that was producing inconsistent numbers.

Dashboard Pages

Executive Summary8 KPI tiles · MoM

SLA PerformancePriority matrix · MTTR

Team WorkloadUtilisation heat map

Incident TrendsVolume by day/hour/cat

Repeat IncidentsRoot-cause patterns

At-Risk TicketsLive P1/P2 SLA alert

Key DAX Measure

SLA Compliance Rate =
DIVIDE(
  CALCULATE(COUNTROWS(fact_incidents),
    fact_incidents[sla_resolution_met] = TRUE()),
  CALCULATE(COUNTROWS(fact_incidents),
    fact_incidents[is_open] = 0,
    NOT ISBLANK(fact_incidents[sla_resolution_met])),
  0
)

// explore_more

Full SQL Scripts & DAX Library

Complete T-SQL schema, ETL, KPI queries, and before/after optimisation examples. Plus a full DAX measures library and star schema documentation — all on GitHub.

View on GitHub ← All Projects

Enterprise KPIDashboard Suite