- Around Six (6) years of IT experience with extensive Data Warehousing implementations across Retail, Financial and Communication Industries.
- Over Five (5) years of ETL and data integration experience in developing ETL mappings and scripts using Informatica PowerCenter 8.x/7.x/6.x and PowerMart 8.x/7.x.
- Experience in all the phases of Data warehouse life cycle involving Requirement Analysis, Design, Coding, Testing, and Deployment.
- Experience in working with business analysts to identify study and understand requirements and translated them into ETL code in Requirement Analysis phase.
- Experience in creating High Level Design and Detailed Design in the Design phase.
- Expertise in Business Model development with Dimensions, Hierarchies, Measures, Partitioning, Aggregation Rules, Time Series, Cache Management.
- Extensively worked on the ETL mappings, analysis and documentation of OLAP reports requirements. Solid understanding of OLAP concepts and challenges, especially with large data sets.
- Well versed in OLTP Data Modeling, Data warehousing concepts.
- Strong knowledge of Entity-Relationship concept, Facts and dimensions tables, slowly changing dimensions and Dimensional Modeling (Star Schema and Snow Flake Schema).
- Experience in integration of various data sources like Oracle, DB2, Sybase, SQL server and MS access and non-relational sources like flat files into staging area.
- Experience in creating Reusable Transformations (Joiner, Sorter, Aggregator, Expression, Lookup, Router, Filter, Update Strategy, Sequence Generator, Normalizer and Rank) and Mappings using Informatica Designer and processing tasks using Workflow Manager to move data from multiple sources into targets.
- Experience in creating Reusable Tasks (Sessions, Command, Email) and Non-Reusable Tasks (Decision, Event Wait, Event Raise, Timer, Assignment, Worklet, Control).
- Experienced in UNIX work environment, file transfers, job scheduling and error handling.
- Extensively worked on developing and debugging Informatica mappings, mapplets, sessions and workflows.
- Worked on Performance Tuning, identifying and resolving performance bottlenecks in various levels like sources, targets, mappings and sessions.
- Experience in writing, testing and implementation of the PL/SQL triggers, stored procedures, functions, packages.
- Involved in Unit testing, System testing to check whether the data loads into target are accurate.
- Experience in support and knowledge transfer to the production team.
- Proficient in interaction with the business users by conducting meetings with the clients in Requirements Analysis phase.
- Extensive functional and technical exposure.
- Assign work and provide technical oversight to onshore and offshore developers.
- Excellent analytical/ communication skills and good team player.
Bachelor of Engineering
Oracle 7.x/8.x/9x/10g/11g, SQL Server 2008/2005/2003/2000, DB2 UDB 7.2, My SQL 5.0/4.1
Oracle Enterprise Manager, Quest TOAD, SQL*PLUS, SQL*Loader
Informatica 8.x/7.x/6.x, Informatica PowerExchange 8.x/7.x
ERWIN , Visio
Oracle Form reports 10g, Oracle Reports 10g
C, PL/SQL, PERL, FORTRAN, SQL
UNIX, Windows ME/2000/2003/NT/XP, IBM AIX
Client: Confidential,OH Aug 2011- Till Date
Role: Sr. ETL Developer
Confidential,is the largest pharmacy health care provider in the United States. CVS Caremark is a market leader in mail order pharmacy, retail pharmacy, specialty pharmacy, and retail clinics, and is a leading provider of Medicare Part D Prescription Drug Plans. As one of the country\'s largest pharmacy benefits managers (PBMs), it provide access to a network of more than 64000 pharmacies, including approximately 7000 CVS/pharmacy® stores that provide unparalleled service and capabilities.
- Analysis of source systems andwork with business analyststo identify study and understandrequirements and translate them into ETL code.
- DevelopedDesign Document and Data Discoveryaccording to clients’ requirement.
- Closely worked withDBA’s for performancerelated issues.
- Used Mapping, Session and Workflow levelparameters.
- Created worklets in order to implementreusable logic composed of multiple sessions.
- Developed mappletsto implement reusabletransformational logic.
- Performanalysis on qualityandsourceof data to determine accuracy of information being reported and analyze data relationships between systems.
- Extensively worked on Data Maskingas well asperformance tuning.
- Used ILM toolforCreation of MappingforData Masking, Created Rules for ILM.
- Created mappings using the Transformations likeSource qualifier, Aggregator, Expression, lookup, Router, Filter, Rank, Sequence Generator, and Update Strategy.
- UsedDebuggerto test the data flowand fix the mappings.
- Actively involved in Performance Tuning.
- Created and monitored workflows and taskusing Informatica PowerCenter Workflow Manager.
- Partitioned Sessionsfor concurrent loading of data into the target tables.
- Created script to execute the workflow usingpmcmdcommand in UNIX.
- UsedDebuggerto test the data flowand fix the mappings.
- Implementeddaily and weekly audit processfor theClaimssubject area to ensure Data warehouse is matching with the source systems for critical reporting metrics.
- Involved in writingSQL scripts, stored procedures and functionsand debugging them.
- Prepared ETL mapping Documents for every mapping andData Migration document for smooth transfer of project from development to testing environment and then to production environment.
- Involved inUnit testing, System testingto check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.
- Migrated repository objects, services and scriptsfrom development environment to production environment.Extensive experience in troubleshootingand solving migration issues and production issues.
- Actively involved inproduction support. Implementedfixes/solutionsto issues/tickets raised by user community.
- Createdstep by step enduser manual forend usersreference.
Environment: Informatica PowerCenter 8.6.1/9.0.1, SQL Server 2008, Erwin 4.1, Windows XP, UNIX, Oracle 10g, Visio
Client: Confidential, OH Dec 2010 - July 2011
Role: Sr. ETL Developer
Confidential,is the first population health management company to truly integrate the best array of health and productivity services available, Nationwide Better Health can help to plan customers’ health and absence management programs more effectively, reduce health care costs and increase productivity.
- Implemented CDC by tracking the changes in critical fields required by the user.
- Developed standard and reusable mappings and mapplets using various transformations like Expression, Aggregator, Joiner, Router, Lookup (Connected and Unconnected) and Filter.
- Extensive use of Persistent cache to reduce session processing time.
- Identified performance issues in existing sources, targets and mappings by analyzing the data flow, evaluating transformations and tuned accordingly for better performance.
- Modified the shell/Perl scripts as per the business requirements.
- Maintained warehouse metadata, naming standards and warehouse standards for future application development.
- Used Workflow Manager for creating, validating, testing and running the sequential and concurrent sessions.
- Developed shell scripts for Daily and weekly Loads and scheduled using Unix Maestro utility.
- Developed shell scripts for file transferring
- Implemented screen door process for cleaning flat files as per the business requirements.
- Preparing ETL mapping Documents for every mapping and Data Migration document for smooth transfer of project from development to testing environment and then to production environment.
- Involved in Unit testing, Iterative testing to check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.
- Preparing and using test data/cases to verify accuracy and completeness of ETL process.
- Actively involved in the production support and also transferred knowledge to the other team members.
- Co-ordinate between different teams across circle and organization to resolve release related issues.
Environment: Informatica PowerCenter 8.6.1, Informix, SQL Server 2005, Erwin 4.1, Windows XP, UNIX.
Client: Confidential, CA May 2009 - Nov 2010
Role: Sr. ETL Developer
Confidential,is one of the nation\'s leading diversified financial services companies and the largest independent broker/dealer supporting over 12,000 financial advisors nationwide.This project was to implement a comprehensive database marketing solution that will provide consumer management and reporting across all channels and all brands. The selected solution is the Harte-Hanks Allink 360° Campaign Solution for storing data, selecting customers for marketing communications, and reporting on results.
- Interacted with the Business users to identify the process metrics and various key dimensions and measures. Involved in the complete life cycle of the project.
- Developed FRD (Functional requirement Document) and data architecture document and communicated with the concerned stakeholders. Conducted Impact and feasibility analysis.
- Extensively involved in Data Extraction from Mainframe source (VSAM), Oracle using PowerExchange when there was change in the source data sets.
- Created mappings using the Transformations like Source qualifier, Aggregator, Expression, lookup, Router, Filter, Rank, Sequence Generator, and Update Strategy.
- Read\\Wrote XML Messages from\\to MQ Series Queues and worked extensively with XML Parser (XSD) and XML Generator Transformations.
- Created Mapplets, reusable transformations and used them in different mappings.
- Created Workflows and used various tasks like Email, Event-wait and Event-raise, Timer, Scheduler, Control, Decision, Session in the workflow manager.
- Made use of Post-Session success and Post-Session failure commands in the Session task to execute scripts needed for clean up and update purposes.
- Implemented parallelism in loads by partitioning workflows using Pipeline, Round-Robin, Hash, Key Range and Pass-through partitions.
- Validation of Informatica mappings for source compatibility due to version changes at the source.
- Trouble shooting of long running sessions and fixing the issues.
- Implemented daily and weekly audit process for the Claims subject area to ensure Data warehouse is matching with the source systems for critical reporting metrics.
- Involved in writing SQL scripts, stored procedures and functions and debugging them.
- Prepared ETL mapping Documents for every mapping and Data Migration document for smooth transfer of project from development to testing environment and then to production environment.
- Involved in Unit testing, System testing to check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.
- Worked with reporting team to help understand them the user requirements on the reports and the measures on them. Helped them in creating canned reports.
- Migrated repository objects, services and scripts from development environment to production environment. Extensive experience in troubleshooting and solving migration issues and production issues.
- Actively involved in production support. Implemented fixes/solutions to issues/tickets raised by user community.
Environment:Informatica PowerCenter 8.6.1, PowerExchange 8.1, Oracle 10g/11g, SQL Server 2005, DB2, Mainframe, XML, UNIX, Erwin.
Confidential, ALSep 2008 - Apr 2009
Designation: Informatica Developer
Description: Intermedia serves the voice, data and high-speed Internet communication needs of organizations such as telecommunications providers, Internet service providers, retailers, financial service institutions, multi-tenant office buildings, business services, and government entities. This project involved development of a data warehouse in order to deliver timely, accurate and integrated information that can be used by internal and external customers. The single information source provided with a data warehouse will employ easy-to-use reporting tools that allow business analysts to report and analyze success metrics and trends.
- Interacted with business analysts and translate business requirements into technical specifications.
- Designed and developed end-to-end ETL process from various source systems to Staging area, from staging to Data Marts.
- Extensively worked on Informatica to extract data from Flat files, Excel files, and MS Access database to load the data into the target database.
- Implemented the Incremental loading of Dimension and Fact tables.
- Created Stored Procedures for data transformation purpose.
- Created Tasks, Workflows, Sessions to move the data at specific intervals on demand using Workflow Manager.
- Developed the control files to load various sales data into the system via SQL*Loader.
- Developed Unix Shell scripts for maintaining Files.
- Designed and created an Autosys job plan to schedule our processes.
- Created PL/SQL Stored procedures and implemented them through the Stored Procedure transformation.
- Develop, test and implement break/fix change tickets for maintenance.
- Involved in writing test cases, assisting Testing team in testing.
- Developed the document of the complete ETL process.
Environment: Informatica 7.1.1, UNIX, TOAD, IBM DB2 8.1, Oracle 9i (SQL/PLSQL), Win 2000, MS Excel 2006, UNIX.
Confidential,India Oct 2006 - Aug 2008
Designation: Informatica Developer
Confidential,one of India\'s leading healthcare groups, provides total healthcare solutions ranging from formulations, active pharmaceutical ingredients, biologicals, herbals, animal healthcare products to cosmeceuticals. As a member of the team, was involved in the design and development of warehouse system featuring an extensive inventory of new, overhauled and serviceable components. Also I involved in ETL part of the Data Warehouse.
- Designed, developedInformatica mappings, enabling the extract, transport and loading of the data into target tables.
- Created Workflow, Worklets and Tasks to schedule the loads at required frequency using Workflow Manager.
- Designed and developed Informatica mappings for data loads and data cleansing.
- Extensively worked onInformatica client tools like Designer, Workflow Manager and workflow monitor.
- Created complex mappings using Aggregator, Expression, Joiner transformations including complex lookups, Stored Procedures, Update Strategy and others.
- Involved in testing of Stored Procedures and Functions, Unit and Integrating testing of Informatica Sessions, Batches and the Target Data.
- Designed and developed table structures,stored procedures, and functionsto implement business rules.
- Extensively involved in migration of ETL environment, Database objects and EDW.
- Tested data integrity among various sources, targets and various performance related issues. Environment: Informatica PowerCenter 6.2.1, Windows 2000, SQL Server 2000, Oracle 8i, PL/SQL.