Data Manager

Chinmay Bapat

Professional Summary:

> Over * years of IT experience with special expertise in development,

analysis, and design of ETL methodologies in all the phases of the

data warehousing life cycle coupled with knowledge of DSS and OLAP.

> Extensive experience in IBM InfoSphere 8.1 (DataStage, QualityStage,

Information Analyzer, Metadata Workbench), IBM WebSphere 8.0

(DataStage, Information Analyzer, QualityStage), Ascential DataStage

7.5/6.0XE/5.0 using components like DataStage Designer, DataStage

Manager, DataStage Director, DataStage Administrator and Parallel

Extender.

> Expert in all the stages of Software Development Life Cycle.

> Extensive experience in designing, developing and maintaining

applications in data warehouse for Healthcare, Telecommunication and

Insurance Industries.

> Strong Decision Support Systems experience using Business Intelligence

tool Business Objects.

> Strong understanding of the principles of data warehouse using fact

tables, dimension tables, star schema modeling and snowflake schema

modeling.

> Excellent knowledge of studying the data dependencies using metadata

of DataStage and preparing job sequences for the existing jobs to

facilitate scheduling of multiple jobs using the DataStage Director.

> Expert in using Information Analyzer for column analysis, primary key

analysis and foreign key analysis.

> Expert in working with QualityStage for data profiling,

standardization, matching and survivorship.

> Expert in data warehousing techniques for data cleansing, slowly

changing dimension phenomenon (SCD), surrogate key assignment and CDC

(Change Data Capture).

> Experienced in UNIX shell scripting and Perl scripting.

> Excellent experience in scheduling and monitoring the production jobs

using ASG-Zena and Autosys.

> Experienced in incorporating various data sources like Oracle, MS SQL

Server, DB2, Teradata, Sybase, XML and flat files into the staging

area.

> Experienced in troubleshooting of DataStage jobs and addressing

production issues like performance tuning and enhancement.

> Expert in unit testing, system integration testing, implementation and

maintenance of databases jobs.

> Dynamic, alert, and capable of producing high quality work under time

constraints.

> Experience in developing Crystal Reports, enable users to graphically

design data connections and report layout

> Excellent communication and presentation skills.

Education Qualifications:

Masters in Computer Science

Skillset:

IBM InfoSphere 8.1 (DataStage, QualityStage, Information Analyzer,

Metadata Workbench), IBM InfoSphere Change Data Capture (InfoSphere CDC or

CDC), IBM WebSphere 8.0 (DataStage, QualityStage, Information Analyzer,

Metadata Server), Ascential DataStage 7.5/6.0XE/5.0 (Designer, Manager,

Director, Administrator and Parallel Extender), MetaStage, Windows

95/98/NT/2000/XP/2003, UNIX, SQL, ERwin 4.1/3.5, SAP BW 3.1, Business

Objects XIR2/6.5, XML, XSL, Oracle 10g/9i/8i, Sybase, DB2 UDB 7.2, Teradata

12/V2R6/V2R5, SQL *Plus and SQL*Loader, Korn Shell Scripting, SQL and

PL/SQL, SQL Server 2008/2005/2000, Sun Solaris 5.8/2.7, HP-UNIX (11.11),

IBM AIX 4.2/4.1, Sun and HP 9000 Series 800 servers, Toad 8, ASG-Zena,

Autosys3.0

Project Summary:

EmblemHealth, NY, NY

02/2010 -

Present

Sr. Datastage Developer

Enterprise Data Accumulated Access (EDAA)

The main purpose of this project was to build a central claims data mart

for life insurance claims, which would bring together all the customer

information of all claims field. The data warehouse was build to centralize

claims data from there sources.

The project was about migrating health insurance related data from GHI

domain and HIP domain to Emblem domain.

Environment:

IBM Infosphere Information Server 8.1 (DataStage, Information Analyzer,

MetaStage, Quality Stage), TOAD, Autosys, Flat files, Cobol Copy books,

Oracle 10g/11g, MS SQL Server 2008/2005, PL/SQL, BO XI, Crystal Reports XI,

HL7, Shell Programming, Unix, Windows NT, Windows XP

RESPONSIBITILIES

> Requirements gathering and business analysis by attending business

meetings.

> Conducted end user interviews to gather reporting and analysis

requirements.

> Assisted in logical and physical modeling, followed Star schema to

build claims datamart.

> Responsible for analyzing source systems - functional & data

requirements, Identify business logic, updating data dictionary.

Source to Target Mapping.

> Analysis, Design and Development, test and implementation of DS Jobs

for extracting the data from the multiple legacy systems.

> Using Datastage, designed the procedures for getting the data from all

system to data Warehouse system.

> Designed DataStage jobs to populate and incrementally load the source

data to the staging area, created the update strategy, Profile Stage,

transformations, filters, lookups, FTP stage, and necessary source-to-

target data mappings to load the target using the DataStage Designer

> Designed, developed and deployed DataStage jobs using various

DataStage stages like Interprocess, Link Collector, Link Partitioner

and Hash Tables, Aggregator, Sort and Funnel, Switch, Transformer,

change capture stage, change apply stage, difference, slowly changing

dimension.

> Used Quality Stage stages such as Investigate, Standardize, Match and

Survive for data quality and data profiling issues.

> Worked with parallel jobs.

> Extensively worked on UNIX and shell scripts.

> Tuned DataStage jobs for better performance by creating DataStage Hash

files for staging data and for lookups.

> Create multiple configuration file for different jobs.

> Debugged jobs using Row Generator and Column Generator stages to

create test data

> Worked with TOAD to interact with Oracle and used SQL Trace and

Explain Plan to improve the performance of the PL/SQL programs.

> Used Autosys to schedule the DataStage ETL batch jobs on a daily,

weekly and monthly basis.

> 24/7 Production Support - Handled all Issues related to Autosys3.0

> Tested and modified jobs running in production environment, with

minimized downtime

> Performed Unit & Integrated testing.

Blue Cross Blue Shield (BCBS), Richardson, TX

01/2009 - 02/2010

DataStage Consultant

Blue Cross and Blue Shield companies have provided millions of families

with top-quality, affordable health insurance. It represents the nation's

oldest and largest family of health benefits companies .BCBS Brands are the

most recognized in the health insurance industry.

The project was about migrating from SQL Server data-warehouse to an Oracle

Data-Warehouse. It also involved designing ETL Process for Data Warehouse

Integration and Auditing purposes.

Environment:

IBM Infosphere Information Server 8.1 (DataStage, Information Analyzer,

MetaStage, Quality Stage), SQL Server 2005, Oracle 10g, Sun Solaris Unix,

Windows XP, IBM DB2, Autosys 3.0.

Responsibilities

> Involved in status meetings, and interacted with the Business Analyst

to get the business rules.

> Migrated Outbound Data Feeds from SQL Server as source to Oracle as

destination.

> Documented the purpose of mapping so as to facilitate the personnel to

understand the process and incorporate the changes when necessary.

> Extracted data from text files, using FTP Stage and loaded into

different databases.

> Designed Parallel jobs using various stages like join, merge, remove

duplicates, filter, dataset, lookup file set, modify, aggregator and

funnel stages.

> Extensively used Built-in (Sort, Merge, Oracle, Aggregator, DB2

Stages), Plug-in Stages for extraction, transformation and loading of

the data.

> Used Quality Stage stages such as Investigate, Standardize, Match and

Survive for data quality and data profiling issues.

> Received the master data and populated the Dimension Tables, Time

generation, Surrogate key generation.

> As per Data Architect's directions, created and executed the SCD Type

2 implementation on Dimensional data

> As per the data volume, presented the size requirements and

partitioning requirements for ODS tables in Oracle.

> Used MetaStage for the synchronization and Integration of Metadata

from various Data Warehouse related tools and also used for

automatically gathering process data from operational systems.

> Developed the customized routines and functions for better job

performance.

> Used Autosys to schedule the DataStage ETL batch jobs on a daily,

weekly and monthly basis.

> Created shell scripts that will invoke the Data Stage jobs passing all

variables for job to execute with parameterized databases connection

information.

> Used Management Studio to query the SQL Server database.

> Analyzed data with discrepancies through Error files and Log files for

further data processing and cleansing

> Tested and modified jobs running in production environment, with

minimized downtime.

> Optimized partitioning and parallelism in jobs.

> Worked on performance tuning and enhancement of DataStage jobs.

Bank Of America, Charlotte, NC

10/2007- 12/2008

DataStage Developer (ETL)

Bank Of America, engages in mortgage lending and other finance-related

operations. The Centralized Data Warehouse is a Multi Tera Byte,

recoverable centralized data repository that makes accessible historical

and current business relevant data as of the prior business day. The CDW's

primary purpose is to centralize corporate data from disparate data sources

residing within companies transaction processing and end-user systems into

one relational data base for decision support, management reporting, ad-hoc

and trend analysis, modeling and data mining purposes. The objective of

this Project was to provide an integrated data from PE (Project Enterprise)

and Midas (Mortgage Information Direct Access System) to Centralized Data

warehouse (CDW).

Environment:

DataStage 7.5 (Parallel Extender), DB2UDB, Unix Scripts, Main Frame, JCL,

DB2,, Oracle 10g, QMF, Windows NT 4.0, IBM AIX, OS/390, Autosys, PL/SQL,

Rational ClearCase and ClearQuest, Vantive, Hummingbird, SAS.

Responsibilities:

> Created process flow diagrams using Microsoft VISIO

> Implemented enhancements to the current ETL programs based on the new

requirements.

> Involved in design and development of Server Jobs and parallel jobs

using Datastage 7.5

> Designed, developed and deployed DataStage jobs using various

DataStage stages like Interprocess, Link Collector, Link Partitioner

and Hash Tables, Aggregator, Sort and Funnel, Switch, Transformer,

change capture stage, change apply stage, difference, slowly changing

dimension.

> Used extensively Aggregator/Sort/Merge/FTP stages for data processing.

> Used Quality Stage stages such as Investigate, Standardize, Match and

Survive for data quality and data profiling issues.

> Extensively followed the concepts of Naming Conventions,

Parameterizing the variables from Global Environment to stage level.

> Used Parallel Extender for parallel processing of data extraction and

transformation.

> Developed job sequences by identifying independent and dependent flows

with proper rollback strategies incorporated in the sequences.

> Wrote various Autosys JIL Scripts to activate the Unix scripts in

production i.e JILs for the File watchers, Box jobs and Commands jobs.

> Used ClearCase & ClearQuest Version Control to move the code from

development sever to Clear Case Dev-Stream.

> Generated Technical Data Quality reports using SAS to meet SOX rules.

> Performed Data Cleansing and Conversion of Mainframe datasets from

EBCIDIC to ASCII format.

> Utilized various Mainframe tools like SYNCSORT, SORTDYN, FILE-AID for

viewing and manipulating datasets and SPUFI, QMF for accessing data on

DB2 tables.

> Used the partitioning techniques in most accurate way for increasing

the speed of job execution

> Played a key role in UAT. Tested and modified jobs running in

production environment with min downtime.

> Hands on experience on Autosys job scheduler used for automating the

monthly regular run of EDW cycle in both production and UAT and

development flow environments.

> Knowledge of IBM Cognos8-BI which delivers the complete range of BI

capabilities - Reporting, Analysis, Dashboards, and Scorecards -- on a

single service-oriented architecture

IBM, Fairfax, VA

09/2006-10/2007

Datastage Consultant

The main objective of the project 'WWPRT3.0' (World Wide Price Release

Tool) is to incorporate PEARL legacy pricing tool functionality into

current strategic WWPRT pricing tool. All the current price data from PEARL

database was migrated to WWPRT and RDH using Datastage tool. This interface

was based on extraction of data by connecting to a source DB2 subsystem for

PEARL, then transforms the data according to the business requirements and

finally loads the data into WWPRT and RDH database. The PEARL data was

migrated from DB2 subsystem MEFSA which is having DB2 7.1on z/OS to WWPRT

3.0 and RDH having DB2 8.0 on AIX 5.1

Environment:

DataStage 7.5.2 (Parallel Extender), DB2UDB, Windows XP, IBM AIX, OS/390,

z/OS

Responsibilities:

> Involved in design and development of parallel jobs using Datastage

designer 7.5.1.

> Developed prevalidation jobs for checking validity and quality of

source data.

> Developed Post validation jobs for verification of target data.

> Extensively Used Join/lookup/remove duplicates/funnel stages in jobs.

> Used DB2 enterprise stages to retrieve and load the data to and from

Db2 database.

> Administered the project wide settings/user defined variable using

Datastage Administrator.

> Developed various SQL Scripts for loading parent tables to maintain RI

relationships among parent and child tables.

> Used extensively Aggregator/Sort/Merge/FTP stages for data processing.

> Used job parameters in the jobs to change parameters on fly.

> Used Datastage manager tool to import/export jobs and associated

components like metadata etc.

> Proficient in creating jobs using Datastage EE for splitting bulk data

into subsets and to dynamically distribute to all available nodes to

achieve best job performance.

> Wrote scripts to perform Data Quality check after loading data into

target.

> Used 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)

> Created Job Sequencer to execute the jobs in batches and created

Shared Containers to increase Object Code Reusability and to increase

throughput of the system.

> Used the partitioning techniques in most accurate way for increasing

the speed of job execution

> Implemented enhancements to the current ETL programs based on the new

requirements.

> Involved in designing and updating the data model and Referential

integrity within tables.

> Performed Unit Testing, Integration Testing and coordinated with QA

for User Acceptance Testing (UAT) for code change and enhancement.

> Worked within a physically distributed team hence enhancing my time

management and coordination skills.

Coventry Health Care, Bethesda, MD

11/2005-08/2006

ETL Consultant/Data Analyst

Coventry Health Care is a provider of managed health care services,

offering members a wide choice of options, high standards of care and

affordable rates. The project involved understanding of Healthcare Data in

the core areas of Claims, Eligibility, Customers, and Providers. The main

objective of the project was to move claims data from oracle into Data

warehouse. Used the Data stage Designer to develop processes for

extracting, cleansing, transforming, integrating, and loading data into

data warehouse database. The purpose of data integration was for end user

reporting.

Environment:

IBM DataStage 7.5, Information Server (Designer, Director, Administrator),

IBM WebSphere DataStage 7.5.2 Enterprise Edition, Parallel Extender,

Autosys, IBM AIX, UNIX Shell Scripting, Oracle 10g, DB2 UDB, MS Access DB,

PL/SQL, TOAD.

Responsibilities:

> Participated in discussions with Team leader, Group Members and

Technical Manager regarding any Technical and Business Requirement

issues.

> Used Datastage to extract client data from Oracle, MS Access and

mapped the data into a target business warehouse.

> Created various Packages, Procedures and Functions for purging the

data.

> Created various functions in DataStage for mappings and other

statistical calculations.

> Extensively used Pivot stage to pivot the source data to achieve

required table structures like converting data from rows into a

column.

> Extensively used all the stages of DataStage like Pivot, Folder,

Container, Aggregator, Sort and Transformer.

> Extensively worked with various stages of Parallel Extender like

Sequential file, Dataset, lookup, peek, transformer, Merge,

Aggregator, row generator, surrogate key generator and many more to

design jobs and load the data in to Fact and Dimension tables.

> Used DataStage Oracle stages like Oracle Bulk Loader and Oracle OCI

with DataStage jobs in loading the data.

> Hands on experience on Autosys job scheduler used for automating the

monthly regular run of EDW cycle in both production and UAT and

devflow environments.

> Conducted various before and after code reviews.

CIGNA, Hartford, CT

02/2004-10/2005

ETL Developer

Defined Contributions Record Keeping (DCRK): This project was involved in

creating ODS, IDS & Data Mart to move data from Mainframe to Oracle. The

goal of the project was to transform and move historical data from existing

DB2 environment to Midrange environment in order to reduce operational

cost, to improve reporting capabilities including real-time/near real-time

generation of reports and to make DB2 environment more efficient.

Environment:

DATASTAGE 7.0, Oracle, SQL Loader, TOAD, SQL* Plus, BO 5.1, Windows NT, Sun

Solaris 8, Erwin, MS-Visio, MF tools, DB2, File-AID, QMF, WRQ Reflection.

Responsibilities:

> Prepared documentation for addressing the Referential Integrity

between the Tables at ETL level.

> Involved in the creation of jobs using DataStage Designer to schedule,

run and monitor the DataStage jobs.

> Involved in designing the procedures for getting the data from

multiple sources to ODS.

> Imported COBOL Copybooks, DSDB2 Meta Data in to DataStage Environment

using DataStage Manager to Access Flat Files.

> Designed jobs with Stages like Link Partitioner and Link Collector for

Initial Load to handle very huge volumes of Data.

> Generated reports using Microstrategy tool for analysis purposes.

> Used the DataStage Designer to develop processes for extracting,

transforming, integrating, and loading data into Data warehouse.

> Initially worked on programs for Data loading and transformations

using DataStage from directly reading DB2 Tables using DB2/UDB-API

stage and loading into Oracle DataBase.

> Involved in creating multi dimensional universe and reports in

Business Objects Environment.

Eagle Software, Delhi, India

06/2003- 02/2004

ETL (DataStage) Developer

Assigned as ETL (DataStage) consultant at Eagle to design and develop data

model and ETL to support Business Intelligence applications. Developed ETL

process to extract data from Sources systems, Transform as per business

requirements and load the data into dimensional data models to support BI

reports, Dashboard and scorecards.

Environment:

Ascential DataStage 5.x (Designer, Administrator, Manager, Director),

Oracle, PL/SQL, Windows NT, UNIX, Shell script.

Responsibilities:

> Enhance and developed the existing data-mart with Oracle to scheduled

loads from ERP operational data.

> Developed / designed various new processes and fixed the existing

process with new business requirements, various meetings with users

Input.

> Business analysis and requirements gathering for RBAD data, analyzed

the hierarchy for Fact & Dimensions Used Ascential DataStage Manager

for importing new job categories & creating new data elements. Used

the DataStage Designer to develop processes for extracting and loading

data into data warehouse.

> Used the PL/SQL, Shell Scripts to develop processes for extracting,

cleansing, transforming, integrating and loading data into data

warehouse database.

> Did Unit Testing and tuned for better performance with updates on data

warehouse tables by non-clustered & clustered indexes using Oracle &

extensively used Ascential DataStage Director for jobs monitoring and

troubles shooting.

> Extensively used Sequential File Stage, Aggregator Stage, and Hashed

File Stage etc. Used Shared Containers for Server Jobs & shell scripts

for job sequences, for handling rejected data, handling NULL values

and complete email reporting of data changes for production support.

Contact this candidate