SUMMARY:

• 7+ years of professional experience with comprehensive technical skill set and expertise in development and implementation of Data Warehouses & Data mart DataStage 8.x/9.x/11.x&with Informatica Power Center 9.X/8.X/ 7.x
• Involved in all phases of Software Development Life Cycle (SDLC): Requirement gathering, analysis, design, development, testing, production support and maintenance. Also worked on waterfall and Agile/Scrum methodology project.
• Good Knowledge on Logical data model, Physical data model, Data warehouse concepts, Dimensional Modeling using Star schema and Snow Flake schema.
• Developing Data Stage Sequences/Jobs, reusable Sequences/jobs and Activities for Daily process to loading heterogeneous data into the data warehouse.
• Extensively used stages like Sequential file, Datasets, DB Connectors (ODBC, Oracle, Netezzza), Aggregator, Joiner, Transformer, Lookup, Change Capture, Remove Duplicates, Peak, Copy and Pivot.
• Executed sequences, both sequential and concurrent for efficient execution of jobs and used other activities like Job Activity, Set Variables, Exec Command, Email Notification, Sequencer, Nested Conditions, Start Loop and End Loop.
• Experience in working with Informatica components like workflows, mappings, mapplets, sessions, tasks, Debugger, partitioning, reusable components and also extensively worked with session logs, workflow logs for error handling and troubleshooting on mapping failures.
• Good experience in developing Informatica Mappings, Mapplet, reusable Transformations, tasks, sessions and Workflows for Daily process to loading heterogeneous data into the data warehouse. Sources include delimited flat files, fixed width files, XML files, DB2 & ORACLE tables.
• Extensively used Transformations like Router, Aggregator, Joiner, Expression, Lookup, Update strategy, Union, Normalizer and Sequence generator.
• Executed sessions, both sequential and concurrent for efficient execution of mappings and used other tasks like event wait, event raise, email and command.
• Good experience in working with various database systems such as DB2, Oracle and SQL Server Databases.
• Identified and streamlined the commonly used logic into mapplets and reusable sessions.
• Proficient in writing the data clean up scripts using SQL queries and UNIX scripts.
• Good experience in setting up new jobs and job dependencies using Control - M
• Added appropriate dependencies at job level & batch process level for each job and Quantitative resources for each database/resource pools at scheduling tool to avoid deadlocks, timeout and connection issues.
• Managed the authenticity of UNIX components by Checking-in & Checking-out code from StarTeam/RTC, a code versioning tool.
• Experience in working with Onsite-Offshore model.
• Excellent communication and interpersonal skills, problem solving skills, analytical skills, flexible, self-direct, ability to work with minimum supervision, and a team player

SKILL:

ETL Tools: DataStage 8.1/9.x/11.x, Informatica Power center 7.X/8.x/9.x

Database Systems: IBM DB2, SQL Server 2005/2008/2014, Oracle, Postgres

Databases Tools: Squirrel, Oracle SQL developer, SQL Server Management Studio

BI Tools: Tableau 10x

Version Control: Star team, IBM RTC (Rational Team Concert)

Languages: UNIX Shell Scripting

Scheduling Tools: Control-M

Other Tools: Putty, Winscp, Visio

PROFESSIONAL EXPERIENCE:

Confidential, SFO, CA

DataStage Developer

Responsibilities:

• Extensively used DataStage for extracting, transforming and loading databases from sources including Oracle, DB2 and Flat files.
• Collaborated with EDW Team in, High Level design documents for extract, transform, validate and load ETL process data dictionaries, Metadata descriptions, file layouts and flow diagrams.
• Collaborated with EDW Team in, Low Level design document for mapping the files from source to target and implementing business logic.
• Generation of Surrogate Keys for the dimensions and fact tables for indexing and faster access of data in Data Warehouse.
• Tuned Transformations and jobs for Performance Enhancement.
• Extracted data from flat files and then transformed according to the requirement and Loaded into target tables using various stages like Sequential file, look up, Aggregator, Transformer, Join, Remove Duplicates, Change Capture Data, Sort, Column generators, Funnel and Oracle Enterprise.
• Created Batches (DS job controls) and Sequences to control set of jobs.
• Extensively used DataStage Change Data Capture for DB2 and Oracle files and employed Change Capture Stage in parallel jobs.
• Executed Pre and Post Session commands on Source and Target database using Shell Scripting.
• Collaborated in design testing using HP Quality Center.
• Extensively worked on Job Sequences to Control the Execution of the job flow using various Activities & Triggers (Conditional and Unconditional) like Job Activity, wait for file, Email Notification, Sequencer, Exception handler activity and Execute Command.
• Extensively used SAP R/3 and SAP BW packs
• Collaborated with BI and BO teams to find how reports are affected by a change to the Corporate Data Model.
• Collaborated with BO teams in designing dashboards and scorecards for Analysis and Tracking of key business metrics and goals.
• Utilized Parallelism through different partition methods to optimize performance in a large database environment.
• Developed DS jobs to populate the data into staging and Data Mart.
• Executed jobs through sequencer for better performance and easy maintenance.
• Performed the Unit Testing for jobs developed to ensure that it meets the requirements.
• Developed UNIX Shell Scripts to automate file manipulation and data loading procedures.
• Scheduled the jobs using AutoSys, Tivoli and Corntab.
• Responsible for daily verification that all scripts, downloads and file copies were executed as planned, troubleshooting any steps that failed and providing both immediate and long-term problem resolution.
• Provided Technical assistance and support to IT analysts and business community.

Confidential, San Antonio, TX

DataStage Developer

Responsibilities:

• Collaborates with project managers and prepare designs for project scope and perform risk analysis on same
• Collects, organizes and generates new documentation like high level technical documents, low level functional documents and data flow diagrams
• Designed and developed medium to complex DataStage jobs using stages such as the Oracle & DB2 connectors, Aggregator, Sequential File stage, Dataset stage, Transformer, Lookup, Filter, Remove Duplicates, Change Capture, FTP stage & Sort stages.
• Used DataStage sequences for Creating, Validating, Testing and running the DataStage jobs in sequential/parallel process flow to process & load full and incremental data into target system.
• Created numerous simple to complex queries involving self joins, correlated sub-queries
• Designed and developed several SQL processes to extensively test the ETL process across the environments
• Works with DBAs and prepares improvement plans for improving extractions & load process.
• Collaborating with cross functional teams and execute all project deployments.
• Managed the authenticity of Data Stage/ UNIX components by Checking-in & Checking-out code from and to RTC (IBM Rational Team Concert )
• Created ETL overview & Code walk through documents for maintenance teams.
• Documented all unit test cases for all tables across the hop’s and uploaded to project site for future reference
• Provides support during implementation and roll out activities
• Added appropriate dependencies at job level & batch process level for each job and Quantitative resources for each database/resource pools at scheduling tool to avoid deadlocks, timeout and connection issues.

Confidential, Bloomington, IL

Informatica Developer

Responsibilities:

• Prepared High-Level Design and Low-Level Design based on Functional and Business required documents of the project.
• Interact with the requirements team and architectural team to get a brief knowledge of business logics.
• Conducted Review sessions with SME’s and Business users for better understanding of the requirements.
• Extensively used ETL processes to load data from flat files into the target database by applying business logic on transformation mapping for inserting and updating records when loaded.
• Extensively used Normal Join, Full Outer Join, Detail Outer Join, and master Outer Join in the Joiner Transformation.
• Extensively used various transformations like Filter, Router, Sequence Generator, Lookups, Update Strategy, Joiner, Source Qualifier, Expression, Sorter, and Aggregator.
• Extensively used Mapping Variables, Mapping Parameters, and Parameter Files for the capturing delta loads.
• Worked with slowly changing dimension, Type1, Type2
• Create and execute unit test cases
• Keep track of the reported defects and support other teams to resolve defects

Confidential, San Antonio, TX

ETL Developer

Responsibilities:

• Developed Informatica mappings to load data into various dimensions and fact tables from various source systems.
• Created and managed Source to Target mapping documents for all Facts and Dimension tables
• Designed and Developed mappings using which involve different sources that includes Flat Files and Relational tables from heterogeneous databases like Oracle, SQL server and DB2.
• Designed and developed medium to complex informatica mappings using transformations such as the Source Qualifier, Aggregator, Expression, Lookup, Filter, Router, Rank, Sequence Generator, Stored Procedure and Update.
• Used Workflow Manager for Creating, Validating, Testing and running the sequential, parallel, sessions that perform Full and Incremental Loads to target system.
• Designed and developed medium to complex DataStage jobs using stages such as the Oracle & DB2 connectors, Aggregator, Sequential File stage, Dataset stage, Transformer, Lookup, Filter, Remove Duplicates, Change Capture, FTP stage & Sort stages.
• Used DataStage sequences for Creating, Validating, Testing and running the DataStage jobs in sequential/parallel process flow to process & load full and incremental data into target system.
• Extensively worked with Slowly Changing Dimensions (SCD) Type1 & Type2 for Data Loads.
• Created numerous simple to complex queries involving self joins, correlated sub-queries
• Identified and created various test scenarios for Unit testing the data loaded in target.

Confidential, San Antonio, TX

Informatica Developer

Responsibilities:

• Developed Informatica mappings to load data into various dimensions and fact tables from various source systems.
• Created and managed Source to Target mapping documents for all Facts and Dimension tables
• Designed and Developed mappings using which involve different sources that includes Flat Files and Relational tables from heterogeneous databases like Oracle, SQL server and DB2.
• Designed and developed Informatica power center medium to complex mappings using transformations such as the Source Qualifier, Aggregator, Expression, Lookup, Filter, Router, Rank, Sequence Generator, Stored Procedure and Update.
• Used Workflow Manager for Creating, Validating, Testing and running the sequential, parallel, sessions that perform Full and Incremental Loads to target system.
• Extensively worked with Slowly Changing Dimensions (SCD) Type1 & Type2 for Data Loads.
• Created Pre/Post Session SQL commands in sessions and mappings on the target instance.
• Created numerous simple to complex queries involving self joins, correlated sub-queries
• Developed and tested all the backend programs, Informatica mappings, sessions and workflows

Confidential, Detroit, MI

Informatica Developer

Responsibilities:

• Assisted gathering business requirements and worked closely with various Application and Business teams to develop Data Model, ETL procedures to design Data Warehouse.
• Extensively used ETL Informatica tool to extract data stored in MS SQL 2003, csv files and Flat files and finally loaded into a Data mart.
• Used various active and passive transformations such as Aggregator, Expression, Sorter, Router, Joiner, connected/unconnected Lookup, and Update Strategy transformations for data control, cleansing, and data movement.
• Designed and developed Mapplets for faster development, standardization and reusability purposes.
• Implemented Slowly Changing Dimension Type 1 and Type 2 for inserting and updating Target tables for maintaining the history.
• Used Debugger to validate transformations by creating break points to analyze and monitor Data flow.
• Tuned performance of Informatica Session by increasing block size, data cache size, sequence buffer length and Target based commit interval, and mappings by dropping and recreation of indexes.
• Involved in pre-and post-session migration planning for optimizing data load performance.
• Performed Unit testing during the mapping phase to ensure proper and efficient implementation of the transformations.
• Worked along with the QA Team and provided warranty support