EXPERIENCE SUMMARY

• Over 10 years of professional experience in IT Industry including expertise in ETL design, Development and Implementation of Data Warehouse application (Informatica).
• Constantly involve in the full development lifecycle from requirements gathering through development and support for Healthcare, Banking, Energy, communications industries using Informatica PowerCenter, Repository Manager, Designer, Workflow Manager, and Workflow Monitor.
• Knowledge of data warehousing techniques, Star schema and ETL methodologies
• Experience in integration of various data sources like Oracle, SQL Server, DB2, Flat files, Teradata, Netezza, etc.
• Installation and configuration of Informatica servers and client environments
• Experience in writing, testing, and implementation of the triggers, cursors, procedures, functions at database level using PL/SQL
• Experience in debugging and performance tuning of sources, targets, mappings and sessions.
• Experience with coordinating cross-functional teams, project management and presenting technical ideas to diverse groups
• Experience in offshore co-ordination
• Excellent communication, interpersonal, analytical skills and strong ability to perform as part of a team.

EDUCATION
Master of Computer Application

TECHNICAL SKILLS

ETL Tools: Informatica PowerCenter 9.x/8.x/7.x/6.x/5.x
Databases & GUI: Oracle 10g/9i/8.x/7.x, SQL Server 2000, DB2, Teradata, MS Access
Reporting Tools: Business objects, COGNOS (Beginner level)
Languages: KSH script, SQL, PL/ SQL, Developer 2000
Environment : WIN95/98/NT/2000/XP, UNIX
Web Tools: XML, HTML, Macromedia DreamWeaver, and Flash 5.0
Other Tools : SQL*Plus, SQL Navigator, TOAD for Oracle, MS Excel, Visio

PROFESSIONAL EXPERIENCE SUMMARY

Mar 2007 to Present

Confidential,Downers Grove, IL

Sr. Technology Consultant

Feb 2006 to Mar 2007

Confidential,Chicago, IL

Technology Consultant

Jun 2005 to Feb 2006

Confidential,Arlington Hts, IL

ETL Consultant

Jun 2003 to May 2005

Confidential,Chennai, India

ETL Developer/Consultant

Feb 2002 to May 2003

Confidential,Chennai, India

ETL Developer

PROJECTS UNDERTAKEN

Organization: Confidential,Texas Jan 2011 - Present
Role: Lead ETL Designer

Confidential,offers health care solutions to its customers. As the lead ETL designer I was responsible for providing end-to-end solutions for the various projects that I was involved in. I also had to manage, lead and guide the offshore team members for the projects. The projects that I was involved in during this period in this account were ESRD (End Stage Renal disease), Aggregate Strategy Phase I and II, RDS (Retiree Drug Subsidy) Mid-year plan, RX_NB Expansion and MedHOK

Responsibilities:

• Go through BR/FR and discuss the high level data flow and design framework
• Get understanding on overall detail level architecture and share the same with offshore team
• Help the offshore team to create the design document and review the same
• Review Unit test cases
• Set up internal code review meeting with offshore team and review the code
• Set up internal and all architect review meeting and present the design
• Create work packages and check out all project related objects
• Set up code review with Caremark architects and present the code
• Review Unit test results and send them out to Caremark architects to obtain their approval
• Review RTM (Requirement Traceability Matrix), QAT test plan and test cases and send them out to Caremark QA lead to obtain his/her approval
• Review QAT results and send it to Caremark QA lead for approval
• Review Implementation related documents
• Attend Implementation meeting with OPS and DBA
• Upload all code to PVCS
• Review Production turnover document with OPS team
• Work with OPS to implement Maestro schedule and all project related code into production
• Validate all implemented items
• Validate production run of jobs

Environment: Informatica PowerCenter 9.1, Oracle 11g, Toad for Oracle, Toad for DB2, Serena PVCS, Putty, MS Visio, MS Project, MS Windows XP Professional SP2, SUN OS

Organization: Confidential, May 2010 - Dec 2010
Role: Sr. ETL consultant

Confidential,maintains a portfolio of marketed pharmaceuticals for hypertension, hyperlipidemia, and bacterial infections around the world and also focuses on the discovery of novel oncology therapies. There were 2 parts to this project. One was to install Informatica for the client and the other was to build a new EDW. I worked on installing and testing the Web Services Hub part during the installation phase and help design the ABC for the new EDW.

Responsibilities:

• Gather information on the environment for the installation
• Help with installation of Informatica server
• Installation of Web services Hub
• Work with Informatica support to troubleshoot issues with installation
• Test the installation of Web services Hub
• Create a test mapping for testing the Web services hub
• Design ABC for the new EDW
• Involved in the preparation of documentation for ETL standards, procedures and naming conventions
• Offshore team co-ordination
• Help the team with QA testing for the project
• Create QA plan and test cases

Environment: Informatica Version 8.6.1 and 9.0.1, Netezza, Oracle 10g, HP Quality Center V 10.0 Enterprise Edition, MS office 2007 Visio, Excel and Word, MS Windows Vista Enterprise

Organization: Confidential, Nov 2009 - Apl 2010
Role: ETL Designer

Confidential,is the trade association for the independent, locally operated Blue Cross and Blue Shield Plans. As an ETL designer I was involved in gathering requirement for the FEPER (FEP Enrollment reporting) project. I lead a team of 5 onsite members for this project.

Responsibilities:

• Requirements gathering
• Analyzed specifications and identified source data that needed to be moved to data warehouse,
• Created ETL High level and Detail level Designs
• Extract data from Flat files, perform mappings based on requirements and load it into target Oracle Tables
• Created complex mappings using filters, expressions, aggregators, lookups, update strategies, sequence generators and stored procedures
• Extensively worked on the Database Triggers, Stored Procedures, Functions and Database Constraints
• Optimized and perform tuned mappings to achieve higher response times
• Worked with various Sources and Targets like Flat Files, CSV Files and Oracle
• Created Unix shell scripts to execute the process
• Help the team with QA testing for the project
• Create QA plan and test cases

Environment: Informatica 8.6.1, DB2 Version 9.2, ClearQuest, Putty, MS office 2007 Visio, MS office 2007 Excel, MS Windows Vista Enterprise SP1

Organization: Confidential,Texas Dec 2007 - Nov 2009
Role: ETL Designer

Confidential,offers health care solutions to its customers. As an ETL designer I was responsible for providing end-to-end solutions for the various projects that I was involved in. In this account I took upon multiple short term projects. The clients come up with these on demand projects as and when the client felt the necessity for them. These projects were mainly address the gaps identified in the existing EDW. The projects that I was involved in during this period in this account were Flexible Copay, iBenefit ARC, Drug Utilization Review, Copay Waiver, RxAmerica, Person Centric, RxInsider Migration and DEA / POC / HRA.

Responsibilities:

• Requirements gathering
• Create ETL High level and Detail level Designs
• Hold design review meetings to walk thru the design to the client architect
• Explain the design to the offshore team
• Assist the offshore team with the development

Involved in the loading and scheduling of jobs to be run in the batch process Used session partitions, dynamic cache memory and index caches for improving performance of Informatica server

• Review code developed by the offshore team
• Created Unix shell scripts to execute the process
• Offshore team co-ordination
• Create QA plan and test cases
• Help the offshore team with QA testing for the projects
• Involved in writing shell scripts for automating pre-session, post-session processes and batch execution at required frequency using power center server manager

Environment: Informatica PowerCenter 7.1.4, Oracle 10g, Oracle SQL Developer 1.2.0, Toad, DB2, Serena PVCS, Putty, MS Visio, MS Project, MS Windows XP Professional SP2, SUN OS

Organization: Confidential,USA Sep 2007-Nov 2007
Role: ETL Specialist

Confidential,offers health care solutions tailored to fit customer’s lifestyle and their specific health goals. As an ETL Specialist in the Master Medical project for this client, I was involved in creating High level and Detailed level Design documents, code some of the complex mappings, perform unit testing, support System/UAT testing. I was also the migration coordinator for this project.

Responsibilities:

• Requirement gathering
• Create ETL High level and Detail level Designs
• ETL Development for complex mappings and workflows
• Helped in designing the ABC (Audit, Balance and Control) for the project
• Review code developed by the offshore team
• Created Unix shell scripts to execute the process
• Created Job stream documents that explained the sequence of execution of the jobs
• Created parameter files as required
• Offshore co-ordination
• Perform Unit testing
• support System/UAT testing

Environment: Informatica PowerCenter 8.1.1, Oracle 10g, Oracle SQL Developer 1.2.0, Toad, DB2, Putty, MS Visio, MS Project, MS Windows XP Professional SP2

Organization: Confidential, Lisle, IL Feb 2006-Aug 2007
Role: ETL consultant

Confidential,is one of the world\'s leading international oil and gas companies, providing its customers with fuel for transportation, energy for heat and light, retail services and petrochemicals products for everyday items. My role as an ETL consultant in the project was to take design and development XML extract portion of the project. I also wrote UNIX shell scripts to compress output XML files and sftp them over to another server. My role included creating and documenting design, testing the code and documenting the results. I had to write complex SQL queries which were used in both development (Source Qualifier / Lookup SQL overrides) and Testing. I had to do conditional testing on the code with each possible outcome of a particular transformation and document them with sample data.

Responsibilities:

• Requirement gathering
• ETL Development including the code to create XML files as outputs sourcing database tables
• Create ETL Design
• Analyzed business process workflows and developed ETL procedures for moving data from source to target systems
• Created complex Informatica mappings and mapplets to load the data. The mappings involved extensive use of transformations like Aggregator, Filter, Router, Expression, and Joiner, Sequence generator
• Writing various UNIX Shell scripts and batch files to FTP between different servers
• Apply Audit Balance and Control (ABC) process wherever required
• Testing the process from end-to-end
• Production implementation
• Post-implementation production support

Environment: Informatica PowerCenter 7.1.1, Microsoft SQL Server 8.0, Kalido DIW 8, Rational ClearCase 2002, Windows server 2003( Infa Server), Windows XP Pro SP2 (Client), WinZip Command Line Support Add-On BETA Version 1.1, PSFTP, UNIX Shell scripts and DOS batch files

Organization: Confidential,California, LA Aug 2005-Dec 2005
Role: ETL Consultant/Upgrade Analyst

Confidential,is one of the leading banking companies in North America and the fourth largest bank in the state of California and one of the 25 largest banks in the US. The company has large number of customers and provides quality services to them. Their client database is huge and the services provided by the company are vast. The main objective of the project was to upgrade the current datawarehouse, which uses Informatica 5.1.2 to a new version Informatica 7.1.2. The role required planning the complete process, making necessary ETL changes, Testing, Implementing, and stabilization and support for a limited period. Responsibilities:

• Listing out the advantages of upgrading to Informatica Version 7.x from 5.x
• Involved in preparing metadata reports for every folder and proceeding based on the reports
• Identifying the strategy to make the appearance of the workflow tree in Infa 7.x to appear exactly the same as it appeared in Infa 5.x
• Making all the necessary ETL changes that had to be made for the upgrade process
• Enhanced mappings in customer dimension and ATM datamart
• Created shell scripts to execute workflows which were managed by TIVOLI Workload Schedulers
• Enhanced PL/SQL Procedures in Approval Matrix
• Performed unit testing, and system testing
• Created command line scripts for administrative and operational work
• Prepared comparison reports to highlight the effect of Infa 7.x over Infa 5.x

Environment: Informatica PowerCenter 7.1.2 and 5.1.2, Oracle 9.2.06, AIX 5.2, SQL Navigator 4.2, Windows 2000 SP4

Organization: Confidential, Dec 2004-Jun 2005
Role: ETL Developer

Confidential,is one of the largest life insurance companies in the U.S. The company offer a variety of products and services, including mutual funds, annuities, real estate brokerage franchises, relocation services, and more. The objective of the project is the development of data mart for Reporting System based on different regions/ divisions etc, to build various reports as per business requirements, thus enabling the management to take corporate decisions. Used Informatica ETL tool data extracted from various mainframe systems including flat files, relational databases and loaded into Oracle target database. Responsibilities:

• Used Source Pre Load, Source Post Load, Target Pre Load, Target Post Load functionality of Stored Procedure Transformation
• Extensively used Informatica functions LTRIM, RTRIM, IIF, DECODE, ISNULL, TO_DATE, DATE_COMPARE in Transformations
• Configured the mappings to handle the updates to preserve the existing records using Update Strategy Transformation (Slowly Changing Dimensions Type-2)
• Optimized the sessions, mappings, source and target databases for better performance
• Used Debugger to test the mappings and fix the bugs
• Involved in enhancements and maintenance activities of the data warehouse including performance tuning, rewriting of stored procedures for code enhancements, creating aggregate tables and modifying target tables
• Worked with QA in Testing the Implemented mappings for the data and logic errors
• Worked against severe deadlines to complete the interfaces on time
• Used SQL*Loader to minimize data load time into Oracle database
• Involved in writing UNIX scripts and used them to automate the scheduling process
• Did research work to solve production issues and data discard during workflow runs

Environment: Informatica PowerCenter 6.2, Oracle 9i, UNIX, PL/SQL, SQL Loader, Windows 2000/XP

Organization:Confidential, Jan 2004 – Dec 2004 Role: ETL Developer

Confidential,a vital part of Discovery Communications Inc., is the leader in real-world entertainment, selling a broad array of products like Electronics, fun clothing, toys, books, pet tools, weather tools etc. The project involved in the creation of Sales Analyzer Data Mart, which integrates data from daily retail sales transactions. This provides Discovery Channel Store with the capability to analyze sales performance from an actual and budgeted perspective and consequently decide the strategy for improving sales performance.

Responsibilities:

• Used Informatica PowerCenter 6.2 for migrating data from various OLTP databases to the data mart.
• The data migration included identifying various databases where the information laid scattered, understanding the complex business rules that need to be implemented and planning the data transformation methodology.
• Worked with different sources like Oracle and flat files.
• Translated the business processes into Informatica mappings for building the data mart.
• Extensively used informatica functions like IIF, DECODE, ISNULL, TO_DATE
• Extensively used PL/SQL for creation of stored procedures
• Performed Performance tuning of targets, sources, mappings and session and performed pipeline partitioning
• Used Debugger to test the mappings and fix the bugs
• Involved in research work to solve production issues
• Defined Target Load Order Plan and Constraint based loading for loading data correctly into different Target Tables
• Involved in writing UNIX scripts and used them to automate the scheduling process

Environment: Informatica PowerCenter 6.2, Oracle 9i, UNIX, PL/SQL, SQL Loader, Windows 2000

Organization:Confidential, Jun 2003 – Jan 2004

Role: ETL Developer

Confidential, is one of the largest diversified financial services organizations in the world. The project involved in designing and development of the Data warehousing project for the Account Management System. This data warehouse was developed to provide reports for the portfolio managers to assist in establishing, monitoring, evaluating, developing and implementing strategies for new account acquisition, credit limit setting, account management and maintenance of account acquisition.

Responsibilities:

• Installed and setup Informatica client systems
• Extensive use of Informatica PowerCenter tools – Source Analyzer, Warehouse Designer, Mapping Designer, Workflow Manager, Workflow Monitor, Mapplets and Reusable transformations
• Used various transformations like lookup, update strategy, router, filter, sequence generator, source qualifier/Joiner on data extracted according to the business rules and technical specifications
• Used Mapping Wizards in creating slowly changing dimensions
• Created sessions in the workflow manager and monitoring them in the workflow monitor
• Debugged mappings for failed sessions
• Tuned Informatica Mappings and Transformations for better performance
• Unit and integration tested Informatica Sessions, Workflows and the Target data
• Performed Oracle tuning for the target database
• Use shell scripting to schedule periodical runs
• Documented all the mappings and the transformations involved in ETL process
• Assisted in the design of report layouts to maximize usability and business relevance

Environment: Informatica PowerCenter 6.2, Oracle 9i, SQL Server, PL/SQL, UNIX

Organization:Confidential,India Feb 2002 – May 2003

Role: ETL Developer

Confidential,is one of the famous banks in India providing financial services to the people. The project involved in designing and development of the Data warehousing project using ETL tool Informatica. This project was developed in order to get data transformed from the base database, so that decisions can be made by looking at the resulting data.

Responsibilities:

Interpreted logical and physical data model for business users to determine common data definitions and establish referential integrity of the system. Participated in the analysis of development environment of extraction process and development architecture of ETL process Coordinated with team lead in finding the sources and targets for data conversion Extracted data from SQL server Source Systems and loaded into Oracle Target tables Used SQL Loader Utility to facilitate bulk loads into Oracle

• Performed unit testing

Environment: Informatica 5.1, SQL Server, Oracle 8.0 and WinNT4.0/2000.