(P-IT-6) Evolution of Blood Product Inventory Monitoring During Covid-19 Pandemic: Establishing Real-Time Web-based Dashboards

To meet the demand for blood products during COVID-19 pandemic and be responsible stewards of existing inventory, a robust blood inventory monitoring system needed to be developed. As of March 2020, our health system was comprised of 14 hospitals including the main campus. The desired outcome was a solution that combined data from all locations into one visual that would display current available blood product inventory.

Study

Design/Methods:

The Pathology and Laboratory Medicine Institute Analytics (PLMIA) department identified operational and information technology leaders at each location. Individual sessions were held with leaders at each hospital to design a process for extracting available blood product inventory data, transforming it into a standardized report format, and securely loading it onto the main campus server. Once acquired, the data was combined into one report that would be distributed to executive leadership daily. Par inventory levels were provided by the blood bank at each location and a stoplight system using the % of par inventory available was created to indicate inventory health. The immediate solution developed was an Excel file with conditional formatting to show if blood products at each location were in a green, yellow, or red status. A designee at each location emailed their available inventory to PLMIA once daily. Data from each location was manually populated into the Excel file which was emailed to executive leadership daily. Phase two of this project involved the automatic transfer of inventory reports from each location to a shared folder on the main campus server using a Secure File Transfer Protocol. Files were linked in SAS (Statistical Analysis System) to generate a single output file that could be connected to a Tableau dashboard. Once each hospital was integrated onto the same Laboratory Information System, a shadow environment of the production database was created, and a Structured Query Language (SQL) query was developed to pull data from blood inventory tables to create a live connection between the database and the Tableau dashboard. This provided real-time inventory data.

Results/Findings:

The blood product inventory dashboard displayed counts of available units for all blood products at each hospital in a grid format. This allowed leadership to make informed decisions regarding ordering practices, transfer of blood to where it was needed most, and triaging blood product usage. The value of this information sparked the development of a detailed dashboard for the main campus in the form of a bullet chart that shows how close inventory is to crossing thresholds (i.e. currently green, but close to yellow) (Figure A). The blood bank currently displays this dashboard throughout the day on a large monitor as part of daily operations.

Conclusions:

Blood product inventory monitoring has developed from email-delivered Excel files to real-time web-based dashboards.