metric_standardization.txt

Standardizing Ranked Metrics Across an EHR Migration

Rebuilding a national rankings submission as governed data products while the underlying EHR was being replaced

Discipline: Data product strategy and mapping
Domains: Healthcare · AI and Automation
Timeframe: 2025 to 2026
Status: Approved as the scoping and mapping approach. Inventory completion and gap remediation moved to the analytics and EHR teams.

what_was_this_project.txt

What was this project?

Standardizing Ranked Metrics Across an EHR Migration is a scoping and mapping design for a large pediatric health system's annual national rankings submission, written while the organization was replacing Cerner with Epic.

National hospital rankings are a submission with a fixed deadline and a wide surface. Metrics span multiple specialties, each with its own clinical reviewers, and each number has to be defensible to the section chief who signs it off.

The submission had always been produced from legacy reports built on the outgoing EHR. Those reports were being retired underneath it. That is the whole problem in one sentence: a deadline that does not move, running on a data platform that is being replaced.

what_problem_did_this_solve.txt

What problem did this solve?

The logic that defined every ranked metric lived inside individual legacy reports rather than in any shared layer, so replacing the EHR meant that nobody could say with confidence what the organization's own metrics were.

There was no centralized metric inventory and no data dictionary. Metric logic was embedded in reports and therefore not reusable. Ownership was unclear, which meant that when two numbers disagreed there was no one whose answer settled it.

The process around it was email driven and coordination heavy: requests, analyst pulls, validation, multi step clinical review, then rework when a discrepancy surfaced late. Every cycle rebuilt work that the previous cycle had already done.

Metric outputs were inconsistent and non repeatable between teams.
Submission timelines slipped because validation was manual.
The work depended on specific analysts and on tribal knowledge that was not written down anywhere.
Epic support and data availability varied by specialty and had not been validated.
Late stage discrepancies drove rework at exactly the point in the calendar where there was no slack.

The risk was not just a missed deadline. It was submitting a number that could not be defended.

what_i_observed.txt

What did I observe?

Working one diagnosis category end to end revealed that the mapping problem was semantic rather than technical, and that a field by field translation from the old system to the new one would have produced confident and wrong numbers.

I traced a single ICD-10 category through the legacy system as a worked example. Three things came out of it that changed the shape of the whole initiative.

Diagnosis codes were not stored in one queryable field. Getting to a code meant traversing a diagnosis table into a nomenclature table that held the actual code value, then out to an encounter table for visit context. Anyone expecting a single diagnosis column would have written a query that returned nothing and concluded the data was missing.
A code family is not a database value. The category I traced represents a set of child codes, and no row anywhere contains the family as a literal. A metric defined against the family has to be expressed as a pattern over its children, and getting that wrong silently changes the patient population.
The old and new data models differ in structure, not just in naming. Mapping by matching field names would have produced something that ran and returned plausible numbers for the wrong cohort. Mapping had to be done on semantic meaning and validated at the level of individual patients.

That last point is why patient level validation became a hard requirement rather than a nice to have. Counts matching is not evidence. Two different cohorts can produce the same count.

The other observation was organizational. Work arrived as tickets rather than as a programme, which is why the same mapping questions were being answered repeatedly by different people with different answers.

my_role.txt

What was my role?

I wrote the scoping brief that framed the initiative, designed the mapping architecture and the governance lifecycle, and produced the worked example that proved the approach on a real diagnosis category.

Wrote the problem framing artifact that set the scope boundary and gave data, clinical, and EHR teams a shared account of why the work existed.
Defined what was in scope and, just as deliberately, what was not: no dashboard layer, no predictive modelling, no replacement of non ranked reporting, and no upstream workflow redesign unless a data gap forced it.
Designed the three layer mapping architecture: source extraction, terminology normalization, then the dimensional model.
Designed the seven step metric lifecycle and assigned an accountable role to each step, so ownership was set at definition time rather than argued about at validation time.
Built the end to end worked example on one diagnosis category, including the extraction path, the code family pattern, the mapping blueprint, and the validation levels.
Named the critical risk in writing: without a complete metric inventory, the scope itself was unknown, and every estimate downstream was a guess.
Wrote the open questions, including what share of metrics Epic could actually support, and whether the target was Clarity, Caboodle, or a hybrid.

I did not build the pipelines, complete the metric inventory across all specialties, or perform the specialty by specialty validation. Those were the next phase and belonged to the analytics and EHR teams.

the_proposed_solution.txt

What was the proposed solution?

The proposed solution is to move metric logic out of individual reports and into a governed metric layer, reached through a three layer mapping architecture and maintained by a seven step lifecycle with a named owner at every step.

The target state has three properties: metric definitions are centralized and reusable rather than embedded in reports, pipelines are repeatable rather than rebuilt per request, and reporting is native to the new EHR's data platform rather than bolted onto an outgoing one.

The structured workflow for each metric is deliberately boring. Define the metric, map it to source data, validate the logic and the output, then confirm the new platform can actually support it. The fourth step is the one that usually gets skipped and is the one that causes the late discrepancy.

Two guardrails were written into the approach. Definitions get approved before anything is built, and no new logic is embedded in a downstream report. Both exist to stop the organization recreating the exact problem it was trying to escape.

system_design.txt

How was the system designed?

The architecture separates extraction, terminology normalization, and dimensional modelling into three distinct layers so that a change in the source system does not force a rewrite of every metric built on top of it.

Layer one gets the data out of the source system, which for diagnosis data means traversing several tables rather than reading one field. Layer two normalizes the result into a standard code representation, so a code family resolves to its children consistently instead of being reimplemented per report. Layer three is the dimensional model the metrics are actually built against.

Keeping terminology mapping separate from analytic logic is the load bearing decision. Fold them together and every metric carries its own private copy of the code logic, which is exactly how the organization ended up with logic trapped in reports the first time.

The lifecycle diagram is the other half. It assigns accountability per step rather than per project, so a metric always has someone who defines it, someone who validates it, and someone who approves it.

Validation was specified at four levels, and the last two are the ones that matter:

Output: the counts match.
Logic: the rules match the written definition.
Patient: the same individual patients appear, not merely the same number of them.
Operational: a clinician reviewing the result agrees it is clinically accurate.

Standardizing Ranked Metrics Across an EHR Migration: system design. The full description is in the diagram's alternative text.

why_this_design.txt

Why was it designed this way?

The approach prioritizes standardization over speed, because the fastest available path was to rebuild the legacy reports one for one in the new system, and that path would have carried every existing problem across the migration intact.

Design choices and the alternatives considered
Choice	Alternative considered	Why the choice won
Governed metric layer	Rebuilding each legacy report in the new platform	A one for one rebuild is faster and reproduces the original fault: logic trapped in reports, no shared definitions, and no owner. The migration was the only moment when fixing it was cheaper than not fixing it.
Map on semantic meaning	Field name matching between the two data models	The two models differ structurally. Name matching produces queries that run, return plausible numbers, and describe the wrong patient cohort. That failure is invisible until someone checks individual patients.
Patient level validation required	Validating that record counts match	Two different cohorts can produce identical counts. Only patient level comparison distinguishes a correct mapping from a coincidence.
Terminology mapping separated from analytic logic	One transformation per metric	Separated, a coding change is fixed once. Combined, it has to be found and fixed in every metric that touched it, which is the situation the initiative existed to end.
Metrics standardized before any dashboard work	Building the visualization layer in parallel	A dashboard on ungoverned definitions makes bad numbers more visible and harder to retract. Dashboards were explicitly deferred to a later phase.
Document ambiguity rather than resolve it early	Making a reasonable assumption and moving on	Where a definition was genuinely contested, recording the ambiguity kept it visible for the clinical owner to settle. Quietly resolving it would have buried a decision that a section chief needed to make.

The critical risk I named first was that no complete metric inventory existed. Without it the scope was unknown, so every timeline and resourcing estimate was a guess dressed as a plan. Naming that as the top risk rather than producing a confident schedule was the most useful thing in the brief.

implementation_plan.txt

How would it be implemented?

The plan front loads the inventory, because until the organization knows how many metrics it reports and which of them the new platform can support, nothing else can be estimated honestly.

Complete the metric inventory across every in scope specialty and decompose the existing report logic into written definitions. This is the phase that converts an unknown scope into a known one.
Validate feasibility in the new platform metric by metric, using the structured mapping approach, and record which metrics are fully supported, partially supported, or blocked.
Define the governance model and assign an owner to each metric across its lifecycle, before any build work starts.
Build the reusable extraction and normalization layers, validated at patient level against the legacy source on real encounters.
Prioritize gap remediation against the submission deadline, so the metrics that are both blocked and required get attention first.

Deployment follows the organization's existing development, test, and production promotion path, with ongoing validation rather than a single sign off at the end.

expected_impact.txt

What impact was expected?

Every figure below is a projection from the design rather than a measured result, and the most honest statement in the original brief was that the scope could not be quantified until the metric inventory existed.

Projected effects and how each would be measured
What would change	Baseline today	How it would be measured
The organization can state its own metric definitions	No centralized inventory or data dictionary exists. Definitions live inside individual reports.	Share of ranked metrics with an approved written definition and a named owner.
Metric logic becomes reusable	Logic is embedded per report and reimplemented per request.	Number of metrics served from the shared layer versus rebuilt per report.
Late stage discrepancies stop driving rework	Disagreements surface during clinical review, near the deadline.	Count of discrepancies raised after clinical review begins, cycle over cycle.
Submission stops depending on specific people	The process relies on particular analysts and undocumented knowledge.	Share of metrics reproducible from documentation alone, without the original analyst.
Feasibility is known before the deadline, not at it	Platform support for each metric is unvalidated and varies by specialty.	Share of metrics with a validated feasibility status, and the size of the remediation backlog.

I did not project a time saving for the submission cycle. With no inventory, the denominator was unknown, and a number produced on top of an unknown denominator would have been exactly the kind of undefendable figure the initiative existed to eliminate.

what_happened.txt

What happened?

The scoping brief and the mapping approach were approved as the way to run the initiative, and inventory completion and gap remediation moved to the analytics and EHR teams.

Three decisions were settled by the brief: standardize metrics and align them to the new platform before building any dashboards, treat the foundational data product of definitions, mapping, and governance as the deliverable, and use structured discovery and mapping workflows rather than ticket by ticket work.

I designed and specified it and produced the worked example that showed it held up on real data. I did not complete the inventory, build the pipelines, or run the submission, and I have no measured outcome to report. The open questions I documented, particularly what share of metrics the new platform could support and whether the target should be the operational or the analytical data model, were still open when the design left my hands.

references.txt

References

Working on something like this?

Bring me the problem before the tool selection.

Schedule a Vibe Check ↗