SUMMARY

• Over 8 years of IT experience in analysis, design, development, test and implementation of software applications in data warehousing and Client/Server environment.
• Extensive experience working wif ETL tool IBM DataStage 9.1/8.7/8.5/8.1/7. x (Information Server, Web Sphere, Ascential DataStage).
• Designed and successfully implemented Enterprise Data warehouse and synchronized Data marts.
• Experience in development and TEM Peffective implementation of Data cleansing, Data acquisition and Data integration tasks using ETL tool Datastage.
• Strong skills in design and implementation of Star Schema and Snowflake Schema used in Dimensional and Multidimensional modelling.
• Excellent technical and analytical skills wif clear understanding of design goals of ER modeling for OLTP and dimension modeling for OLAP using Erwin and MS Visio.
• Expertise wif Data stage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
• Extensively used Parallel Job Stages like StoredProcedure, Dataset/Fileset, Aggregator, Join, Transformer, Sort, Merge, Filter, Modify, Lookup, Funnel and Pivot.
• Extensive Data warehouse experience using Teradata, tools and utilities like BTEQ, Fast Load, Multi Load, Fast Export, Tpump and Teradata SQL assistant.
• Extensively used SQL/PLSQL in creation and execution of Stored Procedures and Database Triggers.
• Expertise wif SQL, complex queries, optimization and fine tuning.
• Worked on Quality Stage for address standardization, match passing, country wide segregation of data
• Expertise in Data Warehousing techniques for Data Cleansing, Slowly Changing Dimension phenomenon (SCD), Surrogate Key assignment and CDC (Change Data Capture).
• Designed, developed and implemented efficient Error handling methods
• Experienced in in corporation of various data sources like Oracle, SQL server, DB2, SAP and flat.
• CreatedShell Scriptsfor invoking SQL scripts and scheduled them using crontab.
• Strong experience in Scheduling jobs using CONTROL - M and AUTOSYS.
• Extensive experience in loading high volume data, and performance tuning
• Experience in integration of various data sources like Oracle 9i/10g/11g, DB2 9.5, Teradata 13/14, SQL Server 2005/2008, MS Access, XML and Flat files into teh Staging Area
• Used BuildOps in Datastage to implement Custom Logic.
• Working knowledge on Reporting tools like Microstrategy and Cognos.
• Extensively used TOAD 9.0/8.5, SQL Navigator 6.1 to access Oracle database.
• Experienced in Onshore - Off Shore coordination, Development and Production Support Teams.
• Thorough business knowledge across industries such as Banking, Telecom, Retail & Health Insurance.
• Having excellent track record as a strong team player wif TEMPeffective communication, analytical and multi-tasking skills, resourceful, result driven and self-motivated.

TECHNICAL SKILLS

ETL Tools: IBM Infosphere Datastage 9.1/8.7/8.5/8.1/7. 5

Databases: Teradata V2R6/R12/R13/R14, Oracle 11g/10g/9i/8i/7.3, PL/SQL, DB2 UDB

Dimensional Data Modeling: Data Modeling, Star Schema Modeling, Snow-Flake Modeling, FACT and Dimensions tables, physical and logical data modeling, ERwin

Languages: UNIX Shell scripting

Methodologies: QC plans, test strategy, Quality center and SDLC.

Scheduling: Control-M, Zena

PROFESSIONAL EXPERIENCE

Confidential, Mettawa, IL

Sr. ETL/Data warehouse Consultant

Responsibilities:

• Interacted wif users in recovery, mortgage and cards businesses to gather requirements for enhancements.
• Designed and developed an ETL process to extract data from legacy collection database, transforming and loading into DW teh first time, tan on daily and weekly.
• Developed mapping to load teh data in slowly changing dimension.
• Designed and developed complex ETL logic using many datastage stages including Transformer, Join, Merge, Funnel, sort, change capture, change apply & aggregator.
• Used Oracle, Teradata EE, DB2 Connector to extract teh data from source systems.
• Worked on Head, Tail and Peek stages for debugging DataStage jobs.
• Used parameter sets for easy maintenance of project specific parameters and better functionality.
• Created shared containers to use in multiple jobs.
• Design and Developed before job and after job subroutines.
• Involved in Performance Tuning of source and target.
• Used Job Sequencer stages to link multiple jobs in Series/Parallel based on teh requirement
• Used Teradata API and Teradata Multiload stages to load teh data into Data Warehouse.
• For One time straight loads used Fastload and MultiLoad Unix scripts.
• Designed and developed sequences and scheduled teh jobs in CA7 for batch runs.
• Participated in design reviews to develop "pre-defined" reports coming out of teh Dealer data mart.
• Coded and debugged numerous Teradata scripts - BTEQs, MLOADs, FLOADs and FastExport.
• Supported teh Data warehouse during and after teh implementation wif 24/7 support.
• Worked on Performance tuning and Optimizing teh datastage jobs and Unix/Teradata scripts.
• Created ‘lessons learned’ and ‘best practices’ documents.
• Created and executed QA test scenarios and test cases in HP quality center.
• Implemented various process checks, data checks and mail notifications to ensure teh quality of teh data that is loaded into teh data warehouse.

Environment: IBM InfoSphere Data Stage 9.1/8.0 (Designer, Director, Administrator), Teradata R12, V2R6 (SQL, Scripts, Macros), MLOAD, FLOAD, BTEQ, FastExport, cognos, Oracle 9i and UNIX

Confidential, Lewiston, MAINE

Sr. Datastage Consultant

Responsibilities:

• Performed data analysis on subjects like customer, products, plans, payments & fraud.
• Developed data transfer strategy from various new and legacy data sources.
• Developed and used Stored Procedures to run on pre session and post session commands.
• Helped wif teh architecture and design of Data warehouse and discovery data store.
• Used teh DataStage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse.
• Extensively worked wif DataStage Stages for Data Staging and Data Transformation
• Extensively worked wif DataStage Designer to pull data from flat files, Oracle to target databases and sequential file.
• Extensively worked wif Job sequences using Job Activity, Email Notification, Sequencer, Wait for File activities to control and execute teh Data stage Parallel jobs
• Extracted data from heterogeneous sources and loaded into Oracle staging area, and executed transformation rules and loaded it into DW
• Used Complex Flat File (CFF) stage to read data from Mainframe sources and handling EBCDIC to ASCII translations.
• Extensively used LookUp, Merge, Join, Aggregator, Remove Duplicate and Transformer Stages.
• Used Surrogate Key generator, aggregate, expression, lookup, update strategy, router, and rank transformation.
• Developed joiner transformation for extracting data from multiple sources.
• Used Web Services like XML input and XML Output in Data Stage 8.1
• Designed and Developed data validation, load processes, test cases, and error control routines using PL/SQL, SQL.
• Involved in design and creation of table structures, sequence generators, indexes, and foreign key constraints.
• Created Stored procedures and database triggers using PL/SQL
• Created materialized views per user requirements, coded complex SQLs.
• Used Information Analyzer for Profiling tasks and Quality Stage for Cleansing Tasks.
• Used Business Glossary to build a vocabulary system between business metadata and technical metadata.
• Created data lineage and impact analysis reports in Metadata Workbench.
• Used Zena Scheduling tool to Schedule teh jobs.
• Designed ETL generic jobs to extract data from fixed width files and load into EDW.
• Developed server jobs to read data from SQL server, transform zoned datatypes, load into sequential files.
• Defined production support methodologies and strategies, prepared production run book.
• Maintained Data Warehouse by loading dimensions and facts as part of project. Also worked on different enhancements in FACT tables

Environment: InfoSphere Information Server DataStage 8.5, Oracle 10g/9i, Teradata, DB2, SQL, UNIX, Zena

Confidential, Columbus, OH

DataStage/Teradata Consultant

Responsibilities:

• Creation of Frame work, logical and physical data models for Level 0, 1 as per BI requirements.
• Define data types, nullability, Primary Indexes and Secondary Indexes.
• Worked on Source system analysis (SSA) on Oracle. Coded complex Oracle queries.
• Created ETL Design, Process and Mapping including Data quality documents.
• Created Functional and Technical specs to load data into Teradata warehouse.
• Involved in teh identification and analysis of teh source data for performing teh ETL operations
• Provide teh staging solutions for Data Validation and Cleansing wif Quality Stage and Datastage ETL jobs
• Designed Quality Stage Jobs in order to perform data Cleansing using Investigate Stage, Standardize Stage, Match Frequency, Survive Stage, Reference match Stage
• Developed various business processes and Context Diagrams to find new ways of doing certain tasks, which resulted in efficient processes, cost and time savings.
• Develop Proof of concept for model ideas
• Used DataStage stages namely Sequential file, Transformer, Aggregate, Sort, Datasets, Join, Funnel, Row Generator, Remove Duplicates, Teradata Extender, Copy stages extensively.
• Developed job sequencer wif proper job dependencies, job control stages and triggers.
• Excessively used DS Director for monitoring job logs to de-bug and resolve issues.
• Worked wif Datastage Manager for importing metadata and take up project backups.
• Used Teradata API and Teradata MultiLoad Datastage stages extensively to load data into EDW.
• Coded numerous BTEQ scripts wif complex logic to load/update aggregate tables for Level 1.
• Coded MLOAD and FLOAD scripts to Load data from staging tables.
• Designed and coded different SQL statements in Teradata BTEQ for generating reports.
• Involved in query translation, optimization and execution.
• Used Explains to optimize Teradata SQL queries for better performance.
• Used teh Teradata tools Teradata SQL Assistant, Administrator and PMON extensively.
• Performance tuning using join Index, Hash Index and derived tables.
• Documented ETL test plans, test cases, test scripts, and validations based on design specifications.
• Used Control-M job scheduler for automating teh monthly regular run of DW cycle
• Wrote Shell Scripts to check for teh existence of files and count comparison.

Environment: IBM DataStage 8.x (Designer, Director, Administrator, Parallel Extender), Teradata R12, V2R6, Control-M, UNIX & Micro Strategy

Confidential

DataStage Developer

Responsibilities:

• Involved wif extracting Plan, claims and cost data.
• Developed Data marts for users as per their Requirements
• Prepared Data Mapping Documents and Design teh ETL jobs based on those Mapping Documents
• Designed and Developed Data stage Jobs to Extract data from heterogeneous sources, Applied transform logics to extracted data and Loaded into Data Warehouse Databases
• Used various Parallel Extender partitioning and collecting methods
• Extensively worked wif Join, Look up (Normal and Sparse) and Merge stages
• Extensively worked wif sequential file, dataset, file set and look up file set stages
• Extensively used Parallel Stages like Row Generator, Column Generator, Head, and Peek for development and de-bugging purposes
• Writing teh UNIX scripts to Execute ETL Jobs, Sequences, FTP files, Sending Emails, Archiving and purging teh files on regular intervals
• Wrote PL/SQL stored procedures & database triggers for enforcing business rules.
• Coded complex SQL queries and fine tunes report SQL scripts.

Environment: IBM Websphere Information server 8.1, Ascential DataStage 7.5.2 (Designer, QualityStage, Manager, Administrator, Director), Oracle 9i/10g, SQL, PL/SQL, Cogonos and UNIX