A Telco Data Model in Power BI: From CDRs to ARPU/Churn Dashboards Without Query Timeouts

Most slower-than-expected telecom reports share the same fingerprints:

• Conflicting KPI numbers across reports during incidents or campaigns.
• DirectQuery models scanning months of data per click; Import models that refresh for hours.
• Averages look “okay,” but p95 latency spikes when operators filter by region, tenant, or technology.
• Ad-hoc exports to Excel because a measure doesn’t match troubleshooting workflows.

Root causes are usually structural, not tooling: facts at mixed grains; joins through verbose operational tables; filters wrapped in functions that kill predicate pushdown; and definitions duplicated in multiple reports. Power BI can be fast at telco scale, but only if the semantic model is tidy, the grains are explicit, and performance features are used deliberately.

Model for reading, not for writes. A practical split is two core fact families plus compact dimensions: • fact_cdr (event grain)—one row per call/session/event with timestamps, call type, cause codes, MSISDN/IMSI (tokenized or hashed), cell/site, technology, tenant/region. • fact_kpi (hour×cell or hour×site grain)—aggregated counters from network OSS/PM feeds: attempts, successes, drops, paging success rate, throughput, p95 latency, utilization. • Dimensions—dim_cell, dim_site, dim_technology, dim_tenant, dim_time, with friendly names and surrogate keys. Keep columns that users filter by clean and pre-parsed (date, hour, region, tech).

Grain is non-negotiable. CDR stays at event; network counters at hour×cell (or hour×site for lighter views). If a report needs both levels, surface a small set of summary tables at hour×cell and hour×site for speed, then drill into event data only on dedicated pages.

Define a handful of measures the NOC cares about and reuse them everywhere: • Drop Rate = Drops / (Attempts) • Success % = Successful Calls / Attempts • Paging SR = Successful Paging Events / Paging Attempts • p95 Latency (ms) from network PM counters (pre-aggregated by hour×cell) • ARPU (period) and Churn %—these live in a finance view, but connect to network context via subscriber/site region.

Add a date table that reflects real usage windows (last 7/30/90 days) and mark it as a date table. Provide helper columns—date, hour, week, month—so filters don’t wrap raw timestamps in functions.

Speed comes from pruning and pre-computation, not bigger hardware.

Incremental Refresh Set rolling windows for large facts (e.g., fact_cdr keep 180–365 days; fact_kpi keep 365–730 days) and refresh only the recent partitions. If your ingestion is hourly, align refresh policy to that cadence. This keeps nightly windows predictable and avoids reprocessing cold history.

Aggregations Create aggregation tables at hour×cell and hour×site for the most common slices (last 7/30/90 days by hour; last 12 months by day). Map them in the model so Power BI hits aggregations first and only drops to detailed data when truly needed. This cuts scan size dramatically for NOC pages and executive overviews.

Composite Models (hybrid) Where near-real-time matters (e.g., live incident boards), use DirectQuery for hot partitions (the last N hours or the current day) and Import for everything else. Structure the visuals so high-cost visuals are isolated on their own pages or tabs. Avoid functions on filter columns; add normalized columns (e.g., call_date, hour) to preserve pushdown.

Query-friendly DAX • Keep measures simple and declarative; prefer calculated measures over calculated columns that explode storage. • Avoid iterators on huge tables in high-traffic pages; pre-aggregate where possible. • Use variables for readability and to limit repeated evaluations. • Validate under the windows operators actually use (e.g., last 2 hours, tenant X, tech 5G). Development-time “full month” tests can hide tail problems.

Operators move faster when they trust the numbers and know what changed.

One-screen definitions For Drop Rate, Success %, Paging SR, p95 Latency, publish purpose, exact formula, caveats, example. Place a small “info” panel near the key visuals; link to a single source of truth for definitions.

Reliability block Show “Updated hh:mm” and a completeness indicator (e.g., “records: 99.5% vs expected”) directly on the dashboard. Add a simple reconciliation badge (e.g., CDR counts source vs warehouse) so people don’t guess whether a sudden drop is data or network.

Row-Level Security (RLS) Partition access by tenant/region and, where required, by role (NOC vs. partner). Keep RLS filters predictable—short logic, documented fields—so troubleshooting doesn’t turn into a permissions maze.

A tiny changelog List what changed in the model this week—new measure, definition update, performance tweak. This prevents “whose number is right?” conversations when a fix shifts results by a small percentage.

You don’t need a rewrite. Harden one path that matters, then repeat.

Week 1 — Pick a target & fix the grain • Choose one high-impact flow (e.g., drop rate by hour×cell with drill into CDR). • Build the star schema: fact_kpi at hour×cell; dim_cell, dim_site, dim_time, dim_tech, dim_tenant. • Define the four core measures (Drop Rate, Success %, Paging SR, p95 Latency) with one source of truth. • Add a date table and helper columns for common filters.

Week 2 — Make refresh predictable • Turn on Incremental Refresh with a rolling window aligned to ingestion. • Create aggregation tables for hour×cell and hour×site. Map them to measures used on NOC pages. • Validate with realistic filters (last 7 days by tenant/region; top 100 cells by volume).

Week 3 — Add near-real-time where it counts • Switch hot slices to a Composite model: DirectQuery for current day/hours; Import for history. • Isolate heavy visuals and restrict cross-filtering that triggers large scans. • Add a small reliability block: updated time, completeness %, last successful load.

Week 4 — Secure, document, and prove it • Implement RLS by tenant/region; test with real users. • Publish one-screen definitions and a changelog. • Capture before/after metrics: page p50/p95 load times, scanned rows, Excel export frequency. • Close the loop: if exports drop and p95 is stable at peak, scale the pattern to the next dashboard.

Checklists Modeling [ ] fact_cdr at event grain; fact_kpi at hour×cell (or hour×site) [ ] Dimensions for cell/site/tech/tenant/time with clean keys [ ] One set of measures reused across reports

Performance [ ] Incremental Refresh on large facts [ ] Aggregations for hour×cell and hour×site [ ] Composite model for hot partitions [ ] Helper columns to avoid function-wrapped filters

Governance [ ] RLS rules by tenant/region [ ] Freshness & completeness visible on pages [ ] One-screen KPI definitions + weekly changelog

Power BI can keep up with telco pace when the model matches how networks run: event-level CDR where you need detail, hour×cell counters where operators decide, and a few performance levers used on purpose.

Start with one dashboard that matters, prove stability at peak, and repeat. The payoff is visible: fewer number fights, faster incident reviews, and pages that load when traffic surges.