SUMMARY:

• 7+ years of experience in ETL process using Informatica Power Center.
• Good knowledge of Data warehouse concepts and principles Star Schema, Snowflake, SCD, Surrogate keys, Normalization/De normalization.
• Experience in integration of various data sources with Multiple Relational Databases like Oracle, SQL Server and Worked on integrating data from flat files like fixed width and delimited.
• Have extensively worked in developing ETL for supporting Data Extraction, transformations and loading using Informatica Power Center.
• 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. Extensively used Informatica Repository Manager and Workflow Monitor.
• Experience in debugging mappings. Identified bugs in existing mappings by analyzing the data flow and evaluating transformations.
• Hands on experience in Performance Tuning of sources, targets, transformations and sessions.
• Good experience in documenting the ETL process flow for better maintenance and analyzing the process flow.
• Worked with Stored Procedures, Triggers, Cursors, Indexes and Functions.
• Strong in UNIX Shell scripting. Developed UNIX scripts using PMCMD utility and scheduled ETL load using utilities like Autosys and Control-M.
• Strong Knowledge in optimizing database performance.
• Good knowledge of external tables and sql loader.
• Migrating data mapping to production and monitoring, troubleshooting and restarting batch processes using Informatica Power Center.
• Demonstrated ability to work and communicate effectively with both business and technical audiences.
• Highly motivated to take independent responsibility as well as ability to contribute and be a productive team member.

TECHNICAL SKILLS:

ETL Tools: Informatica Power Center

Databases: Oracle, SQL Server

GUI Tools: Toad, SQL Navigator, SQL* PLUS, SQL Developer, SQL Assistant.

Scheduling Tools: Informatica Scheduler, Autosys, Control-M

Scripting Languages: UNIX Shell Scripting

Operating Systems: Windows NT/2000/XP Professional/2003 Server, LINUX, UNIX

Oracle Tuning: SQL Tuning, Explain Plan, Table Partitioning, Materialized views, Hints.

PROFESSIONAL EXPERIENCE:

Informatica Developer

Confidential, Pittsburgh PA

Responsibilities:

• Worked closely with the end users in writing the functional specifications based on the business needs.
• Responsible for gathering suit of business requirements, prepare Source to Target Mapping specifications and Transformation rules.
• Created Source to Target Mapping Specification Document.
• Involved in system study, analyze the requirements by meeting the client and designing the system.
• Developed mappings/reusable objects/ transformation mapplets by using mapping designer, transformation developer and mapplet designer in Informatica Power Center.
• Extracted data from different sources like SQL Server, Oracle, Flat files and loaded into ODS.
• Designed and developed complex aggregate, join, lookup transformation rules (business rules) to generate consolidated (fact summary) data identified by dimensions using Informatica ETL tool.
• Used the Update Strategy Transformation to update the Target Dimension tables.
• Created connected and unconnected Lookup transformation to look up the data from the source and target tables.
• Involved in Performance tuning for sources, targets, mappings, sessions and server.
• Wrote SQL, PL/SQL, stored procedures for implementing business rules and transformations.
• Ensured the Data Marts loads, runs on time accurately.
• Developed batch file to automate the task of executing the different workflows and sessions associated with the mappings on the development server.
• Created test cases and completed unit, integration and system tests for Data Warehouse.
• Used Informatica Designer to create complex mappings using different transformations like Filter, Router, Connected & Unconnected lookups, Stored Procedure, Joiner, Update Strategy, Expressions and Aggregator transformations to pipeline data to Data Warehouse.
• Used SCD Type 2 to load historical data into the datawarehouse.
• Involved in versioning the whole process and retiring the old records using the built-in’s DD UPDATE, DD DELETE, and DD INSERT
• Identified sources, targets, mappings and sessions and tuned them to improve performance.
• Written triggers enforcing Integrity constraints, stored procedures for Complex mappings, and cursors for data extraction and to carry out updates of new fields
• Used Debugger in troubleshooting the existing mappings

Environment: Informatica, Windows XP, Oracle 11g, Toad, SQL Developer, SQL Plus and UNIX.

Informatica Developer

Confidential, Miamisburg OH

Responsibilities:

• Team co-ordination for design discussions, code review and mentoring.
• Worked with Informatica Power Center client tools like Repository Manager, Designer, Workflow Manager, and Workflow Monitor.
• Worked with Source Analyzer, Warehouse Designer, Transformation Developer, Mapplet Developer, and Mapping Designer.
• Worked with Task Developer, Worklet Designer, and Workflow Designer in the Workflow Manager.
• Designed and developed mappings utilizing the Source qualifier, Expression, Filter, Joiner, Lookup, Router, Sorter, Union, Normalizer, Rank, sequence generator and Update Strategy.
• Implemented Type I slowly changing dimension to update dimensional data in the table.
• Implemented performance tuning of Sources, Targets, Mappings and Sessions by identifying bottlenecks and used Debugger to debug the complex mappings and fix them.
• Implemented the error-handling process using relational database tables.
• Code review, fixed the invalid mappings and troubleshoot the technical problems of the database.
• Performance tuning of mappings during UAT stage and maintenance phases.
• Tracking Production failures and debug the sessions by utilizing the logs of the sessions.
• Optimized Query Performance, Session Performance and Reliability.
• Used SQL tools like SQL developer to run SQL queries and validate the data.
• Used PNC internal scheduler and Informatica Scheduler for scheduling the jobs.
• Perform Code review and unit testing at various levels of the ETL.
• Involved in migrating the Mappings, Sessions, Workflows from Test environment to Production environment.
• Integration of Unix shell scripting with Informatica PMCMD command utilities.

Environment: Informatica, Oracle 11g, SQL developer, SQL* Plus and UNIX.

Informatica Developer

Confidential, Tulsa

Responsibilities:

• Responsible for design, development, and support of new and existing ETL processes utilizing best practices into Enterprise Data Warehouse.
• Help for support activities in the Accounts Payable, Manufacturing Execution, Inventory Management, and Production Planning functional areas.
• Analyzing the existing model and making the changes as per the Change Requests from the Client.
• Translated and designed maps for the requirements and codes for Informatica and performed the tasks.
• Prepare documentation on all aspects of ETL processes, definitions and mappings
• Performed tuning of SQL queries for extraction of data and Troubleshoot and resolved production Issues.
• Involved in creating Informatica Mapping / Sessions and Workflows.
• Effectively resolved production issues and taking responsibility for seeing those issues thru resolution.
• Supported ETL routines required for populating the Data Warehouse and Experience in loading high-volume data, Tuning and troubleshooting of mapping.
• Extensively worked on Informatica Designer, Workflow Manager, and Workflow Monitor.
• Created different transformations for loading the data into targets using various transformations like Source Qualifier, Joiner transformation, Router, Update Strategy, Lookup transformation, Rank Transformations, Expressions, Aggregator, and Sequence Generator etc.
• Used the Aggregator transformation to load the summarized data for Sales and Finance departments.
• Created Reusable Transformations and Mapplets to use the transformation logic in multiple mappings.
• Worked on Repository manager to create and manage user profiles, and used Repository manager extensively while migration the code from development to production and compared both codes.
• Extensively worked on error handling.
• Worked on connected and unconnected lookups.
• Used the Informatica Workflow Manager to create and run the Workflows, Worklets and Sessions for loading the data using the logic from mappings.
• Create/Maintain database stored procedures, functions, views, types, tables.
• Implemented performance tuning of Sources, Targets, Mappings and Sessions by identifying bottlenecks and used Debugger to debug the complex mappings and fix them.
• Implemented Type II slowly changing dimension to keep a full history of dimensional data in the table

Environment: Informatica, Oracle 11g, Toad, SQL* Plus and UNIX.

Informatica Developer

Confidential, NY

Responsibilities:

• Worked closely with the end users in writing the functional specifications based on the business needs.
• Developed standards and procedures for transformation of data as it moves from source systems to the data warehouse.
• Created Technical design specification documents based on the functional design documents and the physical data model.
• Developed mappings to extract data from Oracle, Flat files, XML files and load into Data warehouse using the Mapping Designer.
• Used Source Analyzer and Warehouse designer to import the source and target database schemas, and the Mapping Designer to map the sources to the target.
• Used Informatica Designer to create complex mappings using different transformations like Filter, Router, Connected & Unconnected lookups, Stored Procedure, Joiner, Update Strategy, Expressions and Aggregator transformations to pipeline data to Data Warehouse.
• Involved in versioning the whole process and retiring the old records using the built-in's DD UPDATE, DD DELETE, and DD INSERT
• Extensively used B2B Data Exchange and Integrated data coming from outside the Enterprise and managed the business process.
• Involved in design and development of data warehouse environment, liaison to business users and/or technical teams gathering requirement specification documents and presenting and identifying data sources, targets and report generation.
• Worked on Informatica Power Center 8.6 tool - Source Analyzer, warehouse designer, Mapping Designer, Workflow Manager, Mapplets, and Reusable Transformations.
• Using Informatica Designer designed Mappings, which populated the Data into the Target Star Schema on Oracle 8i Instance.
• Optimized Query Performance, Session Performance and Reliability.
• Extensively used Normalizer, Router, Lookup, Aggregator, Expression and Update Strategy Transformations.
• Translation of Business processes into Informatica mappings for building Data marts.
• Involved in the Migration process from Development, Test and Production Environments.
• Used SQL tools like TOAD to run SQL queries and validate the data.
• Wrote stored procedures and triggers in Oracle 8i for managing consistency and referential integrity across data mart.
• Debugging invalid mappings using break points, testing of stored procedures and functions, testing of Informatica sessions, batches and the target Data.
• Developed shell scripts, PL/SQL procedures, for creating/dropping of table and indexes of performance for pre and post session management.
• Used DTS package to import and export data to from the database.
• Implemented various Performance Tuning techniques on Sources, Targets, Mappings, and Workflows.
• Identified sources, targets, mappings and sessions and tuned them to improve performance.
• Written triggers enforcing Integrity constraints, stored procedures for Complex mappings, and cursors for data extraction and to carry out updates of new fields
• Used Debugger in troubleshooting the existing mappings
• Tuning the Mappings for Optimum Performance, Dependencies and Batch Design.
• Schedule and Run Extraction and Load process and monitor sessions using Informatica Workflow Manager.
• Scheduled the batches to be run using the Workflow Manager.
• Involved in identifying bugs in existing mappings by analyzing the data flow, evaluating transformations and fixing the bugs so that they conform to the business needs

Environment: Informatica Power Center, Oracle, TOAD, SQL, PL/SQL, Windows XP, HP UNIX

Confidential, Dublin, OH

Developer (ETL Developer)

Responsibilities:

• Prepared the required application design documents based on functionality required
• Migrated repository and mapping from Informatica 6.2 to 7.1
• Designed the ETL processes using Informatica to load data from Oracle and Flat Files (Fixed Width) to staging database and from staging to the target Oracle Data Warehouse database.
• Implemented the best practices for the creation of mappings, sessions and workflows and performance optimization.
• Created mappings using transformations like Source Qualifier, Joiner, Aggregator, Expression, Filter, Router, Lookup, Update Strategy, and Sequence Generator.
• Designed and developed the logic for handling slowly changing dimension tables load by flagging the record using update strategy for populating the desired.
• Involved in cleansing and extraction of data and defined quality process for the warehouse.
• Involved in performance tuning and optimization of Informatica mappings and sessions using features like partitions and data/index cache to manage very large volume of data.
• Documented ETL test plans, test cases, test scripts, test procedures, assumptions, and validations based on design specifications for unit testing, system testing, expected results, preparing test data and loading for testing, error handling and analysis.
• Used Debugger in troubleshooting the existing mappings
• Developed UNIX shell scripts to control the process flow for Informatica workflows to handle high volume data.
• Strong Experience on writing SQL Queries, PL/SQL Procedures in Oracle Databases.
• Experience in PL/SQL Programming (Stored procedures, Triggers, Packages) using Oracle (SQL, PL/SQL).
• Developed shell scripts, PL/SQL procedures, for creating/dropping of table and indexes of performance for pre and post session management.
• Used DTS package to import and export data to from the database.
• Created documentation on mapping designs and ETL processes.
• Created and scheduled Sessions, Jobs based on demand, run on time and run only once using Workflow Manager.
• Involved in migration of mappings and sessions from development repository to production repository
• Involved in Unit testing, User Acceptance Testing to check whether the data is loading into target, which was extracted from different source systems according to the user requirements.
• Involved in production support working with various mitigation tickets created while the users working to retrieve the database.
• Created Stored Procedures to transform the Data and worked extensively in PL/SQL for various needs of the transformations while loading the data.

Environment: Informatica Power Center, Oracle 10g/9i, TOAD, SQL, PL/SQL, Windows XP, UNIX