Telecom Network Performance & Fault Analytics data model
Kimball warehouse for telecom OSS analytics: a periodic-snapshot hourly network KPI fact per cell carrying PM counters and a fault-event accumulating-snapshot fact tracking alarm raise/clear with MTTR, customers-impacted estimate, and revenue-at-risk. Conformed dimensions (cell tower SCD2, region, RAT, vendor, fault category, severity, plus a smart-key hour dimension) support per-RAT trending, dropped-call and setup-failure ratios, MTTR/MTBF by element or vendor, and outage impact reporting bridged to operations and revenue.
Mobile-network performance & fault analytics for a telecom operator: OSS Performance Management KPI counters (accessibility, retainability, mobility, throughput, PRB utilization, handover success) landed at per-cell hourly grain alongside Fault Management alarm lifecycles (raise, clear, MTTR, ticket workflow) over conformed cell-tower, region, radio-access-technology (2G/3G/4G/5G NR), vendor, fault-category, and severity dimensions, deployed on Snowflake to feed KPI dashboards, alarm-storm detection, SLA scorecards, and vendor benchmarking. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 89
- columns
- 14
- relationships
- 0
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hourly periodic-snapshot KPI fact per network element
OSS PM counters are emitted on fixed 15-minute / hourly ROP (reporting-output period) granularity per cell; modelling them as a periodic-snapshot fact at hourly grain preserves time-series shape for trend, anomaly, and busy-hour analysis without per-counter explosion.
Accumulating-snapshot fact for the alarm raise-to-clear lifecycle
Each FM alarm has a finite lifecycle (raise → acknowledge → clear) with two anchoring dates and computable MTTR; an accumulating-snapshot fact captures both endpoints and lets MTTR/MTBF, ticket ageing, and is_repeat_fault be queried without process-mining joins.
Conformed technology dimension to compare 4G vs 5G performance
Operators run multi-RAT (2G/3G/4G/5G NR/SA) estates; a conformed technology dimension lets KPI and fault facts roll up by RAT for side-by-side 4G vs 5G NR comparison and refarming decisions while keeping the fact narrow.
Severity and vendor dimensions for SLA and vendor scorecards
NOC SLAs and vendor MSAs are written against severity tiers and vendor identity; carrying both as dimensions on the fault fact makes time-to-respond, time-to-clear, and repeat-fault scorecards trivially sliceable per vendor and per severity.
Customers-impacted estimate stored as a measure on the fault fact
Bridging operations and revenue requires a stable estimate of subscribers affected per outage; persisting customers_impacted_estimate and revenue_at_risk on the accumulating-snapshot fact freezes the as-computed value at clear time and avoids drift from later subscriber-base changes.
Tables in the Telecom Network Performance & Fault Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (2)
fct_network_kpi_hourly
Grain: one row per network element per hour per KPI bundle (periodic snapshot)
Hourly periodic-snapshot of OSS PM counters per cell.
| Column | Type | Keys |
|---|---|---|
| network_kpi_hourly_key | bigint | PK |
| hour_key | bigint | |
| date_key | bigint | |
| cell_tower_key | bigint | |
| region_key | bigint | |
| technology_key | bigint | |
| vendor_key | bigint | |
| accessibility_pct | decimal | |
| retainability_pct | decimal | |
| drop_call_ratio | decimal | |
| setup_success_rate | decimal | |
| throughput_dl_mbps | decimal | |
| throughput_ul_mbps | decimal | |
| prb_utilization_pct | decimal | |
| active_users_avg | decimal | |
| total_traffic_gb | decimal | |
| signaling_load | decimal | |
| handover_success_rate | decimal |
fct_fault_event
Grain: one row per network fault event — alarm raise + close lifecycle (accumulating snapshot)
Accumulating-snapshot fact capturing alarm raise-to-clear lifecycle.
| Column | Type | Keys |
|---|---|---|
| fault_event_key | bigint | PK |
| raised_date_key | bigint | |
| cleared_date_key | bigint | |
| cell_tower_key | bigint | |
| region_key | bigint | |
| technology_key | bigint | |
| fault_category_key | bigint | |
| vendor_key | bigint | |
| severity_key | bigint | |
| alarm_id | varchar | |
| ticket_id | varchar | |
| duration_minutes | decimal | |
| mttr_minutes | decimal | |
| customers_impacted_estimate | integer | |
| revenue_at_risk | decimal | |
| currency | varchar | |
| is_outage | boolean | |
| is_planned_maintenance | boolean | |
| is_repeat_fault | boolean | |
| ticket_status | varchar |
Dimension tables (8)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_business_day | boolean |
dim_hour
Grain: one row per calendar hour (yyyymmddhh) for hourly KPI snapshots
| Column | Type | Keys |
|---|---|---|
| hour_key | bigint | PK |
| hour_datetime | timestamp | BK |
| hour_of_day | smallint | |
| is_peak_hour | boolean | |
| day_part | varchar |
dim_cell_tower
Grain: one row per radio cell / sector identified by cell_id
SCD type 2
| Column | Type | Keys |
|---|---|---|
| cell_tower_key | bigint | PK |
| cell_id | varchar | BK |
| lac | varchar | |
| enb_id | varchar | |
| region | varchar | |
| country | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| antenna_height_m | decimal | |
| technologies_supported | varchar | |
| is_indoor | boolean | |
| site_type | varchar |
dim_region
Grain: one row per network reporting region
| Column | Type | Keys |
|---|---|---|
| region_key | bigint | PK |
| region_code | varchar | BK |
| region_name | varchar | |
| country | varchar | |
| market_tier | varchar |
dim_technology
Grain: one row per radio access technology (RAT)
| Column | Type | Keys |
|---|---|---|
| technology_key | bigint | PK |
| technology | varchar | BK |
| description | varchar | |
| generation_number | smallint | |
| frequency_band | varchar | |
| duplex_mode | varchar |
dim_vendor
Grain: one row per equipment vendor
| Column | Type | Keys |
|---|---|---|
| vendor_key | bigint | PK |
| vendor_id | varchar | BK |
| vendor_name | varchar | |
| equipment_family | varchar | |
| vendor_country | varchar |
dim_fault_category
Grain: one row per fault category in the FM catalogue
| Column | Type | Keys |
|---|---|---|
| fault_category_key | bigint | PK |
| fault_category | varchar | BK |
| description | varchar | |
| fault_type | varchar | |
| root_cause_class | varchar |
dim_severity
Grain: one row per alarm severity level
| Column | Type | Keys |
|---|---|---|
| severity_key | bigint | PK |
| severity | varchar | BK |
| severity_name | varchar | |
| severity_level | smallint | |
| sla_response_minutes | integer |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_network_kpi_hourly.hour_key → dim_hour (many-to-one)
- fct_network_kpi_hourly.date_key → dim_date (many-to-one)
- fct_network_kpi_hourly.cell_tower_key → dim_cell_tower (many-to-one)
- fct_network_kpi_hourly.region_key → dim_region (many-to-one)
- fct_network_kpi_hourly.technology_key → dim_technology (many-to-one)
- fct_network_kpi_hourly.vendor_key → dim_vendor (many-to-one)
- fct_fault_event.raised_date_key → dim_date (many-to-one)
- fct_fault_event.cleared_date_key → dim_date (many-to-one)
- fct_fault_event.cell_tower_key → dim_cell_tower (many-to-one)
- fct_fault_event.region_key → dim_region (many-to-one)
- fct_fault_event.technology_key → dim_technology (many-to-one)
- fct_fault_event.fault_category_key → dim_fault_category (many-to-one)
- fct_fault_event.vendor_key → dim_vendor (many-to-one)
- fct_fault_event.severity_key → dim_severity (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.