Analysis and Optimization of an Enterprise Data Factory
A leading healthcare information provider built a centralized data factory to process, synthesize, and publish data from multiple health care facilities around the country to its various value-added applications.
This company’s factory, built by another systems integrator no longer on-site, was performing extremely poorly and risked lost revenue for the various products that it supported. The system was intended to hold 10 TB of data and process 24 hours a day, yet it was having issues processing a single, multi-gigabyte data set. The client needed the process fixed and fixed fast.
Methodology and Solution
CBIG was hired to do a month-long assessment of the system that would conclude with a set of recommendations for improvement. The goal was to identify and fix the issues and design flaws that prevented the system from reaching its desired potential. Even though this was a very complex system (involving UNIX and Perl scripts, Informatica workflows, PL/SQL stored procedures, an enterprise scheduler, and an Oracle 10g database), CBIG was able perform this assessment quickly with minimal staff.
CBIG’s approach to this assessment was twofold. First, there was an overall review of every layer of the entire architecture and code to understand how the system worked and note potential problems based on CBIG’s extensive experience in these areas. Second, CBIG performed a performance analysis of the entire processing and began looking into the slowest components to determine which issues posed the most potential for improvement.
CBIG delivered a set of recommendations that included architectural changes to the SAN and server configuration, database setup and configuration, backup strategy, database object configuration, ETL design, data model enhancements, security, overall batch flow, and more. In addition, there were specific modifications recommended for individual queries that each saved hours of processing time.
Due to the overwhelmingly positive response of the assessment, CBIG was asked to stay and help implement many of the recommended changes. CBIG led several targeted follow-on projects to optimize the SAN layout, implement an optimized RMAN backup strategy, modify the database configuration, optimize the database, improve the Informatica code, and automate the performance testing.
The data factory is now performing as originally envisioned, supporting several applications, and the client is very happy with the outcome. The client felt that CBIG’s experienced staff, and common sense approach to tackling the problem (by fixing the biggest and most common problems first) were the key reasons that their product is now successful.
A leading provider of analytical data to the healthcare industry. Based in Chicago, the company serves customers across the nation.
The client built a data factory that was to be the core data provider for all of its key products. However, the system could cripple the business unless the performance was vastly improved.
- Oracle 10g