National Healthcare Organization: Migration of Mission Critical Applications and Workloads from Teradata to Oracle Exadata

The Customer is one of the largest healthcare organizations in the U.S. With locations in all major U.S. states, they provide healthcare, insurance and related services to more than 12.2 million members. The Customer also operates and is affiliated with dozens of hospitals and healthcare facilities nationwide, including thousands of doctors, nurses, consultants and more.

The Customer had become increasingly dissatisfied with their costly Teradata platform, which was hindering their ability to meet business and reporting goals related to performance and scalability. In addition, all regions utilized Clarity, a reporting database supporting the Epic clinical information system. A database migration to Oracle's Exadata platform would give the organization much-improved functionality and interoperability with a host of Clarity modules that were not compatible with the existing Teradata platform.

However, any database modernization plan would involve many moving parts, including upstream and downstream custom applications, queries, operations, query systems and database performance as well as disaster recovery (DR). Compounding the challenge was the fact that the project comprised seven semi-autonomous regional entities, each with its own separate Teradata instance and individual configuration. Regional business users were reluctant to migrate away from Teradata, as each region’s environment had undergone significant customizations over many years of use.

Due to these issues, as well as the cost, complexity, scale and significant security issues involved, the organization needed an experienced migration provider. With our demonstrated track record in similarly complex database modernization projects, the Customer decided to partner with mLogica.

For each of the seven regions the scope of this modernization included the migration of all

• Queries
• DDLs
• Macros
• BTEQ files
• Embedded SQL in ELTs
• Reports
• Various other applications including mainframe

from the source Teradata SQL code to the new Oracle Exadata SQL format. The organization was running Teradata SQL queries for data access, data reports and ETL jobs and needed to convert all queries from Teradata SQL to Oracle PL/SQL.

Our team of subject matter experts (SMEs) optimized these translated queries to improve their performance in the target Oracle Exadata environment. Additionally, since the legacy Teradata system had been in place for decades, mLogica staff were tasked with training end users on mLogica's STAR*M and Oracle's SQL developer and query conversion tools, as well as how to use these new SQL statements in the destination Oracle Exadata environment. Performance and tuning efforts were also conducted at operating system and database levels to ensure users are enjoying superior performance.

We conducted extensive QA testing, and all regions had to attest that the converted queries were working optimally in the new Oracle Exadata environment and meeting agreed-upon performance thresholds. In addition, mLogica implemented an intensive defect resolution process to remediate any issues reported by users. The successful completion of all these phases was required in each region before the organization deployed the production environment.

Key details and requirements related to the source environment included:

• Database environment: multiple Teradata databases
• Primary applications: Clarity and Epic
• Ancillary applications: BOXI/Crystal Reports for reporting, Informatica for ETL jobs, Tableau, SAS mainframe and Windows for reporting and ETLs
• Conversion and remediation of mainframe Db2 queries
• Data size: 8 petabytes

Query migration was required in all of the organization’s seven regions, in each of which the organization was providing services via Clarity Healthcare Solutions. The organization was also using the Epic application to generate analytics reports related to the performance and scalability of their business.

The Epic system was supported by analytics tools including Caboodle, SlicerDicer and Healthy Planet. The Customer chose Exadata largely because it would enable the integration of these modules into their healthcare delivery system, enhancing their data analysis capabilities and driving optimal decision-making, predictive analytics and reporting.

Additional ancillary sub-systems and sub-applications included BOXI/Crystal Reports, which executed reporting functions, and Informatica for ETL jobs. These applications were running in all seven regions, with the data mart for Epic running on Clarity.

Prior to the kick-off of this database modernization project, mLogica performed a comprehensive assessment of the source environment to gain visibility into all existing dependencies, complexities and challenges that could hamper risk-free implementation of query migration.

Our analysis resulted in the following key findings:

• Since the legacy Teradata platform had been in use for decades, there were many more queries requiring conversion than initial Customer estimates suggested.
• A high percentage of queries that needed to be converted were owned by end users across all regions. These were not centrally located and required extended conversion via mLogica’s proprietary STAR*M software, as well as follow-up quality testing.
• A large number of queries also needed extensive optimization and tuning to resolve preexisting performance issues.
• Many users of auxiliary technologies such as Tableau, SAS and more required specialized support for self-conversion of queries.

Although this application and database migration had been in the works for several years, the Customer still had reservations regarding the long-term viability of the project and wanted assurance that running Clarity on Oracle Exadata was sustainable. Therefore, during this initial assessment phase, our team conducted a series of collaborative consultations with the Customer. Our team provided a thorough analysis of the existing technical gaps within the source Teradata system, a demonstration of how Oracle Exadata could remediate those gaps, and a review of all additional value this extensive nationwide migration would deliver.

A key Customer concern was the potential impact such a transition could have on the organization’s clientele, including clinical users, vendors, affiliates and millions of patients, since failure could disrupt thousands of reports and millions of queries. In addition, since healthcare is a highly scrutinized and regulated sector, any technical malfunctions or breaches might result in governmental compliance issues as well as devastating public relations fallout. With such high stakes, the Customer needed to leverage technology and methodologies that surpassed traditional manual approaches to successfully complete this massive project.

With hundreds of millions of dollars riding on this initiative, our team needed to provide the Customer with a comprehensive migration roadmap that would allow them to feel confident in the project’s sustainability. mLogica, in partnership with Oracle, presented our STAR*M automated database migration software as the superior alternative to a prohibitively resource-intensive, time-consuming and expensive manual migration. STAR*M automates 100% of the translation process of the source SQL code to the destination Oracle Exadata PL/SQL code, mitigating the risk of human coding errors and significantly reducing time to market.

To demonstrate STAR*M’s automated translation capabilities, proofs of concept were performed on a range of test queries, including its ability to identify, categorize and prioritize by the complexity of the query. Once deployed, this software would ultimately allow the mLogica team to accurately migrate the organization’s database, containing over 100,000 SQL files, within agreed-upon timelines.

The Customer also needed an end-to-end execution strategy that could help fulfill the project’s complex goals across seven national regions. In-depth assessments of the source environments for each region provided a complete picture of the issues, dependencies and risks. Our analysis of all available data on the organization’s source environments also gave us a foundation upon which to build an execution strategy that could overcome any roadblocks during query migration.

We leveraged SQLSearcher, a component of our STAR*M software suite, to identify critical embedded SQL code in the application source code by comparing it to a large list of SQL keywords. SQLSearcher then reported the outcome, providing statistics on the types of statements identified, as well as the file and line in which they appeared, making it easier and faster to translate them from the Teradata format to the Oracle Exadata format.

STAR*M provided mature, robust support for this Teradata to Oracle SQL translation, with SQLSearcher also being leveraged effectively for migration of:

• BOXI/Crystal Reports
• Informatica ETLs
• SAS mainframe and Windows reporting and ETLs to be converted to the distributed Exadata database

When our team initially deployed SQLSearcher to translate the embedded ETL SQL the success rate was even higher than expected. As query translation progressed, our team saw again saw a steady surge in the tool’s success rate, with our Delivery and Engineering teams collaborating on a daily basis as part of our automation hardening process.

By the end of the project, STAR*M’s SQLSearcher was achieving a 90%+ success rate in migrating all embedded SQL code. Further enhancements were developed to automate statistical, status and outcomes reporting in accordance with the Customer’s business processes.

With mLogica’s team and STAR*M automated software suite, query migration was completed within the agreed-on two-year time frame; three to five years faster than timelines submitted by competitors that utilize manual migration methodologies. Since this was a region-by-region phased deployment, multiple regions were live in Exadata just over a year after project initiation. This significantly reduced time-to-market was due both to the experienced SME team and mLogica’s automated STAR*M migration suite.

The transition to Oracle Exadata allowed this nationwide health care organization to standardize their database technologies, streamline platform management and establish a clear roadmap for future technology upgrades. It also allowed each region to be more agile in responding to continuously evolving business needs.

The initial project budget was estimated at $228M with a timeframe of forty-nine months. mLogica completed the project three months early and under budget, with a spend of only $186M. Our team migrated over eight petabytes of data without any data loss or corruption, while keeping approximately forty-five environments in sync.

The project migrated over 364,000 queries and DDLs, approximately 15,000 ETL jobs, and over 12,000 BOXI/ Crystal Reports, while 1,519 self-conversions were executed. Overall, forty-eight full racks of Exadata were deployed.

The positive impacts of this upgrade were immediately evident to end users nationwide, who were now able to enjoy enhanced database performance and reporting capabilities. In addition, the organization’s IT teams were now freed from having to perform time- and labor-intensive customizations to their legacy environments to utilize critical Epic reporting and analytics capabilities.

Moving from Teradata to Oracle Exadata also resulted in a significant decrease in the total cost of ownership (TCO) organization-wide. In one region alone, infrastructure savings were estimated at $33M, while the nationwide savings were projected at $571 million through 2024. In addition, the implementation of disaster recovery (DR) environments is projected to save the organization $254 million through 2024.

For years the Customer was aware they would have to migrate off their Teradata platform as it was hindering their ability to meet reporting and business goals. However previous estimates, which had projected a timeline of five to seven years to complete this transition, created understandable resistance at both the regional and national leadership levels.

The project was initiated with a national board-level approval that emphasized the cost and operational benefits, as well as the soft values this transition was likely to provide. Though many competing proposals were considered, mLogica secured the project due to our proven track record in database modernization projects and the unmatched capabilities of STAR*M.

The complexity and scale of the project meant there were many constituent parts to consider, from applications and databases to sub-systems, query translation and performance, disaster recovery and more. There was also an absolute need to ensure both complete continuity of service throughout the project and a secure, seamless transition in each region.

Since it was such an extensive project, initially the most challenging aspect was precisely measuring the scope and calculating the associated risks. A complete assessment of the organization’s huge technology stack, including applications, databases and technologies helped us determine how they would integrate into the end-state Oracle Exadata platform.

This analysis also allowed our team to understand existing technological gaps, dependencies and risks, and deploy solutions that would help the Customer achieve the required benchmarks. mLogica’s STAR*M automated database migration software significantly reduced the need for manual recoding, with its associated errors, costs and risks, which resulted in dramatically shortened timelines.

As we executed this project our team faced an array of challenges. The organization’s immense data store comprised forty-five environments, and the widespread scope of the initiative meant that any significant errors could potentially impact millions of users across regions nationwide.

In addition, there were significant reputational risks tied to this project, since vendors, facilities, customers and partners were all connected to the Customer’s system. Given the stakes, our team had to deliver a precision execution plan that minimized potential disruption to the organization’s IT and business ecosystems.

Another key challenge was overcoming leadership and user resistance on the Customer side, as their internal teams were initially reluctant to embrace such a significant change. mLogica’s transparent, collaborative approach, including consistent, supportive messaging about the myriad ways Oracle Exadata would improve efficiency, capabilities and performance, was instrumental in building buy-in among users and stakeholders.

Extensive end-user training was also integral to the success of this project. mLogica was responsible for assisting regional users with the onboarding process for their new Oracle Exadata environment. We designed a training program to fully equip them with the skills and tools needed to use the new Oracle Exadata environment and help them adopt the new technology stack. Initially, forty-four online classes and thirty-three in-person classes were planned, but these were so successful that the mLogica team ultimately provided a total of 349 classes nationwide.

The success of this project rests on three key factors: technology, experience and collaboration. mLogica’s commitment to excellence in all three led to the secure and seamless transition of the organization’s technology stack from Teradata to Oracle Exadata, allowing the Customer to enjoy dramatic cost savings as well as greatly enhanced technological capabilities across all regions.