By Jay  |   |   |  0 Comments

A leading University’s medical school looked to take its long tradition of leadership in pioneering research, effective clinical therapies, and creative teaching protocols to the next level. As a pioneering trendsetter in biomedical evolution, the University constantly sought to upgrade its students and academic staff who joined its programs through a highly competitive admission and selection process. The University leadership laid great emphasis on data to track the progress of their students, their programs, and research initiatives. The university leadership invested in the cutting edge Informatica 10.4 as their BI(Business Intelligence) and reporting system.

The University chose DataTerrain to convert the legacy Oracle PL/SQL stored procedure based ETL jobs to Informatica Powercenter and provide a responsive maintenance support service.  Based on its track record, DataTerrain was tasked with delivering the following –

  • Seamlessly convert ETL jobs to the new platform
  • Fix issues and gaps in the legacy system during the transition
  • Improve the performance of ETL batch jobs
  • Improve service levels to end-users and University teams

DataTerrain’s proprietary approach for Informatica Powercenter rollout and support

SMEs(Subject Matter Experts) from DataTerrain have developed a robust approach for Informatica Powercenter rollouts which they customized for the unique needs of the University. Combining deep domain knowledge with specialized technical skills, DataTerrain performed the following major activities –

  • Business requirements mapping and design
    1. User workshops to map AS-IS business processes and document technical implementations including identification of redundant data loads
    2. Consultative discussions to define and document TO-BE requirements including rationalization of data loads and jobs
    3. Documentation of source system design elements consisting of DB objects/mappings/flows/dependencies.
    4. Rationalization of objects/tables/views/flows to create detailed mapping and ETL design documents.

 

  • Development/Enhancement/Bug fixing –
    1. Development of new ETL adhering to the specific business standards of the university and based on industry best practices
    2. Importing required database objects into Informatica Designer and developing new ETL mappings/sessions/workflows/tasks/schedulers
    3. Equivalent transformations created in ETL mappings for the features used in the SQL queries of the source system.
    4. Automated validation scripts developed using python for comparing hundreds of database tables and automatically validating data loads.
    5. Ownership for incident life cycle, troubleshooting, debugging, issues resolution within SLA, and root cause analysis(RCA)
    6. Preventive measures for repetitive issues, and development of permanent solutions
    7. Resolution of complex bugs with long term fixes following best practices and collaboration with University teams for solution reviews

 

  • Support, Load plan/monitoring –
    1. Schedule and monitor the PRODUCTION and UAT jobs every day and troubleshoot/fix the issues
    2. Create and execute monthly/yearly/on-demand ETL data load as per Business requirements
    3. 24X7 production system support including “off hours” and weekend “on-call” production support
    4. Support for daily builds run on UAT and production environment, management of application releases. transition of the ETL mappings/ sessions/ workflows/ tasks/ schedules from UAT to PROD and update flow for downstream systems to receive data through new ETL jobs
    5. Implementation of change control processes, continuous integration, and ownership for the smooth release of changes into production
    6. Daily spot checks of system health, session monitoring and support for ETL processes,
    7. Logging load statistics, failure, and log analysis,
    8. Communication with stakeholders in case of delays or SLA breaches
    9. Resolving failures and delays during daily process execution
    10. Coordination with DBAs to resolve issues

 

Benefits to the University

Migrating to Informatica PowerCenter led by DataTerrain’s best-in-class services resulted in significant benefits to the University as below –

  1. The business impact of the following advanced features of Informatica Powercenter–
    1. Data Integration agility – Accelerating integration through a metadata-driven approach that enables quick setup of interfaces with a variety of University systems.
    2. Scaleability – A real-time engine that delivers high performance through always-on data integration services that easily scale to support increasing data volumes from multiple users, projects, university departments, and data types.
    3. Robust operations – Delivering consistent performance and timely distribution of quality data to create value for University stakeholders. Early warning systems provide alerts on ineffective resource utilization or unexpected behavior in data integration workflows.
  2. Productivity improvement as ETL processes complete faster due to performance tuning performed by DataTerrain SMEs.
  3. Faster turnaround and increased user satisfaction due to the timely resolution of issues and completion of defect fixes and enhancements.
  4. Improved traceability due to the extensive documentation of data flows and mappings provided by DataTerrain.
  5. Reduction in technical debt based on continual improvement initiatives to fix technical issues, resulting in a reduction in the number of incident tickets and a progressive reduction in resolution times.

The DataTerrain Difference

The University leadership has highlighted the following factors as the critical difference made by DataTerrain –

  1. Optimized and Seamless delivery with a highly skilled SME team – DataTerrain SMEs advised and guided the University team as trusted partners. They helped rationalize the ETL processes and simplified the implementation of unique requirements like BLOBs/CLOBs and appropriate code pages for Informatica data connection.

 

  1. Global delivery model – DataTerrain teams from its Global delivery centers were available 24X7 to monitor jobs, respond to issues, and ensure that overnight batches complete before the start of every business day.

 

  1. Domain knowledge of DataTerrain SMEs – DataTerrain SMEs quickly understood the unique business needs of the University and helped rationalize and eliminate redundant data loads. The documentation provided by the SMEs is very useful for the University associates to understand the system and project the impact of changes.

DataTerrain has helped the University to quickly adopt Informatica Powercenter and benefit from its many new features. This has helped the University accelerate its quest to improve health through greater collaboration and innovation in health-care, research, and education.