SUMMARY

• Strong experience in analysis, design, implementation and testing activities of Enterprise Datawarehouse applications.
• Efficient in all phases of teh development lifecycle; coherent with Data Cleansing, Data Integration, Data Transformation, Data Aggregation and Performance Tuning
• Hands - on experience in designing both server and parallel jobs using various stages like Transformer, Join, Merge, Lookup, Funnel, Aggregator, Copy, Modify, Remove Duplicatesand Filter
• Experienced in using advance datastage plug in stages like XML input and XML output.
• Familiarity in integration of various data sources like Oracle, MS SQL Server, Teradata, Complex Flat Files and Sequential Files
• Hands-on experience in troubleshooting production issues and performance tuning of ETL jobs
• Experienced in design and development of common re-usable ETL components to define audit processes, Job monitoring and error logging processes.
• Expertise in design & implementation of SQL queries, PL/SQL packages, stored procedures and functions.
• Strong hands on experience using Teradata utilities (SQL, B-TEQ, Fast Load, MultiLoad, FastExport, Tpump)
• Strong knowledge & experience in UNIX shellscripting and perl scripting.
• Experienced in working with SOA environment and familiar in using MQ Connector stage and DTS stage.
• Had training on Hadoop and Big Data technology stack. Has exposure on ClouderaHadoop distribution, PIG, HIVE and MapR platform. Also familiar with Big data integration tool Talend
• Hands-on experience with Job scheduling tools like Autosys& TWS.
• Familiarity in using teh service management and IT governance tools (HP Open View Service Desk, HPSM and HP ITG)
• Efficient in creating source to target mappings (ETL Mapping) from various source systems into target EDW (Enterprise Data warehouse) and documenting design specifications.
• Strong understanding of SDLC and Agile Methodologies
• Strong Analytical and Problem Solving Skills.
• Can quickly adapt to a changing environment.

TECHNICAL SKILLS

ETL Tool: IBM Web Sphere Datastage v.7.5, IBM Info Sphere Information Server 8.5, 8.7,9.1.2 (Datastage)

Databases: Oracle 10g/9i/8.x/7.x, Oracle RDB, Teradata 14.1, MS SQL Server 2000, MS Access 2003

Environment: IBM AIX 5.3, AIX 6.1, Linux, VMS V8.3,Windows Xp/7

Others: SQL Plus, PL/SQL, SQL*Loader, TOAD, HPSD Open View, HPSM, Autosys 4.5, Tivoli Workload Scheduler, HP - IT Governance tool

PROFESSIONAL EXPERIENCE

Confidential

ETL Lead Developer

Responsibilities:

• Involved in understanding of business processes and coordinated with business analysts to get specific user requirements.
• Designed and developed jobs dat extract data from teh source databases using DB connectorsOracle, DB2 and Teradata.
• Designed and developed jobs dat read teh data from diverse sources such as flat files, XML files, and MQs.
• Created job parameters and parameter sets, shared containers.
• Consistently used Change Capture, Transformer, Modify, Copy, Join, Funnel, Aggregator, Lookup stages and other processing stages to develop parallel jobs.
• Experienced in development and debugging of jobs using stages like Peek, Head & Tail Stage, Row generator stage, Column generator stage, SampleStage
• Generated Surrogate Keys for composite attributes while loading teh data into teh data warehouse.
• Imported Metadata from teh source database. Imported metadata definitions into teh repository. Exported and imported Data Stage components.
• Developed Job Sequences and batches/routines; edited teh job control to has jobs run in sequence.
• Performed troubleshooting and tuning of Data Stage Jobs for better performance.
• Designed and developed PL/SQL scripts and extensively used Teradata utilities like BTEQ scripts, FastLaod, MultiLoad to perform bulk database loads and updates.
• Involved in creating SQL queries, performance tuning and creation of indexes.
• Extensively used materialized views for designing fact tables.
• Ensured dat operational and analytical data warehouses are able to support all business requirements for business reporting.
• Developed Unix shell scripts and Perls scripts for controlled execution of Datastage jobs
• Extensively designed jobs in TWS scheduler for execution of teh jobs in sequence
• Participated in DataStage Design and Code reviews.
• Worked in Agile/Scrum environment.
• Documented ETL test plans, test cases, test scripts, and validations based on design specifications for unit testing, system testing, functional testing, prepared test data for testing and error handling

Environment: IBM Information server 8.5, AIX 5.3, AIX 6.1, Linux 2.6.18, Oracle 10g, TWS, Teradata 14.1, TOAD, SQL*Loader, SQL Plus, PL/SQL, HPSM

Confidential

Sr ETL Developer

Responsibilities:

• Extensively used Data stage Designer used various stages such as OCI, Remove Duplicates, Sort, Lookup, Join, Aggregator, Transformer,Copy andFunnel stage in job design.
• Has written Job Control routines to invoke one DS job from another. Also designed a custom BuildOpstage to suit our project requirements, which was reused in teh jobs designed.
• Used teh Administrator to set teh environmental variables and user defined variables for different environments for different projects
• Job Parameters were extensively used to parameterize teh jobs.
• Incorporated error handling in job designs using error files and error tables to log errors containing data discrepancies to analyze and re-process teh data.
• Designed wrapper UNIX shell scripts to invoke teh DS jobs from command line
• Developed teh TWS schedules &jobsto schedule teh running of jobs
• Troubleshooting teh technical & production issues aroused during teh load cycle
• Implemented enhancements to teh current ETL programs based on teh new requirements.
• Involved in all phases of testing and effectively interacted with testing team.
• Documented ETL high level design and detail level design documents.

Environment: IBM DataStage 7.5.1, AIX 5.3, Oracle 10g, TWS maestro, TOAD, SQL*Loader, SQL Plus, PL/SQL, HPSM, Mercury ITG

Confidential

Sr ETL Consultant

Responsibilities:

• Developed Data Stage server and parallel jobs to extract, transform and load data into data warehouses from various sources like Oracle 10g, flat files etc.
• Developed Parallel jobs using Stages such as Aggregator, Funnel, Join, Merge, Lookup, Sort, Remove Duplicates, OCI and Transformer.
• Used DataStageManager for importing metadata in to repository using OCI plugin.
• Generations of Surrogate IDs for teh dimensions to be used in teh fact table for indexed and faster data access.
• Incorporated error handling in job designs using error files and error tables to log errors containing data discrepancies to analyze and re-process teh data.
• Created job sequences to facilitate scheduling of multiple jobs to automate teh ETL process.
• Used teh DataStage Director to run, test and debug jobs, and monitoring teh resulting logs.
• Importing and exporting developed jobs within development and test environments using DataStage Manager.
• Created shell scripts for teh environment setup and to execute teh DS jobs from command line Confidential UNIX level.
• Designed and developed AutoSys jobs based on teh business requirements and job dependencies.
• Created Unit Test Plans and review System Test Plan documents and supported QA team during System testing.

Environment: IBM DataStage 7.5/7.5.1, AIX 5.3, Oracle 10g,AutoSys 4.5, TOAD, SQL*Loader, SQL Plus, PL/SQL, HP OpenView Service Desk, Mercury ITG

Confidential

ETL Consultant

Responsibilities:

• Monitor teh incoming input data files on daily basis and communicate with source systems about teh non availability of teh files on regular basis.
• Perform catch-up load for all teh data files accumulated in dat particular month.
• Run DataStage Jobs (Server& Parallel), PL/SQL scripts, Shell Scripts & SQL*Loader jobs to extract, transform and load teh data on to teh warehouse.
• Schedule teh jobs using teh scheduler tool AutoSys 4.5
• Troubleshoot teh technical & production issues aroused during teh load cycle
• Modify existing jobs as per teh change requests and tune teh performance of teh ETL jobs
• Resolve teh incidents/service calls opened by teh users.
• Communicate teh load status to teh business users on regular basis.
• Daily turnover of status to offshore team and provide technical assistance to them.

Environment: IBM DataStage 7.5/7.5.1, AIX 5.3, Oracle 10g, AutoSys 4.5, TOAD, MS Access 2003, SQL*Loader, SQL Plus, PL/SQL, HP OpenView Service Desk, Mercury ITG

Confidential

ETL Consultant

Responsibilities:

• Continuous monitoring of teh application and provide technical support round teh clock.
• Work on enhancement requests and also fine tune teh application as per user’s request
• Troubleshoot teh issues faced by teh users
• Communicate with users regarding any updates to teh application.
• Handle administrative tasks such as adding new users or deleting existing users or modifying existing users to teh application
• Design and develop new reports or forms for teh application as and when requested by teh users
• Perform unit & system testing for teh new modules developed
• Resolve teh incidents/service calls opened by teh users.
• Handle teh server & database migrations for teh application

Environment: SQL Server 2000, MS Access 2003, SQL*Loader, SQL Plus, PL/SQL, HPSD OpenView Service Desk

Confidential

ETL Consultant

Responsibilities:

• Designing and publishing teh calendar to users with release dates of teh data.
• Running teh ETL Jobs (includes PL/SQL scripts, Unix Shell Scripts, COBOL, DCL scripts & SQL Server DTS packages) to extract, transform and load data on to teh warehouses.
• Scheduling teh jobs using teh scheduler tool AutoSys 4.5
• Troubleshooting teh technical & production issues aroused during teh load cycle
• Designing new ETL jobs as per teh new requirements, modifying existing jobs as per teh change requests and tuning teh performance of teh ETL jobs
• Resolving teh incidents/service calls opened by teh users and update teh progress regularly in teh HP OpenView Service Desk
• Handling server migrations and environment migrations. Also performing system testing during migrations.
• Designing & running teh SQL server DTS packages or PL/SQL scripts to pull teh data from warehouses and generate reports as per teh user’s requirements.
• Communicating teh load status effectively to teh business users on regular basis.
• Working along with DBA to enhance teh performance of teh datawarehouse databases.
• Daily turnover of status to teh OM team (offshore & onshore) and provide technical assistance to them.

Environment: AIX 5.3, AIX 5.2, Oracle 10g, Oracle 8.x, SQL Server 2000, SQL*Loader, SQL Plus, PL/SQL, COBOL, AutoSys 4.5, TOAD, HP OpenView Service Desk, Mercury ITG

Confidential

ETL Consultant

Responsibilities:

• Design and publish teh calendar to users with release dates of data.
• Execute cleanups, update parameters and prepare for new load cycle.
• Schedule teh extract jobs (DCL Scripts and COBOL scripts) for new load cycle on teh legacy VMS server
• Run teh DataStage Jobs (Server), PL/SQL scripts, Shell Scripts, COBOL & DCL scripts to extract, transform and load teh data on to teh warehouses.
• Schedule teh jobs using teh scheduler tool AutoSys 4.5
• Troubleshoot teh technical & production issues aroused during teh load cycle
• Design & develop new ETL jobs as per teh work orders, modify existing jobs as per teh change requests and tune teh performance of teh ETL jobs
• Resolve teh incidents/service calls opened by teh users
• Daily turnover of status to offshore team and provide technical assistance to them.

Environment: DataStage 7.5, AIX 5.3, AIX 5.2, VMS V8.3, Oracle 10g, Oracle RDB, AutoSys 4.5, TOAD, MS Access 2000, SQL*Loader, SQL Plus, PL/SQL, COBOL, HPSD OpenView Service Desk, Mercury ITG

Confidential

ETL Consultant

Responsibilities:

• Design and publish teh calendar to users with release dates of data.
• Execute cleanups, update parameters and prepare for new load cycle.
• Schedule teh extract jobs (DCL Scripts and COBOL scripts) for new load cycle on teh legacy VMS server
• Run teh DataStage Jobs (Server), PL/SQL scripts, Shell Scripts, COBOL scripts, DCL scripts and SQL*Loader jobs to extract, transform and load teh data on to teh warehouses.
• Schedule teh jobs using teh scheduler tool AutoSys 4.5
• Troubleshoot teh technical & production issues aroused during teh load cycle
• Work on teh enhancement requests as per teh users requests and tune teh performance of teh jobs.
• Resolve teh incidents/service calls opened by teh users and update teh progress regularly in teh HP OpenView Service Desk.
• Handling server migrations and environment migrations. Also performing system testing during migrations.
• Communicate teh load status to teh users on regular basis.
• Working along with DBAs to enhance teh performance of teh datawarehouse databases.
• Daily turnover of status to teh OMteam (offshore & onshore).

Environment: DataStage 7.5, AIX 5.3, VMS V8.3, Oracle 10g, Oracle Rdb, AutoSys 4.5, TOAD, MS Access 2000, SQL*Loader, SQL Plus, PL/SQL, COBOL, HPSD OpenView Service Desk, Mercury ITG

Confidential

ETL Consultant

Responsibilities:

• Creation of teh schedule for teh PAAX activities each month.
• Generating extracts for various subject areas using data from various systems, which include Data Warehouses, Data Marts & several external input files using DataStage 6.5 &Cobol Programs.
• Analyzing teh problems occurred during extraction and fix them.
• Converting teh extracts generated using COBOL scripts.
• Generating teh Statistical reports from teh extracts and submitting for review.
• Loading teh converted data after getting teh approval from business
• Continuous interaction with client and Onsite team.

Environment: AIX 5.2, VMS V8.3, Oracle 8, Oracle RDB, AutoSys 4.5, SQL*Loader, SQL Plus, PL/SQL, COBOL, DataStage 6.5

Confidential

ETL Developer

Responsibilities:

• Extensively used DataStage Designer to develop various Parallel jobs to extract, cleanse, transform, integrate and load data into Enterprise Data Warehouse tables.
• Worked with DataStage Manager to import/export metadata, DataStage Components between teh projects.
• Involved in Design, Source to Target Mappings between sources to operational staging targets, using Star Schema, Implemented logic for Slowly Changing Dimensions.
• Involved in Performance Tuning of Parallel Jobs using Performance Statistics, Dump Score.
• Used Various Standard and Custom Routines in DataStage jobs.
• Extensively worked with DataStage Shared Containers for Re-using teh Business functionality.
• Actively participated in teh Team meetings to gather teh business requirements and developing teh Specifications.
• Designed and Created BuildOp and Wrapper Stages to execute Stored Procedures using Unix Scripts.
• Extensively wrote user-defined SQL coding for overriding for Auto generated SQL query in DataStage.
• Involved in unit testing, system testing and integration testing.
• Created and edited teh design specification documents for teh jobs
• Participated in teh review of Technical, Business Transformation Requirements Document.
• Participated in discussions with Team leader, Group Members and Technical Manager regarding any technical and Business Requirement issues.
• Coordinated with team members Confidential times of change in Business requirements and change in Data Mart Schema.

Environment: Ascential Datastage 7.5.X/PX, TOAD, MSSQL, DTS, DB2, PL/SQL, SQL*Loader IBM AIX 5.1