Lesson 1Supporting dimension tables: provider, location, facility, code lookupsThis section details supporting dimension tables like provider, location, facility, and code lookups. It includes hierarchies, slowly changing attributes, and how thoughtfully designed dimensions enhance filtering, grouping, and detailed analyses in clinical data.
Provider and specialty dimensionsLocation and facility hierarchiesClinical code and value set lookupsManaging slowly changing dimensionsLesson 2Encounter/Visit entity: admit/arrival, discharge, visit type and timestampsThis section outlines the encounter or visit entity, incorporating admission, arrival, discharge, visit type, and timestamps. It explains connecting encounters to patients, locations, and payers, aiding metrics like length-of-stay and throughput in healthcare settings.
Encounter types and classificationsAdmission, transfer, and discharge timesLinking encounters to patientsVisit grouping and episode logicLesson 3Canonical patient entity: identifiers, demography, merges and survivorshipThis section establishes a canonical patient entity for analytics, addressing identifiers, demographics, merges, and survivorship protocols. It covers mastering from multiple sources, duplicate management, and safeguarding historical identity shifts securely.
Core patient identifiers and keysDemographic attributes for analyticsPatient matching and merge logicSurvivorship and source precedenceLesson 4Procedures and orders entities: procedure codes, order IDs, performing providerThis section addresses modelling procedures and orders, including procedure codes, order identifiers, and performing providers. It details linking orders to results, scheduling, and status, while bolstering analytics for utilisation, quality, and throughput.
Order header and line item structureProcedure and order coding standardsLinking orders, procedures, and resultsOrder status, timing, and priorityLesson 5Keys and relationships: patient_id, encounter_id, result linking and referential integrityThis section elaborates on how patient, encounter, and result keys uphold referential integrity in clinical datasets. It discusses natural versus surrogate keys, cascading rules, and approaches for late-arriving or amended records.
Patient and encounter key designResult and order linkage patternsSurrogate keys vs natural identifiersCascades, deletes, and orphan recordsLesson 6Diagnoses and problem list entities: fields, code system, severity, onset and resolutionThis section centres on diagnoses and problem list entities, featuring fields for codes, status, severity, onset, and resolution. It tackles coding systems, chronic versus acute issues, and managing revisions or inactivation over time.
Core diagnosis and problem fieldsICD, SNOMED, and other code systemsOnset, resolution, and episode timingActive, historical, and resolved problemsLesson 7Lab result entity design: test code, specimen, collection time, result value, units, reference range, statusThis section specifies lab result entity design, encompassing test codes, specimens, collection times, values, units, reference ranges, and statuses. It handles abnormal flags, panels, and corrections or repeats for analytics purposes.
Test, panel, and component structureSpecimen type and collection detailsResult value, units, and reference rangeResult status, flags, and correctionsLesson 8Schematic examples: star schema for analytics and entity relationship mappingThis section presents star schemas for clinical analytics and compares them to entity-relationship diagrams. Participants see how facts, dimensions, and relationships align with EHR concepts to enable efficient analytic queries.
Clinical fact and dimension tablesStar vs snowflake in healthcareMapping EHR entities to factsBridging many-to-many clinical linksLesson 9Principles of analytics data modeling vs transactional modelingThis section contrasts analytic and transactional data models in healthcare. It covers normalisation, denormalisation, query patterns, and workload traits, informing choices that optimise performance, adaptability, and data integrity.
OLTP vs OLAP workloads in EHRsNormalization and denormalization tradeoffsSlowly changing clinical attributesModeling for longitudinal patient views
