NUR 7010 Module 4 Data Warehousing Paper: A Nursing Quality Data Mart With a Star Schema Example

One Number for Falls: Designing a Nursing Quality Data Mart for Four Bergen County Hospitals

Student Name

M.S. in Nursing, Nursing Informatics, Thomas Edison State University

NUR-7010: Nursing Informatics: Databases and Knowledge Management

Mentor Name

Month Day, Year

One Number for Falls: Designing a Nursing Quality Data Mart for Four Bergen County Hospitals

Three Fall Rates

The hospitals and figures in this paper are invented. A four-hospital system in Bergen County reports nurse-sensitive measures to its board each quarter. Falls come from three places: the incident reporting system, the electronic health record's fall documentation and the hospitals' submissions to a national nursing quality database. Each produces a different rate. Staffing data come from a separate scheduling system, and patient experience scores come from a vendor's files. Each hospital's quality analyst builds reports by copying data into spreadsheets, and each defines patient days slightly differently. At the last quality council meeting, forty minutes went to arguing about whose fall rate was correct.

The system's informatics nurse proposed a nursing quality data mart: one place where nursing measures are built once, from agreed definitions, and reported the same way for every unit.

What a Warehouse Is

Inmon (2005) gave the classic definition: a warehouse gathers data to support management decisions, and it is organized by subject, integrated across sources, kept over time and left unchanged once loaded. Subject-oriented means organized around the subjects the organization cares about, such as patients or units, rather than around the applications that produced the data. Integrated means that data from different sources are made consistent in names, codes and units. Time-variant means that the warehouse keeps history so that trends can be studied. Non-volatile means that data, once loaded, are not changed by daily transactions, so a report run today and next month gives the same answer for the same period.

The current spreadsheets failed each test. They were organized by source system, not by subject. They defined patient days differently. They overwrote last quarter's numbers when corrections arrived. And they changed whenever an analyst edited a cell.

Dimensional Modeling

Kimball and Ross (2013) described dimensional modeling as a way to make warehouse data understandable and fast to query. Measurements of a business process are stored in a fact table, one row per event or period at a stated grain, with numeric measures and keys to dimension tables. Dimension tables describe the who, what, where and when of each fact, such as date, unit, patient type or staff role. Drawn on paper, the facts sit in the middle with dimensions around them, which gives the star schema its name. Kimball and Ross stressed conformed dimensions: a dimension such as date or unit is built once and shared by every fact table, so that measures from different processes can be compared on the same terms.

For the data mart, conformed dimensions solve the argument directly. If every measure uses the same unit dimension, with the same unit codes and the same mapping to unit type, and the same date dimension, then falls, staffing and patient experience can be lined up and compared.

A Clinical Example

Murphy et al. (2010) described i2b2, Informatics for Integrating Biology and the Bedside, a platform developed to let researchers query clinical data across an enterprise. Its data are stored in a star schema with a central observation fact table, in which each row records one observation about a patient at a time, linked to dimensions for patients, visits, concepts and providers. The design lets new kinds of data be added as new concepts without changing the table structure, and its query tool lets users find groups of patients without writing code. The platform has been adopted by many academic centers.

The i2b2 example showed the informatics nurse that a simple star schema can hold diverse clinical data and remain usable by people who are not programmers.

The Data Mart Design

The fall event fact draws from the incident system, cross-checked nightly against the record's fall documentation; falls appearing in only one source are flagged for the unit's quality nurse to resolve. Patient days are calculated once, from midnight census in the record, and stored in the unit day fact. The fall rate becomes a single calculation: falls divided by patient days, per 1,000, for any combination of unit, hospital, unit type and period.

First Reports From the Mart

The first report built from the data mart compared each unit's fall rate with its nursing hours per patient day and its patient experience score for responsiveness, a comparison the old spreadsheets could not make. Two medical units with similar patients had very different fall rates, and the mart showed that the unit with more falls had fewer technician hours at night. The quality council spent its meeting on that finding instead of on whose number was right. The council also asked for a drill-down by hour of day, which required no new tables because the fall event fact already stored the time of each fall.

Loading and Governance

A nightly load extracts data from each source, maps codes to the conformed dimensions and loads the facts. A nursing data governance group, with a quality analyst from each hospital, owns the definitions and approves any change. Corrections are loaded as new rows with a date, preserving history so that reports for a past quarter stay stable. A short data dictionary on the intranet explains every measure in plain language.

Limits

A data mart moves inconsistency into the open; it does not remove it. Falls that were never reported anywhere remain invisible. The first months will reveal disagreements about definitions that were hidden in spreadsheets, and governance will need time to settle them.

Conclusion

Three fall rates came from three sources, three definitions and spreadsheets that changed without warning. Inmon's warehouse principles explained what was missing, Kimball and Ross's star schema with conformed dimensions provided the design and i2b2 showed that clinical data fit the pattern. The data mart gives the quality council one number for falls and a way to compare it with staffing and patient experience on the same terms.

References

Inmon, W. H. (2005). Building the data warehouse (4th ed.). Wiley.

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