Manager Data

SATHISH RAMAMOORTHI

Mobile: 651-***-****

Email: ************@*****.***

ETL Informatica Consultant/ Tech Lead

Professional Overview

Enterprising professional with a reputation for exceptional analytical ability and demonstrated success utilizing teamwork and communication skills Key accomplishments and abilities:

Skills Highlights

Informatica PowerCenter 9.6.1

HP Quality Center

Teradata 15.0, Oracle 10g, DB2

Toad 9.6, PLSQL

Guidewire PolicyCenter

ServiceNow/IT2B

ERStudio Data Modelling

Harvest, BuildForge

Unix Shell Scripting

ESP, CA7 Scheduler

Agile, Waterfall

Production Support

Onshore-Offshore Model

ETL Testing

Professional Highlights

Over 7 years of experience as an ETL consultant in Business Analysis, Design, Development and Production Support using industry accepted methodologies and procedures.

Highly motivated and goal-oriented individual with a strong background in SDLC Project Management and Resource Planning using AGILE methodologies.

5 Years of experience in Health Care domain, 2 years of experience in Insurance domain

Experience in creating mappings, mapplets, reusable transformations, Sessions, Worklets and workflows using Designer, workflow manager, workflow monitor Using Informatica Power Center.

Extensive experience in Data Loading, Analysis, Design by using Various Technologies like Informatica Power Center 8.6, 9.x and Oracle, Teradata 15.0, DB2 in Data Warehousing, ensuring the deliverables within the time & cost parameters.

Developing ETL processes like Data Extraction, Transformations, and Loading using Informatica Power Center 8.6 & 9.x versions Designer, Workflow Manager and Workflow Monitor tools.

Strong hands on experience using Teradata utilities (SQL, Fast Load and MultiLoad), Teradata parallel support and Unix Shell scripting.

Experience in SQL, PLSQL queries and stored procedures. Having good experience in writing UNIX/Linux scripts.

Involved in various testing like unit testing and Responsible for performance Tuning and Error Handling of Informatica mapping.

Experienced in Teradata Parallel Transporter (TPT). Worked with different Teradata load operators.

Designing and developing Informatica mappings including slowly changing dimensions type 1 and 2.

Experienced in using advanced concepts of Informatica like push down optimization (PDO).

Experience in Informatica mapping specification documentation, tuning mappings to increase performance.

Experience in creating logical/physical Data Modelling in ERStudio. Hands on experience in creating Macros in ERStudio. Experience in HP Quality Center for logging defects and tracking defects.

Experience in developing Test Plans, Test Strategies and Test Cases for Data Warehousing projects ensuring the data meets the business requirements, also good work experience in SQL, PL/SQL, UNIX

Experience in designing and implementing relational database model as per business needs.

Expertise in leading the policy migration and downstream Integration of Guidewire PolicyCenter and ClaimCenter projects to the legacy systems for multiple rollouts

Good knowledge of Transact-SQL (DDL, DML). Experience in Teradata utilities Fastload, Multiload, Fastexport

Knowledge in creating complex Bteq scripts. Experience in calling TPT connections inside Informatica mappings

Leadership quality in managing offshore and onsite people

Experience in Agile and Waterfall project life cycle, experience in running daily stand ups

Designed, developed and supported BI solutions, Involved in Debugging and monitoring, and troubleshooting issues/tickets.

Applied statistical methods and tools to solve business-related problems. Analyzing complex issues and distilling information down to key points and recommendations.

Education

BACHELORS OF ENGINEERING ANNA UNIVERTITY

Major: Computer Science and Engineering.

Related coursework: Academic project related to Protection of Database Security.

Employer

SR. Developer ETL Information control company Aug 2016 to Till Date

Senior IT Analyst Cognizant Technology Solutions Feb 2011 to Aug 2016

Experience

Project Title: Nationwide Insurance – One Product - Columbus, OH (Aug 2016 – Feb 2018)

Project Description: The One Product Program is a component of Personal Lines Transformation (PLT); a multi-year initiative to simplify Nationwide’s product management, distribution, underwriting, and policy servicing. As a result of the One Product Program, a single product will be implemented in all distribution channels for each line of Personal Lines business (standard auto, homeowner, umbrella, power sports, tenant, condo, dwelling fire) in every state where personal lines are currently sold, with the exception of state specific regulatory requirements.

Responsibilities:

Lead the offshore team and coordinated its activities with the onshore team.

Involved right from the analysis of requirements, converting the functional description to the technical workflow design, realization of the design into Teradata-Informatica mappings, code review and test cases review, coordinating with the onsite team, oversee the testing of the entire workflow including support once the production installation has been done

Design Data Architecture and Dimensional Data Modeling for new projects. Designed, developed, and tested the components using Informatica PowerCenter 9.0 all the way through Informatica PowerCenter 9.6

Responsible for definition, development and testing of processes/programs necessary to extract data from operational databases, Transform and cleanse data, and Load it into data warehouse using Informatica Power center.

Created Conceptual, Logical & Physical models, used ERStudio & VISIO for Dimensional Data Modeling.

Created complex mappings in Power Center Designer using Expression, Filter, Sequence Generator, Update Strategy, Joiner and Stored procedure transformations.

Demonstrated expertise utilizing ETL tools and Informatica ETL package design in RDBMS systems like Oracle, Sybase, Teradata etc.

Extracted data using BTEQ scripting which includes Fast Export and Multi Load utilities. Implemented Slowly Changing Dimensions (SCDs, Both Type 1 & 2).

Cleansed the source data, extracted and transformed data with business rules, and built reusable mappings, known as ‘Mapplets’ using Informatica Designer.

Involved in the development of Informatica mappings and also performed tuning for better performance.

Setting up sessions to schedule the loads at required frequency using Power Center, Workflow manager, PMCMD and also using scheduling tools

Created Unit Test Cases for the ETL Informatica mapping validation

Performed extraction, transformation and loading of data from different sources like XML, FlatFiles, DB2 and Teradata in accordance with requirements and specifications.

Created complex Informatica mappings using Unconnected Lookup, Joiner, Rank, Source Qualifier, Sorter, Aggregator, dynamic Lookup and Router transformations to extract, transform and loaded data to ODS.

Implemented slowly changing dimensions (SCD) to maintain history data as per user requirement.

Handling of Error and its logging- Used workflow monitor to monitor the jobs, reviewed session/workflow logs that were generated for each session to resolve issues, and used Informatica debugger to identify issues in mapping execution.

Involved in system testing, performance and System Integration testing stages of the project.

Ensuring the developed code as per the coding standard.

Had additional responsibility in ensuring quality, technical guidance, code review.

Importing Teradata source tables through informatica and loading Teradata targets through informatica

Verifying the source and target table DDLs are matching as per the mapping document and making necessary DDL change based on the requirements.

Checking the Indexes (PRIMARY, UNIQUE, SECONDARY) and properties (SET, MULTISET) of the table.

Mocking up source table data to test all the logics which was implemented through informatica coding.

Creating Source qualifier query and Lookup override queries based on the requirements.

Create staging tables and loading staging before data moves into target. Perform pre-post SQL to move data from stage to target.

Monitoring in Teradata View Point for skew while code is running. Checking Stats were collected for all the tables and index columns were used in the joins

Checking informatica load id has grant access to perform DDL/DML operations into Teradata tables. Handling the Error tables and UV tables in case of failures

Importing Oracle source tables through informatica and loading Oracle targets through informatica

Creating Source qualifier query and Lookup override queries based on the requirements.

Creating Oracle connection strings and give grant access to load oracle tables via informatica. Mocking up source table data to test all the logics which was implemented through informatica coding.

Post validation- taking backup of production tables to recover data in case of failures.

Calling Code tables through stored procedures. Daily status reporting to various stack holders of the project.

Had additional responsibility in ensuring quality, technical guidance, and code review. Ensuring the developed code as per the coding standard.

Involved with Clients in requirement gathering. Took additional responsibility in delivering on time. Often conducting daily scrum meetings while working for Agile project cycle. Experience in lead role- managing people over offshore and onsite.

Technical Tools Used: Windows7, Informatica PowerCenter 9.6, Teradata 15.0, Oracle 10g, Toad 9.6, Putty, WinSCP, Harvest, ESP scheduler, Power Designer, Service Now.

BCBS Project Title: AVM (Application Value Management) Foundation- Eagan MN ( Nov 2015 – July 2016)

Project Description: AVM project aims in providing added value to existing applications in Blue Cross Blue Shield of Minnesota (BCBS-MN). The applications are various domains in Healthcare; The Integrated Data warehouse (IDW) holds the transactional data backup in the form of tables. AVM portfolio handles monitoring of all application load to Teradata tables, Incident management, data quality validation, enhancement activities, and administration of Teradata database and Informatica server. The production support of BCBSMN comes in three phases namely wave 1,2 & 3. Each have list of application and jobs associated with a application, these Jobs runs basically on Mainframes and Informatica. All jobs are interdependent and timed to run through schedulers. The successful run of these applications takes care of transactional data getting backed up in Datawarehouse for various domains in healthcare for BCBSMN.

Responsibilities:

Development and unit test of ETL mappings using Informatica PowerCenter, Informatica PowerExchange as per the Business Requirements.

Worked on Fast load and Multiload Teradata utilities to load staging data. Raising clarification and following it to closure.

Created Technical Design and UTC for the ETL Informatica mapping validation. Involved in Implementation, reviews and Unit Testing.

Responsible for debugging and performance tuning of targets, sources, mappings and sessions.

Optimized the mappings and implementing the complex business rules by creating re-usable transformations and Mapplets.

Involved in writing BTEQ, MLOAD and FLOAD scripts to load the data into Teradata tables.

Optimized the source queries to control the temporary space and added delay intervals depending upon the business requirement for performance

Used Informatica workflow manager for creating, running the Batches and Sessions and scheduling them to run at specified time.

Executed sessions, sequential and concurrent batches for proper execution of mappings and set up email delivery after execution.

Preparing Functional Specifications, System Architecture/Design, Implementation Strategy, Test Plan & Test Cases.

Implemented and documented all the best practices used for the data warehouse.

Handling of Error and its logging. Interaction with QA and BA team to resolve the issues. Have done ETL testing by loading the source data into temp tables through SQLs by preparing complex queries. Will log the defects into HP Quality Center and will track the existing defect’s status through QC.

Analysis of Specification document and mapping document. Raising clarification and following it to closure.

Created Technical Design and UTC for the ETL Informatica mapping validation. Involved in Implementation, reviews and Unit Testing.

Handling of Error and its logging. Imported source/target file data directly into Teradata tables through Fastload and Multiloads and used it for unit testing.

Worked on Power Center Designer tools like Source Analyzer, Warehouse Designer, Mapping Designer, Mapplet Designer and Transformation Developer.

Used Debugger within the Mapping Designer to test the data flow between source and target and to troubleshoot the invalid mappings.

Worked on SQL tools like TOAD and SQL Developer to run SQL Queries and validate the data.

Scheduled Informatica Jobs through Autosys scheduling tool.

Checking the performance of the Source Qualifier Query through explain

Interaction with QA and BA team to resolve the issues. Weekly status reporting to various stack holders of the project.

Had additional responsibility in ensuring quality, technical guidance, and code review. Ensuring the developed code as per the coding standard.

Involved with Clients in requirement gathering. Assigned work to offshore team on daily basis and made sure that the project is delivered on time.

Took additional responsibility in delivering on time. Experience in lead role- managing people over offshore and onsite

Technical Tools Used: Informatica PowerCenter 9.5, Informatica Power Exchange 9.6.5, SQL Server 2008, Windows7, Oracle 10g, PL/SQL, ClearCase, TOAD, Putty, Build Forge, UNIX, Teradata 14, Erwin 7.5, ESP, WinSCP, IT2B, Service Now

Project Title: BCBSMN-Accepted Claims - Eagan MN ( Jan 2015 – Oct 2015 )

Project Description: Accepted Claims is enhancement module in claims migration along with few new developments. This includes change in the database structure, data modelling, Informatica mapping changes and new Informatica design. The objective of Accepted Claims is to provide updated format of tables along with new table with load data based on updated business logic (ETL Process).

Created Technical Design and UTC for the ETL Informatica mapping & Wherescape validation.

Involved in Implementation, reviews and Unit Testing.

Handling of Error and its logging.

Interaction with QA and BA team to resolve the issues.

Had additional responsibility in ensuring quality, technical guidance, code review.

Ensuring the developed code as per the coding standard.

Assigned work to offshore team on daily basis and made sure that the project is delivered on time.

Took additional responsibility in delivering on time.

Designed the jobs with so many dependencies without any issues.

Involved in setting up the MoveIt process and job schedule.

Involved in post-production support and resolving the incident during this phase.

Environment: Operating System: Windows 7, Tools: Informatica 9.5 and PowerDesigner, Databases: Teradata, Automation Tool: CA7.

Project Title: BCBSMN- PPL_I Automate - Eagan MN ( Mar 2013 – Dec 2014)

Project Description: Post payment letters (PPL) are a cost containment measure, by making sure the correct party pays for medical care, BCBSMN can continue to hold down medical care costs for their members. PPL or I-Automate is in house application. The I Automate is automating the process of identifying the members whose claims are to be verified to find whether the claim is paid by the correct party.

Involved in analyzing the existing process for product specific and design for enhancements.

Analysis of Specification document and mapping document.

Raising clarification and following it to closure.

Created Technical Design and UTC for the Informatica code.

Involved in Implementation, reviews and Unit Testing.

Handling of Error and its logging.

Weekly status reporting to various stack holders of the project.

Had additional responsibility in ensuring quality, technical guidance, code review.

Ensuring the developed code as per the coding standard.

Involved in post-production support and resolving the incident during this phase.

Ensuring the developed code as per the coding standard.

Automated the code by running the code based upon the line of business.

Automated the process of the running the Packages using automation tool and the powershell script to invoke the jobs.

Involved in post-production support and resolving the incident during this phase.

Environment: Operating System: Windows 7, Tools: Informatica 9.5, Databases: Teradata, Automation Tool: CA7.

Project Title: BCBSMN-CMR Alineo & UMPR – Chennai,INDIA ( Jan 2012 – Feb 2013 )

Project Description: The CMR is a replication of MeDecision’s Alineo’s Cache database normalized to Blue Cross Blue Shield of Minnesota structures using Blue Cross Blue Shield of Minnesota naming. The Enhancement requirement is to add additional functionalities to the existing tables and create new tables for CMR Database. Our scope is to load the xml data into various tables.

Designed, developed and tested the components using Informatica 9.1/Informatica 9.5 on Teradata Database.

Validating the XML files using the Altova XMLspy.

Modelling the tables according to the XML files with the necessary surrogate keys to join them using PowerDesigner.

Designed and developed standards for the Informatica development team.

Involved in analysis of the defects and ensured the defects are fixed with expected results.

Involved in creation and testing of all the mappings in the datawarehouse.

Extensively used informatica client tools-Designer, Workflow Manager and Workflow Monitor.

Involved in preparation of unit test case and captured Unit test Results.

Performance tuning, including collecting statistics, analyzing explains & determining which tables needed statistics. Increased performance by 35-40% in some situations.

Multiload, BTEQ, created & modified databases, performed capacity planning, allocated space, granted rights for all objects within databases, etc.

Tested the Workflow and involved in writing SQL Queries for ETL Validating the data transformed, Ensured that all expected data is loaded.

Worked with Business Analysis team in gathering the requirements and created Functional and ETL specification documents.

Analyzed the Business Requirement Documents (BRD) and laid out the steps for the data extraction, business logic implementation & loading into targets.

Extensively worked with Informatica – Source Analyzer, Warehouse Designer, Transformation developer, Mapplet Designer, Mapping Designer, Workflow Manager, and Workflow Monitor to develop various complex mappings, mapplets, reusable transformations, session tasks and Workflows.

Performed extraction, transformation and loading of data from different sources like Flat Files, DB2 in accordance with requirements and specifications.

Used workflow monitor to monitor the jobs, reviewed session/workflow logs that were generated for each session to resolve issues, used Informatica debugger to identify issues in mapping execution.

Used Workflow manager to create and configure workflow and session task to load data. Used Informatica workflow monitor to create, monitor workflow in case of process failures.

Creating tasks, sessions, events, timers etc in the workflow manager and monitoring them in the workflow monitor.

Involved in system testing, performance and System Integration testing stages of the project.

Environment: Operating System: Windows 7, Tools: Informatica 9.1/Informatica 9.5, Altova XMLspy, Databases: Teradata.

Project Title: BCBSMN-Claims Migration- Chennai INDIA ( Feb 2011 – Dec 2012 )

Project Description: Claims Migration project for BHI primarily involved migration of the existing DB2 data architecture and the allied components (including the DB2 scripts, Informatica mappings) to a better model, from a data warehousing perspective, involving Teradata database. BHI stands for the entire set of applications such as Member, Claims, Pharmacy, Account and Provider for Blue Health Intelligence.

Designed, developed, and tested the components using Informatica 9.1/Informatica 9.5 on Teradata database.

Developed Informatica workflows/worklets/sessions associated with the mappings using Workflow Manager.

Involved in the analysis of source to target mapping provided by data analysts.

Created complex Informatica mappings using Unconnected Lookup, Joiner, Rank, Source Qualifier, Sorter, Aggregator, dynamic Lookup and Router transformations to extract, transform and loaded data to ODS.

Created integrated workflow per subject area keeping the dependency within the subject area.

Wrote Unit Test cases and executed test scripts successfully.

Involved in performance tuning of Informatica code using standard Informatica tuning steps.

Involved in code Reviews as per ETL/Informatica standards and best practices.

Generated 12 Reports related to Medical Claims, Pharmacy, Member, Provider and Revenue Claims etc.

Environment: Operating System: Windows 7, Tools: Informatica 9.1/Informatica 9.5, Databases: Teradata, Scheduler: CA7.

Contact this candidate