SUMMARY

• Over Nine years of experience with special emphasis on Development, Analysis and Design of ETL methodologies in all the phases of the Data warehousing life cycle with knowledge Decision Support Systems (DSS) and Data Warehousing.
• Experienced in analysis, design, development, Testing and implementation of ETL using DataStage and QualityStage.
• Extensively worked on Parallel Extender on Orchestrate Environment for splitting bulk data into subsets and to dynamically distribute to all available processors to achieve job performance.
• Knowledge of Dimensional Data Modeling - Physical and Logical Data Modeling using ERwin 4.1/3.x., Star and Snow Flake Schema modeling.
• Expertise in working with various relational databases like Oracle, DB2/UDB, SQLserver, Teradata, Sybase.
• Excellent knowledge of studying the data dependencies using Metadata stored in the DataStage Repository.
• Experience in creating custom transformations, functions and parallel and server routines.
• Experienced with BI OLAP reporting tools like Cognos.
• Created Functional and Technical Mapping specifications and documentation.
• Experienced in Evolving Strategies and developing Architecture for building a Data warehouse by using data modeling tool ERwin.
• Strong understanding of the principles of Data Warehouse using Fact Tables, Dimension Tables and star schema modeling.
• Hands on experience in writing, testing and implementation of the Stored Procedures, functions at Database level using PL/SQL.
• Experience on Unix Shell scripting for running DataStage jobs, to automate file manipulation and data loading procedures.
• IBM WebSphere MQ to read Message queues.
• Extensive experience in loading high volume data, and performance tuning.
• Experience in incorporation of various data sources like Oracle, MS SQL Server, DB2UDB, XML, Complex and Flat Files into the staging area.
• Played significant Tech lead role in various phases of project life cycle, such as requirements definition, functional & technical design, testing, Production Support and implementation.
• Excellent team member with problem-solving and trouble-shooting capabilities, Quick Learner, highly motivated, result oriented and an enthusiastic team player.

EDUCATION
Bachelors of Engineering (B.E) in Computer Science

SKILLS
ETL Developer/ IBM InfoSphere DataStage and QualityStage Version 8.1, Ascential DataStage 6.X/7.5.2 (Designer, Director, Manager, Administrator),IBM WebSphere MQ Explorer, Information Analyzer, Cognos, Report net, Report Studio, C, C++, SQL, PL/SQL, Oracle 8.0/9i/10g, Sybase 12.6, Microsoft SQL Server 2008 V10.0, Teradata V2R6, DB2 UDB V9.1, Erwin, Oracle D2K with FORMS4.x/5.x/6.x, Reports 4.x/5.x/6.x, Windows XP NT/2000, UNIX (HP-UX, Sun-Solaris, IBM-AIX), AS/400, MVS, OS/390.

EXPERIENCE

Apr 2011 - Current
Feb 2009 - Nov 2010 Sr. DataStage Consultant/Tech Lead
Confidential, Des Moines, IA
Confidential, is a leading insurance company owned by its policyholders. Project Membership and Group Medicare, integrates data from Real time Membership system on Mainframes and then populates in to a IDS (Integrated Data Store) and IDW (Integrated Data Warehouse) Data Warehouse from where reports are created for Business Intelligence. Also worked in Projects EAPG Packaging Logic in Warehouse, Care Management - Disease Monitor & CCMS Refresh Membership refresh and National Account Strategy - Health Management Service.

• Worked as ETL DataStage Technical Lead and developer for a project from design until production and support.
• Involved in Analysis, Design, Process step flow and Mapping documentation for the ETL process, development, testing and promoting to production.
• Conducted sessions for mentoring other team members new to DataStage.
• Code reviews for the jobs developed by other DataStage developers in the team.
• Extensive use of Complex Flat File Stage to read COBOL layout source files from Mainframes with EBCIDIC binary format
• WebSphere MQ Stage to read messages from the Queues and also worked on IBM WebSphere MQ Explorer to test the Queue Depth, dead letter queue and number of messages.
• Performance tuning of the DataStage jobs, tuning the SQL code by analyzing the query plan.
• Running Integration cycles for daily and weekly processes in development environment.
• Involved in designing logical and physical modeling of the dimension model architecture.
• Created Korn shell Scripting to run DataStage job sending parameters, checking lock file.
• Worked on creating SAS code to create reports using text files output from DataStage.
• Extensively worked on DB2/UDB Enterprise Stage to read tables in parallel with DB2 partition.
• Created many Stored Procedures and SQL scripts for Quality check of the data load.
• Extensively used Change Capture stage for Type 1 and Type 2 slowly changing dimensions.
• Surrogate Key Generator stage for the Warehouse Dimension tables while loading new records.
• Sybase Bulk Load Stage and Enterprise Stage to truncate and load tables in the Warehouse.
• Used Serena DimensionsVersion controltool for maintaining all kind of code versions of DataStage jobs, KSH, SQL load scripts.
• Created an Audit, Balance and Check on all read and write counts of the tables in the Database.
• Involved in all phases of project right from loading into Staging (STG) from Mainframes, then loading in to the Integrated Data Store (IDS) with common record format and then loading in to Integrated Data Warehouse (IDW).
• Support for Production failures, used to carry a pager and was on call support for 24/7.
• Worked with XML files for reading the claims payload files from web services.
• Created DataStage Parallel routines in C for ASCII cleansing and error pattern replace in a string.
• Created Parallel Shared container of complex codes used repeatedly by other developers.
• Developed QualityStage jobs to standardize Member Names and Addresses.
• Created MS Visio process flows to understand the process step flow of the jobs and KSH scripts.
• Created Work Orders with different priorities for production installs of new code and Defect fixed code.

Environment: IBM Information Server V8.5(DataStage Designer, Director, Enterprise Parallel Extender), IBM WebSphere MQ Explorer V6.0, DB2UDB, Sybase 12.6,SQL Server 2008 V10.0, Oracle 9i, PL/SQL, DataStage Basic, IBM AIX UNIX, Unix Shell scripting KSH, Serena Dimensions 10.1.1, XML files, Sybase Power Designer 15, SAS 9.2, Attachmate Reflection X 14.0.

Nov 2010 - Apr 2011 Sr. DataStage Consultant/Tech Lead,
Confidential, Mettawa, IL
Confidential, is one of the largest banking and financial services organizations in the world. Risk Dashboard is a project to analyze and maintain the Risk Management operations of North America HSBC based on various key risk indicators (KRI) like Capital deductions, Operational risk, Credit and Market risk weighted assets, using with an integrated Data Warehouse.

• Worked as ETL DataStage consultant for a project from design until production and support.
• Reviewed and validated the existing job code and designed and developed new jobs for the future releases.
• Worked with offshore developers to co-ordinate the ETL work and requirements and reviewed the code developed at offshore.
• Fixing the defects raised by SIT and UAT validations.
• Worked on a parallel project for DataStage upgrade from version 7.5 to version 8.1.
• Developed UNIX scripts to run the Datastage job and other file validations.
• Experience on MKS Integrityversion control tool to maintain the versions of DataStage code.
• Worked on Actuate reporting tool console to schedule and monitor reports.
• Performance tuning of DataStage jobs and the SQL used inside the job.
• Worked on Cognos reports for unit testing of DataStage jobs by checking the values in the report.
• Worked on Mercury Quality center for defect tracking in the process of DataStage upgrade.
• Experience on Control-M scheduler to run the DataStage jobs.

Environment: IBM Information Server V8.1(DataStage Designer, Director, Enterprise Parallel Extender), DB2UDB, SQL Server 2008 V10.0, Oracle 9i, PL/SQL, DataStage Basic, IBM AIX UNIX, Unix Shell scripting KSH, MKS Integrity, XML files, Cognos, Actuate, Quality Center 9.2,Control-M.

Feb 2008 - Jan 2009 Sr. DataStage Consultant,
Confidential, Denver, CO
Confidential, is one of the nations leading Yellow Pages and online, local commercial search companies. EIW is a data warehouse system containing finance, sales and marketing data. This project deals with enhancement, maintenance and production support activities to the EIW system, it is built using DataStage over an oracle database and the reporting tool is Cognos.

• Production supported and maintained for all ETL process for EIW project.
• Promoted jobs from development to test and then to production environment.
• Worked with DataStage Version control to maintain the versions of error fixing jobs and to initialize and promote the job code to test and production environments.
• Worked on KBA utility to consolidate the DataStage jobs and schedule them using Control-M.
• Track defects during production support using Mercury Quality Center.
• Created job hierarchy and pameters.ini files to run a batch job.
• Migrated DataStage jobs and stored procedures to fetch the data from new source system Kgen.
• Worked extensively on development and testing of DataStage jobs and sequencers for new entities.
• Troubleshot and performed testing and tuning for several parallel and server jobs.
• Extensive experience on Cognos reporting tool to analyze and debug the various reports.
• Investigating the reasons for data discrepancy in the Database using PL/SQL and Cognos reports.
• Pivoted the records vertically and loading as per functional requirement.
• Designed the mappings between sources external files and databases such as SQL Server, Oracle and XML files to Operational staging targets.
• Involved in Design Documentation and generating reports for the DS jobs.
• Created a Batch job and scheduled them to run weekly and monthly.
• Debugged and created routines, transforms in DataStage manager.
• For better performance implemented Co-Sort utility instead of normal Sort stage for sorting Millions of records.
• Worked extensively on DataStage director, manager, designer on development.
• Developed and tuned PL/SQL for higher performing activities at database level
• Created a User variables Activity in DataStage Job Sequencer for fetching the records from past 24 hours.
• Worked with users and identifying the data which we need from the source systems.
• Involved in creation of logical and physical data model using ERwin 4.0.
• Designed and Developed ETL jobs in Developing Parallel as well as Server Jobs using DataStage tool to load data warehouse and Data Mart.
• Designed, built, and managed complex data integration and load process.
• Parameterized DataStage jobs to allow portability and flexibility during runtime.

Environment: IBM Information Server V8.0(DataStage Designer, Director, Manager, server and Parallel Extender, Version control), IBM InfoSphere DataStage Version 8.0, Oracle 9i, Oracle 10g, PL/SQL, DataStage Basic, Cognos, Report NET, Report studio, HP UNIX, ERwin 4.0, MS-Access, XML files, Windows XP.

Jan 2007 - Nov 2007 DataStage Consultant

Confidential, Bridgewater, NJ
Sanofi-Aventis is a pharmaceutical Company engages in the research, development, manufacturing, marketing of pharmaceutical products. The purpose of the Disclosure project is to collect together all of the information regarding clinical studies in an automated fashion, and assists its users creating documents which are shared with groups external to Sanofi-Aventis.

• Extracted data transformed and loaded into Oracle database according to the required provision.
• Consulted Source system users & Business Users to create data share agreements and BI requirements.
• Created Jobs for Error validations and Job monitoring. And also worked on Error handling of defected jobs.
• Designed and Developed ETL jobs in Developing Parallel as well as Server Jobs using DataStage tool to load data warehouse and Data Mart.
• Extensively used Database Procedures and Triggers
• Designed and developed dimension modeling of the data ware house by using Erwin 4.3.
• Modeled the Star Schema Data Marts by identifying the Facts and Dimensions using Erwin Data modeling tool.
• Expert in DataStage Jobs version control and Migration using Version management (Clear Case and Quest) & DS-Manager tools.
• Performed data manipulation using Basic functions and DataStage transforms.
• Created Log Tables containing data with discrepancies to analyze and re-process the data.
• Migrated data from Staging Area into Marketing and Sales Data Marts to store reliable data for decision support system using DataStage as ETL tool.
• Import relational metadata information for project.
• Defined constraints, stage variables and derivations in a transformer.
• Developed DS Routines for extract job parameters from files and developed DS routines for job Auditing.
• Created and used DataStage Shared Containers, Local Containers for DS jobs and retrieving Error log information.
• Developed test data and conducted performance testing on the developed modules.
• Performance tuning of ETL jobs.
• Developed PL/SQL for higher performing activities at database level.
• Extensively worked on Error handling, cleansing of data, creating lookup files and performing lookups for faster access of data.

Environment: Ascential DataStage 7.5.2(Designer, Director, Manager, Parallel Extender), Oracle 9i, Oracle 10g, PL/SQL, DataStage Basic, HP UNIX, MS-Access, XML files, ERwin 4.0, Windows XP.

Feb 2006 - Nov 2006 DataStage Consultant

Confidential, Omaha, NE
Confidential, will provide insurance products and support services to the health care community and maintain a stable insurance environment. Project was on Greenfield program Management for building Data warehouse for PMIC in various subject areas like Financial Transaction, Money Provision, Money transfers, Agreements.

• Involved in Developing Parallel as well as Server Jobs.
• Involved in creating entity relational and dimensional relational data models using Data modeling tool Erwin.
• Developed Data Stage Extract/Transform/Load jobs using STAR Schema and followed development standard into different projects.
• Generated Surrogate ID's for the dimensions in the fact table for indexed and faster access of data.
• Used DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
• Coded, tested and implemented components for querying and updating the database.
• Designed documentation and generating reports of the DataStage jobs.
• Created surrogate key tables to map the required dimensions.
• Designed the Target Schema definition and Extraction, Transformation and Loading (ETL) using DataStage.
• Maintained all the data needed for supporting the Sales Results and Compensation processing rules.
• Involved in low-level design and developed various jobs and performed data loads and transformations using different stages of DataStage and pre-built routines and functions
• Mapped Data Items from Source Systems to the Target System.
• Used the Data stage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
• Worked on programs for scheduling Data loading and transformations using Data Stage.
• Designed and Developed PL/SQL Procedures, functions, and packages to create Summary tables.
• 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.
• Worked with DataStage Manager for importing metadata from repository, new job Categories and creating new data elements.
• Performed tuning of ETL jobs.

Environment: DataStage 7.1(PX & Server), ERWIN, Oracle 8i, DB2, LDAP, SQL, Teradata V2R6, PL/SQL, SQL* Loader, Oracle 9i, AIX UNIX, OS/390, DBMS, XML, Queues, Windows XP

Mar 2003 - Dec 2005 DataStage Consultant

Confidential, Orem, UT

Confidential, is a global supplier of pumps, valves, seals, automation, and services to the power, oil, gas, chemical and other industries. ONEVIEW is the project which used to maintain and analyze Vendor information like bookings, addresses and shipments from various source systems of Flowserve.

• Extensively used DataStage Manager, Designer, Administrator and Director for creating and implementing jobs.
• Extensively worked on Error handling, cleansing of data, creating lookup files and performing lookups for faster access of data.
• Used Dynamic RDBMS stage to pull the data from source.
• FTP the flat files to the local server
• Worked in multiple projects like One View, Ariba Extraction, SulphurSprings Requirements and Data Archival.
• Worked on Web services loading and importing XML files.
• Used DataStage Manager to import the Meta Data from sources and targets.
• Developed DataStage jobs based on business requirements using various stages like lookup file, lookup stage, join stage, merge stage, and sort stage.
• Involved in creating Designing documents and mapping documents for DataStage jobs.
• Used DataStage Director to schedule running the server jobs, monitoring scheduling and validating its components
• Written test cases to test jobs.
• Plan and distribute the work among the off-shore team mates.
• Involved in writing Shell scripts for reading parameters from files.
• Involved in system integration and implemented the system in various outlets
• Coded, tested and implemented components for querying and updating the database.
• Used Link Practitioner to Portion Data in order to enhance performance of DataStage jobs. And Link Collector to collect portioned data together.
• Analysis, Design, and Development of the Data Entry Subsystem.
• Complex queries are written to facilitate the supply of data to other teams.

Environment: DataStage 6.1(Server), DB2UDB, Flat Files, Teradata, Oracle9i, Shell scripts, Sybase, HP UNIX, Windows 2000.