When I first started working as an enterprise DBA, I was really excited to have the opportunity to start working with high-caliber hardware. My first experiences with SQL Server were with standalone instances with local rotational storage. My first enterprise-grade job was administering a third party hybrid data warehouse solution using rotational storage on a SAN.

Data warehousing in the healthcare sector is a challenging endeavor in that you have to contend with varying data quality and late-arriving data. Your institution may acquire best-of-breed applications for patient registration, official electronic medical record, operating room scheduler, etc., but they don’t necessarily interface directly to exchange data amongst themselves. What they do have is a type of event messaging standard akin to Electronic Data Interchange (EDI) named Health Level 7 (HL7).

If you don’t have access to the RDBMS instance that each of these source systems have, you can take the HL7 telemetry they transmit, atomicize the data in each message, and store the attributes in an operational data store (ODS). Combine all the HL7 messages from all the source systems and you can construct the entire patient record, or so you think.

Not all data is available via HL7 at the same time, not all of it is easily atomicized, and data entry errors at the source system propagate downstream, requiring you to continually conduct data quality and reconciliation operations.

Healthcare institutions also have ongoing regulatory requirements to the their state(s) of operation and the federal government. They also are seeking opportunities for cost savings and revenue generation. Ultimately, they seek to improve patient outcome by evaluating the success of treatment options in the past, the circumstances when the outcome wasn’t as good, and identifying areas for improvement. All these obligations and opportunities rely on warehousing data to identify trends and anomalies.

It may seem that you can have two systems, the OLTP operational data store and the traditional data warehouse modeled after Kimball or Inmon. But when you’re looking to improve the outcome of a patient currently in the ER waiting for a bed to be admitted and you want to compare the patients vitals with those of similar patients that have come to your hospital over the years, the functionality needs to merge, and it needs to be quick to benefit the current patient. Can you design a hybrid system that does both fairly well?

The first attempt of setting up such a system may revolve around setting up hardware recommended by a vendor specializing in HL7 message parsing (atomicizing). They may have an ODS model already available or they can provide the API framework and you can build your own. But predominantly, it’s an OLTP system. One such incarnation may have a ODS SQL instance like the following…

The astute DBA or engineer may note a missing item or two, but it’s been done on purpose as it reflects what the challenges are in identifying how to scale up.

I was responsible for such a system for just under two years. Data was coming gradually from different HL7 message sources and operational reports were being introduced little by little. Between the initial capacity and core count, and knowing we can add more LUNs from the SAN, confidence was high that the gear would be sufficient until end of warranty or the depreciation cycle. Most importantly, users were happy they were getting a unified view of their patients. As long as they were happy, we were happy. Performance wasn’t an issue.

After focusing on the DBAs ABC’s (availability, backups and capacity), I opted to transfer to the larger BackOffice DBA team where they were managing over 100 instances. With only one prod instance and the data model locked in by the vendor, a change in scenery made sense.

Little did I know that things would start to get interesting…

Over time, word of the application, and opportunities to leverage it, grew. Its operational data store followed suit. The application developers introduced more data sources as time went by. As the footprint got larger, the disk subsystem got busier, and the system started to slow down.  Like with any application you buy off-the-shelf, you go to tech support. In this case, they went to their vendors, their Windows engineers and their storage engineers for a solution, and got different options to choose from.