Sql Server Data

Location:

Middleton, WI, 53562

Posted:

March 09, 2010

Contact this candidate

Resume:

RAVI CHINTALA

abnukh@r.postjobfree.com Cell: 317-***-****

SUMMARY

• Around 10 (Ten) years of experience in Programming, Database design and

development which includes around six years in Data Warehousing using Informatica

Power Center/Power Mart for Extraction, Transformation, Loading (ETL) of data from

multiple database sources in Windows/UNIX environment for medium to large

enterprise data warehouses.

• Strong experience in coding with SQL,PL/SQL (including stored procedures,

functions, packages) on Oracle 10g/9i/8i/7.x, MS SQL Server, Teradata,

DB2UDB.

• Highly experienced in optimization of Informatica components for Performance

tuning at Target, Source, Mapping and Session levels.

• Strong knowledge of OLTP system study, analysis and development of Database

Schemas like Star schema, Snowflake schema and 3rd Normal Form schema used

in relational, dimensional and multidimensional modeling.

• Excellent analytical, problem solving skills and a team worker with a strong customer

focus.

• Experienced in supporting large databases, troubleshooting the problems.

• Hands on experience in Data modeling using Erwin tool.

• Strong experience in UNIX environment and monitoring jobs on Autosys or Tivoli.

• Experienced in gathering requirements and converting to use cases and object models.

• Involved in different phases such as Analysis, Design, Coding, Testing, Installation,

User Training during the projects.

• Diverse background with fast learning skills and creative analytical skills.

• Good communication skills, interpersonal skills, team coordination and well-versed with

software development processes.

EDUCATION:

• Bachelor of Technology, Nagarjuna University, India

• PGDCA, Hyderabad, India.

TECHNICAL SKILLS:

RDBMS: Oracle 10g/9i/8i/8.0, MS SQL Server, DB2UDB

Tera data, COBOL, MS Access 97/2000.

ETL Tools: InformaticaPowerCenter/PowerMart 6.2/7.1.x/8.1.1.

Data Modeling: Star-Schema Modeling, Snowflake Schema Modeling,Fact,

Dimension, Summary tables and ERwin 4.0.

Business Intelligence: Business Objects Designer 6.5, Cognos Report Net.

Programming Languages: SQL, PL/SQL, Visual Basic 6.0, UNIX Shell scripting, JCL, XML,

ASP.

Operating Systems: WINDOWS 2000/2003/NT/XP,UNIX, LINUX.

Others: Oracle SQL Loader, Toad, WinSQL, Rapid SQL, HP Quality

Center, Autosys, Tivoli, V.S.S, Oracle Forms & Reports, Oracle

Financials modules GL,AR,AP and PO.

Training Abinitio, SAS,Oracle Financials 11i

Certification: OCP -Oracle Certified Professional (2006)

PROFESSIONAL EXPERIENCE

DEAN HEALTH PLAN April 2008 – Present

Madison, WI

Senior Informatica Consultant

Dean Health Plan is one of the leading Health Care companies in the State of Wisconsin.

The business of DHP runs in several domains such as Contracts, Pricing Management,

Claims, Memberships, Diagnosis Related Groups, Ambulatory Provider Groups etc.

The current project ARMOR is planned to handle the fast growing company functionality and

data in particular. Data from main frames and other older systems will be handled by new

systems after the go live of the project. All the main processes are being integrated with

METAVANCE (application bought from EDS).

This project has different phases like Conversion, Integration etc. In the conversion phase,

the existing data from main frames and other legacy systems are being cleaned and

scrubbed to suit the new functionality. The integration links the converted data to

warehouse and handles the transition for the day to day operation of the data warehouse.

Star schema model of database design is adopted for this project. The sources for the

database are Flat files, Oracle and DB2(main frames). The warehouse is being

developed in Oracle. Informatica 8.1.1 is being used for the ETL operations.

Responsibilities:

• Participated in number of JAD sessions with the business users for analysis and

carving out the requirements.

• Informatica mappings are being developed using various transformations such as

Source Qualifier Transformation, Normalizer transformation, Expression

Transformation, Aggregator transformation, Look-up transformation, Update

Strategy transformation, Filter transformation, Transaction Control transformation,

Router transformation, Joiner transformation etc.

• Creating sessions and work-flows for the Informatica mappings.

• Creating mappings with different look-ups like Connected look-up, Unconnected look-

up and Dynamic look-up.

• Creating various mapplets as part of mapping design.

• Wring Oracle stored procedures and functions for calling during the execution of

Informatica mappings.

• Creating different command tasks in Informatica for the functionality of renaming,

merging, archiving, validating etc.

• Involved in designing the ETL testing strategies for functional, integration and

system testing for Data warehouse implementation.

• Unit test cases are being developed and uploaded to HP Quality Center.

Environment: Informatica 8.1.1, IBM Mainframes, UNIX, Windows XP, Oracle

10g/9i, SQL, PL/SQL, SQL * Loader, SIEBEL, DB2, SQL Server, Rapid SQL, VSS, HP

Quality Center.

PRINCIPAL, Des Moines Sep 2007 - March 2008

Informatica Consultant

The Principal (The Principal Financial Group) is a leading global financial company offering

businesses, individuals and institutional clients a wide range of financial products and

services. The range of products and services include retirement solutions, life and health

insurance, wellness programs, and investment and banking products.

The project was for creating a data warehouse for the business intelligence for Retirement

Investor Services (RIS) division. The business needs reports such as Retirement Plan

Investment Review (RPIR) report, Retirement Plan Summary (RPS) report etc.

A data ware house was designed with contract and member level tables with a star schema

model for addressing the reporting needs of the RIS wing of The Principal. The ware house

was developed in DB2. The sources were flat files and DB2 tables.

Responsibilities:

• Informatica objects are developed and scheduled to run at required intervals of time

to populate data into different tables.

• Various transformations of Informatica such as Source Qualifier Transformation,

Expression Transformation, Look-up transformation, Update Strategy transformation,

Filter transformation, Router transformation, Joiner transformation etc., were used

for developing Informatica mappings.

• Created mappings with different look-ups like Connected look-up, Unconnected look-

up, Dynamic look-up with different caches such as persistent cache etc.

• Created various mapplets as part of mapping design.

• Workflows were created with tasks such as sessions, command task, decision task,

link task etc. for running the Informatica mappings.

• UNIX shell scripts were used for merging files, data validation, load validation and

archiving of files by using the command tasks of Informatica.

• Mapping parameters, mapping variables and session parameters used as needed by

the business logic.

Environment: Informatica 7.1.4, IBM Mainframes, DB2UDB, flat files, UNIX,

Windows 2000, Oracle, SQL Server, WinSQL, Mercury Quality Center.

BMW, New Jersey Jan 2007 – Aug 2007

Senior Informatica Developer

Bavarian Motor Works, is the world’s top manufacturer of automobiles and motorcycles.

BMW is the parent company of the MINI and Rolls-Royce car brands, and was the owner of

Rover.

The company manufactures automobiles in series of models like BMW 5 series, BMW 6

series, X3 series, X5 series etc. The company also produces wide variety of Motorcycles.

The company’s operations like manufacturing, sales etc., are spread over across the world.

The company has dealer network all over the U.S.A. Millions of transactions occur at the

dealer locations with regard to the automobiles and Parts of the automobiles.

The current project by name ATLAS (Advanced parTs Logistics in After Sales) is a

huge project for the redesign and optimization of the material and order management

processes in the parts business of BMW. Supply Chain Planning(SCP), Supply Chain

Execution(SCE) and Warehouse Management (WM) are the modules of this project.

These modules comprise of features like Demand Planning, Material Planning etc. The

objective of Warehouse Management is to modify and standardize the warehouse

functionality on wholesale level.

Star schema model of database design is adopted for this project. The sources for the

database are Flatfiles, Oracle and DB2. The warehouse is developed in Oracle.

Informatica 7.1.4 is used for the ETL operations.

Responsibilities:

• Involved in Analysis phase of the business requirement and design of the Informatica

mappings.

• Using various transformations of Informatica, such as Source Qualifier

Transformation, Expression Transformation, Look-up transformation, Update

Strategy transformation, Filter transformation, Router transformation, Joiner

transformation etc for developing Informatica mappings.

• Created sessions and work-flows for the Informatica mappings.

• Configured sessions for different situations including incremental aggregation, pipe-

line partitioning etc.

• Created mappings with different look-ups like Connected look-up, Unconnected look-

up, Dynamic look-up with different caches such as persistent cache etc.

• Creating various mapplets as part of mapping design.

• Wrote Oracle stored procedures and functions for calling during the execution of

Informatica mapping or as Pre or Post session execution.

• Created UNIX shell scripts and called during execution of the Informatica sessions

and work-flows.

• Involved in designing the ETL testing strategies for functional, integration and

system testing for Data warehouse implementation.

Environment: Informatica 7.1.4/8.1.1, IBM Mainframes, UNIX, Windows XP,

Oracle 10g/9i, SQL, PL/SQL, SQL * Loader, SIEBEL, DB2, SQL Server, SAP, Toad,

HP Quality Center.

Johnson & Johnson, New Jersey Mar 2006 – Dec 2006

ETL Applications Consultant

Johnson & Johnson is one of the top pharmaceutical companies of the world. The day to

day operations of the company take place in several places across the globe and data from

all over the world is maintained in Oracle databases with different front ends especially

from SIEBEL. Informatica 7.1.1/8.1.0 versions are used to carry out the data

warehousing operations. On a daily basis, different issues both from Informatica and

Oracle crop up with different severity levels. Issues were fixed based on the severity and

priority of the issue.

A datawarehouse called CTMS-DWH was developed using using Informatica 8.1.0

version. CTMS is a Clinical Trial Management System for Johnson & Johnson to track

and support the Execution of Clinical Trials. This Clinical Trials system is built for users at

different Pharmaceutical sector Companies such as Alza, Centocor, the Janssen-Cilag (J-C)

companies, Janssen Ortho-McNeil, Pharmaceutical Research & Development (J&JPRD),

McNeil, Ortho Biotech (OBI), Scios, Tibotec Therapeutics, Therakos, and Tibotec.

This system provides a comprehensive view on important issues such as timelines,

performance and critical trial related events. It is also used for supporting all monitoring

visit activities and the collection of associated management data during site visits by field

monitors.

The data warehouse is designed to capture data from the different systems. The warehouse

is designed in Oracle. Informatica 8.1.0 is used as the ETL Tool. The reporting phase of

the project uses Cognos Report Net.

Responsibilities:

• Involved in gathering requirements from the end-users of CTT REPORTS and worked

closely with Business analysts during design phase.

• Developed CTMS Datawarehouse for the Johnson & Johnson Pharma Research

Development wing using Informatica 8.1.0 version.

• Extensively used various transformations like the expression, joiner, look-up, update

strategy etc., for the creation of different work-flows.

• Created different mapplets based on the requirement.

• Extensively used mapping variables and parameter files in the design of the project.

• Wrote various stored procedures in Oracle back-end and called them using the Stored

Procedure transformation.

• Extensively used the pre and post sql query options in the design.

• Created worklets for handling batch processing of sessions.

• Used the email tasks for notifications of success or failure of tasks.

• Utilized the scheduler tool of Informatica for scheduling work-flows.

• Attended meetings with business users to resolve issues and to include improvements to

the application for the forth-coming releases.

• The production issues of data being extracted by Informatica from different sources

like Oracle, Flat-files, etc. loaded on incremental basis to different target databases

have issues to be resolved immediately. These were handled effectively.

• Involved in resolving Post-production ticket issues and with change management

team for moving developed objects from development environment to production

environment.

• Tuned performance of the database queries using explain plan, SQL Trace and

Tkprof for tickets arising due to performance issues.

• Worked closely with end users and System analysts during Production phase on

customization and enhancement tickets.

Environment: Windows 2000, Oracle 10g/9i, SQL, PL/SQL, Informatica 8.1/7.1.1,

SIEBEL, Toad and UNIX.

Liberty Mutual, Indianapolis Jan 2005 – Feb 2006

Informatica Developer

Liberty Mutual is an insurance company dealing with various insurance needs. The ETL tool

Informatica was used for developing the datawarehouse for this company. Informatica 7.1.1

was used to extract data from various sources like Oracle, MS SQL Server, DB2, Flat files to

load dimension and fact tables.The warehouse was built on Oracle database for storing

historical information and generation of business reports for the management. Huge

volumes of data from all over the country are systematically organized for studying

performance trends of different products. The later phases of the data warehouse involved

creation of reports using Business Objects version 6.5. Star schema data modeling was

adopted for the design and development of the data warehouse.

Responsibilities:

• Extensively used Informatica Tools like Designer, Workflow Manger and Workflow

Monitor.

• Extracted source data from Oracle, MS SQL Server, IBM DB2, Flat files and XML

using Informatica, and loaded into Oracle or flat file targets.

• Created reusable transformations and mapplets to use in multiple mappings.

• Extensively used Informatica to load OLTP data into the Staging Area and Data

Warehouse using mappings and transformations such as Aggregator, Lookup,

Expression, Joiner, Source Qualifier, Filter and Sequence Generator.

• Was involved in error checking and testing of ETL procedures and programs using

Informatica session log.

• Used Workflow Manager/Monitor for creating and monitoring workflows and worklets.

• Tuned performance of Informatica sessions for large data files by implementing

pipeline partitioning and increasing block size, data cache size, target based

commit interval.

• Created Bitmap indexes to improve performance. Performed SQL query tuning and

created materialized views in coordination with the DBA..

• Used PL/SQL programming to develop stored procedures and functions.

• Involved in fixing invalid Mappings, Unit testing and Integration Testing of

Informatica Sessions, Batches and the Target Data.

• Developed Technical and User Documentation.

Environment: Informatica PowerCenter 7.1.1, Business Objects 6.5, TOAD, Oracle

9i, DB2UDB, MS SQL 2000, Flat files, ERwin 4.0, UNIX Shell Scripting, SQL,

PL/SQL, SQL * Loader.

National Premium Budget, Michigan Jul 2004 – Dec 2004

ETL Developer

National Premium Insurance & Re-Insurance company primarily caters to business insurance

needs with regard to Marine, Industrial & Commercial Insurance. Datawarehouse for this

company was developed using Informatica PowerCenter to transform the data that is

extracted from various sources like Oracle, MS SQL Server, Flat files and Excel files to load

dimension and fact tables. Oracle was used as the target database. This warehouse is to

generate reports and analyze the sales of various products. The product data is categorized

depending on the product group and product family. The later phases of the Data

Warehouse life cycle use Business Objects OLAP tool for reporting purposes. Individual data

marts were developed for all kinds of applications and user bases.

Responsibilities:

• As per the required business logic, developed various mappings and sessions using

Informatica Tools like Designer and Workflow Manager.

• Data was extracted from differenct sources such as from Oracle, DB2UDB, Flat files

and XML using Informatica, and loaded into Oracle or flat file targets.

• The various transformations of Informatica such as Aggregator, Filter, Lookup,

Expression, Joiner, Source Qualifier and Sequence Generator were used

extensively.

• Extensively used Informatica session logs for identifying the errors while testing ETL

mappings.

• Functions and procedures were developed in the back end database and were later

called from Informatica.

• For repeated business logic, mapplets and reusable transformations were created and

adopted. Used Workflow Manager/Monitor for creating and monitoring workflows and

worklets.

• Fixed invalid mappings, performed Unit testing and Integration Testing of

Informatica Sessions.

• Involved in developing Technical and User Documentation.

Environment: Informatica PowerCenter (7.1), Cognos Impromptu 7.0, TOAD,

Oracle 8i/9i, DB2UDB, MS SQL Server 2000, ERwin 4.0, UNIX, SQL, PL/SQL.

DELTA AIRLINES, Atlanta Jan 2004 – Jun 2004

Informatica Developer

The project was to design, develop and maintain a data warehouse to understand the client

needs, undertake trend analysis, provide better customer service and assess current and

future profitability, cross-sell products to increase revenue generation from the most

profitable customers. Used Informatica Powercentre as an ETL tool to transform and load

the data to the Enterprise Datawarehouse. Base tables in the data warehouse were created

using Oracle 8i. Source analyzer, Transformation developer and Mapping designer have

been used to build data for the metadata, which was then stored in the repository.

Transformation language is used for writing filter conditions and expressions.

Responsibilities:

• Studying the existing source databases and interacting with decision makers for

analyzing their business plans.

• Analyzed the sources, targets, transformed the data, mapped the data, and loaded the

data into the targets using Informatica.

• Developed mappings using various transformations like update strategy, lookup,

stored procedure, router, filter, sequence generator, joiner, aggregate

transformation and expression.

• Debugged invalid mappings using break points, tested stored procedures, functions,

Informatica sessions, batches and the target data.

• Wrote stored procedures in PL/SQL and UNIX Shell Scripts for automated execution of

jobs.

• Identified performance bottlenecks and resolved those issues by query optimization,

dropping Indexes and Constraints, bulk loading.

• Involved in developing ETL routines.

• Created reusable transformations and mapplets to use in multiple mappings

Environment: Informatica Power Center 5.1/6.2, Business Objects 5.0, Oracle 8i,

SQL Server 2000, SQL/PLSQL, UDB, UNIX Shell Programming, Erwin 4.0 and

Windows 2000.

Aventis Pharmaceuticals, NJ May 2003 – Dec 2003

ETL Data Warehouse Developer

Successfully developed and maintained number of ETL maps to Extract, Transform and Load

data from various sources like Flat files, Relational data and XML files into the Enterprise

Data warehouse called Pharma Data Warehouse Management system. This includes creating

and running scripts, loading data into Dimension tables, Fact tables. Data warehouse

collects full life cycle data for Avantis Pharmaceutical’s products right from the new product

introduction through its manufacturing, testing, shipping, and success and failure analysis.

Responsibilities:

• Developed mappings using various transformations like Update strategy, Lookup,

Stored procedure, Router, Filter, Sequence generator, Joiner, Aggregate

transformation and Expression.

• Involved in fixing invalid mappings, testing of Stored Procedures and Functions, Unit and

Integration Testing of Informatica Sessions, Batches and the Target Data and involved in

quality assurance of data and automation of processes.

• Involved in the development and testing of programs, Informatica mappings and update

processes.

• Involved in loading data to Warehouse tables, loading data to Staging area using SQL

Loader.

Environment: Informatica Power Center, Cognos Impromptu 6.0, Oracle 8.0, DB2,

MS-SQL Server (7.0), TOAD, SQL loader, SQL, PL/SQL, Erwin and Windows NT.

ICICI Lombard General Insurance Company Jan 2002 – Mar 2003

Hyderabad, India

Informatica Developer

ICICI Lombard General Insurance Company is an insurance organization serving businesses

and individuals with a wide range of insurance products and insurance-related services. It

has wide range of Insurance Products that include Auto Insurance, Personal Insurance,

Merchant Cover and Home Insurance. Claims Management System provides the technology

that assists users in administering claim practices in a timely and effective manner. This

application involves in designing and development of the Data Warehouse. The company’s

data comes from different operational sources like MS SQL, Oracle, and Sybase and is then

loaded into the Claims Data Warehouse built on Oracle using Informatica.

Responsibilities:

• Designed and developed mappings, mapplets and sessions from source to target

database using Informatica PowerCenter, and tuned mappings for improving

performance.

• Involved in debugging invalid mappings using break points, testing of stored

procedures and functions, testing of Informatica sessions and the target data.

• Extensively used transformations like router, aggregator, lookup, joiner, expression,

aggregator and sequence generator for extracting data.

• Used workflow manager for session management, database connection management

and scheduling of jobs to be run in the batch process.

• Worked with different sources such as Oracle, MS SQL Server and flat files.

Environment: Informatica PowerCenter 5.1, 6.0, Oracle 8.0, DB2, SQL Server (7.0),

TOAD, SQL, PL/SQL, Erwin and Windows NT.

Chennai Petroleum Corp. Ltd. Sep 2000 – Dec 2001

Chennai, India.

Oracle Developer

The project “MIS (Material Information System)” The System has been classified into

four modules based on the functions of materials department as Planning, Purchases, Stores

and Ledger Processing. It covers the entire function of materials department from the stage

of receiving indents from various departments of the refinery, stock verification, purchase

order processing, follow up and closure, automatic pricing and valuation of transactions and

stock updating. System has distinct queries covering the functionality of the complete

system, catering Managerial Information System.

Responsibilities:

• Data Base designing.

• Coding PL/SQL functions, procedure, triggers and alerts to be used in the project.

• Developing Reports.

• Coding review, Testing and debugging of the codes.

Environment: Oracle 8i, PL/SQL, Forms & Reports.

Lotus Agencies (T.V.S. Dealer) Jun 1998 – Jul 2000

Hyerabad, India.

Oracle Developer

The project “Financial A/c, Payable & Receivable Module” is for maintaining the

complete chart of accounts with Multiple Journals in different transactions. It maintains the

Receive Entries from Accounts Payable & Accounts Receivables. It prints Trial balance,

Balance sheet, Cashbook, Bankbook, Fund, and Cash flow statements. It contains all the

tools to monitor incoming & outgoing cash flow, analyse the vendor histories and identify

the payment dates & requirements.

Responsibilities:

• Data Base designing

• Coding PL/SQL functions, procedure, triggers and alerts to be used in the project