Dimension Value Management

Workflow

Phase 1: Extract ICM Context

1. Get ICM details using mcp_icm_get_incident_details_by_id (preferred — mcp_icm_get_ai_summary often returns empty)
2. Parse the title to extract alert type and dimension name:
   • AggreUnmatched_{DimensionName}_Merge_Activity → SND unmatched alert (proceed to Phase 2)
   • DimValidation_{DimensionName}_Activity → DimValidation alert (different issue — see note below)
3. Parse the description/summary for: affected date(s), pipeline name, alert details
4. Validate the incident is from Triage (ODIN Infra) team — skip if not
5. For batch processing (multiple ICMs): call mcp_icm_get_incident_details_by_id in parallel for all IDs

DimValidation alerts indicate that a standard value in DimMapping CSV is missing from the PLSCoreData resource file. This is NOT an unmatched raw value problem. Action: check recent DimMapping CSV changes, identify the missing standard value, and add it to PLSCoreData. Mark as ❌ in review file.

Phase 2: Query Unmatched Values from ClickHouse

1. Look up the dimension in references/dimension-config.md to get:

   • ClickHouse table name and query columns (see Quick Reference table — column names differ from CSV for some dimensions)
   • Dimension type (Legacy or Standard)

2. Query ClickHouse using the correct query pattern (see dimension-config.md for template):

   • Always query the LATEST ProcessDate only (not from alert date — ClickHouse stores recent days, we want current state)
   • Always exclude __DUMMY__ rows (placeholder rows, not real unmatched values)
   • Use mcp_alwayson-mcp_submit_clickhouse_query. Default database is ClickHouse WW (databaseId as 159, doraEndpoint as "Superset").

3. For batch processing (multiple ICMs): query all dimensions in parallel, then proceed to Phase 2.5.

Why latest date? The snd_alert tables store rolling recent data. Querying only the latest date shows which values are currently unmatched. Values that appeared in older dates but not the latest may have already been resolved.

Phase 2.5: Output Intermediate Review File (MANDATORY)

Before making ANY CSV edits or sending PM emails, generate a review file at .tmp/unmatched-review-{YYYY-MM-DD}.md.

📖 File format & template: See references/review-template.md

For each dimension:

1. Classify each unmatched value as ✅ auto-resolvable, ⏳ needs PM, or ❌ skip
2. For ✅ values: compute hash keys and include proposed CSV rows
3. For ⏳ values: include PM email draft with correct recipients per references/pm-routing.md

STOP here — wait for user to review and confirm before proceeding.

Phase 3: Execute Approved Actions

After user confirms the review file:

1. Auto-resolved values (✅): proceed directly to Phase 4 CSV updates
2. PM-needed values (⏳): tell user to send the email drafts from the review file, then wait for PM replies
3. Skipped (❌): no action

📖 PM routing & email template: See references/pm-routing.md

When user returns with PM's reply (filled standard values), proceed to Phase 4.

Phase 4: Update Dimension Files

After user confirms the review file and/or returns with PM's reply:

Phase 4a: Determine dimension type

Look up dimension type in references/dimension-config.md:

• Legacy dimension → Phase 4b (DimMapping CSV in NitroUMDP)
• Standard dimension → Phase 4c (StandardDimension CSV in NitroUMDP)

Phase 4b: Legacy Dimension — Update DimMapping CSV

File location: sources/dev/NitroUMDP/src/AlwaysOn/PipelineBuilders/Dimension/MappingFiles/DimMapping_{DimensionName}.csv

For each new mapping row from PM reply:

1. Compute hash key using mcp_alwayson-mcp_get_dimension_long_key:
   • Single-column dimensions (e.g., FeatureActionType): values: ["{raw_value}"]
   • Multi-column dimensions (e.g., AppPlatform): values: ["{col1}", "{col2}", "{col3}"], multiColumn: true
2. Look up the standard dimension key:
   • If standard value is "Unknown" or empty → key = -1
   • Otherwise compute: values: ["{standard_value}"] → get key
3. MANDATORY: Verify standard value exists in PLSCoreData (skip this step only if standard value is "Unknown"):
   • Look up the PLSCoreData resource file path from references/dimension-config.md (PLSCoreData Resource Path column)
   • The PLSCoreData repo should be cloned locally (default: same parent directory as NitroUMDP; ask user for path if not found)
   • Full local path: {PLSCoreData_repo_root}/sources/dev/PLSCoreDimensions/Dimensions/{resource_path} (or PLSCoreData root for Product dimension)
   • Read the resource file and search for the standard value (case-insensitive)
   • If found: proceed to step 4 — the standard value already exists, no PLSCoreData update needed
   • If NOT found: the standard value is NEW — flag it for PLSCoreData update:
     1. Create a new branch in PLSCoreData repo
     2. Append the new standard value to the resource file (follow existing format — check header row for columns)
        • IMPORTANT: Do NOT add a trailing empty line after the last value. Match the original file's EOF style (most resource files end with no newline at EOF). Read the last bytes of the file before editing to confirm.
     3. Create a PR in PLSCoreData repo (this PR must merge BEFORE or alongside the NitroUMDP PR)
   • Never skip this check — adding a DimMapping row referencing a non-existent standard value will cause DimValidation alerts
4. Append row to CSV (tab-separated): {hash_key}\t{raw_col1}\t...{raw_colN}\t{dimension_key}\t{standard_value}

CSV column order — refer to references/dimension-config.md for each dimension's exact column structure.

Phase 4c: Standard Dimension — Update StandardDimension CSV

File location: sources/dev/NitroUMDP/src/AlwaysOn/configs/StandardDimension/IDEAS{DimensionName}.csv

For each new mapping row:

1. Compute the dimension key: mcp_alwayson-mcp_get_dimension_long_key with values: ["{standard_value}"]
   • If standard value is "Unknown" or empty → key = -1
2. Append row (tab-separated): {raw_value}\t{standard_value}\t{key}

Phase 5: Create PR and Verify

1. NitroUMDP PR (always needed):

   • Read docs/pull_request_template.md for PR template
   • Create PR using helix CLI:

   npx -y bun .github/skills/telemetry/helix.ts create-pr --title "[Dimension] Add {DimensionName} mappings for {date}" --description-file pr-desc.md
   

2. PLSCoreData PR (only for Legacy dimensions with NEW standard values):

   • Clone/navigate to PLSCoreData repo
   • Update the dimension resource file
   • Create PR in PLSCoreData repo

3. Post-deployment verification (after PR merges and pipeline reruns):

   • Re-query ClickHouse to confirm unmatched values are resolved
   • Same query as Phase 2 — expect zero or fewer results

Reference

• Dimension Configuration — dimension types, columns, file paths, ClickHouse tables
• PM Routing & Email Templates — dimension→PM mapping, email template, Group B self-lookup rules
• Review File Template — .tmp/unmatched-review-*.md output format
• DRG.py (legacy tool): sources/dev/Tools/DimensionResourceGenerator/MappingUpdate/DRG.py
• PLSCoreData repo: https://o365exchange.visualstudio.com/O365%20Core/_git/PLSCoreData

Self-Assessment

Usage Validation

This skill should trigger when user mentions:

• "unmatched dimension", "new dimension value", "AggreUnmatched"
• "dimension value management", "dimension mapping"
• "EmptyCheck failure", "snd_alert"
• ICM incident ID with dimension-related alert
• "add dimension mapping", "update DimMapping"

Test Scenarios

Verification Checklist

• ICM context extracted correctly (dimension name, date)
• ClickHouse query returns unmatched values
• Email drafted with correct recipients and format
• Hash keys computed correctly (single vs multi-column)
• CSV updated with correct column order and tab separation
• PR created following template

Known Limitations

• ClickHouse query requires appropriate cluster access (not available via SAW/debug accounts)
• PLSCoreData repo must be cloned separately for Legacy dimension resource file updates
• PM email is drafted but must be sent manually by the user (no automated email)
• Group B self-lookup is heuristic — always review suggested matches before applying
• Hash computation via MCP tool — verify results match existing CSV entries before bulk updates
• mcp_icm_get_ai_summary often returns empty — use mcp_icm_get_incident_details_by_id as primary method
• DimValidation alerts (e.g., DimValidation_Product_Activity) are different from AggreUnmatched — they indicate PLSCoreData resource file gaps, not new raw values