UMESH MAHADEV GURAV

Cell# 216-***-**** Email: adkrl7@r.postjobfree.com

●7 years 11 month of experience of working in IT industry with strong background in data warehousing, business intelligence and ETL process using Informatica PowerCenter.

●Experience in loading/updating/manipulating data, troubleshooting, debugging mapping, performance tuning of Informatica (Source, target, mapping, session) and fine tune transformations to make components more efficient in terms of session performance.

●Strong experience of using databases like Oracle, SQL server, Teradata, Mainframe.

●Strong experience of technologies like SQL, PL/SQL procedure/Functions, triggers and packages, sql queries.

●Strong experience of using Teradata versions 13.1, 14.1 and 15.1 and its utilities like FastLoad, MultiLoad, Tpump, Bteq and also have knowledge of Teradata indexes. I have made use of all these utilities in Informatica connectors.

●Strong experience of work in Unix shell scripting language.

●Strong knowledge of data warehouse techniques and practices, experience, dimensional data modeling (star schema, snow flake schema, fact and dimension tables), OLTP and OLAP.

●Automation Informatica jobs using CA7, deployment of Informatica/Unix/database components thru Udeploy and Artifactory.

●Worked on BI tool such as OBIEE and also data quality tool as Informatica developer (IDQ) for some time.

●My domain expertise is in Banking and financial services industry.

●ETL Tools: Informatica PowerCenter, PowerExchange.

●Data modeling:Physical modeling, logical modeling, dimensional modeling (star schema, snow-flake schema)

●Databases: Oracle, Teradata 13.1 14.1 and 15.1, Mainframe, SQL server.

●Language: Sql, PL/SQL, Unix shell script, Java

PNC Financial Services Group, Cleveland, OH 09/ 2019 – Present

Project #7: PNC Investments

ETL developer

Description:

PNC Investments is a data warehouse project for all the investments information such as holdings, brokerages, employees to client relations, commission etc. National financial services is main source for this project which provides all the Investments related information in the bank. I deal with Different source database such as Teradata 15.1, Mainframe and flat files and our target is oracle. This project works on agile methodology. I recently joined this project as an Informatica developer and my roles and responsibilities are as below.

Responsibilities:

●To work on user stories assigned to me as a part of sprint deliveries. Also work with business and data analyst team to understand the requirements

●Prepare the HLD and LLD for the requirements.

●Create Informatica mappings, workflows as per the requirements, also involved in unit testing for it.

●Involved in dimensional modeling, table creation, metadata management etc.

●Involved in performance tuning part of Informatica by using Teradata utilities and design the database tables for that.

●Create CA7 jobs to automate the workflow execution in PROD.

●Create Unix script to NDM the source file from source server to Informatica server and also configure this whole process.

PNC Financial Services Group 10/ 2017 – 08/ 2019

Project #6: ATM Channel transactional/systemware report.

ETL developer

Description

Under BRT systemware project we pulls ATM transactional xml files from ATM machine server, process it thru Informatica and generates mainframe file in the form of report which then send to systemware portal. Report at systemware typically contains all the transactional information for the transactions performed on ATM machine and which gets captured in source XML. Some of the transactions are Check/Cash deposits, Withdrawal, Checkcashing, Pin change, login, logout etc. We also generate reports for every ATM terminals and their operational/maintenance details on weekly basis.

Responsibilities

●To understand Business requirements and preparation of project design document.

●To prepare shell script to pull files from source server to etl unix server based on requirements.

●Worked on PL/SQL scripts includes procedures, functions and cursor to handle some requirements.

●Development of Informatica components as per requirements and design documents.

●To modify existing components to accommodate new changes in xml due to latest ATM server/client builds.

●Coordinate with testing team to get the report validated against the transactions performed on ATMs and also to make sure report fulfill the requirements.

●To create Informatica package to promote code to higher environment using Artifactory, Udeploy and ServiceNow.

Languages/Databases: Oracle SQL, Linux Shell Scripting, Mainframe

Special Software: InformaticaPowerCenter 9.6.1, InformaticaPowerExchange 9.6.1, Toad, Putty, Harvest,WinSCP, Mainframe

PNC Financial Services Group, Cleveland, USA07/ 2017– 09/2017

Project #5: Asset Management- KPMG brokerage feed

ETL developer

Description:

Under Asset Management group we have requirements for KPMG brokerage feed wherein we are generates

Two out bond files for vendor to process. These files holds account and position information and processed on

Daily basis IRW(Oracle) is source for this requirement and mainframe files as a target. Through EFX/NDM file transfer process files will be sent to vendor server to process further.

Responsibilities:

●To understand Business requirements and prepare design documents for it.

●To understand the functionality of the existing systems.

●Preparation of Source to target mapping documents based on the business requirements and sources and targets.

●To develop new ETL components as per business requirements.

●To modify existing ETL, Unix and PL/SQL components.

●Unit testing for the ETL/Unix components developed or modified.

●To review the components promoted and also review the unit test results prepared by other team members.

Languages/Databases: Oracle SQL, Linux Shell Scripting, Mainframe

Tools/software: InformaticaPowerCenter 9.6.1, InformaticaPowerExchange 9.6.1, Toad, Putty, Harvest,

WinSCP, Mainframe

PNC Financial Services Group, Pune, India 10/ 2016-10 –06/ 2017

Project #4 Home Lending Integration

ETL developer

Description:

Under PNC's Home Lending Integration (HLI) initiative, home equity installment loans (HEILs) and Home equity

Line of credit (HELOC) will be converted from current loan servicing systems to the Black Knight Financial

Services (BKFS) and Mortgage Servicing Package (MSP) loan servicing system. We receive source data from EDW.

We do categories the source data based on source systems and loads into Teradata tables for respective service type.

Responsibilities:

●Worked on data modeling for this project and also identified the impact of LT initiative on ADAPT components/jobs.

●Mapped legacy ACLS/FDR attributes to new EDW MDB attributes under LT initiative based on STM.

●Prepared STM documents for impacted/required components and HLD document for LT requirements.

●Worked on developing new ETL, Unix and PL/Sql components and also modified existing one as per impact

●Analysis done and per the business requirements.

●Used PowerExchange 9.6.1 to read and write data from/to delimited and fixed width mainframe files and also read/write to Teradata database.

●Created Power Exchange Data Maps for Mainframe Files.

Languages/Tools: Informatica 9.6.1, Teradata, UNIX scripting, Informatica Power exchange 9.6.1

Databases: Teradata 13.1 and 14.1, Oracle, Mainframe

PNC Financial Services Group, Pune India 01/ 2016 – 09/ 2016

Project #3 ADAPT 1.5

ETL developer

Description:

Migrated Integrated customer data mart ICDM application from Oracle to Teradata environment as part of ADAPT 1.5 project.

Responsibilities:

●Developed ETL code for converting existing UNIX and PL/SQL scripts on oracle database and get it migrated it to Teradata Environment

●Worked with SIT/UAT testing team to resolve the issue/defects.

●Worked on databases such as oracle, Teradata, mainframe SQL server.

●Worked on Audit balance control part of project where we applied checks on both target and source for auditing.

●Used PowerExchange 9.6.1 to read and write data from/to delimited and fixed width mainframe files of GDG base.

●Created Power Exchange Data Maps for Mainframe Files. So that to read/write mainframe file thru Informatica.

Languages/Tools: Informatica 9.6.1, Toad, Teradata, UNIX scripting, PL/SQL, Informatica Power exchange9.6.1

Databases: Teradata 13.1 and 14.1, Oracle, Mainframe

PNC Financial Services Group, Pune, India 07/ 2013 –12/ 2015

Project #2 Data Quality

ETL developer

Description

PNC Bank implements Data quality checks on data from different source systems from EDW in order to check the data accuracy and correctness. We do have reports on weekly/monthly basis to report the data quality index to customers.

Responsibilities

●Developed ETL code for Data quality check, in which we are applying some rules on data such as business rules or structural rules to check the data accuracy, correctness.

●Involved in unit testing and negative testing of the ETL components developed for the requirements.

●Worked in lineage activities. Lineage is to find out the data flow from source to end target.

●Involved in Teradata migration activity, performed UAT for different workflow.

●Worked on Informatica Analyst Tool for profiling of data.

●Worked on Informatica developer tool (IDQ) to apply some defined data quality checks on source data from Teradata.

Languages/Tools: Informatica 9.5.1, Toad, Teradata, Oracle, UNIX scripting, Informatica developer(IDQ)

Database: Teradata 13.1, Oracle

Shop All - Gandhi Nagar, Gujarat 03/ 2013 –06/ 2013

Project #1 Shop All

ETL and Web application Developer

Description:

●Web application for Shop All online retail business offers variety of shopping options for customer.

●It has 3 modules as Admin, service users and inventory.

●The functions provided to Admin are: To validate, approve and terminates users Manages access to his employee and toUpdate inventory.

●The function provided to the users to register him and get access to the products.

●Users have their own wish lists. User can update their portfolio.

●The Inventory handler has access to all components form the inventory all the data about damaged and returned products to the shops so that he can reshuffle them

●Add and update to inventory.

Responsibilities

My roles and responsibility as following:

●Performed IQA's and EQA's for different web pages and its functionality.

●Developed the web page to implement features of admin.

●Developed ETL to implement Type 2 for inventory table.

Languages/Tools: SQL+, JSP, HTML

●Bachelor of engineering in Computer Science and engineering from Shivaji University, Maharashtra, India, 2012

●Achieved IBM DB2 Database certification 10.1 Fundamentals.

●TCS Business Domain Academy: Certificate in US Banking

●TCS Business Domain Academy: Certificate in Retail Banking Functions & Delivery Channels

Summary

Technical skills

Professional experience

Education

Certifications/Achievements

Contact this candidate