HIM 550 Module 4 Warehousing Short Paper Example

What Happens at 2 A.M.: The Nightly Load and the Meaning of Hospital Data

[Student Name]

Southern New Hampshire University

HIM 550: Data Management and Data Quality

Warehousing Short Paper

[Instructor Name]

[Date]

The organization, setting and figures below are a composite written as a model document. No real employer, client, colleague or patient is described.

What Happens at 2 A.M.: The Nightly Load and the Meaning of Hospital Data

Every night at 2 a.m., a set of scripts copies the previous day's registrations, discharges, diagnoses and bed movements from Cypress Hollow Medical Center's source systems into its data warehouse. Every report leaders see the next morning comes from that copy. The quality assessment in this course found several problems that began not in the source systems but in the load itself. This paper explains how the warehouse is built, what metadata it lacks and how its design and load process should change.

What a Warehouse Does

A data warehouse gathers data from several operational systems into one store designed for analysis rather than daily transactions. Operational systems are built to register a patient or post a charge quickly; a warehouse is built to answer questions across months and departments. Kimball and Ross (2013) describe the dimensional approach most warehouses use: fact tables that record events, such as one row per discharge, surrounded by dimension tables that describe them, such as the patient, the unit, the date and the admission source. Analysts join facts to dimensions to count, group and compare.

Health systems have shown what careful warehouses can do. Chute et al. (2010) described Mayo Clinic's Enterprise Data Trust, which integrated clinical, administrative and research data while tying values to shared vocabularies so their meaning survived the move. Murphy et al. (2010) described i2b2, a platform that lets researchers query warehouse data across institutions. Both depended on documented rules for how source values are transformed, which is the part Cypress Hollow's warehouse lacks.

How Data Move Each Night

The nightly process has three stages. Extraction pulls new and changed rows from registration, the clinical record and the bed management system into a staging area. Transformation cleans, recodes and joins those rows: codes are translated to descriptions, dates are standardized and records from different systems are matched on encounter number. Loading writes the results into the fact and dimension tables. Each stage makes decisions about data, yet at Cypress Hollow those decisions live only inside scripts written by an analyst who left in 2019.

Three Quiet Changes to Meaning

Reading the scripts line by line turned up three problems. First, a transformation rule assigns the value Other to any blank admission source. Reports therefore show a large Other group, and no one can tell missing data from true other sources. Second, when unit codes changed on March 14, the unit dimension was simply updated in place, so earlier discharges now appear under unit names that did not exist when those patients were there. Third, the load has no audit step. If an extraction fails partway, the warehouse loads a partial day, and the next morning's census is quietly short. The analytics team discovered one such night in April only because a nurse manager questioned a report.

The Metadata the Warehouse Needs

Metadata, or data about data, is what allows users to trust and interpret a warehouse. Table 1 lists the three kinds the warehouse needs and what each would have prevented.

Table 1. Metadata Types and the Problems Each Would Prevent

Note. Prepared by the author from a review of the warehouse load scripts.

Keeping History in the Unit Dimension

The unit problem has a well-known design answer. Kimball and Ross (2013) describe slowly changing dimensions, and their second type keeps history by adding a new row whenever a descriptive attribute changes, with dates showing when each version was valid. Under that design, a February discharge would stay linked to the unit as it existed in February, and a March discharge to the reorganized unit. Rebuilding the unit dimension this way, using the crosswalk prepared for the quality assessment, would make every unit comparison before and after March honest. The same approach should apply to any dimension likely to change, including physician groups and service lines.

Why Small Rules Have Large Effects

It is tempting to treat these as technical details. They are not. The discharge-before-noon dashboard, the length of stay reports sent to the board, the state discharge submission and the transfer center's planning counts all read from the same tables. When a single rule turns blanks into Other, every one of those products inherits the change, and each team assumes someone else checked it. A warehouse concentrates both value and risk: one well-documented rule improves dozens of reports at once, and one undocumented rule misleads all of them.

The analysts who maintain the load are also few. Two people keep the scripts running alongside their report requests, and neither was present when most of the rules were written. Without documentation, every change they make is a guess about what an earlier colleague intended, which is how the unit dimension came to be overwritten in March. Written rules protect the people doing the work as much as the people reading the reports.

Recommended Changes

Four changes follow. The warehouse team should load blank admission sources as Missing, with a flag, and document the rule in the data dictionary. The unit dimension should be rebuilt to preserve history. A load audit should compare rows extracted with rows loaded for each table every night and alert the analytics team when counts differ or fall outside expected ranges. Finally, every transformation rule should be written in a shared dictionary with its purpose and approving owner, and any change to a rule should pass through the data governance committee before release. None of these changes requires new software; they require documentation and a habit of review.

Conclusion

A warehouse is only as trustworthy as the rules inside its load. At Cypress Hollow, those rules changed the meaning of admission source, erased unit history and let partial days pass unnoticed. Documented metadata, a history-keeping design and a nightly audit would let leaders trust that the numbers they read each morning mean what they appear to mean.

References

Chute, C. G., Beck, S. A., Fisk, T. B., & Mohr, D. N. (2010). The Enterprise Data Trust at Mayo Clinic: A semantically integrated warehouse of biomedical data. Journal of the American Medical Informatics Association, 17(2), 131-135. https://doi.org/10.1136/jamia.2009.002691

Kimball, R., & Ross, M. (2013). The data warehouse toolkit: The definitive guide to dimensional modeling (3rd ed.). Wiley.

Murphy, S. N., Weber, G., Mendis, M., Gainer, V., Chueh, H. C., Churchill, S., & Kohane, I. (2010). Serving the enterprise and beyond with informatics for integrating biology and the bedside (i2b2). Journal of the American Medical Informatics Association, 17(2), 124-130. https://doi.org/10.1136/jamia.2009.000893