IBM Datastage, Oracle, Unix

Pranay Kesharwani

Cell Phone: 617-***-**** Email: ******.**********@*****.***

-

SUMMARY

More than Eleven years of IT experience including Ten years in the field of Data warehousing, Data Migration, Data Integration, Data Extracts using ETL tools like IBM DataStage Parallel Extender 7.5.1A/7.5.1/8.5/11.3 and Informatica Power Center 8.0.

IBM-certified in WebSphere Information Integration Softwares

(DataStage Enterprise Edition v7.5 and Infosphere Datastage v8.5)

Worked as an ETL Architect for designing the ETL process for the TASGDMP Project at UBS and ISO Extracts for State Street Corp.

Worked as a Production Support Analyst for various clients like State Street Corp, Aetna Inc. and Health Care Authority of WA.

Worked as an Onsite lead for a number of projects, acting as a single point of contact between the client and the offshore development team.

In-depth experience with different stages of the project life cycle including requirements analysis, application design, code development, testing and deployment.

Specialized in HealthCare and Securities Domain. Worked extensively on the modules like claims, members and providers.

Expertise in data warehousing, ETL programming, and fulfillment of data warehouse project tasks including data extraction, cleansing, transforming and loading.

Worked with Data Stage Server, DataStage Parallel (7.5.1A/7.1) and DataStage 8.5/11.3.

Experience working with different databases such as Oracle, SQL Server, Sybase and DB2.

Expertise in UNIX Shell Scripting.

Highly motivated and competent individual with excellent communication and analytical skills.

EMPLOYMENT HISTORY

Name of the Company

Designation

Duration

CNSI Inc

ETL Datastage Lead

July’14 to till date

Maxima Consulting

Technical Consultant

July’13 to Jun’14

Cognizant Technology Solutions

Senior Associate

July’10 to July’13

Wipro Ltd.

Associate Consultant

Aug'08 to July’10

uCertify Ltd.

Software Consultant

May'08 to Aug'08

Cognizant Technology Solutions

Associate - Projects

May'04 to May'08

SKILLS/TOOLS

Operating Systems

Windows (98/XP/vista/10), Unix

Tools

Toad, WinSCP, Putty, SQL Developer

ETL Tools

Data Stage 7.5.1A/7.1/8.5/11.3

Scheduling Tool

Autosys, Crontab, Zeke, Datastage Scheduling

Database

Oracle, SQL Server, Sybase

Programming Languages

UNIX Shell Scripting

PROFESSIONAL EXPERIENCE

Client: Washington State Health Care Authority Olympia, WA

Role: ETL Datastage Lead

Duration: July'14 to till date

Environment: DataStage 8.5, DataStage 11.3, UNIX, Oracle 12c, Crontab

Project Name: ProviderOne ODS

Project Description: ODS is CNSI’s data warehouse solution to assist HCA in achieving their data objectives: Reporting, Data Extracts and Data Integration. ODS is being designed with the main purpose of bringing in ProviderOne data into ODS. ODS allows HCA to perform many resource intensive processes which is not possible in (OLTP) and in addition, OLTP would not have data for longer periods due to shorter retention periods.

Responsibilities:

Lead and guide development of Datastage based ETL architecture.

Develop solution in highly demanding environment and provide hands on guidance to other team members.

Understand and translate business needs into data models.

Develop full SDLC project plans to implement ETL solution and estimate resource requirements.

Tuned ETL jobs and Sequencers for the optimized performance.

Work with project manager on the RFP and RPI for the new leg requests.

Work with the OLTP team in resolving the Data Quality Issues raised by HCA.

Worked on the installation of Datastage 11.3 and the Project code migration to the latest version.

Participate in peer quality reviews of Datastage code, Oracle objects and UNIX scripts.

Enforce applicable standards, policies, procedures, and best practices in the implementation and/or maintenance of application.

Develop Parallel Jobs using various Development / Processing Stages (Transformer, Aggregator, Change Capture, Join, Lookup, Modify, Filter, Sort and Merge, Funnel, Remove Duplicate Stage) Debug Stages (Peek Stage, Head and Tail Stage, Row generator stage, Column generator stage, Sample stage).

Client: State Street Corp. Boston, MA

Role: Senior Datastage Consultant

Duration: July'13 to June’14

Environment: DataStage 8.5, UNIX, Oracle 11g and Autosys

Project Name: CCAS Development

Project Description: State Street Corp has bought Risk Authority from Moody’s Analytics to create regulatory reports on Basel I, II and III and send it to federal government on a monthly basis. CCAS Development involves creating the ETL jobs to pull data from various sources within State Street Corp and feeding it to the Risk Authority.

Responsibilities:

Closely worked with Moody’s team in understanding their product and assisting them in installation of Risk Authority in our environments.

Analyze, estimate and plan ETL tasks, milestones and deliverables.

Determine architectural and design practices during implementation of new ETL mappings.

Define suitable architectures for data integration from multiple disparate data sources including legacy systems, relational databases, flat files and spreadsheets.

Design and develop ETL jobs using IBM Infosphere Datastage 8.5 to extract, transform and load the data into Risk Authority Schema.

Review the Mapping Specs and created High-level design documents and technical design documents as per the specifications provided.

Client: Aetna Inc. Hartford, CT

Role: Application Developer II

Duration: Aug'10 to May’13

Environment: DataStage 7.5/8.5, UNIX, DB2 and Zeke

Project (1) Name: External Hedis Claims Processing

Duration: June’12 to May’13

Project Description: Currently in Aetna, processing of the Hedis claims was being done in Ab Initio. Through this project we moved the processing of the Hedis claims from Ab Initio to Datastage 8.5. This project involved loading the 6 files to the warehouse after performing transformations. The files were received on request basis.

Responsibilities:

Designed the Generic ETL framework so that a single generic job can be invoked multiple times for the same functionality across different subject areas.

Worked with the different cross functional teams to finalize on the requirements and turn those requirements into Application Design specifications.

Worked on the detailed design document after the application design document has been signed off.

Designed and coded the ETL logic using Datastage 8.5 and UNIX scripts and created the Zeke jobs for scheduling.

Worked with ETQA team for the system testing and bug fixes.

Worked with project managers to provide the project status on a weekly basis and alert him before hand on any potential risks to the timelines of the project.

Provided post production warranty support to the project.

Project (2) Name: EDW Datastage upgrade from DS 7.5 to DS 8.5

Duration: Feb’12 to Dec’12

Project Description: As part of the infrastructure upgrade. Aetna decided to move from Datastage 7.5 to Datastage 8.5 and hence had to upgrade all the projects currently running in DS 7.5 to DS 8.5. I handled the upgrade for one of the informatics project EDW Adhoc. This involved upgrading all the jobs from DS 7.5 to DS 8.5 and testing it to make sure all jobs are running and the results match between DS 7.5 and DS 8.5.

Responsibilities:

To develop a project plan for this upgrade and provide the estimates.

To lead a team of developers to get the project done on time and with minimal defects.

To devise a testing approach for the upgrade project.

To create the artifacts required as part of the APLC in Aetna.

To work on the technical issues encountered in the upgrade because of the mismatch in functionalities between the versions of the Datastage.

To successfully complete the project on time and with no discrepancy in the output of the 2 versions of Datastage.

To work with Business team on procuring the test data files and later on working with them for UAT.

Project (3) Name: SDW Production Support

Duration: Aug’10 to Jan’12

Project Description: The SDW (Strategic Data Warehouse Project) is a comprehensive project being undertaken by Aetna Inc across its business groups to store and address its electronic and physical data management practices in 36 major jurisdictions. Applications that fall under the umbrella of SDW are CCR (Contact Center Reporting), HRDW (Human Resource Data warehouse), HWDM (Health and Wellness Datamart), MMIS (Medical Management) and many more.

Responsibilities:

To provide 24/7 support to the SDW batches, some of them were daily, monthly and adhoc.

To fix the issue or to come up with a work around for any issues that may cause jeopardy in the SLA of the batch.

To carry out all the Datastage code migration including UNIX components and Zeke changes for the monthly major and minor releases.

To review the code and the scheduling pattern of the projects before they go live in production and to make sure they comply with the informatics framework.

Coordinate with the offshore team on the overnight batches.

Client: State Street Corp. Boston, MA

Role: Senior ETL Consultant / Production Support Analyst

Duration: Mar'09 to June’10

Environment: DataStage PX, UNIX, Sybase and Autosys

Project Name: IMS ETL2

Project Description: IMS ETL 2 Project is about sending the Standard (ISO) and Customized extracts to the institutional clients of State Street Corp across the globe. Currently we were sending extracts to 6 clients, which totals to about 120 extracts send daily without any SLA breached.

Responsibilities:

Designed the overall Extraction process for the Standard (ISO) and Customized extracts using DataStage jobs and Sequencer.

Created the Autosys jil's to schedule the DataStage jobs and maintain the dependencies across systems.

Developed the UNIX Wrapper Scripts for invoking the DataStage jobs and for the transmission of the extracts to the hub.

Worked with the offshore team to convert the High Level Design into Detailed level Design followed by the Development and Unit testing.

Worked on the production support activities for the project. Supported the system 24*7 with a very low percentage of SLA breaches.

Leveraged the reusability of the Standard ETL Process (ISO), to create the standard extracts for the new client on board.

Worked independently on the Tolerance check application.

Client: UBS (United Bank of Switzerland) Stamford, CT

Role: ETL Architect

Duration: Aug'08 to Feb'09

Environment: DataStage 7.5, UNIX, Oracle and Autosys

Project Name: TAS GDMP

Project Description: The scope of this engagement is to have an ETL and data warehouse based solution to archive the data coming in the scope of trade and asset services group of UBS-NA.

Responsibilities:

Developed the architecture of ETL and Data Warehouse based solution for GDMP.

Worked closely with cross functional business teams to document ETL requirements and turn those requirements into ETL jobs. planning, Development and Unit Testing of ETL processes.

Conducting ETL performance tuning, troubleshooting, support, and capacity estimation.

Maintaining a thorough understanding of the business processes, ETL processes, and identifying efficiencies and needed controls.

Collaborate with offshore team to implement the solution Lead the offshore resources in terms of approach and query resolution Test plan preparation, Review of test plans, unit & integration testing.

Client: Blue Cross Blue Shield of Minnesota Minneapolis, MN

Duration: Aug’07 to Mar’08

Role: ETL Lead Consultant

Environment: Informatica Power Center 8.0, DB2, UNIX scripting

Project Name: IHP (Integrated Health Platform)

Project Description: This project was to develop ETL jobs which involve extracting data from the EAW (Enterprise Analytics Warehouse) hosted on DB2 and generate flat files to be sent across to the third party vendor. The different subject areas worked on are claims, providers, members, member eligibility. These files were then FTP to a different server.

Responsibilities:

Lead on-shore development team of 2 members, explained critical requirements, participate in design sessions, provided consulting in complex technical development.

Worked on the various modules of the Healthcare data warehouse like medical claims, Rx claims, membership, benefits and providers.

Developed business requirements; created and maintained mapping documents, high-level ETL design, process flow design, and other various development standards.

Designed and coded the ETL logic using Informatica Power Center’s Mapping Designer to generate flat files for data analysis to be sent to a third-party vendor - RHI (Resolution Health Inc.). These flat files were generated from the Enterprise Analyst Warehouse on DB2.

Extensively worked on the Informatica Designer, Repository Manager, Repository Server, Workflow Manager/Server Manager and Workflow Monitor.

Scheduled the Informatica Workflows using the Workflow Manager.

Client: The Regence Group Portland, OR

Duration: Jan’07 to Aug’07

Company: Cognizant Technology Solutions Pvt Ltd.

Role: ETL Lead Developer

Environment: DataStage PX, Oracle 9i, MS SQL Server 2000, IBM AIX

Project Name: CPSS (Common Process Single System)

Project Description: This project was to develop ETL jobs which involve extracting small group information, benefit information for group’s Medical, Dental, Pharmacy, Vision and Complementary care, from the Facets 4.31 tables and load it into Benefit Description systems. Before loading the data to the target tables, it also sends alerts for groups which have missing benefit information, group information and the other criteria specified in the technical specifications.

Responsibilities:

Lead off-shore development team of 7 members, explained critical requirements, participate in design sessions, and provide consulting in complex technical development for the various Regence ETL applications required for the implementation of the CPSS project.

Ensured the timely delivery of the developed and unit tested code from the onsite and the offshore team members.

Worked as an Onsite Coordinator for the project acting as a single point of contact between the clients and the offshore development team in Chennai facility.

Created technical specs from functional use cases; created high-level process flow design and ETL architecture.

Mentored new engineers to the project both technically and functionally.

Created shared containers to use in multiple jobs.

Client: Premera Blue Cross Blue Shield Seattle, WA

Duration: Jan’06 to Dec’06

Company: Cognizant Technology Solutions Pvt Ltd.

Role: Data Warehouse Developer

Environment: IBM DataStage 7.1 Server Edition, SQL Server 2000, AutoSys

Project: PRISM (Premera Information System Management)

Project Description: PRISM II is the PREMERA data warehouse for their Facets source system. ETL Process Enhancements is all about enhancing the newly replatformed PRISM II ETL modules according to the new business requirements and also making the PRISM II ETL modules compliant to the Facets Version 4.11.

Responsibilities:

Assisted clients in the Mapping and Modeling of new business requirements, doing an impact analysis of the functionality changes.

Estimated the development work required for developing, changing and testing the DataStage jobs, SQL Servers Stored procedures to implement the new business functionalities and helping clients in system implementation of new functionality releases.

Extracted data from flat files and loaded data into SQL Server 2000 staging database using IBM WebSphere DataStage 7.5.1.

Extracted data from SQL Server staging area, transformed the data, and loaded it into the data warehouse in the SQL Server database according to the required provisions using IBM DataStage 7.5.1.

Used DataStage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into the data warehouse database.

Wrote the SQL scripts to analyze the tables and create indexes once the data is loaded to the Data warehouse tables.

Developed VB scripts to integrate and consolidate DataStage jobs, sync sorts and stored procedures into a single script.

Client: United Health Group Minneapolis, MN

Duration: July’04 to Dec’05

Role: Developer

Location: Cognizant Technology Solutions, Ltd. India

Environment: ASP, MS SQL Server 2000, Windows

Project: UHG PMO

Project Description: The UHG PMO is an exclusive for UHG projects and is one stop portal providing information on various projects, processes, infrastructure and staffing related to UHG projects.

Responsibilities:

Requirement analysis

Design the architecture and class details for the PMO portal.

Coding

Testing the developed code.

Adhere to Cognizant Standards and quality processes

Suggest better and innovative solutions to make the application more user friendly

EDUCATION

B.E. Computer Science

Institute of Technology and Management, Gwalior (M.P.)

Affiliated to RGPV, Bhopal (M.P.)

Contact this candidate