SUMMARY:

• Over 7+ years of IT experience and Technical proficiency in ETL and Data Warehousing involved with Business Requirements Analysis, Application Design, Development, testing and documentation. Implementation of full lifecycle in Data warehouses and Data marts in various industries.
• Extensive experience in developing scalable enterprise applications to the user needs which serves for Financial, Insurance, Bank and Travel Industries.
• Excellent knowledge in Extraction, cleansing and modification of data from/to various Data sources.
• Developing, Analysing and Designing of ETL methodologies in all the phases of the Data Warehousing life cycle.
• Experienced converting the Business Logic into Technical Specifications.
• Involved in business meetings to gather the business requirement to define the Data Modeling.
• Extensively worked on Parallel Jobs using various stages like Join, Lookup, Transformer, Lookup File Set, Change Capture, Filter, Funnel, Copy, Surrogate Key Generator, Sort, File set, Sequential File, Dataset, Oracle Enterprise, Merge, Aggregator, Remove Duplicate Stages.
• Strong working experience on Informatica, SQL, PL/SQL, Store Procedures, Triggers and Packages. Performed debugging, troubleshooting and performance tuning.
• Well acquainted with Informatica Designer Components - Source Analyzer, Warehouse Designer, Transformation Developer, Mapplet and Mapping Designer.
• Worked extensively with complex mappings using different transformations like Source Qualifiers, Expressions, Filters, Joiners, Routers, Union, Unconnected / Connected Lookups and Aggregators.
• Strong Experience in developing Sessions/tasks, Worklets, Workflows using Workflow Manager Tools - Task Developer, Workflow & Worklet Designer.
• Experience in using the Informatica command line utilities like pmcmd to execute workflows in non-windows environments.
• Implementation of full lifecycle in Data warehouses and Business Data marts with Star Schemas and Snowflake Schemas.
• Created and implemented common UNIX shell scripts to FTP the files from/to different servers, validate, and process the Flat Files for different projects.
• Strong technical and professional client interactional skills. Interacted with Technical, Functional & Business audiences across different phases of the project of life cycle.
• Having good experience in the preparation of JIL scripts to schedule the jobs through Autosys.
• Good experience in various databases like Oracle 11g/10g/9i/ 8i, DB2 and SQL Server.
• Experienced in all phases of development lifecycle including production support and performance tuning.
• Provided 24X7 support to the users and having good experience in resolving high priority production issues within the time.

TECHNICAL SKILLS:

ETL Tools: Informatica 9.1/8.5.1/7.1 (Power Center/Power Mart) (Designer, Workflow Manager, Workflow Monitor, Server Manager, Power Connect), DataStage8.0/8.5

Databases: Oracle 11g/10g/8i/9i, SQL Server 2005/2008, Teradata, DB2

Languages: PL/SQL, Visual Basic, SQL, Pro* C, UNIX shell programming, C, HTML

Tools: SQL Navigator, SQL*Loader, SQL-Programmer, TOAD

Operating Systems: Windows,SunSolaris 2.6/2.7, HP-UX, UNIX

Others: MS VISIO, AUTOSYS

PROFESSIONAL EXPERIENCE:

Confidential, Charlotte, NC

Informatica ETL Developer

Responsibilities:

• Requirements Gathering and Business Analysis. Project coordination, End User meetings.
• Translated the business requirements into technical specifications to build the Enterprise data warehouse.
• Involved in System Study analysis for logical/physical data model there by defining strategy for implementing Star Schema with Fact and Dimension tables.
• Responsible for Data Modeling. Created Logical and Physical models for Staging, Transition and Production Warehouses using Erwin 7.2.
• Used IDQ / IDE for data cleansing and Analysis
• Responsible for developing, support and maintenance for the ETL (Extract, Transform and Load) processes using Informatica PowerCenter
• Extensively used Informatica Client tools - Source Analyzer, Warehouse Designer, Mapping Designer, Mapplet Designer, Informatica Repository Manager and Informatica Workflow Manager.
• Worked on multiple disparate Data sources ranging from flat files, XML Files, Oracle, SQL server databases to load the data into Oracle Database.
• Created and modified source database tables as per the design requirements and applying constraints to maintain complete Referential Integrity and creating indexes for performance and loading the data into Oracle database tables.
• Designed and developed Mappings using Mapping Designer to load the data from various sources using different transformations like Aggregator, Lookup (connected and unconnected), Filter, Router, Rank, Joiner, Source Qualifier, Expression, Stored Procedure, Sorter, Sequence Generator and Update Strategy transformations.
• Developed mappings with transformations (reusable) and mapplets confirming to the business rules. Developed and wrote procedures for getting the data from the Source systems to the Staging and to the Data Warehouse and Data Mart.
• Integrated various sources in to the Staging area in Data warehouse for Integrating and Cleansing data.
• Performed Source to Target Mapping.
• Installed, Maintained and Documented the Informatica setup on multiple environments.
• Designed, Developed, Deployed and implemented ETL mappings using Informatica
• Responsible for Design, Development, Administer and automation of ETL processes Informatica.
• Created Sessions, Workflows and Worklets for data loads using Workflow Manager
• Created events and tasks in the work flows using workflow manager
• Monitored Workflows and Sessions using Workflow Monitor.
• Executed sessions, sequential and concurrent batches for proper execution of mappings.
• Executed Pre and Post session commands on Source and Target database using Shell Scripting.
• Used mapping parameters and variables at mapping and session levels to tune the performance of Mappings.
• Migrated Workflows, Mappings, and other repository objects from Development to QA and then to production.
• Created Informatica sessions in workflow manager to load the data from staging to Target database.
• Created mappings to in corporate Incremental loads.
• Created reusable Mailing alerts, events, Tasks, Sessions, reusable worklets and workflows in Workflow manager.
• Followed proper naming conventions for the transformations and other repository objects.
• Re-designed existing Informatica mappings to have them compliant with the best practices which included eliminating un-necessary source lookups.
• Responsible for performance tuning at all levels of the Data warehouse. Implemented performance tuning by using lookup caches, using tables with fewer rows as the master table in joiner transformations, dropped indexes and re-created them after loading data to targets and increased the commit interval.
• Worked on Performance Tuning of various sources, targets, mappings and sessions to identify and remove Bottle Necks.
• Improved workflow performance by shifting filters as close as possible to the source and selecting tables with fewer rows as the master during joins.
• 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 and to transfer knowledge to production support and other team members.

Environment: Informatica PowerCenter 9.0.1 /8.6, IDQ, IDE, PL/SQL, SQL, SQL Loader, Toad, ETL, Erwin 7. x, Flat files, IMS Rx Data, Oracle 11g/10g, MS SQL Server 2008/2005, SAP BOBJ 4.0/BO XI R3/R2, shell Programming, IBM DB2 8.0, Control M, Toad, Excel and Unix scripting, Sun Solaris, Windows NT, Qlikview10.0

Confidential, Dallas, TX

Sr. Informatica Developer

Responsibilities:

• Responsible for Business Analysis and Requirements gathering.
• Created the (ER) Entity Relationship diagrams & maintained corresponding documentation for corporate data dictionary with all attributes, table names and constraints.
• Used Erwin 7.2/4.x for data modeling and Dimensional Data Modeling.
• Designed Sources to Targets mapping from primarily Flat files to Oracle using Informatica PowerCenter.
• Data Quality Analysis to determine cleansing requirements. Used Trillium for cleansing the data.
• Developed a data mart for Sales & Marketing to support the reporting requirements.
• Developed ETL methodology to custom fit the ETL needs of Sales & Marketing Datamart.
• Developed ETL procedures to ensure conformity, compliance with standards and lack of redundancy, translated business rules and functionality requirements into ETL procedures.
• Coordinating with source system owners, day-to-day ETL progress monitoring, Data warehouse target schema Design (Star Schema) and maintenance.
• Worked on Informatica Power Center 8.1/8.6 tool - Source Analyzer, Data warehousing designer, Mapping Designer & Mapplets, and Transformations. Developed and documented Data Mappings/Transformations, and Informatica sessions as per the business requirement.
• Various kinds of the transformations were used to create robust mappings in the Informatica Power Center Designer to implement simple and complex business logic. Transformations used are: Aggregator, Joiner, Union, Custom, Router, Expression, Update Strategy, Filter, Sorter transformations, stored Procedure, connected & unconnected lookups, Expressions, source qualifier, sequence Generator, etc.
• Created and Configured Workflows, Worklets, and Sessions to transport the data to target warehouse Oracle tables using Informatica Workflow Manager.
• Worked closely with Development managers to evaluate the overall project timeline.
• Involved in converting data stored in Oracle, XML, Flat files and SQL Server to Oracle and SQL Server using Informatica
• Used First Logic and its components ACE, DATA RIGHT, MERGE/PURGE for data cleansing.
• Actively contributed in framing the Data Warehouse SDLC standards, methodologies and best practices.
• Mapplets and Reusable Transformations were used to prevent redundancy of transformation usage and maintainability.
• Worked on Informatica Power Center tools- Mapping Designer, Repository Manager, Workflow Manager and Workflow Monitor.
• Scheduling the session tasks comprising of different mappings for data conversion and extraction in order to load data into target database.
• Migrated mappings, sessions, and workflows from Development to testing and then to Production environments.
• Implemented Autosys for scheduling Informatica jobs.
• Monitor; troubleshoot batches and sessions for weekly and Monthly extracts from various data sources across all platforms to the target database.
• Developed shell scripts for Informatica pre-session, post-session scripts.
• Tuned performance of Informatica session for large data files by increasing block size, data cache size and target based commit interval.
• Mapplets and Reusable Transformations were used to prevent redundancy of transformation usage and maintainability. Troubleshoot problems by checking sessions and error logs. Also used debugger for complex problem troubleshooting.
• Configured the sessions using workflow manager to have multiple partitions on Source data and to improve performance.
• Created Stored Procedures for data transformation purpose. Extensively worked on the Database Triggers, Functions and Database Constraints.
• Designed and developed UNIX scripts for creating, dropping tables which are used for scheduling the jobs. Developed a UNIX Shell scripts which will send the reports to client over the network by using file transfer protocol (FTP) and generating the log file, which will keep the history for the FTP reports.
• Extensively worked in the performance tuning at all levels, ETL Procedures and processes. Made performance improvements to the database by building Partitioned tables, Index Organized Tables and Bitmap Indexes.
• AUTOSYS was used as a scheduling tool to kick of the batch files sitting on the INFA server.
• Used Business Objects to create various reports.
• Maintained, developed, tested production mapping migration Using Repository Manager. Also used Repository Manager to maintain the metadata, Security and Reporting. Unit & Integration testing.

Environment: Informatica Power Center 8.6/8.1, (Source Analyzer, Data warehousing designer, Mapping Designer, Mapplet, Transformations, Workflow Manager, Workflow monitor, Worklets), Erwin 7.x/4.x, Oracle 11g/10g, Toad 7.6, DB2 8.0, MS SQL Server 2000, Autosys, First Logic, Business Objects XI/6.5, PL/SQL, SQL*Plus, SQL*Loader, XML, Windows NT 4.0, Sun Solaris Unix 2.6, Unix Shell Scripting.

Confidential, Bloomfield, CT

Informatica Developer

Responsibilities:

• Gathered requirements from business users and analyzed them based on the requirements.
• Designed and built Datamarts by using Star Schema’s. Created Logical and Physical Data models using Erwin extensively.
• Created Data acquisition and Interface System Design Document.
• Designed the Dimensional Model of the Data Warehouse Confirmation of source data layouts and needs
• Created Source and Target Definitions in the repository using Informatica Source Analyzer and Warehouse Designer.
• Identify source systems, connectivity, tables, and fields, ensure data suitability for mapping.
• Collection of data from source systems into database.
• Created Sessions and executed them to load the data from the source system using Informatica Workflow Manager.
• Developed and documented Data Mappings/Transformations, and Informatica sessions as per the business requirement.
• Assisted other ETL Developers in resolving complex scenarios.
• Extensively worked in the performance tuning of programs, ETL procedures and processes.
• Performance and delivery of advanced analytics: metrics design and analysis, data mining services, and operations research services
• Extensively worked HIPAA compliance. Worked with HIPAA rules and regulations and HIPAA transaction sets
• Extensively worked Claims, Enrollment, Eligibility verification for Members and Providers, benefits setup, and backend payment cycle.
• Extensively worked with the suite of Healthcare Applications such as Membership, Provider, Eligibility, Group, Product, Enrollment, Provider, Claims, Correspondence and Letters, Facilities, Customer
• Organized the developers according to various skill sets in order to quickly trouble shoot quality issues related to health care transactions which involved 835, 837 & 999 transactions. Responsible for 837 inbounds, 834 & 820 health care transactions & Reports
• Worked with business to understand various health care transactions that were required for the 5010 compliance
• Designed mapping specification for ETL teamto start coding for future enhancements
• Helped in historical data fixes and various reports that involved reconciliation & claim audits
• Researched sources and identified key attributes for Data Analysis.
• Developed Complex mappings in Informatica to load the data from various sources using different transformations like Custom, Union, Source Qualifier, Look up (connected and unconnected), Expression, Aggregate, Update Strategy, Sequence Generator, Joiner, Filter, Rank and Router transformations. Used debugger to test the mapping and fixed the bugs.
• Developed Mapplets using corresponding Source, Targets and Transformations.
• Executed sessions, sequential and concurrent batches for proper execution of mappings and sent e-mail using server manager.
• Tune Informatica Mappings and Sessions for optimum performance.
• Used session partitions, dynamic cache memory, and index cache to improve the performance of Informatica server.
• Documented the mappings used in ETL processes.
• Design source qualifier from File, Relational and Normalize Systems. Assisted in Migrating Repository.
• Designed and Developed the Informatica workflows/sessions to extract, transform and load the data into Target. Created database triggers for Data Security.
• Wrote SQL, PL/SQL codes, stored procedures and packages.
• Developed Informatica Mappings/Sessions to populate the Data Warehouse and Data Mart.
• Error checking and testing of the ETL procedures and programs using Informatica session log.
• Developed Unix Shell scripts to extract and convert data from different legacy systems in databases.
• Developed canned and ad hoc reports using Cognos and also Created custom reporting solutions
• Used Maestro for scheduling the jobs.
• Designed and developed Oracle PL/SQL Procedures and wrote SQL, PL/SQL scripts code for extracting data to system
• Performed integrated testing for various mappings. Tested the data and data integrity among various sources and targets. Provided Support.

Environment: Informatica PowerCenter 8.1 /7.1, Informatica PowerConnect, (Repository Manger, Designer, Server Manager, Workflow Monitor, Workflow Manager), ETL, Erwin 4.5, Flat files, Oracle 10g/9i, MS SQL Server 2005/2000, PL/SQL, Cognos, Maestro, HIPAA rules and regulations, HIPAA transaction sets, Shell Programming, SQL * Loader, IBM DB2 8.0, Toad, Excel and Unix scripting, Sun Solaris, Windows NT

Confidential, Denver, CO

ETL Developer

Responsibilities:

• Mapping Data Items from Source System to the Target System.
• Preparing the ETL Technical Design doc based on requirements.
• Writing the PL/SQL Procedures based on the business rules.
• Performing Data Modeling for staging tables and Target tables.
• Exception handling in the ETL(pl/sql) process based on the business rules
• Generating XML files and CSV files through ETL (pl/sql) process for ModelN System.
• Testing the ETL Jobs for Unit Test.
• Analyzing and Formatting the History data.
• Performed import and export DataStage jobs and various components through DataStage manager.
• Identifying suitable dimensions and facts for schema.
• Complex designing and mapping.
• Involved in system testing and unit testing.
• Used DataStage director to schedule and run jobs on daily, weekly, monthly.
• Responsible for supporting and managing database activity during the development process.
• Extensively worked on data validation and cleansing tools like QualityStage, Audit Stage, and Profile Stage.
• Worked on standardization of files to audit the input data and make sure that the data is valid.

Environment: DataStage 8.5/8.1, Oracle 10g/11g, SQL, PL/SQL, Shell Script, MS-VISIO

Confidential, Canton, OH

ETL Developer

Responsibilities:

• Developed DataStage Parallel & Server jobs for extraction transformation and loading of the data from source systems.
• Profiling data using IBM information analyzer. That means before extract the data into staging area need to do profiling for various countries like India, Brazil, and GPS.
• Prepared Functional specification documents, Process flow diagrams, Technical specification document, Mapping Documents for Source to Target mapping.
• Used Environment Variables, Stage Variables and Routines for developing Parameter Driven Jobs.
• Extensively used all activities like Routine Activity, Wait for File Activity, Exception Handler and Notification Activity.
• Worked extensively on different types of stages like Sequential file, Lookup File set, ODBC Stage, Aggregator, Transformer, Copy, Merge, Join, Filter, Column Generator, Funnel, Peek, Change Capture, Change Apply Stage, and several other stages for developing Parallel Jobs.
• Implemented the Slowly Changing Dimension, SCD Type-II strategy in DataStage, for the OLAP Dimension tables storing history data.
• Used DataStage Director to Run and Monitor the Jobs performed, automation of Job Control using Batch logic to execute and schedule various DataStage jobs.
• Created batch files to archives the production files and cleans from production once it’s archived.
• Created Test Plans and Test Scripts for Unit and Integration Testing for designed jobs.
• Loading data into warehouse environment, monitoring certain automatic loads, correction reloads, all in support of an existing environment.
• Created Shared Containers to increase Object Code Reusability and to increase thru put of the system.
• Performed extraction, transformation and loading of data using different types of stages and by performing derivations over the links connecting these stages.
• Worked with DataStage Director to schedule running the solution, testing and debugging its components, job logs, job resources and status files.
• Identified performance bottle-necks and suggested improvements.
• Worked with others- onsite team and offshore team, in a supporting role, to ensure ongoing quality of and availability of data warehouse environment.

Environment: DataStage 8.0.1/7.5.2, IBM Information analyzer, Oracle 9i, Toad, Windows NT, Clear case, MS-VISIO, Quest, Unix Scripting, MS Access