METRO Procedure Dependency Reference
Technical map of METRO modeled procedures showing read and write dependencies, task-based execution order, timeline transformation logic, and reporting views that consume cleaned and corrected presence outputs.
Overview
Separates reporting extract procedures from timeline-refresh procedures so status data can be rebuilt and validated before downstream interval and call-detail reporting runs.
Business Purpose
Provide trusted presence and session activity context for reporting by controlling how raw status history is cleaned, corrected, sliced, and consumed.
Technology Stack
Business Impact
Broken ordering or stale timeline tables can misstate 30-minute interval occupancy, session overlap behavior, and queue-level performance reporting.
Procedure Footprint
6 modeled procedures: 4 table-refresh procedures and 2 report-return procedures.
Task Graphs
2 active graphs: status split path and clean/corrected timeline path.
Primary Targets
4 modeled timeline tables refreshed through MERGE or delete/insert patterns.
Reporting Views
3 presence views consume clean and session-corrected timeline outputs.
Procedure Dependency Matrix
| Procedure | Returns | Primary Write Target | Primary Read Dependencies |
|---|---|---|---|
| SP_MSD_CALLDETAIL | TABLE | None (result set only) | OCSESSIONS, SESSIONPARTICIPANTS, SYSTEMUSERS, VW_TBLIDLIST, QUEUE, closure reason and disposition objects |
| SP_MSD_INTERVALDATA | TABLE | None (result set only) | OCSESSIONS, SESSIONPARTICIPANTS, SYSTEMUSERS, VW_TBLIDLIST, QUEUE, AGENTSTATUSHISTORIES_SPLIT |
| SP_UPDATEAGENTSTATUSHISTORY | VARIANT | AGENTSTATUSHISTORIES_SPLIT | AGENTSTATUSHISTORIES, PRESENCES, SYSTEMUSERS, VW_TBLIDLIST |
| SP_REFRESH_CLEAN_TIMELINE | VARIANT | AGENTSTATUSHISTORIES_CLEAN_TIMELINE | AGENTSTATUSHISTORIES |
| SP_REFRESH_CORRECTED_TIMELINE | VARIANT | AGENTSTATUSHISTORIES_SESSION_CORRECTED_TIMELINE | AGENTSTATUSHISTORIES_CLEAN_TIMELINE, SESSIONPARTICIPANTS, OCSESSIONS |
| SP_UPDATEAGENTSTATUSHISTORY_CLEAN_TIMELINE | VARIANT | AGENTSTATUSHISTORIES_SPLIT_CLEAN_TIMELINE | AGENTSTATUSHISTORIES_CLEAN_TIMELINE, SYSTEMUSERS, VW_TBLIDLIST |
Run-logging note: all VARIANT-return procedures call DATASTORE.MODELED.SP_LOG_PROCEDURE_RUN for execution telemetry.
Execution Graphs and Ordering
| Graph | Root Task (Schedule) | Children | Primary Outcome |
|---|---|---|---|
| Graph A: Raw Split Path | TASK_UPDATEAGENTSTATUSHISTORY (CRON 7,37 hourly, US/Eastern) | TASK_UPDATEAGENTSTATHISTORY_CHILD1_AUXREPORT | Refresh AGENTSTATUSHISTORIES_SPLIT and run auxiliary reporting refresh. |
| Graph B: Clean and Corrected Path | TASK_REFRESH_CLEAN_TIMELINE (CRON 10,40 hourly, UTC) | TASK_UPDATEAGENTSTATUSHISTORY_CLEAN_TIMELINE and TASK_REFRESH_CORRECTED_TIMELINE | Rebuild clean timeline, then independently create split-clean slices and session-corrected timeline. |
- SP_REFRESH_CLEAN_TIMELINE must complete before SP_REFRESH_CORRECTED_TIMELINE because corrected output depends on clean timeline rows.
- SP_UPDATEAGENTSTATUSHISTORY_CLEAN_TIMELINE and SP_REFRESH_CORRECTED_TIMELINE can run independently after clean refresh success.
- For full manual freshness before report extraction, run: SP_UPDATEAGENTSTATUSHISTORY, SP_REFRESH_CLEAN_TIMELINE, SP_UPDATEAGENTSTATUSHISTORY_CLEAN_TIMELINE, SP_REFRESH_CORRECTED_TIMELINE, then report procedures.
Modeled Tables and Reporting View Consumption
| Modeled Table | Written By | Used By |
|---|---|---|
| AGENTSTATUSHISTORIES_SPLIT | SP_UPDATEAGENTSTATUSHISTORY | SP_MSD_INTERVALDATA |
| AGENTSTATUSHISTORIES_CLEAN_TIMELINE | SP_REFRESH_CLEAN_TIMELINE | SP_UPDATEAGENTSTATUSHISTORY_CLEAN_TIMELINE, SP_REFRESH_CORRECTED_TIMELINE, VW_PRESENCE_REPORT_CLEAN, VW_CLEAN_TIMELINE_MONITOR |
| AGENTSTATUSHISTORIES_SESSION_CORRECTED_TIMELINE | SP_REFRESH_CORRECTED_TIMELINE | VW_PRESENCE_REPORT_SESSION_CORRECTED |
| AGENTSTATUSHISTORIES_SPLIT_CLEAN_TIMELINE | SP_UPDATEAGENTSTATUSHISTORY_CLEAN_TIMELINE | Interval-slice reporting on clean timeline path |
Reference note: VW_TBLIDLIST is consumed in multiple procedures but its DDL source is maintained outside this repository snapshot.
- SP_MSD_CALLDETAIL returns agent-level call and chat detail rows with queue, closure, and disposition context.
- SP_MSD_INTERVALDATA returns one row per agent per 30-minute EST interval with blended call and status metrics.
- Both procedures write only temporary tables and return report-ready result sets without persistent target-table writes.
- Date logic applies EST windowing safeguards to reduce UTC boundary drift in report extracts.
Operational role: on-demand extraction procedures that rely on prior refreshes of split and timeline structures.
- SP_UPDATEAGENTSTATUSHISTORY reads raw status history and PRESENCES, then MERGEs generated 30-minute slices into AGENTSTATUSHISTORIES_SPLIT.
- SP_UPDATEAGENTSTATUSHISTORY_CLEAN_TIMELINE uses the same slicing approach against clean START_TIME and END_TIME rows.
- Both procedures use generator-based interval slicing and object-id filters from VW_TBLIDLIST.
- Clean timeline slicing does not require PRESENCES lookup because normalized presence values already exist in source rows.
Reliability focus: ensure expected date windows are written and duplicates are prevented by MERGE keys.
- SP_REFRESH_CLEAN_TIMELINE rebuilds a 7-day rolling timeline with dedupe, boundary normalization, and capped end-time logic.
- SP_REFRESH_CORRECTED_TIMELINE overlays valid session envelopes to force busyDND during active session windows.
- Corrected timeline logic splits status rows into overlap variants and preserves original presence value fields for traceability.
- Session validation excludes null-end, zero-duration, and over-24-hour sessions before overlap calculations.
Reporting outcome: clean and corrected presence views offer auditable, analyst-friendly interval interpretation.
Operational Validation Checklist
- Verify SP_REFRESH_CLEAN_TIMELINE success before running or reviewing SP_REFRESH_CORRECTED_TIMELINE output.
- Confirm AGENTSTATUSHISTORIES_CLEAN_TIMELINE row counts for the rolling 7-day window are stable or increasing as expected.
- Validate that AGENTSTATUSHISTORIES_SESSION_CORRECTED_TIMELINE was refreshed for the same 7-day range.
- Confirm split-table writes occurred for expected dates in AGENTSTATUSHISTORIES_SPLIT or AGENTSTATUSHISTORIES_SPLIT_CLEAN_TIMELINE.
- Spot-check VW_PRESENCE_REPORT_CLEAN and VW_PRESENCE_REPORT_SESSION_CORRECTED for expected EST interval outcomes.
- Run known-date checks of SP_MSD_CALLDETAIL and SP_MSD_INTERVALDATA for expected queue and domain coverage.