Manager Data

KALYAN

KUMAR

Senior INFORMATICA/ETL Architect / Developer

PROFESSIONAL SUMMARY

o A Senior INFORMATICA/ETL Architect/Developer with 8+ years of overall

experience in IT Industry with emphasis on Data Warehousing tools using

industry accepted methodologies and procedures.

o Extensively worked on ETL and SQL for over 6 years using Informatica.

o Technical expertise in ETL methodologies, Informatica 6.x/7.x/8.x/9 -

Power Center, Client tools - Mapping Designer, Mapplet Designer,

Transformations Developer, Workflow Manager/Monitor and Server tools -

Informatica Server Manager, Repository Server Manager, and Power

Exchange.

o Expertise in Data Warehousing, Data Migration, Data Modeling, and Data

Cleansing.

o Experience in Installing Informatica and Configuration in Windows and

UNIX environment.

o Directly responsible for the Extraction, Transformation & Loading of data

from multiple sources into Data Warehouse. Complete knowledge of data

ware house methodologies (Ralph Kimball, Inmon), ODS, EDW and Metadata

repository.

o Expertise in Extraction, Transformation & Loading of data using

heterogeneous sources and targets.

o Experience in Performance Tuning of Informatica (sources, mappings,

targets and sessions) and tuning the SQL Queries.

o Expertise in Oracle (SQL/PLSQL) performance tuning using optimization

techniques.

o Expertise in Source to Target mapping in Enterprise and Corporate Data

Warehouse environments.

o Experience in using the Informatica command line utilities like pmcmd to

control workflows in non-windows environments.

o Extensive database experience and highly skilled in SQL in Oracle, MS SQL

Server, DB2, Teradata, Sybase, Mainframe Files, Flat Files, MS Access.

o Skilled in SQL, PL/SQL, SQL*Plus, TOAD, SQL Assistant.

o Skilled in UNIX, Shell programming on UNIX systems using Vi Editor,

Windows scripting and in Informatica Sessions.

o Expertise in delivering End-User report generating tools Cognos,

Reporting Services.

o Experienced in using Business Objects 5.x/6.x to build user defined

queries and reports to enable drill-down and slice and dice analysis on

multiple databases. Thorough knowledge of Software Development Life Cycle

including Planning, Analysis, Design, Implementation, and Maintenance.

o Expertise in configuring and manipulating Dimensional Data Modeling, Star

Schema Modeling, Snow-Flake Modeling, Fact and Dimensions Tables,

Physical and Logical Data Modeling.

o Outstanding communication and Interpersonal skills, ability to learn

quickly, good analytical reasoning and high compliance to new

technologies and tools.

o Self-motivated and able to work independently and as a member of a team.

o Excellent team work spirit and capable of learning new technologies and

concepts.

TECHNICAL SKILLS

Databases: Oracle 7.x/8.x/9i/10g/11i, SQL Server

2008/2005/2003/2000, Essbase, SQL Server DTS (Data

Transformation Services), SSIS, Teradata V2R4 V2R5,

DB2 UDB 7.2 SQL Assistant, MySQL 5.0/4.1, MS-Access.

Editors (SQL Navigator, Toad)

ETL Tools: Informatica (PowerCenter 9, 8.x, 7.x, 6.x / 5.x,

PowerMart 6.2 / 6.0 / 5.1,

Power Channel 6.2, Power Exchange 5)

ERWIN, ER/Studio

C, Shell Scripting (K-Shell, C-Shell), SQL,PL/SQL,

HTML, JAVA Script, J2EE, Visual basic, ASP,JSP

Data Modeling Tools:

Programming Skills:

Methodologies: Data Modeling - Logical, Physical

Dimensional Modeling - Star / Snowflake

OLAP Tools: Cognos, Reporting services 2005, Developer 2000(Forms

5.0, Reports 6i), Crystal Reports 10, Crystal Reports

2008

Operating Systems: UNIX (Sun-Solaris, HP/UX), Windows 95/98/00/NT/XP

PROFESSIONAL EXPERIENCE

TD Ameritrade, Baltimore, MD

Apr' 2009 - Till date

Role: Senior Informatica Developer

Company:

TD Ameritrade is an online broker with over 6 million U.S. customers, and

many more internationally, that has grown rapidly through acquisition to

become the 746th-largest US firm in 2008. TD Ameritrade Holding Corporation

(NASDAQ: AMTD) is the owner of TD Ameritrade Inc. Services offered include

common and preferred stocks, futures, ETFs, option trades, mutual funds,

fixed income, margin lending, and cash management services.

Project:

The project at TD was to streamline the entire process of Insurance and to

keep track of various financials on a daily, weekly, monthly basis. This

process involved designing, developing, and maintaining claims Data Mart -

CDM, Marketing Data Mart-MDM, Operations Data Mart- OPM, Financial Data

mart-FDM & Underwriting Data mart-UDM. The data warehouse was to provide

the managers of HCI, the Slice and Dice capability to analyze the data.

Responsibilities:

> Interacted with the Business Users to analyze the business requirements

and transform the business requirements into the technical requirements.

> Prepared technical specifications for the development of Informatica

(ETL) mappings to load data into various target tables and defining ETL

standards.

> Created logical and physical data models using Erwin.

> Created Entity Relationship (ER) diagrams based on requirements.

> Preparing ETL mapping documents for every mapping and Data Migration

document for smooth transfer of project from Development to Testing

environment and then to production environment.

> Worked with various Informatica client tools like Source Analyzer, Target

designer, Mapping designer, Mapplet Designer, Transformation Developer,

Informatica Repository Manager, Informatica data explorer, Workflow

Manager and Workflow Monitor extensively to create, execute and monitor a

mapping's run process.

> Created mappings using different transformations like Source Qualifier,

Joiner, Aggregator, Expression, Filter, Router, Lookup, Update Strategy,

and Sequence Generator etc.

> Developed Mapplets and Worklets for reusability.

> Involved in upgrading Informatica 8.6 to Informatica 9 and worked on

Informatica 9.

> Developed Fast Export scripts to send data files to other teams.

> Developed mappings which load data mart data into Teradata.

> Designing and creation of complex mappings using SCD type II involving

transformations such as expression, joiner, aggregator, lookup, update

strategy, and filter.

> Implemented Performance tuning in Mappings and Sessions by identifying

the bottlenecks and Implemented effective transformation Logic.

> Maintain Development, Test and Production mapping migration Using

Repository Manager. Involved in enhancements and maintenance activities

of the data warehouse including performance tuning.

> Experience in analyzing the reporting requirement.

> Extensively used Stored Procedures, Functions and Packages using PL/SQL

scripting for creating Connected and Unconnected Stored Procedure

Transformations.

> Created AutoSys schedules/jobs for automation of ETL load process.

> Involved in Unit Testing, User Acceptance Testing to check whether the

data loads into target are accurate, which was extracted from different

source systems according to the user requirements.

> Preparing and using Test data/cases to verify accuracy and completeness

of ETL process.

> Actively involved in the Production support and also transferred

knowledge to the other team members.

Environment: Informatica Power Center 9, 8.6.0 HF 11, Power Exchange 8.6.0,

Oracle 10g, DB2, MS SQL Server 2005, TOAD, Erwin, SQL, PL/SQL, XML, SunOS

5.9

Bank of America, Agoura Hills, CA

Jan' 2008 - Mar' 2009

Role: Senior Informatica Developer

Company:

On January 11, 2008, Bank of America announced they would buy California

based Countrywide Financial for $4.1 billion. Countrywide provided

mortgage servicing for nine million mortgages valued at US$1.4 trillion as

of December 31, 2007. This purchase made Bank of America Corporation the

leading mortgage originator and servicer in the U.S, controlling 20-25% of

the home loan market. Countrywide Financial has changed its name to Bank of

America Home Loans.

Project:

The scope of this project was to meet growing information demands on

various modifications programs such as

MHA, NON-MHA to treasury and other regulatory agencies for Countrywide's

mortgage department. The project was planned to be executed in multiple

phases over the course of several months. For the first phase of this

project, the scope was to aggregate all the data from primary modifications

data sources across the enterprise and hosting the data on a central data

platform. All of this data is planned to be hosted on Phoenix Central

Mortgage data warehouse platform

Responsibilities:

> Collaborated with Business analysts and the DBA for requirements

gathering, business analysis and designing of the data marts.

> Worked on dimensional modeling to Design and develop Star Schemas,

identifying Fact and Dimension Tables for providing a unified view to

ensure consistent decision making.

> Involved in Data Modeling Using Erwin.

> Worked with heterogeneous sources from various channels like Oracle, SQL

Server, flat files.

> Worked on Informatica tool Source Analyzer, Warehouse Designer, Mapping

Designer, Workflow Manager, Workflow Monitor, and Repository Manager.

> Extensively used Transformations like, Aggregator, Router, Joiner,

Expression, Lookup, Update Strategy, and Sequence Generator.

> Setting up Batches and sessions to schedule the loads at required

frequency using Power Center Workflow manager, PMCMD and also using

scheduling tools. Generated completion messages and status reports using

Workflow manager.

> Extensively worked in the performance tuning of ETL mappings and

sessions.

> Wrote Shell Scripts for Informatica Pre-session, Post session Scripts.

> Written PL/SQL stored Procedures and Functions for Stored Procedure

Transformation.

> Analyzed Session Log files in case the session failed to resolve errors

in mapping or session configurations.

> Performed Unit testing and verified the data.

> Supported Business Objects end-user in areas including software

(Designer, Reporting, Web Intelligence, and Publisher) and creating

reports.

Environment: Informatica Power Center 8.1/7.1.4, Flat files, XML,

Oracle8i, UNIX, Windows NT, and PL/SQL.

Irwin Financial Corporation, Columbus, IN

Aug' 2006 - Dec' 2007

Role: ETL Developer

Description:

Irwin Financial Corporation is a bank holding company, which focuses

primarily on the extension of credit to small businesses and consumers, as

well as providing the ongoing servicing of those customer accounts.

Project:

This project included creation of Customer Financial Services Data Mart,

Extraction & Transformation of data from various systems such as MS-Access,

SQL Server, Oracle, and Flat Files and loading using Informatica.

Responsibilities:

> Configured Informatica Repository Manager to manage all the repositories

of various applications. Created users, user groups, security access

controls.

> Planned and designed the flow process of the data stored in the

repository, in the form of metadata.

> Designed and constructed the Star Schema. Wrote procedures, functions in

PL/SQL.

> Designed and created mappings using Mapping Designer.

> Worked extensively on performance tuning by making changes to SQL in

Source Analyzer.

> Used various Informatica transformations like expressions, filters,

joiners, aggregators, routers and lookups to load better and consistent

data into the targets.

> Maintain Development, Test and Production mapping migration Using

Repository Manager. Involved in enhancements and maintenance activities

of the data warehouse including performance tuning.

> Used Mapping Designer to join tables from various databases like Oracle,

SQL Server, and DB2.

> Designed and developed catalogs by creating links between the Fact table

and the Dimension table to form a Star Schema.

> Used TOAD to FTP file moving processes to and from source systems.

> Created scheduling of jobs in Workflow Manager to trigger tasks on a

daily, weekly and monthly basis.

> Wrote shell scripts to join or concat target files, to delete or remove

one or more such files if not needed after joining, and etc.

> Monitored the production loads and fixed them, when necessary.

> Involved in the Performance Tuning of Informatica servers and other

legacy databases.

> Worked with the reporting team as well to create detailed reports.

Environment: Informatica 7.1, Oracle 9i, PL/SQL, SQL Server, DB2, MS Excel,

UNIX, TOAD.

Integrity Medical Systems, Fort Myers, FL

Apr' 2005 - Jul' 2006

Role: Informatica Developer

Description:

Integrity Medical Systems are Buyers and Sellers of refurbished and used

medical equipment since 1989.

Project:

This project includes creation of a Data Warehouse by utilizing an Oracle

Database. It included extraction and transformation of data from various

sources such as Flat Files, MS SQL Server, VSAM and DB2/UDB and loading

them into star schema with fact & dimension tables.

Responsibilities:

> Configured Informatica Repository Manager to create user groups and user

profiles.

> Granted administrator rights and assigned security & privileges to user

groups.

> Designed & executed SQL, PL/SQL, stored procedures, triggers according to

need of database environment.

> Deployed Informatica tools - Power Center, Workflow Manager and Workflow

Monitor.

> Utilized the Mapping Designer tools - Source Analyzer and Warehouse

Designer to create and/or import the source and target database schemas.

> Used the Mapping Designer to map the sources to the target and to create

the various transformations.

> Used Source Analyzer to work on SQL override for performance tuning.

> Worked extensively on different types of transformations like expression,

filter, aggregator, rank, lookup, stored procedure, sequence generator,

and joiner etc.

> Used Workflow Manager to create workflows and sessions to run mappings

and load data into tables, warehouses or just folders in the form of

reusable files.

> Extensively used Informatica to load data from Oracle and Flat files to

Oracle.

> Worked on various kinds of data sources while making them extremely

consistent and secure for the enterprise data warehouse.

> Configured & managed Informatica Server Manager to create database

connections, sessions, and batches to run the mappings.

> Created and executed UNIX shell scripts and PL/SQL procedures, pre-

session and post-session scripts to ensure timely, accurate processing

and ensure balancing of job runs.

> Used Erwin to visualize and organize data for future managing and

programming of the same.

> Supported users with word processing files, spreadsheets, and

presentation software.

Environment: Informatica 7.1, Oracle 10g, Erwin, PL/SQL, SQL* Loader, SQL

Server, UNIX, Windows NT, MS Excel.

IFLEX Solutions (Oracle Financial Services Ltd), Bangalore, India

Feb' 2003 - Mar' 2005

Role: Oracle / ETL Developer

Description:

Oracle Financial Services Software Limited (formerly called i-flex

Solutions Limited) is an IT solution provider to the banking industry.

Oracle Financial Services is majority owned by Oracle Corporation. It

claims to have more than 900 customers in over 135 countries.

Project:

The project was designed and developed for maintenance of all IFLEX

products. The database facilitated maintains data related to the entire

FLEXCUBE warehouse. The project involved Oracle Development and ETL Work.

Responsibilities:

> Based on the requirements created Functional design documents and

Technical design specification documents for ETL.

> Extracted data from various Sources like Oracle, SQL Server and Flat

Files as per the requirements.

> Designed and developed mappings and Mapplets using Informatica Source

analyzer, Warehouse designer, Transformation designer and Mapplet

designer.

> Implemented "Slowly Changing Dimension Type 2" to represent historical

data correctly.

> Created tables, views, indexes, sequences and constraints.

> Developed stored procedures, functions using PL/SQL according to specific

business logic.

> Transferred data using SQL*Loader to database.

> Involved in testing of Stored Procedures and Functions.

> Designed and developed table structures, stored procedures, and functions

to implement business rules.

> Involved in design and development of data validation, load process and

error control routines.

> Analyzed the database for performance issues and conducted detailed

tuning activities for improvement.

> Coordinated database requirements with Oracle programmers and wrote

report SQL's for sales data.

Environment: Informatica Power Center 5.1/6.2, Oracle 8, SQL, PL/SQL, SQL

*Loader, Windows 2000

EDUCATION

. Bachelors of Technology - Jawaharlal Nehru Technological University

Contact this candidate