SUMMARY

• Around 6+ years of experience in Data warehousing in the areas of ETL Design and Development. Proficient in all stages of Software Development life - cycle including System Analysis, Design, Development, Implementation, Production Support and Maintenance.
• Extensive experience in Extraction, Transformation and Loading (ETL) data from various sources using IBM Websphere DataStage, Ascential DataStage. Worked on DataStage client tools like DataStage Designer, DataStage Director and DataStage Administrator.
• Strong understanding of the principles of DataWarehouse using fact tables, dimension tables and star/snowflake schema modeling.
• Familiar in using highly scalable parallel processing infrastructure using parallel jobs.
• Experienced in scheduling sequence using DataStage Director, UNIX scripts.
• Worked extensively with Dimensional modeling, Data migration, Data cleansing, ETL Processes for data warehouses.
• Migrated parallel jobs from DataStage 8.5 to DataStage 11.5 as per requirement.
• Involved in DataStage migration and backup from Version 8.5 to 11.5 and also modified the all UNIX/scheduling scripts as per DataStage 11.5.
• Used Enterprise Edition/Parallel Extender stages like Datasets, Sort, Remove Duplicate, Join, Lookup, Merge, Change Capture, Funnel, Row Generator and many other stages in accomplishing the ETL Coding
• Extracted the data from the mainframe applications which is the hierarchy file system by using the complex flat file stage.
• Worked with and extracted data from various database sources Oracle 10g/9i/8i, DB2, SQL Server, Teradata, Sequential files.
• Developed the DataStage jobs Deals with binary data as well as fixed length data from mainframe systems.
• Worked with the federation server stage to extract and load the data in to the mainframe applications.
• Experience in troubleshooting of jobs and addressing production issues like performance tuning and enhancement.
• Expertise in Unit testing, Integration testing, back-end testing and maintenance.
• Expert in performance analysis of the jobs, queries and performance enhancement and tuning of jobs.
• Worked on Quality Stage to cleanse the data. Used stages like Investigate, Standardize, Match, Survive, Transfer, Collapse, Sort, and Abbreviate.
• Assisted in development efforts for Datamarts and Reporting.
• Technical and analytical skills with clear understanding of design goals of ER modeling for OLTP and dimension modeling for OLAP.
• Extensive experience in writing Triggers, Packages, Stored procedures and Functions using PL/SQL and T-SQL.

TECHNICAL SKILLS

ETL Tools: IBM Info Sphere Data Stage 8.5/9.1/11.5 , IBM Web Sphere Data Stage 8.0.1, (Designer, Director, Administrator), Ascential Data Stage 7.5.2 (Designer, Director, Administrator, Manager).

Data Modeling: Data Modeling, Star Schema Modeling, Snow-Flake Modeling, Fact and Dimensions, Physical and Logical Data Modeling using ERwin.

Databases: Oracle10g/9i/8i, IBM DB2/UDB, Mainframes, Teradata, SQL Server 2003/2005/2008.

Tools: SQL Plus, SQL*Loader, TOAD.

Operating system: IBM AIX 5.2/4.x, Linux, Windows 2000/2003/XP.

Languages: SQL, PL/SQL, UNIX Shell Scripting, Java, HTML, DHTML

BI Reporting Tools: Business Objects, Congo's and Crystal Reports

PROFESSIONAL EXPERIENCE

Confidential, Chicago, IL

ETL Data stage Developer

Responsibilities:

• Extensively involved in the data migration team to build the Re-usable Datastage job templates, common parameter sets, common Datastage job containers, SQL extract procedures and common re-usable shell scripts.
• Worked with business analyst to identify, develop business requirements, transform it into technical requirements and responsible for deliverables.
• Provide the staging solutions for Data validation with PL/SQL and Datastage ETL jobs.
• Extensively worked with DataStage 11.5 V - Designer and Director to load data from source extract files to warehouse house.
• Designed and developed DataStage jobs for Loading Staging Data from different sources like Teradata, Oracle, SQL Server DB into Data Warehouse applying business rules which consists data loads, data cleansing, and data massaging.
• Used the Datastage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions
• Created Parameters and Parameter sets where necessary.
• Scheduled the server jobs using DataStage Director, which are controlled by DataStage engine and also for monitoring and performance statistics of each stage.
• Created shell script to run Datastage jobs from UNIX and then schedule this script to run Datastage jobs through scheduling tool
• Experience in troubleshooting of jobs and addressing production issues like performance tuning and enhancement.
• Extensively involved in Unit testing, Integration testing, back-end testing and maintenance.
• Worked with experienced team members to conduct root cause analysis of issues, review new and existing code and performed unit testing.
• Created system documentation/play book.

Environment: Information server - Datastage 11.5, DB2,Netezza, Oracle, UNIX AIX and Windows 7, PL/SQL,ORACLE

Confidential, Jersey City NJ

ETL Datastage Developer

Responsibilities:

• Extensively involved in the data migration team to build the Re-usable Datastage job templates, common parameter sets, common Datastage job containers, SQL extract procedures and common re-usable shell scripts.
• Worked with business analyst to identify, develop business requirements, transform it into technical requirements and responsible for deliverables.
• Expertise in all the phases of System development life Cycle (SDLC) using different methodologies like Agile, Waterfall.
• Provide the staging solutions for Data validation and Cleansing with PL/SQL and Datastage ETL jobs.
• Implemented Aggregate, Filter, Join, Lookup, Rank and Update Strategy transformations.
• Extensively worked with DataStage 8.5 V - Designer and Director to load data from source extract files to warehouse.house.
• Created Datastage jobs to load data from sequential files, CSV, Flat files, SQL Server and Oracle.
• Cleansed, Standardize data using Quality stage.
• Developed various jobs using ORA OCI, ODBC, Aggregator, Sequential file stages.
• Worked towards optimal performance when using Stages like LOOKUP, JOIN, and MERGE.
• Designed and developed DataStage jobs for Loading Staging Data from different sources like Teradata, Oracle, SQL Server DB into Data Warehouse applying business rules which consists data loads, data cleansing, and data massaging.
• Used the Datastage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions
• Created Shared Container to simplify the Datastage Design, and used it as a common job component through out the project.
• Worked with Metadata Workbench, Import and Export of DataStage jobs using Datastage tools functionality.
• Created Parameters and Parameter sets where necessary.
• Tuned DataStage jobs for better performance by creating DataStage Hash files for staging the data and lookups.
• Scheduled the server jobs using DataStage Director, which are controlled by DataStage engine and also for monitoring and performance statistics of each stage.
• Performed functional testing for new release enhancements.
• Participated actively in the team meetings with the developers for bug reviews.
• Developed Shell Scripts to automate file manipulation and data loading procedures.
• Developed Parallel jobs using Parallel stages like Merge, Join, Lookup, Transformer
• (Parallel), Funnel, Dataset, Oracle Enterprise Stage.
• Writing PL/SQL procedures for processing business logic in database .Tuning of SQL queries for better performance.

Environment: Information server - Datastage 8.5/9.1, SAP ECC, SQL Server, DB2, Oracle 10g, UNIX AIX and Windows 7, PL/SQL,ORACLE Data Integrator

Confidential, Richmond, VA

ETL Datastage Developer

Responsibilities:

• Extracted data from source systems transformed and loaded into Oracle database according to the required provision.
• Involved in system analysis, design, development, support and documentation.
• Created objects like tables, views, Materialized views procedures, packages using Oracle tools like PL/SQL, SQL*Plus, SQL*Loader and Handled Exceptions.
• Involved in database development by creating Oracle PL/SQL Functions, Procedures, Triggers, Packages, Records and Collections.
• Created views for hiding actual tables and to eliminate the complexity of the large queries.
• Created various indexes on tables to improve the performance by eliminating the full table scans.
• Involved in the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating and loading data into Data Marts.
• Used Aggregator stages to sum the key performance indicators in decision support systems and for granularity required in DW.
• Used the DataStage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions on an ad hoc or scheduled basis.
• Implemented parallel extender jobs for better performance using stages like Join, Merge, Sort and Lookup, transformer with different source files complex flat files, XML files.
• Scheduled DataStage job using Autosys scheduling tool.
• Developed complex store procedures using input/output parameters, cursors, views, triggers and complex queries using temp tables and joins.
• Created shell script to run data stage jobs from UNIX and then schedule this script to run data stage jobs through scheduling tool.
• Developed Test Plan that included the scope of the release, entrance and exit criteria and overall test strategy. Created detailed Test Cases and Test sets and executed them manually.
• Involved in the IMS upgrade (Information Management System) in mainframes from IMS version 11 to IMS version 13.
• Involved in testing the jobs and running the jobs in mainframes.
• Worked on mainframe JCLs.
• Solid experience in using UNIX and IBM AIX commands and writing UNIX shell scripts.
• Experienced in working with scheduling tool Autosys.
• Extensive experience in loading high volume data and performance tuning.

Environment: IBM Info Sphere Data Stage 8.5,Sap r/pack, Oracle 11/10g, Mainframes, Flat files, VSAM files, UNIX, NDM, SQL plus, SQL server, MS Visio.

Confidential

ETL Data stage Developer

Responsibilities:

• Developed several ETL specifications documents (Programmer level specifications) and also developed jobs using DataStage and other tools.
• Used Version Control extensively to jobs from one project to another.
• Extracted the data from Oracle, SQL Server using PL/SQL scripts to transfer the data to clients depending on the requirements.
• Utilized various Oracle blocks like packages, triggers, indexes, cursors, joins to extract the data that is needed for the creation of a DataStage job.
• Programmed and created several routines and shell scripts to change the data according to the business specifications.
• Part of the team in Dimensional modeling of the Data warehouse. Used Erwin to design the business process, dimensions and measured facts. Identified and documented the slowly changing dimensions
• Worked with the Business Analysis in understanding the business and development process of the client and facilitated appropriate data and development solutions.
• Worked with several DataStage stages like OraOci, ODBC, Teradata Multiload/ Fastload etc. to transfer the data in Oracle, SQL Server to Teradata.
• Responsible for monitoring all the jobs that are running, scheduled, completed and failed.
• Troubleshooting the failed job is a primer in these situations.
• Worked on database connections, SQL joins, cardinalities, loops, aliases, views, aggregate conditions, parsing of objects and hierarchies.
• Created multiple repositories, users and privileges in DataStage Manager and Administrator
• Tuned of SQL queries for better performance for processing business logic in the database.
• Configured DataStage Server and Client components.

Environment: Ascential DataStage 7.5, Teradata v2 R4.0/v2R5.0, Teradata SQL Assistant 6.1, Oracle 9i., PL/SQL SQL Server 2000, Autosys, UNIX Shell Scripting, Erwin 4.1