PROFESSIONAL SUMMARY:

• Over 7 years of experience in Information Technology with emphasis on Data Warehouse/Data Mart development using developing strategies for extraction, transformation, and loading (ETL) in Informatica Power Center 9.5/9.1/8.6/8.1/7.1.1 from various database sources.
• Strong work experience in Data Mart life cycle development, performed ETL procedure to load data from different sources like SQL Server, Oracle, Teradata and flat files into data marts and data warehouse using Power Center Repository Manager, Designer, Server Manager, Workflow Manager, and Workflow Monitor.
• Involved in understanding of Business Processes, grain identification, identification of dimensions and measures for OLAP applications.
• Extensive knowledge of Data Modeling, Data Conversions, Data integration Data Mining and Data Migration with specialization in Informatica Power Center.
• Extensive experience in designing and developing complex mappings from varied transformation logic like Unconnected and Connected lookups, Normalizer, Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, Update Strategy.
• Good experience in working with UNIX Shell Scripts for automatically running sessions, aborting sessions and creating parameter files using Control - M scheduler.
• Good experience in Writing number of Shell scripts to run various batch jobs.
• Solid experience in writing SQL queries, Stored Procedures, Cursors, Indexes, Views, Sequences and Triggers.
• Experience on Debugger to validate the mappings and gain troubleshooting information about data and error conditions.
• Proven track record in troubleshooting Informatica Sessions and addressing production issues like performance tuning and enhancement.
• Involved in import /export of existing mappings into different environments including Development, QA and Production.
• Expertise in Control-M scheduler extensively worked on Control-M to create and maintain jobs.
• Experience on post-session and pre-session Shell Scripts for tasks like merging flat files after Creating, deleting temporary files, changing the file name to reflect the file generated date etc.
• Proficient in the Integration of various data sources with multiple relational databases like Oracle11g /Oracle10g/9i, MS SQL Server, DB2, Teradata, Flat Files into the staging area, ODS, Data Warehouse and Data Mart.
• Developed Teradata Loading and Unloading utilities like Fast Export, Fast Load, and Multiload.
• Articulate with excellent communication and interpersonal skills with the ability to work in a team as well as individually

TECHNICAL SKILLS:

ETL Tools: Informatica Power Center 9.5/9.1/ 8.x.

Databases: Oracle 10g, 11g, MS SQL Server, Teradata,DB2

Environments: Unix, Windows, Linux

Tools: Toad 9.1, SQL developer, DB Visualizer, ERWIN 3.5/4.0, OBIEE, Control-M 9.0, ESP

Languages: UNIX shell Script, SQL, PL/SQL

PROFESSIONAL EXPERIENCE:

Confidential

Sr. ETL /Informatica Developer

Responsibilities:

• Analyzed the system for the functionality required as per the requirements and created System Requirement Specification document (Functional Requirement Document).
• Extensively used ETL to load data from heterogeneous sources like flat files (Fixed Width and Delimited), Oracle tables, and DB2 tables.
• Used Type 1 and Type 2 mappings to update Slowly Changing Dimension Tables.
• Extensively used Informatica client tools Source Analyzer, Warehouse designer, Mapping Designer, Transformation Developer, Informatica Repository Manager and Informatica Workflow Manager.
• Designed and Developed ETL Mappings with Usage of Lookups, Joiner, Expression, Aggregator, XML, Rank, Mapplet, connected and unconnected stored procedures, SQL Overrides in Lookups, Source filter and SQL Override in Source Qualifiers and data flow management into multiple targets using Routers were extensively done.
• Invoked and Executed Stored Procedures to transform the Data and worked extensively with PL/SQL for various needs of the transformations while loading the data into Oracle Database. Created Mappings with shared objects/Reusable Transformations/Mapplet using Mapping/Mapplet Parameters/Variables.
• Created Workflow, Worklets and Tasks to schedule the loads at required frequency using Workflow Manager.
• Used breakpoints and various test conditions with Debugger tool to test the logic and the validity of the data moving through the mappings.
• Configured workflows with Email Task, which would send mail with session log file for Failure of a sessions and for Target Failed Rows.
• Worked with UNIX shell scripts for job execution and automation. Scheduled Informatica workflows using Control-M to run at regular intervals.
• Created Job Streams and added job definitions in Control-M 9.0 and ESP and executed
• Extensively worked on Control-M 9.0 Scheduler to create and maintain the Cycles.
• Used SQL tools like Query Analyzer and DB Visualizer to run SQL queries and validate the data. Tuning the Mappings for Optimum Performance, Dependencies and Batch Design.
• Recording, tracking and closing of defects/bugs in a systematic manner into a Defect Tracking System and preparation of bug report with bug severity and bug priority.
• Documented ETL Design Documents for entire ETL processes.
• Involved in Unit testing, System testing, Integrated testing and User Acceptance Testing to check whether the data was loading into target, which was extracted from different source systems according to the business requirements.

Confidential, Detroit, MI

Informatica Developer

Responsibilities:

• Collected the mapping documents from the data architect and studied the same extensively and fitted the same into the existing frame work.
• Created / updated ETL design documents for all the Informatica components changed.
• Created Informatica transformations/mapplets/mappings/tasks/worklets/workflows to load the data from source to stage, stage to persistent, stage to reject and stage to core.
• Extensively used ETL to load date from various source systems like flat files, Oracle tables.
• Made use of various Designer transformations like Source Qualifier, Connected and Unconnected Lookups, Expression, Filter, Router, Sorter, Aggregator, Joiner, Normalizer, Rank, Router, Sequence generator, Union and Update Strategy transformations while creating mapplets/mappings.
• Made use of various Informatica source definitions viz. Flat files and Relational sources.
• Made use of various Informatica target definitions viz. relational data base targets.
• Made use of reusable Informatica transformations, shared sources and targets.
• Created mappings for Type1 and Type2 slowly changing dimensions (SCD) / complete refresh mappings.
• Implemented various loads like daily loads, weekly loads, quarterly loads and on demand load using Incremental loading strategy and concepts of changes Data Capture (CDC).
• Extensively used the Workflow Manager tasks like Session, Event-Wait, Timer, Command, Decision, Control and E-mail while creating worklets/workflows.
• Utilized Push down Optimization (PDO) for loading.
• Used Teradata Utilities (SQL Assistant, BTEQ, MultiLoad, and FastLoad) to maintain the database.
• Wrote complex SQL queries, Function, Store Procedure using cursor and record data type and trigger in Oracle database.
• Created Job Stream and added job definitions in Tivoli Workload Scheduler (TWS) and executed
• Created UNIX KSH shell scripts to FTP and cleanse source data files and archive data and to kick off the Informatica workflow / BTEQ’s in batch mode.
• Invoked Informatica using “pmcmd” utility from the UNIX script and Teradata using “BTEQ”.
• During the course of the project, participated in multiple meetings with the client and data architect / ETL architect to propose better strategies for performance improvement and gather new requirements.

Environment: Informatica 9.5.1, Teradata 14.0, Teradata SQL, UNIX shell scripting, Oracle 10g/11g, PL/SQL.

Confidential, Dallas, TX

Informatica Developer

Responsibilities:

• Collected requirements from business studied the same extensively and created transaction and summary tables for various lines of business Viz. Fund raising (FR) Preparedness Health Safety Services (PHSS) and Volunteer.
• Created / updated ETL design documents for all the Informatica components changed.
• Imported various Application Sources, created Targets and Transformations using Informatica Power Center 9.1 Designer (Source analyzer, Warehouse developer, Transformation developer, Mapplet designer, and Mapping designer).
• Created SSIS packages in the production support
• Analyzed relational databases of source systems for PHSS and FR.
• Created Informatica transformations/mapplets/mappings/tasks/worklets/workflows to load the data from existing data warehouse to the new constituent data mart.
• Collection of data source information from all the legacy systems and existing data stores.
• Involved in Data Extraction, Transformation and Loading from source systems to ODS.
• Made use of various Designer transformations like Source Qualifier, Connected and Unconnected Lookups, Expression, Filter, Router, Sorter, Aggregator, Joiner, Normalizer, Rank, Router, Sequence generator, Union and Update Strategy transformations while creating mapplets/mappings.
• Extensively used the Workflow Manager tasks like Session, Event-Wait, Timer, Command, Decision, Control and E-mail while creating worklets/workflows.
• Extensively used Dynamic lookup to implement the type 2 changes
• Used Push down optimization (PDO) to load the summary and transaction tables and compared the performance against the regular Informatica loading.
• Improved Performance by tuning SQL Queries at mapping levels. Tuned performance by changing index and data cache sizes, DTM buffer size and target based commit intervals.
• Implemented various loads like Daily Loads, Weekly Loads, and Quarterly Loads using Incremental Loading Strategy using the concepts of Change Data Capture (CDC).
• Used mapping parameters and variables / session parameters to make the code more flexible and achieve better reusability.
• Created UNIX KSH shell scripts to kick off the Informatica workflow using “pmcmd” utility in batch mode and also Teradata using “BTEQ”
• During the course of the project, participated in multiple meetings with the client to propose better strategies for performance improvement and gather new requirements.

Environment: Informatica 8.6.1/ 9.1.0, Teradata 12.0, Teradata SQL, UNIX shell scripting, Toad, Erwin.

Confidential, Northbrook, IL

Informatica Developer

Responsibilities:

• Involved in gathering, analyzing, and documenting business requirements, functional requirement for database changes, ETL, Business Reports.
• Extracted Data from different source systems like Oracle, and Flat files.
• Extensively worked on Informatica PowerCenter 9.1 for Extraction, Transformation and Loading of data from various sources into the target database which is in Oracle 11g/Oracle 10g.
• Extensively used various Data Cleansing and Data Conversion functions like LTRIM, RTRIM, TO DATE, Decode, and IIF functions in Expression Transformation.
• Worked with various transformations in Informatica power center like Filter, Aggregator, Joiner, Rank, Router, Sorter, Source qualifier, and Update Strategy transformations.
• Extensively worked with Joiner functions like normal join, full outer join, master outer join and detail outer join In the Joiner transformation.
• Worked with PL/SQL to create complex queries, function, store procedures, trigger etc.
• Developed Slowly Changing Dimension Mappings for Type 1 SCD and Type 2 SCD.
• Extensively used SQL* loader to load data from flat files to the database tables in Oracle.
• Responsible for best practices like naming conventions, Performance tuning, and Error Handling
• Responsible for Performance Tuning at the Source level, Target level, Mapping Level and Session Level.
• Performance tuning of SQL scripts using Oracle Explain Plan.
• Responsible for Unit testing and Integration testing of mappings and workflows.
• Reviewed and tested Informatica ETL code and wrote test plans.
• Responsible for 24/7 Informatica production support.
• Involved in various meetings, seminars, presentations and group discussions.

Environment: Informatica Power Center 9.1, Oracle 11g, Pl/SQL, TOAD, Erwin, SQL SERVER, Shell Scripts, Teradata.

Confidential

Informatica Developer

Responsibilities:

• Developed number of complex Informatica mappings, Mapplets, reusable transformation to implement the business logic and to load the data incrementally.
• Implementation of Data warehousing involving ETL Design, configuring of ETL tools, Data mapping transforming and loading in high volume environment and data processing at Terabytes level.
• Co- ordinations with offshore development and maintenance of projects.
• Developing the shell scripts to send the source files to the archiving folders or directories.
• Developed mappings involving SQL overrides in Lookups and aggregator and sending them into targets and also using the Router transformation on large chunk of data.
• Also used various transformations like Filter, Router, Expression, update strategy in various complex mappings.

Environment: Informatica 7.1.1, Windows XP, Oracle 9.2, Access, TOAD.