PROFESSIONAL SUMMARY:

• 10+years of experience in the field of Information Technology with expertise in analysis, design, development and maintenance of Enterprise Data Warehouse and Business Intelligence applications using ETL tool Datastage.
• Strong experience in analysis, design, implementation and testing activities of Enterprise Datawarehouse applications.
• Efficient in all phases of the 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 the 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.

SKILL:

ETL Tool: Confidential Web Sphere Datastage v.7.5, Confidential 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: Confidential 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

WORK 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 that extract data from the source databases using DB connectorsOracle, DB2 and Teradata.
• Designed and developed jobs that read the 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 the data into the data warehouse.
• Imported Metadata from the source database. Imported metadata definitions into the repository. Exported and imported Data Stage components.
• Developed Job Sequences and batches/routines; edited the job control to have 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 that 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 the 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: Confidential 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 the jobs designed.
• Used the Administrator to set the environmental variables and user defined variables for different environments for different projects
• Job Parameters were extensively used to parameterize the jobs.
• Incorporated error handling in job designs using error files and error tables to log errors containing data discrepancies to analyze and re-process the data.
• Designed wrapper UNIX shell scripts to invoke the DS jobs from command line
• Developed the TWS schedules &jobsto schedule the running of jobs
• Troubleshooting the technical & production issues aroused during the load cycle
• Implemented enhancements to the current ETL programs based on the 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: Confidential 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 the dimensions to be used in the 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 the data.
• Created job sequences to facilitate scheduling of multiple jobs to automate the ETL process.
• Used the DataStage Director to run, test and debug jobs, and monitoring the resulting logs.
• Importing and exporting developed jobs within development and test environments using DataStage Manager.
• Created shell scripts for the environment setup and to execute the DS jobs from command line Confidential UNIX level.
• Designed and developed AutoSys jobs based on the business requirements and job dependencies.
• Created Unit Test Plans and review System Test Plan documents and supported QA team during System testing.

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

Environment: Confidential 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

ETL Consultant

Confidential

Responsibilities:

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

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

ETL Consultant

Confidential

Responsibilities:

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

ETL Consultant

Confidential

Responsibilities:

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

ETL Consultant

Confidential

Responsibilities:

• Creation of the schedule for the 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 the problems occurred during extraction and fix them.
• Converting the extracts generated using COBOL scripts.
• Generating the Statistical reports from the extracts and submitting for review.
• Loading the converted data after getting the 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 the 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 the Business functionality.
• Actively participated in the Team meetings to gather the business requirements and developing the 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 the design specification documents for the jobs
• Participated in the 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 Confidential AIX 5.1