Lesson 1Supporting Measure Tables: Provider, Location, Facility, Code LookupsThis section explains supporting measure tables like provider, location, facility, and code lookups. It covers levels, slowly changing details, and how good designs improve filtering, grouping, and detailed analysis in clinical data.
Provider and specialty dimensionsLocation and facility hierarchiesClinical code and value set lookupsManaging slowly changing dimensionsLesson 2Encounter/Visit Part: Admit/Arrival, Discharge, Visit Type, and Time StampsThis section describes the encounter or visit part, including coming in, arriving, leaving, visit type, and time stamps. It covers linking encounters to patients, places, and payers, and supports measures like stay length and flow in facilities.
Encounter types and classificationsAdmission, transfer, and discharge timesLinking encounters to patientsVisit grouping and episode logicLesson 3Standard Patient Part: Identifiers, Demography, Merges, and SurvivorshipThis section defines a standard patient part for analysis, covering identifiers, people details, merges, and survivorship rules. It explains mastering from different sources, handling duplicates, and keeping historical changes safe.
Core patient identifiers and keysDemographic attributes for analyticsPatient matching and merge logicSurvivorship and source precedenceLesson 4Procedures and Orders Parts: Procedure Codes, Order IDs, Performing ProviderThis section covers modeling procedures and orders, including codes, order numbers, and who performs them. It explains linking orders to results, scheduling, and status, while supporting analysis on use, quality, and flow.
Order header and line item structureProcedure and order coding standardsLinking orders, procedures, and resultsOrder status, timing, and priorityLesson 5Keys and Links: patient_id, encounter_id, Result Linking, and Data IntegrityThis section details how patient, encounter, and result keys keep data links strong across clinical sets. It covers natural vs made-up keys, rules for changes, and ways to handle late or fixed records.
Patient and encounter key designResult and order linkage patternsSurrogate keys vs natural identifiersCascades, deletes, and orphan recordsLesson 6Diagnoses and Problem List Parts: Fields, Code System, Severity, Onset, ResolutionThis section focuses on diagnoses and problem list parts, including fields for codes, status, seriousness, start, and end. It addresses code systems, long-term vs short problems, and handling changes or stopping over time.
Core diagnosis and problem fieldsICD, SNOMED, and other code systemsOnset, resolution, and episode timingActive, historical, and resolved problemsLesson 7Lab Result Part Design: Test Code, Specimen, Collection Time, Result Value, Units, Reference Range, StatusThis section details design for lab result parts, including test codes, samples, collection times, values, units, normal ranges, and statuses. It addresses flags for abnormal, groups, and handling fixed or repeated results for analysis.
Test, panel, and component structureSpecimen type and collection detailsResult value, units, and reference rangeResult status, flags, and correctionsLesson 8Diagram Examples: Star Schema for Analysis and Part Link MappingThis section introduces star schemas for clinical analysis and compares them to part-relationship diagrams. Learners see how facts, measures, and links map to EHR ideas and support fast analysis queries.
Clinical fact and dimension tablesStar vs snowflake in healthcareMapping EHR entities to factsBridging many-to-many clinical linksLesson 9Principles of Analysis Data Modeling vs Transaction ModelingThis section compares analysis and transaction data models in healthcare. It explains normalizing, denormalizing, query ways, and work loads, guiding choices that balance speed, flexibility, and data quality.
OLTP vs OLAP workloads in EHRsNormalization and denormalization tradeoffsSlowly changing clinical attributesModeling for longitudinal patient views
