SUMMARY

• Around 8 years of Data Warehousing experience in the areas of Extraction, Transformation and Loading (ETL) design and development.
• Expert level experience in DataStage Client components - DataStage Designer, DataStage Manager, DataStage Director and DataStage Administrator.
• In Depth knowledge in Data Warehousing and Business Intelligence concepts with emphasis on ETL and full Life Cycle Development including requirement analysis, design, development, testing and implementation.
• Experience in Designing, Compiling, Testing, Scheduling and Running DataStage jobs.
• Designed Parallel jobs using various stages like join, merge, lookup, remove duplicates, filter, dataset, Sequential file, modify, aggregator, sort, CFF, Transformer.
• Used Data stage Director to debug, validate, schedule, run and monitor Data stage jobs.
• Experience in designing Batch Jobs with Job Sequences for scheduling parallel jobs using DataStage Director, Autosys, Control-M.
• Experience in writing UNIX shell scripts and hands on experience with scheduling of shell scripts using AUTOSYS.
• Experience in writing complex SQL queries for back-end testing.
• Proficiency in Defect management, including Defect creation, modification, tracking, and reporting using Industry standard Tools like Quality Center, Test Director, Clear Quest.
• Experience in production support, ETL executions, resolving and performing root cause analysis.
• Proven track record in troubleshooting of DataStage Jobs and addressing production issues such as performance tuning and enhancement.
• Experience in design and implementation of Star, Snowflake schemas and multi dimensional modeling.
• Experienced in interacting with Clients, Business Analysts, UAT Users and Developers.
• Experience in Data Analysis, Data Cleansing (Scrubbing), Data Validation and Verification, Data Conversion, Data Migrations and Data Mining.
• Experience in integration of various data sources like DB2, SQL Server, Oracle and Teradata into staging area.
• Excellent experience in most RDBMS including Oracle 11g/10g/9i/8i/8.0, SQL Server 10.0/9.0 7.0/6.5 and DB2 8.1/9.0.
• Involved in Logical and Physical Design, Backup, Restore, Data Integration and DTS (Data Transformation Services) and creating Database objects (tables, indexes, triggers, views, and stored procedures).
• Experience in writing, testing and implementation of Triggers, Procedures, Functions at Database level and form level using PL/SQL.
• A versatile team player with strong programming, analytical and problem solving skills, with ability to interact with individuals at all levels.
• Willingness and ability to quickly adapt to new environments and learn new technologies

TECHNICAL SKILLS

ETL Tools: DataStage 8.5/8.1/8.0 (Manager, Designer, Director) Ascential DataStage 7.5/7.0 (Parallel Extender, Orchestrate, Integrity, Meta Broker, Meta Stage)

Dimensional Data Modeling: Dimensional Data Modeling, Star Schema, Snow-Flake Schema, FACT and Dimensions Tables, Physical and Logical Data Modeling

Databases: DB2 UDB, Oracle 11g/10g/9i/8i/8.0, MS SQL Server 2008/2005/2000/7.0/6.5 , Sybase 5.0.1, Terradata

Operating systems: Windows XP/NT/2000/98, Unix, Linux

Other tools: Autosys, Control M, Toad, Putty, FTP, FileZilla, WinScp MS Office (Word, Excel, PowerPoint, Access)

Languages: C, C++, SQL, PL/SQL, Unix, Shell scripting

PROFESSIONAL EXPERIENCE

Confidential, Charlotte, NC

Sr. DataStage Developer/ Production support

Responsibilities:

• Interacted with end user community to understand the business requirements and in identifying data sources.
• Analyzed the existing informational sources and methods to identify problem areas and make recommendations for improvement. This required a detailed understanding of the data sources and researching possible solutions.
• Implemented dimensional model (logical and physical) in the existing architecture using Erwin.
• Assisted in preparing the mapping document from source to target.
• Developed Gap Analysis, Functional and Technical Design documents.
• Designed and developed ETL processes using DataStage designer to load data from Oracle, MS SQL, and Flat Files (Fixed Width) to staging database and from staging to the target Data Warehouse database.
• Used DataStage stages namely Hash file, Sequential file, Transformer, Aggregate, Sort, Datasets, Join, Lookup, Change Capture, Funnel, Peek, Row Generator stages in accomplishing the ETL Coding.
• Developed job sequencer with proper job dependencies, job control stages, triggers.
• Created complex logical summary facts as per business requirements.
• Worked with DataStage Manager for importing metadata from repository, new job Categories and creating new data elements
• 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, error handling and analysis.
• Extensively worked on implementing Slowly Changing Dimensions (SCD).
• Used DataStage Director to validate, schedule, run and monitor DataStage jobs.
• Developing shell scripts to automate file manipulation and data loading procedures.
• Monitoring of Websphere servers in Confidential for funds and investments message endpoints.
• Familiar with Datastage, Database errors in production and notify to the respective team.
• Checking the Audit Table frequently make sure that all sequencers are running in Autosys in Production.
• Preparing the Shift handover document to the next shift.
• Tested data migration to ensure that integrity of data was not compromised.
• Involved in extensive DATA validation using SQL queries and back-end testing
• Have worked in production support team for EDW ETLs execution support.
• Effectively coordinated with the development team for closing a defect in Production
• Debugging and Scheduling ETL Jobs/Mappings and monitoring error logs.
• Used Autosys job scheduler for automating the daily and monthly regular runs of DW.

Environment: IIS DataStage 8.5, Autosys, SQL Server 2008, Teradata, Oracle 11g/10g/9i, SQL Developer, PL/SQL, Windows 2000/2003/XP, UNIX, Shell Script, OBIEE.

Confidential, Cypress, CA

Data Warehouse Designer/Developer

Responsibilities:

• Designed and developed DataStage jobs for Claims, Benefits and member eligibilities.
• Participated in gathering of business requirements and participated in discussions with data modeler’s when creating a Data Model for Data mart and data warehouse.
• Involved in preparing technical design/specifications for Data Extraction, Transformation and Loading.
• Extensively used Parallel jobs to run jobs in Parallel Environment.
• Used Lookup, Join and CDC operator stage’s to take care of slowly changing dimensions.
• Designed the mappings between sources (external files and databases) to operational staging targets.
• Used Data Stage Manager for importing metadata from repository, new job categories and creating new data elements.
• Used Autosys job scheduler for automating the jobs and monitoring the resulting executable (on an ad hoc or scheduled basis).
• Worked on programs for scheduling Data loading and transformations using Data Stage from legacy system to Oracle 9i.
• Created Tables, Views, Database Procedures, Functions, Rollback segments, Table spaces, User creation in oracle database.
• Developed user defined Routines and Transformations.
• Used Parallel jobs for Material master to split the load to different processors.

Environment: DataStage 8.1 (Manager, Designer, Director, Administrator), Oracle 9i, DB2, SQL Server, PL/SQL, UNIX.

Confidential, Cleveland, OH

DataStage Developer

Responsibilities:

• Preparing technical design/specification for data extraction, Transformation and Loading and Data Cleansing.
• Used DataStage Designer to develop processes for extracting, cleansing, transforming, integrating and loading data into Data Warehouse database
• Created Jobs in ETL tool DataStage to populate the data files with lookup values. Used Sequential File stage, ODBC Stage, Hashed File stage and Transformer stage. Used constraints to get accurate values and to throw errors.
• Extensively used DataStage Parallel Extender (Orchestrate) and Integrity for creating and implementing jobs.
• Responsible for creating extract queries from oracle
• Wrote DataStage jobs using DataStage Designer and DataStage Manager and in depth experience in dealing with different stages (Hash, Ftp, Merge, Sort, Aggregator, Lookup)
• Used DataStage Manager to create many custom transforms and table definitions
• Responsible for creating DS job run scripts and scheduling them
• Performed Import and Export of DataStage components and table definitions using DataStage Manager
• Worked on database connections, SQL joins, views, aggregate conditions, parsing of objects and hierarchies
• To obtain better performance, tuned Data Stage jobs by creating Datasets for staging the data when performing joins, merge and lookups.
• Worked on Production issues and User Acceptance issues pertaining to data from Source to target

Environment: Data Stage 8.1 (Designer, Director, Administrator, Manager), Oracle 9i, SQL, PL/SQL, SQL* Loader, Shell Scripts, ERWin, MS SQL Server 7.0, Business Objects 5.0, Windows NT/2000

Confidential, Huston, TX

DataStage Developer

Responsibilities:

• Applied migration process from diverse systems.
• Expansively used Datastage to load data from Oracle, DB2 and Flat Files, COBOL files to Oracle.
• Broadly involved in Data Extraction, Transformation and Loading (ETL process) from Source to target systems using Data stage PX.
• Developed number of staging jobs to load the data into target table.
• Created number of complex jobs in this life cycle as per business requirements.
• Involved in migration, error management and reporting, data quality issues,
• Widely used stages like DB2, Flat File, Lookup, Joiner, Pivot, Transformer, Sort, Aggregator, Merge, Row Generator, and Column Generator.
• Used diverse partitioning methods like Hash, Same, Entire etc.
• Involved in creation of Data stage parallel jobs using various stages, Sequencers and arranged scripts to run them.
• Concerned with Unit testing, System testing to check data reliability.
• Extensively used Scheduling tool Control-M for scheduling Batch jobs to run assigned date and time.
• Developed SQL scripts to supplement the ETL process and to verify on final target data.
• Developed PL/SQL programs and Unix Scripts.
• Program Specification Document preparation and review, ETL Development, review and testing,
• Performance Tuning of the system.
• Organized data reports in by using filters, sorting and highlighted data with alerts.

Environment: IBM Data Stage 8.0 Parallel Extender, Data stage7.0, Oracle10g/9i, IBM DB2, PL/SQL, Toad, UNIX, Windows Server 2003, Control-M.

Confidential, Kansas

ETL Developer.

Responsibilities:

• Analysis and design review of the existing and new system.
• Mapping and migrating legacy system data to new n-tier system.
• Design and development Confidential Confidential source system data and finally make the data available to satisfy the reporting and analytical requirements of the long distance entry effort.
• Designed ODS to quick operations on individual data.
• Loaded data into load, staging and lookup tables.
• Performance tuned DataStage to reduce batch run time.
• Designed the DataStage jobs to extract the data from Text files, transform the data according to the business requirements and load them into Oracle tables.
• Worked with Business Analysts to prepare ETL Mapping Documents.
• Extensively used Stage variables and Hash files.
• Wrote Oracle stored procedures and called the stored procedures in Data Stage.
• Extensively worked with Dimensional Data Modeling, Star and Snowflake Schema.
• Implemented Slowly Changing Dimensions and Surrogate keys for designing the Data Marts
• Closely worked with the System analyst and application development teams Implemented Best Practices of DataStage.

Environment: DataStage 7.5, Oracle, MS SQL Server 2005, UNIX and shell scripts, Erwin, SQL Loader.

Confidential

Jr. Developer

Responsibilities:

• Interacted with business users in gathering the business requirements, identifying the relationship within and between the groups of data.
• Created the Functional/Technical specification documents by maintaining standards of the enterprise.
• Designed the technical document for various jobs, unit test documents with various scenarios and acceptance level of test case results.
• Designed the mappings between sources external files and databases such as Oracle, DB2 and
• Implemented logic for Slowly Changing Dimensions.
• Created Jobs for Error validations and Job monitoring.
• Designed and developed ETL Parallel Jobs using various stages like Join, Lookup, Lookup File Set, Change Capture, Change apply, Filter, Funnel, Copy, Column Generator, Peek, Dataset, Sequential File, Oracle Enterprise, Merge, Transformer, Aggregator, Remove Duplicates Stages etc to load data into data warehouse and Data Mart.
• Developed Job Sequences for automating the Data stage Jobs using various Triggers (Conditional and Unconditional).
• Worked with Data Stage Manager to import/export metadata from database, Data Stage Components between Data Stage projects.
• Performed data manipulation using Basic functions and DataStage transforms.
• Created a User variables Activity in DataStage Job Sequencer for fetching the records from past 24 hours.
• Involved in Performance tuning of DataStage Parallel jobs and implemented the standards and best practices.
• Designed, built, and managed complex data integration and load process.
• Extensively worked on Error handling, cleansing of data, creating lookup files and performing lookups for faster access of data.

Environment: DataStage Enterprise Edition 7.5 (Designer, Director, Manager, Parallel Extender), SQL, PL/SQL, Oracle 9i, DB2 UDB, MS-Access, Windows XP, Toad, Erwin.