SUMMARY:

• 7 of IT experience in analysis, design, development, test and implementation of software applications in data warehousing and Client/Server environment.
• Extensive experience working with ETL tool IBM DataStage 9.1/8.7/8.5/8.1/7. x (Information Server, Web Sphere, Ascential DataStage).
• Designed and successfully implementedEnterprise Data warehouse and synchronized Data marts.
• Experience in development and effective 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 with clear understanding of design goals of ER modeling for OLTPand dimension modelingfor OLAPusingErwin and MS Visio.
• Expertise with 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 with SQL, complex queries, optimization and fine tuning.
• Proficient with BuildOps and wrappers.
• 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).
• Experience with extracting data from SAP System.
• Experience In SAP Migrations techniques, ALE and iDocs.
• Experienced in in corporation of various data sources like Oracle, SQL server, DB2, SAP and flat.
• Created Shell Scripts for 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 the Staging Area
• 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 with effective communication, analytical and multi-tasking skills, resourceful, result driven and self-motivated.

TECHNICAL SKILLS:

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, SAP R/3 ABAP4

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

Scheduling: Control-M, Zena, Autosys, Control M

PROFESSIONAL EXPERIENCE:

Confidential, Redmond, WA

Sr. ETL/Data warehouse Consultant

Responsibilities:

• Interacted with 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 the first time, then on daily and weekly.
• Developed mapping to load the 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 the 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 the requirement
• Used Teradata API and Teradata Multiload stages to load the data into Data Warehouse.
• For One time straight loads used Fastload and MultiLoad Unix scripts.
• Designed and developed sequences and scheduled the jobs in CA7 for batch runs.
• Participated in design reviews to develop "pre-defined" reports coming out of the Dealer data mart.
• Coded and debugged numerous Teradata scripts - BTEQs, MLOADs, FLOADs and FastExport.
• Supported the Data warehouse during and after the implementation with 24/7 support.
• Worked on Performance tuning and Optimizing the datastage jobs and Unix/Teradata scripts.
• Created ‘lessons learned’ and ‘best practices’ documents.
• Designed GUI using C# and WPF.
• Created and executed QA test scenarios and test cases in HP quality center.
• Implemented various process checks, data checks and mail notifications to ensure the quality of the data that is loaded into the data warehouse.

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

Confidential, Salem, Oregon

ETL - Conversion Engineer

Responsibilities:

• Functioned as conversion developer in one or more conversion areas of the CSE application (case management, enforcement/establishment, financial management, etc.).
• Served with project expertise for the IBM InfoSphere Datastage tool
• Maintained direct knowledge of and applies all project quality standards.
• Worked as a member of the Conversion team.
• Completed conversion deliverables on time according to project schedules
• Converted high volume data from legacy systems to DB2 RDBMS using IBM InfoSphere / DataStage ETL software version 9
• Used ETL tool to write complex transformation algorithms
• Performed data validations and cleansing during conversion
• Developed custom development algorithms
• Generated reports out of the ETL tool to indicate the health of conversion
• Installing and configuring IBM InfoSphere enterprise edition software in workstation and server environment(s)
• Stored Procedures in DB2

Environment: IBM InfoSphere Data Stage 11.5 (Designer, Director, Administrator)Confidential, Dallas, TX

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 with the architecture and design of Data warehouse and discovery data store.
• Used the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse.
• Extensively worked with DataStage Stages for Data Staging and Data Transformation
• Extensively worked with DataStage Designer to pull data from flat files, Oracle to target databases and sequential file.
• Extensively worked with Job sequences using Job Activity, Email Notification, Sequencer, Wait for File activities to control and execute the 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.
• 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.
• 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, IBM Tivoli,Hadoop, MapReduce, HDFS, Hive.

Confidential, Columbus, OH

DataStage/Teradata Consultant

Responsibilities:

• Creation of Frame work, logical and physicaldata 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 the identification and analysis of the source data for performing the ETL operations
• Provide the staging solutions for Data Validation and Cleansing with 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 with proper job dependencies, job control stages and triggers.
• Excessively used DS Director for monitoring job logs to de-bug and resolve issues.
• Worked with 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 with 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 the 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 the monthly regular run of DW cycle
• Wrote Shell Scripts to check for the existence of files and count comparison.

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

Confidential - Cref, Charlotte, NC

Sr. DataStage Developer

Responsibilities:

• Involved in development phase for Business Analysis and Requirements Gathering.
• Designed logical and physical models using Erwin data modeling tool.
• Designed the mappings between sources (external files and databases) to operational staging targets.
• Responsible for creating extracted jobs from File net Teradata database.
• Implemented multi load and fast load methods for loading of Teradata data.
• Created transformation jobs to create load ready files and used FTP to transfer different data between the servers.
• Used Parallel Extender for distributing load among different processors by implementing pipeline and partitioning of data in parallel extender.
• Worked on DataStage Administrator for tuning and setting up the permissions and for creating the environment variables.
• Used Operators like Look-up stages, File set stage, Funnel Stage, Data Set stage and Merge Stage.
• Created DataStage jobs, batches and job sequences and tuned them for better performance.
• Extensively worked on Hash-file Stage for look-ups, DB2Bulk Load, ODBC, Hashed file, Aggregator, Sequential file, Link Partitioner and Link collector stages.
• Worked with DataStage Designer for importing metadata from repository, new job categories and creating new data elements.
• Used the Data Stage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions.
• Scheduled batch jobs in Autosys and coordinated with System Operators to Schedule the batch jobs in Autosys.

Environment: DataStage 8.7/8.5/8.1(EE) Parallel Extender, Oracle, Teradata,DB2, Teradata, WebSphere Quality Stage, Flat Files, Autosys JILs Scheduling and programming.

Oracle, Teradata,DB2, Teradata, WebSphere Quality Stage, Flat Files, Autosys JILs Scheduling and programming.

Confidential

DataStage Developer

Responsibilities:

• Involved with extracting Plan, claims and cost data.
• Developed Data marts for users as per their Requirements
• Prepared Data Mapping Documents and Design the 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 with Join, Look up (Normal and Sparse) and Merge stages
• Extensively worked with 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.
• Worked with migration of the jobs from DataStage 7.5.2 to higher level version i.e. DataStage 8.1
• Writing the UNIX scripts to Execute ETL Jobs, Sequences, FTP files, Sending Emails, Archiving and purging the 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

Confidential, Hopewell, NJ

Jr. DataStage Developer

Responsibilities:

• Interacting with customers in Teleconferences regarding the business requirements and enhancements.
• Responsible for developing DataStage ETL processes for Clients Dimensions and Fact table loading.
• Designed Datastage ETL job to extract data from sources from XML files.
• Developed oracle stored procedures and created Unix Shell scripts to execute the oracle stored procedures and SQL code.
• Creating primary objects (tables, views, indexes) required for the application along with DataStage Administrators.
• Involved Data analysis activities for new sources/ existing data marts.
• Created and implemented dimensional models (star schemas and SCD’s) and Erwin tool.
• Used different types of Stages like Sequential File, Funnel, Copy, Join, Lookup, Merge, Transformer, RDBMS and ODBC for developing different Jobs.
• Designed and developed DataStage Shared Contained jobs for all the common modules (e.g. generation of surrogate key) to allow reusability of code.
• Designed and developed common modules for error checking (e.g. to check if the reject records output file is empty and to check if there are duplicate natural keys in a given table.
• Involved in performance tuning of ETL processes & databases.
• Worked with Administration activities for oracle database.
• Involved in reviewing of code and Technical Support to the Team and responsible for conducting the System and Unit Testing using Autosys.

Environment: IBM DataStage 7.5.2/8.0 Parallel Extender, Oracle 10g, PL/SQL, Toad, Microsoft Office 2007, Windows NT 4.0, Flat Files, Autosys JILs Scheduling and XML files.