Data Manager

Amoolya Reddy

SUMMARY

. Around * years of professional experience in IT Industry with

expertise in Analysis, Design, Development, Implementation, Modeling,

Testing, and support for Data warehousing applications.

. Extensive experience in developing complex mappings from varied

transformations like Router, Filter, Sorter, Connected and Unconnected

lookups, Normalizer, Expression, Aggregator, Joiner, Union, Update

Strategy, Stored Procedure and Sequence Generator etc.

. Experienced in loading data, troubleshooting, Debugging mappings,

performance tuning of Informatica (Sources, Targets, Mappings and

Sessions) and fine-tuned transformations to make them more efficient

in terms of session performance.

. Extensive experience in using Informatica PowerCenter

9.5/9.1/8.6.1/8.5/8.1/7.x/6.x/5.x., Power Exchange, Power Mart

5.x/4.x, Informatica Data Analyzer (Reporting Tool), Informatica Data

Quality (IDQ) and Data Validation Option (DVO), Use Cases, UAT,

Business Process Mapping, Gap analysis.

. Proficient knowledge in Data warehousing and Business Intelligence

concepts with emphasis on ETL and life cycle development using

Informatica PowerCenter (Repository Manager, Designer, Workflow

Manager and Workflow Monitor).

. Extensively worked on Dimensional modeling, Data migration, Data

cleansing and Data Staging of operational sources using ETL processes

and providing data mining features for data warehouses.

. Worked on Slowly Changing Dimensions (SCD's) and its implementation to

keep track of historical data.

. Used Debugger to test the data flow and Optimized the Solution by

using the performance tuning methods in the Oracle SQL queries like

Indexing techniques, Hints, Partitioning and Parallel execution

process.

. Extensive experience in developing Workflows, Worklets, sessions and

tasks to effectively manage the Load and PMCMD (Informatica command

line utility) to create, schedule and control workflows, tasks, and

sessions.

. Proficient in the integration of various data sources involving

multiple relational databases like Oracle11g/10g/9i, MS SQL Server,

DB2, COBOL files and Flat File( fixed width, delimited ), into the

staging area of ODS, Data Warehouse or Data Mart.

. Experience in implementing Update-Strategies, incremental loads,

Informatica B2B data transformation and change Data capture.

. Knowledge of web services, complete software development life cycle

including Requirement Analysis, Requirement Gathering, Cost

Estimation, Project Management, Design, Development, Implementation

and Testing.

. Expertise in OLTP/OLAP System Study, Analysis and E-R modeling,

developing database Schemas like Star schema and Snowflake schema used

in relational, dimensional and multidimensional data modeling.

. Performed system Analysis and QA testing and involved in Production

Support.

. Experience in coding using SQL, SQL * plus, PL/SQL

procedures/functions, triggers and exceptions. Good experience in

Relational Database concepts, Entity relation diagrams.

. Loading data from various data sources and legacy systems into

Teradata production and development warehouse using BTEQ, FASTEXPORT,

MULTI LOAD, FASTLOAD and Informatica.

. Experience in UNIX working environment, writing UNIX shell scripts for

Informatica pre & post session operations.

. Created UNIX shell scripts to run the Informatica workflows and

controlling the ETL flow along with file transfers, job scheduling and

error handling.

. Excellent communication, presentation, project management skills, a

very good team player and self-starter with ability to work

independently and as part of a team.

TECHNICAL EXPERTISE

Data Informatica Power Center 9.5/9.0.1/8.6/8.1/7.1, Informatica

Warehousing/ETL Power Exchange 8.x, SSIS

Data Modeling Dimensional Data Modeling, Star Join Schema Modeling,

Snowflake Modeling, FACT and Dimension Tables, Physical and

Logical Data Modeling, Erwin 7.1.3, MS-Visio

Databases Oracle 11g/10g/9i, Teradata, Sybase, MS SQL Server

7.0/2008/2005, My SQL, DB2, MS-Access

Programming SQL, PL/SQL, SQL Plus, MS SQL, UNIX Shell Scripting(K-Shell,

C-Shell), C++,Visual Basic

Environment Win 2000/07/08, Win XP, UNIX, Sun Solaris, LINUX

Other tools & SQL*Plus, Business Objects 6.5, Teradata SQL Assistant,

Utilities Teradata BTEQ, Sybase SQL Advantage Sybase ISQL, SQL

Navigator, Toad, SQL tools, Autosys, Quest, MS-Office

EDUCATION

. Bachelor degree in Computer Science & Engineering from JNTU,India

PROFESSIONAL EXPERIENCE

Client: Cigna, Windsor, CT

March 2014 -Till Date

Informatica Developer

Project: CPM Go To Market

Cigna is a leading health service company committed to help Medicare and

Medicaid beneficiaries. Cigna-Health-Spring key products include Medicare

Advantage, Medicare Prescription Drug Coverage, Medicaid and other health

service products.

Responsibilities:

. Gathered user Requirements and designed Source to Target data load

specifications based on Business rules.

. Used Informatica Power Center 9.5 for extraction, loading and

transformation (ETL) of data in the data-mart.

. Designed and developed ETL Mappings to extract data from flat files,

MS Excel and Oracle to load the data into the target database.

. Developing several complex mappings in Informatica a variety of

PowerCenter transformations, Mapping Parameters, Mapping Variables,

Mapplets & Parameter files in Mapping Designer using Informatica

PowerCenter.

. Extensively used ETL processes to load data from various source

systems such as DB2, SQL Server and Flat Files, XML files into target

system Teradata by applying business logic on transformation mapping

for inserting and updating records when loaded.

. Expertise in conversions from SQL Server to Teradata.

. Created complex mappings to load the data mart and monitored them.

The mappings involved extensive use of Aggregator, Filter, Router,

Expression, Joiner, Union, Normalizer and Sequence generator

transformations.

. Ran the workflows on a daily and weekly basis using Active Batch

Scheduling tool.

. Examined the workflow log files and assigning the ticket to the

Informatica support based on the error.

. Performed operational support and maintenance of ETL bug fixes and

defects.

. Maintained the target database in the production and testing

environments.

. Worked with Teradata utilities such as M-load, F-load, export etc.. to

load or extract tables.

. Supported migration of ETL code from development to QA and QA to

production environments.

. Migration of code between the Environments and maintaining the code

backups.

. Designed and developed Unix Shell Scripts, FTP, sending files to

source directory & managing session files.

. Experience in developing Unix Shell Scripts for automation of ETL

process.

. Done extensive testing and wrote queries in SQL to ensure the

loading of the data.

. Loading data from various data sources and legacy systems into

Teradata production and development warehouse using bteq, fast-export,

multi load, fastload and Informatica.

Environment: Informatica Power Center 9.5, Oracle 11g/10g/9i,PL/SQL,

Teradata, Flat files, XML, MS Access, SQL Server, VSS, Active Batch.

JPMorgan Chase, Wilmington, DE

Aug 2012 - Feb 14

Informatica Developer

Project: Banking and Finance services Data Collection.

JPMorgan Chase & Co. is an American multinational banking and financial

services holding company. It is a major provider of financial services. It

is used by the investment banking, J.P. Morgan Asset Management, private

banking, private wealth management and treasury & securities

services divisions. Strives to develop innovative programs that leverage

the core strengths, capabilities and expertise of our business and our

people.

Responsibilities:

. Involved in Discussions with MDM team and Prepared Data Extracts for

integration with MDM.

. Gathered user Requirements and designed Source to Target data load

specifications based on Business rules.

. Used Informatica PowerCenter 9.1 for extraction, loading and

transformation (ETL) of data in the data-mart.

. Designed and Implemented the ETL Process using Informatica power

center.

. Involved in Data Extraction from Oracle, Flat files using Informatica.

. Prepared ETL flow of data from Staging to Data Mart.

. Used Source Analyzer and Warehouse designer to import the source and

target database schemas.

. Created mappings from source systems to target systems. Implemented

Join, Expression, Aggregate, Sort, Rank, Lookup, Update Strategy,

Filter and Router Transformations in Mappings.

. Worked on Designer tool -Source Analyzer, warehouse designer, Mapping

Designer & Mapplet Designer and Transformation Developer.

. Involved in complete software development life cycle (SDLC) of the

project.

. Used Mapplets and Reusable Transformations to prevent redundancy of

transformation usage and maintainability.

. Created Reusable Transformations and Mapplets in a mapping.

. Involved in Creating tasks, worklets, workflows and Schedule, Run,

Monitor sessions by using workflow manager and workflow monitor.

. Worked on moving mappings from Development to Test and from Test to

Production environments.

. Migrated Mappings, Sessions, and Workflows from Development to Test

and then to Production environment.

. Experience in developing Unix Shell Scripts for automation of ETL

process.

. Wrote UNIX Shell Scripting for Informatica Pre-Session, Post-Session

Scripts and also to run the workflows. Wrote Unix scripts, perl

scripts for the business needs.

. Coded Unix Scripts to capture data from different relational systems

to flat files to use them as source file for ETL process.

. Wrote Quote Site command (Shell Scripting) as a part of FTP'ing the

Files to the mainframe region.

. Identified and fixed the Bottle Necks and tuned the Mappings and

Sessions for improving performance. Tuned both ETL process as well as

Databases.

. Created and monitored sessions and batches to run the mappings.

Environment: Informatica PowerCenter 9.1, SQL Server 2008, Windows Xp,

Oracle 11g, VSS, Toad, UNIX, Unix- SunOS, Korn Shell, Quality Center 10.

Mississippi State Department of Health, Jackson, MS

March2011- July12

Sr. ETL Developer

Project: Data Migration from Unix & Db2 8.1 to IBM balanced warehouse 9.5

Highly comprehensive resource on public health in Mississippi, including

online documents, requests for public records, databases of public health

advisories, program Web sites, and contact information.

RESPONSIBILITIES:

. Involved in all phases including Requirement Analysis, Design, Coding,

Testing and Documentation.

. Translated the business processes into Informatica mappings.

. Responsible for coordinating development and testing efforts with

offshore team members.

. Develop, test and maintain ETL procedures employing both ETL tools and

custom PL/SQL.

. Reused the single workflow for loading data from various regional

files with similar file structure using indirect loading as source

type in session properties.

. Expertise in Performance Tuning by identifying the bottlenecks at

sources, targets, transformations and session level. For SQL tuning:

used techniques like Explain plans, collecting stats

. Converted complex Sybase stored procedures to Teradata.

. Configured nodes in event engine to design SMART process.

. Responsible for developing complex Informatica mappings using

different types of transformations like UNION transformation,

Connected and Unconnected LOOKUP transformations, Router, Filter,

Aggregator, Expression and Update strategy transformations for Large

volumes of Data.

. Implemented slowly changing dimension Type 1 and Type 2 for Change

data capture .

. Responsible for Error Handling in Workflows and sessions using

Informatica workflow manager and monitor the workflow run and

statistic properties on Informatica Workflow Monitor.

. Responsible for Defining Mapping parameters and variables and Session

parameters according to the requirements and performance related

issues.

. Used Debugger to validate the mappings and gain troubleshooting

information about data and error conditions.

. Developed processes for automation of loading data using parameter

driven sessions for various workflows, verification and re-

conciliation of data stored in several different source systems.

. Configured and scheduled Pre and Post Session commands with Shell

Scripts and invoked them using command task.

. Participated in the development and maintenance of a Data Warehouse

Routine Load Schedule.

. Created UNIX shell script to FTP flat files from different ordering

systems to the ETL server.

. Created the Unix script to validate data between Sybase and Teradata.

. Wrote stored procedures for generating the data warehouse cycle dates

and to drop, re-create the indexes.

. Developed Test Cases according to Business and Technical Requirements

and prepared SQL scripts to test data.

. Used Incremental aggregation to capture new source data and in

scenarios where the incremental changes do not significantly change

the target so that the PowerCenter server updates the target

incrementally.

Environment: Informatica Power Center 9.0.1,Teradata, BTEQ, Sybase, SQL,,XML,SAP, Flat Files, Mainframe Files, Windows 7, UNIX Scripting, Event

Engine, TechExcel.

Hartford Financial Group, Hartford, CT

Dec'09 to Feb'11

Role: ETL Informatica Developer

Project: DWH for Auto Insurance.

The Hartford, is a United States-based investment and insurance company

that is part of the Fortune 500 list. The Hartford sells its products

primarily through a network of agents and brokers, and has been the direct

auto and home insurance Auto and homeowners' insurance, Business insurance,

including workers' compensation, auto, property, general and professional

liability Employee benefits, including disability, life & accident and

retiree health and Mutual funds .

Responsibilities:

. Interacted with Business Analysts to understand the requirements and

the impact of the ETL on the business.

. Worked with data modelers in preparing logical and physical data

models and adding/deleting necessary fields using Erwin.

. Extracted data from flat files and oracle database, applied business

logic to load them in the central oracle database.

. Developed mappings/Reusable Objects/Transformation/mapplets by using

mapping designer, transformation developer and mapplet designer in

Informatica Power Center.

. Created reusable transformations and mapplets and used them in

mappings.

. Implemented slowly changing dimensions to maintain current information

and history information in dimension tables.

. Involved in creation of Folders, Users, Repositories, Deployment Group

using Repository Manager.

. Worked on different data sources such as Oracle, SQL Server, Flat

files etc.

. Developed PL/SQL and UNIX Shell Scripts for scheduling the sessions in

Informatica.

. Created E-mail notifications tasks using post-session scripts.

. Wrote SQL, PL/SQL, stored procedures & triggers, cursors for

implementing business rules and transformations.

. Used Change Data Capture to implement incremental load.

. Used CDC mechanism to extract data which has changed at source since

last extract and load it in Data marts

. Created various reports in report studio and packages in framework

manager.

. Generated various reports like List, Crosstab, Chart, Master detail,

Drill through reports in report studio.

. Provided support to develop the entire warehouse architecture and plan

the ETL process.

Environment: Informatica Power Center 9.1/ 8.6.1, Oracle 11g/10g,

SQLServer2008, PL/SQL, XML, MS Access, Toad, UNIX, UDB DB2 8.1,Toad,

Autosys, Cognos.

Reddy Pharmaceuticals, India Nov'06 to

Nov'07

Role: ETL Informatica Developer

Project: GCD Data warehouse.

Provides total customer satisfaction and achieve leadership in chosen

markets, products and services across the globe, through excellence in

technology, based on world-class research and development committed to meet

the customer requirements through developing and manufacturing of high

quality pharmaceutical ingredients and marketing at agreed delivery

schedules, implementing Current Good Manufacturing Practices and

continually improving the effectiveness of the Quality Management System.

Responsibilities:

. Design and developed Oracle PL/SQL Scripts for Data Import/Export,

Data Conversions and Data cleansing.

. Extensively used Power Center/Mart to design multiple mappings with

embedded business logic. Created Mapplet and used them in different

Mappings.

. Provide Knowledge Transfer to the end users and created extensive

documentation on the design, development, implementation, daily loads

and process flow of the mapping.

. Developed Complex mappings and workflows using PowerCenter designer

and Workflow manager for loading data into Datawarehouse from

staging.

. Developed and maintained custom PL/SQL packages and procedures for

analysis of source system data and also for data validation

. Extensively involved in creating design documents for loading data

into Datawarehouse and worked with the data modeler to change/update

the Datawarehouse model when needed.

. Reporting based on Slowly Changing Dimension of Type 1 SCD and Type 2

SCD.

. Responsible for Performance Tuning at the Source & Target level,

Mapping Level and Session Level.

. Implemented various loads like Daily Loads, Weekly Loads, and

Quarterly Loads using Incremental Loading Strategy using the concepts

of Change Data Capture (CDC).

. Responsible for 24/7 Informatica production support.

Environment: Informatica Power Center 6.0 (ETL),SQL server 2005, Oracle

9i/8i, SQL, Windows NT/2000/95.

Cipla Health&Pharma, India

May 2005- Oct 2006

Software Engineer

Project: Enhancement, Maintenance and Support of Data-Warehouse

Cipla is a pharmaceutical company that manufactures world class generic

medicines at affordable prices. The MPMS (Medical Practice Management

System) project was aimed at building a data warehouse for tracking patient

records. Flat Files, Oracle tables, DB2 tables and Excel spreadsheets were

part of the source data, which came in on a daily, weekly and monthly

basis.

Responsibilities:

. Developed various mappings using Mapping Designer and worked with

Aggregator, Lookup, Filter, Router, Joiner, Source Qualifier,

Expression, Stored Procedure and Sequence Generator transformations.

. Implemented Slowly Changing Dimensions of type 1 & type 2 to store

history according to business requirements.

. Used Parameter files to pass mapping and session parameters to the

session.

. Tuned the Informatica mappings to reduce the session run time.

. Developed PL/SQL procedures to update the database and to perform

calculations.

. Worked with SQL*Loader to load data into the warehouse.

. Developed reports in Cognos Reportnet.

. Contributed to the design and development of Cognos framework model.

. Wrote UNIX shell scripts to work with flat files, to define parameter

files and to create pre and post session commands.

. Performed Unit testing and System testing of Informatica mappings.

. Involved in migrating the mappings and workflows from Development to

Testing and then to Production environments.

Environment: Oracle7i, SQL, PL/SQL, SQL*PLUS, Informatica PowerCenter 7,

Windows2000.

Contact this candidate