Database migration involves the transferring of data from one system to another system and typically is associated with information systems, database administration, relational databases, analytics, and business intelligence. Database migration requires careful planning and poses many challenges related to data assessment and cleansing, migration, testing, and managing risks throughout the project. This article describes the database migration activities associated with Penn Medicine's initiative to insource the clinical engineering department at Princeton Health into the health system's corporate information services department. Achieving established milestones throughout the project was pivotal to its successful execution. Defining key goals and making business decisions that would positively affect and support the migration also was imperative. Moreover, the experience described here highlights multiple stages of a computerized maintenance management system (CMMS) database migration project, conveys the importance of building internal and external relationships, and addresses additional ways to improve on future migration projects.
Penn Medicine is a large academic medical center that acquired a New Jersey–based health center (Princeton Health) in December 2016. The corporate clinical engineering (CE) team was tasked with insourcing the CE department being acquired; this transition occurred in 2019. The experience described here highlights multiple stages of one aspect of this merger: a computerized maintenance management system (CMMS) database migration project.
Data stored in current-day information systems play a critical role for any organization. According to Karnitis and Arnicans,1 “On occasion, organizations migrate from one system to another whether it be by technological or consumer demand. As a result, data from legacy systems needs to be migrated to a new system.”
Initially, the entire project was planned to be executed within a six-month time frame. However, the start date was pushed out because of the uncertainty of the merger-and-acquisition timeline. Also, resource availability was limited because of an additional integration project occurring in which the same resources were needed. Moreover, budget funds for the database migration project were limited, which created a need to internally perform the migration rather than having the vendor perform the needed work at a high additional cost. The time frame for absorbing the CE department ended up being closer to four rather than six months.
Solving these problems required several strategies to be developed around process and technology. Building relationships with both internal and external resources and getting buy-in from management at all levels solved the people part of the equation. Doing the appropriate workflow analysis to support existing operational workflows and implementing any additionally needed workflows correlated with addressing the process piece of the problem.
Along with replacing the CMMS technology, data analytics (user data and reports) needed to be reviewed and validated in order to continue to measure and access the current operational metrics for the acquired CE department. Early in the process, we recognized that obtaining data, particularly from a third-party source, would be challenging and therefore planned accordingly. A common perspective of analysts and database managers is that data attained from acquisitions or outside vendors should be pursued with caution. Attention should be exercised because of data entry errors that may have occurred.2
This article addresses the detailed phases of technology redesign and implementation for healthcare technology management (HTM), mainly in regard to a CMMS database migration. A framework for a database migration is described, while the article also conveys lessons learned, the importance of building internal and external relationships, and the value of a successful go-live launch through the migration stages of the project.
Steps in CMMS Database Migration
The database migration plan for the initiative described in this article consisted of six steps: data assessment, data cleansing, migration planning, migration building, final migration, and dual maintenance. A CMMS database migration schedule was created to illustrate the steps taken (Figure 1).
The initial assessment of the data helped determine the scope and activities for the migration project. It also helped ensure that the data planned for transfer had integrity and consistency, ensured accuracy and tracking of service contracts and assets, and defined support protocols. The bulk of the data obtained from the previous CMMS vendor consisted of around 7,000 assets (unique equipment records), more than 3,800 schedules that were tied to the assets, and more than 34,000 unique historic work orders.
Having medical devices in an inventory system allowed access to details such as model and quantity counts for particular devices. A good inventory is the basis for capable equipment management. It provides a basis for the scheduling of preventive maintenance [PM] inspections, chronicles the number of service calls, and provides details on recalls. The inventory can also display financial data, which can help with budgeting.3
The risks of not having accurate data for medical devices can cause patient care–related problems, such as not having a valid location listed for a needed device on record or having an incorrect PM inspection due date. Therefore, CE departments must have an accurate medical device inventory along with an efficient operational process that powered by optimal technology systems, allowing management of devices to appropriately support patient care.
Regarding the inventory aspect of the project, we also assessed on-site physical parts/counts, obtained new vendor service contracts and retired outdated contracts, and determined whether new reports were needed. Parts are used as part of medical equipment repairs or PM inspection kits. As part of the assessment, for example, we found that service contracts were needed for the original equipment manufacturer to service equipment in cases where the on-site biomedical equipment technician (BMET) was not trained. As a result of examining workflow, new reports were also created to meet business needs and to meet standards of The Joint Commission (TJC).
The TJC Environment of Care standards (EC.02.04.01 EP 4) state that hospitals need to identify activities and frequencies in writing for the maintenance, inspection, and testing of their medical equipment inventory.4 By having additional data metrics on hand (e.g., parts, contracts, reports), we were able to develop better analytics, which is vital to driving effective business decisions.
Data cleansing must be performed to ensure that the data are accurate and robust. This process may involve updating and resolving corrupt data, such as eliminating null values, validating duplicate values, specifying whether a data field should contain a categorical or quantitative value, and addressing general formatting issues. Data cleansing is a crucial component in the overall migration process, as high-quality data are vital to making well-informed business decisions. Without proper planning, the final stages of any project can be compromised when facing budget and deadline constraints. However, investing in the time needed to cleanse data will inevitably pay dividends later.
Cleansing data required the most amount of time of all the tasks within the project, as considerable planning was required to reach a quality result. Data needed to be defined consistently for the duration of the project. When consolidating data from legacy systems, a plan must be made to develop common architecture with consistent data definitions and formats.5 As we reviewed data provided by the previous CMMS vendor, each data field (related to the medical equipment inventory) needed to be thoroughly analyzed.
Starting with the equipment inventory, the most obvious issues (e.g., data fields that contained null and duplicate values among “like equipment” category and model types) were addressed first. The current CMMS contains required values within its equipment inventory module, such as an asset number, asset description, manufacturer name and model, equipment category and subcategory, equipment type, equipment status, and multiple location fields (e.g., building, floor, department). The vendor data had to be analyzed to ensure all required fields within the new CMMS had an accurate data field import. Next, PM inspection schedules had to be analyzed to ensure that equipment requiring a PM inspection contained a schedule and that the next due date for the schedule was not listed in the past or extreme future.
Perhaps the greatest challenge within the data cleansing process was reviewing the historic work order data. Similar to the equipment inventory, the work orders within the CMMS consisted of required fields (e.g., work order description, department, type, status, substatus, priority). Other fields within the CMMS that were not required, such as completion indicators (problem, cause, and action, if any) were also reviewed. Having valuable historic data can further help with analytics when reviewing different reporting metrics (e.g., environment of care reports). Helpful work order reports for CE departments can consist of “equipment not found” listings that list equipment that could not be located during a PM sweep or reports (e.g., the number of corrective calls in comparison with problem indicators).
A CMMS provides opportunities for improvements in HTM by using analytics to adjust schedules and inspection frequencies, informing accurate work orders/tickets, and containing knowledge of equipment and its history. However, without accurate data, these opportunities cannot be effectively seized.
Migration Planning and Building
Continuous data adjustments consisted of ensuring that all historic data were mapped into the right places in the new CMMS. If a column (field) was not listed within the new CMMS, custom fields had to be created or data from miscellaneous fields needed to be combined into a single stored text field to capture historic data.
Final Migration and Dual Maintenance
The phases for the final migration steps consisted of migrating the data to a testing environment and training users to use the application alongside respective work processes. The last step consisted of verifying that all data transferred from source to target system was complete.6
Throughout the process, we continued to troubleshoot any ongoing or new issues that arose. Hospitals that don't have standardized processes for item maintenance run a risk of having data flaws. The fundamental key to ongoing data maintenance after an initial cleansing is to prevent degradation of the data.7
Data monitoring should be part of an organization's database/application processes. After data have been migrated to a testing environment, users can review the data and processes and provide feedback. Feedback, for example, may consist of the time it takes for a BMET to complete a work order or to add a new medical device to the inventory.
We were able to avoid potential pitfalls, such as not having all data in the new CMMS as a result of accidental omission or other unforeseen circumstances. Also, if all users were not properly trained on the new CMMS prior to go-live, trust issues could have surfaced (e.g., competency of the corporate team's knowledge of the new CMMS) with the new in-sourcing initiative, making it harder to get buy-in from new users as the program evolved.
Solutions for Challenges Encountered
During the project, problems were encountered that needed to be mitigated. Because data cleansing was the most time-consuming aspect of the project, prioritizing this task was critical. In addition to the importance of working with the vendor, the outsourced CE team played a major role in the process. The team assisted in obtaining needed information, such as serial numbers, model numbers, and/or accurate department locations. In the event that most data cleansing could not be met by go-live, a contingency plan of using existing dashboards in the health system's centralized CMMS would help track invalid data.
Next, working with executive level leadership within the health system helped CE attain appropriate security clearance that was needed for the facility at Princeton to be able to access the CMMS, as it was hosted at an off-site location that, at the time, was not entirely part of the Penn network. Having a fully optimized CMMS allows for the tracking of the location of physical equipment and CE staff time spent performing tasks. It allows work orders to be viewed and enables users within a health facility to create work orders through a web portal for more efficient tracking. This optimization helps with real-time ticket creation and turnaround with work orders.8 Also noteworthy is that staging data in a test environment helped move the migration process forward.
During user training, asking questions such as “Who is using it?” and “What types of business decisions are affected by it?” helped in gaining valuable information.9 Training CE users before go-live was pivotal. In addition to helping with preparations for the final stages of the database migration, the training allowed other areas to be addressed, such as analyzing system reports and other key performance indicators.
Lessons Learned and Conclusion
To achieve the best results for a database migration project, consider all factors associated with the scope of the plan and consider and use alternative methods as needed. By taking this approach in the endeavor described here, we were able to focus on customer service within clinical departments and ultimately save money for the organization.
A key lesson learned was the importance of knowing all terms and conditions up front for any vendor. Our team wanted to have access to the previous CMMS vendor's application after the initial go-live. Unfortunately, we were not able to get that request approved. However, all inventory and historic data were captured in a database that could be referenced.
The first and most critical data assessment milestone was met and contributed to successful system cleansing, which was assessed at completion of the project (having about 97% accuracy). This success rate was due to the collaboration between the two CE departments (that from Penn Medicine and the CE team being insourced) working together to transition the data accurately. Data metrics were measured weekly to indicate basic data nomenclature concerns, such as missing or invalid labels on equipment (e.g., manufacturer, model, serial number, warranty information, schedules, location). The insourced CE team was able to review and obtain updated data values from most medical devices. Further, after evaluating the historic data, any information that may have not been useful to the new CMMS and business needs was captured and combined in a single data field on each unique equipment record.
The key result in working with the Penn Medicine database services team was gaining support of the database migration initiative, though it was not part of the original scope of the project because of funding restrictions. The database team was able to assist with data imports and testing. This was a huge win for the enterprise because the CMMS vendor was not needed for the migration.
Building trustworthy relationships both internally and externally was instrumental to the success of this CMMS database migration. Developing buy-in to support the goals of the project led to their successful completion, which in turn helped our organization fulfill its overall mission.