MA Star Ratings — measure dimension

What it is

1. 1A definitional/reference dimension that catalogs every CMS Medicare Advantage & Part D Star Ratings measure for each annual rating-year cycle (RY2015-RY2026): the measure's slot ID, name, Part C/D, domain, scoring weight, scoring direction, value format, the data window that feeds it, and CMS methodology/provenance metadata.
2. 2It is the lookup spine that gives meaning and weights to the per-plan measure scores in the Stars fact tables; it holds no plan-level performance values itself.

Grain

How to read it

1. 1To see how a measure's scoring weight evolved over time, group by the stable canonical_name: SELECT rating_year, weight FROM dim_stars_measures WHERE canonical_name='Care Coordination' ORDER BY rating_year — returns the weight trajectory 1.5 (2015-2020) → 2.0 (2021-2022) → 4.0 (2023-2025) → 2.0 (2026), revealing the RY2023 patient-experience weight-doubling and its RY2026 rollback.
2. 2To enrich a per-plan Stars measure score, join the fact's (measure_id, rating_year) to this table for name/domain/weight/scoring-direction.

Traps

1. 1measure_id is NOT a stable measure identity — it is a per-rating-year slot code CMS renumbers every cycle. A single ID like C25 maps to 6-8 different measure concepts across RY2015-2026. To track ONE measure over time, group/join on canonical_name (always present) or cmit_number (RY2024+ only), never on measure_id alone. Any join to a per-plan Stars fact MUST include rating_year.
2. 2The grain is (measure_id, rating_year) = 538 distinct = 538 rows. None of the profiler's candidate-key columns (cmit_number, dual_numbered_with, recompute_gap_id, weight_override_puerto_rico_only) are part of the key — most are mostly-null annotations. measure_id alone has only 49 distinct values; do not treat it as a primary key.
3. 3weight is rating-year-specific and changes substantially over time for the same concept (e.g. Care Coordination 1.5→2.0→4.0→2.0). The RY2023-2025 era (methodology_version 'ry2023_v1_weight_doubling') quadruples patient-experience/CAHPS weights. Never assume a single weight per measure — always read it at the target rating_year.
4. 4Several columns are declared-but-effectively-empty or constant and carry no signal today: is_retired is FALSE for all 538 rows (measure availability is shown by which rows EXIST per year, not this flag); tech_notes_pdf_sha256 is NULL for all rows; methodology_source is the same hand-authored string for all rows; is_current is TRUE for all rows (single snapshot, no dedup filter needed yet).
5. 5Null is meaningful on the annotation columns: cmit_number is NULL for RY2015-2023 (CMIT IDs only exist from RY2024); dual_numbered_with NULL = not a C/D dual-counted measure; recompute_gap_id NULL = recomputable (non-null ⟺ the 24 composite rows); weight_override_puerto_rico_only NULL = use normal weight, 0.0 = measure excluded from Puerto Rico ratings (only the 3 med-adherence measures).
6. 6This is a DEFINITION table, not a performance table — it holds zero plan/contract-level scores or rates. It is the dictionary that gives weights and meaning to the actual MA Star Ratings measure-value fact tables. measurement_period is free-text (69 variant formats: MY####, CY####, multi-year HOS spans, CAHPS survey windows) and lags rating_year by ~2 years; it is the history/time column but is NOT part of the grain key. primary_data_source has spelling variants (PDE_MPF vs PDE-MPF vs PDE/MPF) — normalize before aggregating.

Columns (22)

Query it

describe_data_source(source_fqn="medicare_silver.dim_stars_measures")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.dim_stars_measures WHERE measure_id = '<value>' AND rating_year = '<value>' LIMIT 100")