Training Effectiveness Dashboard Pipeline (SP_GET_TRAINER_EFFECTIVENESS)
Snowflake monthly trainer-effectiveness procedure that returns additive survey, observation, ESAT, and in-training attrition components for Power BI reporting.
Overview
Builds a trainer-by-month spine for the current year and enriches each row with profile attributes, certifications, and additive metric components from multiple source systems.
Business Purpose
Provide a governed monthly training-effectiveness dataset that supports accurate re-aggregation of trainer performance metrics in Power BI across arbitrary date and org slices.
Technology Stack
Business Impact
Incorrect trainer assignment or additive-component logic can distort comparative trainer scoring, month-over-month trend analysis, and training attrition interpretation.
Scope Window
Current calendar year only, from January through current month, with no future months returned.
Result Grain
One row per trainer per month based on trainers derived from majority-manager assignment during trainee training windows.
Metric Design
Returns additive totals and counts, not pre-averaged values, so BI measures recalculate correctly after slicing.
Trainer Filter
Final rows are constrained by approved trainer job titles in DATASTORE.MODELED.TBL_JOBTITLE_TE.
Power BI Data Contract
| Column Group | Fields | Usage |
|---|---|---|
| Key and Profile | MONTH_DATE, TRAINER, JOBTITLE, PROFITCENTERNAME, WORKLOCATIONNAME, MANAGERLOGONUSERID, VERTICALNAME | Trainer-month slicing, organization rollups, and leadership hierarchy context. |
| Certifications | PPQ_STATUS, ALC_STATUS | Readiness and accreditation segmentation for trainer comparisons. |
| Survey Components | SURVEY_SCORE_TOTAL, SURVEY_RESPONSE_COUNT | Post-training survey average using DIVIDE(SUM score, SUM count). |
| Observation Components | OBS_SCORE_TOTAL, OBS_COUNT | QA observation average using DIVIDE(SUM score, SUM count). |
| ESAT Components | ESAT_SCORE_TOTAL, ESAT_RESPONSE_COUNT | ESAT average using DIVIDE(SUM score, SUM respondents). |
| Attrition Components | TERMINATED_TRAINEE_COUNT, TRAINEE_COUNT | In-training attrition rate using DIVIDE(SUM terminated, SUM trainees). |
End-to-End Processing Flow
- Initialize runtime date boundaries from current-year start to current-month end.
- Build in-year trainee scope from EngageHR training-end records and derive earliest training start needed for timesheet extraction.
- Stage timesheet activity-115 training seconds and resolve each trainee's majority manager from SCD2 overlap logic in DIM_EMPLOYEETS.
- Build trainee-level started-training and terminated-during-training flags by trainee and training-end month.
- Build PPQ and ALC certification lookups for trainer enrichment.
- Stage and normalize end-of-class survey, ESAT, and QA observation additive components.
- Aggregate trainer-month survey and combined observation/attrition metrics.
- Generate month and trainer spines, join profile plus certifications plus metrics, apply trainer job-title allowlist, and return final resultset.
- Trainee scope requires TRAININGENDDATE between current-year start and current-month end.
- Month spine covers January through current month only, preventing future-month rows.
- Trainer population is derived from majority-manager assignment for in-scope trainees, not requisition assignment.
- Final output further constrains trainers to approved job titles from TBL_JOBTITLE_TE.
This design guarantees consistent monthly row coverage for each in-scope trainer while preserving strict current-year boundaries.
- Majority trainer is selected by maximum overlap days between each trainee's training window and manager SCD rows.
- Tie-breaker for overlap ties uses the most recent manager-assignment STARTDATE.
- Started-training requires summed ACTIVITYID 115 seconds greater than zero from TimeQey timesheets.
- Terminated-training requires started-training plus TERMINATIONDATE before TRAININGENDDATE.
These rules align training outcomes to the trainer with the highest in-window managerial responsibility.
- Survey, observation, ESAT, and attrition outputs are additive components intended for SUM-based recomposition in BI tools.
- Procedure intentionally avoids pre-averaged metrics to prevent mathematically incorrect cross-month averages.
- Months with no source activity remain present because metrics are left-joined onto a trainer-month spine.
- No-activity months yield null additive fields, allowing report logic to handle blanks versus true zero values.
Recommended measures: Post Training Survey, Obs Scores, ESAT Score, and Attrition Rate should each use DIVIDE over summed additive columns.
Primary Dependencies and Consumers
Business outcomes: reliable trainer-month comparability, consistent cross-source attribution, and re-aggregatable performance metrics for the Training Effectiveness Power BI dashboard.