Data Sql

A ddress:

S mruthi Chikatamalla

*** * ******** ******, *** 3806,

M obile: +1-414-***-****

Chicago, I ll inois – 60601.

E mail: *.*******@*****.***

T itle: D ata Engineer / PL/SQL / Data Warehouse / ETL Developer

Summary:

• Around 6 years of IT experience in building custom applications and support solutions, covering all

aspects of the Software Development Lifecycle in Insurance, Telecom and Banking domains.

• E xpertise in Data Warehousing, Data modeling, ETL methodologies, Master Data Management

( MDM), database designing and PL/SQL development.

• Thorough knowledge of Ralph K imball's Data Modeling concepts including Data Modeling, Data

M ar ts, Dimensional Modeling, Star and Snowflake schema, Fact aggregation and Dimension

t ables and slowly changing dimensions.

• Broad experience in database development including effective use of database objects, SQL Trace,

E xplain Plan, Hints, Indexes, Table Partitions, Materialized Views, Global Temporary tables, and

O racle Buil t-in Functions.

• Excellent SQL and PL/SQL skills with vast experience in building complex packages, procedures,

f unctions, t r iggers and queries.

• Strong expertise in Data Modeling using Normalization techniques and Top-Down and Bottom-Up

approaches. Created conceptual, logical and physical data models using SQL Data Modeler.

• Expertise in converting/importing the physical data models to actual database objects using the

SQL data modeler and thus minimizing redundancy in work.

• Extensive knowledge in Development, Analysis and Design of ETL methodologies in all phases of

Data Warehousing life cycle. Extracted, t ransformed and loaded data from different data

warehouses, databases, f lat files, CSV and excel files using SQL Loader, shell scripts, external

t ables and complex PL/SQL procedures.

• Experience in creating and maintaining batch scripts to extract data from different databases.

Page 1 of 7

• Experience with Data Cleansing, Data Profiling and Data analysis to suit business requirements.

• Proficient in Performance Tuning and Query Optimization. Received appreciation in various

p rojects for my outstanding Performance Tuning skills.

• Experience in database Capacity and Security management. Worked with network engineers to

u nderstand and analyze the capacity and security requirements from a network perspective.

• Gathering business requirements and creating a technical solution to match the requirement.

E xperience in creating and maintaining requirement, functional and design documents and, data

d ictionary. Created end-to-end data f low diagrams using Visio.

• Excellent writ ten and verbal communication skills. Ability to liaise with people from different

teams and background. Demonstrate strong leadership and team player qualities.

E ducation: Bachelor’s in Electronics and Communication Engineering, J.B.I.E.T., JNT University, India

(2004-2008)

Employer: I nfosys Technologies ( Infosys L td.) since 2008.

Cur rent Designation: T echnology Analyst

Technical Skills:

: Oracle 8i, 9i (9.2.0.5, 9.2.0.6), 10g, 11g

Databases

: MS Windows 2000/9/NT/XP & VISTA

O perating Systems

: SQL, PL/SQL, Unix Shell scripting

Languages

: Toad, SQL Developer

Database tools

: SQL Data Modeler

Data Modeling tools

: SQL* Loader, AutoSys, WinSCP, Putty, Purisma

Other Utility tools

: MS OfficeSuite

Documentation

: VSS, Tortoise

Configuration

m anagement

P roject Experience:

Global Master Data Management solution to support

Project Nov 2012 -

G lobal Risk Insight Platform (World’s Largest Repository

: P resent

I nsurance and Risk Information and Analytics ) for

leading Insurance Broker (Phase2: Client and

Page 2 of 7

I ndustry Data Redesign )

Client AON Corporation, leading global provider of Risk services and

I nsurance

:

Team Lead / Technology Analyst

Role

:

Design and create an enterprise Data Warehouse solution to clean up and redesign the existing Client

and Industry entities of AON Corporation, to suit the changing downstream business requirements.

E xtract, t ransform, validate, stage and load data from multiple local system across 25 count ries. Create

A udit Trail and form Global Hierarchies using complex PL/SQL to help AON decommission the existing

Purisma(ETL) tool. Create Materialized views and Data Mar ts for downstream data consumers. Build a

Java based U I portal to facilitate convenient Global Data Stewardship activities. The main consumers of

t his data is G R IP (Global Risk I nsight Platform) which is the world’s largest global data

r epository t hrough which AON has earned more than $100M revenue.

• Responsible for defining the scope and business rules of the project, gathering business

requirements, and document them. In teracted with cross functional and downstream teams to

facilitate gathering of business requirements.

• Worked on a nalyzing the existing data and identifying the bottle necks and pain points.

• Created f unctional and business database design documents to suit the requirements and

m inimize the pain points. Created and socialized the end-to-end data f low diagrams.

• Created E-R, logical and physical d ata models based on the snow flake schema model.

• Worked on analyzing the d ata trends, predicting its growth and capacity planning for

databases to ensure smooth run through its lifecycle.

• Saved t ime and money for the client/Data Stewards by:

o Redesigning and developing the ETL process using complex PL/SQL procedures

eliminating the need of a third party tool. Existing ETL process used Purisma, a third

party ETL tool. Data is extracted on an ongoing basis from multiple sources such as f lat

f iles, existing databases, databases across the globe (data is collected from various

count ries) and CSV files using shell scripts, Oracle procedures and SQL Loader .

o Automating the Data Governance processes to pre populate the stewarded att ributes

by observing the governance t rend.

Page 3 of 7

• Developed complex PL/SQL packages to perform pre-loading, threshold and aggregation

(business and functional) validations and create global hierarchies.

• Responsible for design and development of e fficient error handling and Audit t rail using

complex PL/SQL.

• Created M aterialized views, Data Ma r ts and users specific to the needs of the downstream

systems.

• Performance tuned the existing and new procedures to decrease the data aggregation time by

70%, using SQL explain plan, creating indexes, minimizing JOINS and analyzing the data to

b reak i t down into multiple processes.

Global Master Data Management solution to support Global Risk

Project D ec 2011 - Oct

I nsight Platform (World’s Largest Repository Insurance and Risk

: 2012

I nformation and Analytics ) for leading Insurance Broker (Phase1:

M aster Data Management)

Client AON Corporation, leading global provider of Risk services and

I nsurance

:

Technology Analyst

Role

:

This is a Global Master Data Management (MDM) solution designed to manage data of various AON

entities such as Clients, Industries, Carriers and Products, with their data coming from various local

sources spread over 20 count ries. This application consists of a Java based U I Portal, Master data

management hub (Purisma), their underlying objects in Oracle database and SQL Server Integration

Services (SSIS).This application is responsible for publishing the aggregated, consolidated and

h ierarchical view of the entities to the target downstream systems.

• Coordinated with the front end design team to provide them with the necessary stored

package and procedures and insight into the data.

• Created jobs to schedule daily refresh of the Materialized views and r eport via e-mail i f any

job/refresh fails.

• Responsible for analyzing and implementing the C hange Requests.

• Modified the existing business rules in the SSIS package to suit the new business requirements.

• Worked extensively on performance tuning of the existing ETL process.

Page 4 of 7

• Partitioned the fact tables and m aterialized views to enhance performance.

• Handled system u tilization issues, performance statistics, capacity planning, integrity

monitoring, maintenance, and reorganization of the data.

• Handled errors using Exception Handling extensively for the ease of debugging and displaying the

e rror messages in the application.

• Created and maintained D ata Dictionary and pursued to reach consensus.

• Performance tuned and optimized the procedures, in-line queries and views for quicker ret rieval

t ime of data through the U I Portal. Was able to r educe the ret rieval time by almost 75%.

• Responsible for p roviding ad-hoc analysis reports and queries to support Global Data

S tewards.

Dr iver & Vehicle License E-services

Project J uly 2010 - Nov

(www.eservices.gov.ky )

: 2011

Client Cayman Islands Government (CIG)

:

Senior Database Engineer

Role

:

Complete end to end automation of the vehicle and driving license e-services (application and renewal

p rocess) for Cayman Islands Government (CIG). Involved creating a complete back end solution for the E-

services portal and connecting to other confidential databases to extract and validate the user

i nformation. Administrator should be able to provide/assign an ESID (random unique identifier) for each

user.

• Analyzed the requirement document provided by the Business Analyst and created functional and

technical specification documents.

• Created logical and physical data model diagrams using SQL Data modeler. Imported the

physical data model to the database to create the required database objects. Thus, r educing the

e ffort of w riting SQL t o create objects.

• Created D ata flow diagrams, Data mapping f rom Source to stage and Stage to Target mapping

documents indicating the source tables, columns, data types, t ransformations required and

business rules to be applied.

Page 5 of 7

• Designed and implemented S tar schema (fact and dimensional data modeling) for the user

details module and, Snowflake schema for the vehicle and driving license module.

• Implemented the logic to generate and assign random unique identifier to every new user.

• Developed views, functions, procedures, and packages using PL/SQL & SQL to t ransform data

between existing database and proposed database .

• Developed SQL queries to perform D ata Validation and Data I ntegrity testing .

• Created T est cases and Test scripts for System In tegration Testing (SIT), User Acceptance

Testing (UAT) and Unit In tegration Testing.

• Created T r igger based Audit T rail .

Global Business Continuity and Disaster Recovery Data

Project J an 2009 - June

Warehouse

: 2010

Client Bank Of America

:

ETL and PL/SQL Developer

R ole

:

Data Warehouse to store history, facilitate and create reports for the weekly Disaster Recovery (DR)

exercises performed by various applications of the bank. Created data mart for the Resiliency Testing and

A nalysis team to analyze and provide score to the applications tested. Slots were booked and results

entered by participating teams through a U I portal. Involved complex calculations, study of data t rends

and patterns to create dynamic reports using PL/SQL.

• Analyzed Requirements and created Use Case Diagrams using Visio .

• Designed database objects such as Tables, Constraints, Views, and Indexes in coordination with

t he application development team.

• Created complex P L/SQL procedures to integrate, extract and t ransform data into meaningful

i nformation.

• Applied various statistical methods and in-built Oracle functions to cluster and classify the

data to report on every application that participated in Disaster recovery.

Page 6 of 7

• Created d atabase Downtime and Uptime Report by extracting the information from the a lert

log files.

• Created and maintained Data Dictionary.

• Involved in wri ting T est plans for P L / SQL S tored Procedures and Functions.

• Involved in U nit testing and System integration .

Asset management Project

P roject J une 2008 - Dec

: 2008

Client Verizon

:

PL/SQL Developer

Role

:

The main goal of this project was to provide a centralized solution to manage and t rack the various assets

of Verizon (telecom provider) ranging from infrastructure to stationery i tems. This application consists of

a Java based portal and Oracle 8i database. User and machinery information was extracted and loaded

using PL/SQL procedures and SQL loader. The assets were t racked and assigned to a user through the U I

Portal.

• Effectively communicated, de-briefed and followed up with Business Users and Application

Owners.

• Developed P L /SQL B usiness Functions, procedures and packages for data models to create

r eports f rom J2ee web Application.

• Involved in Gathering the Requirements for creating data models for data analysis and reporting.

• Involved in developing E TL Process to load the Data from MS Excel to Oracle Database models.

• Created ad-hoc reports on request using SQL queries.

Page 7 of 7

Contact this candidate