Manager Data

BIREN PATEL

Senior Informatica Developer

**********@*****.***

**** ********** **., ******, **- 08817

Ph: (201)

***-****

SUMMARY

. About 7+ years of experience in Information Technology with a strong

background in Database development and Data warehousing and nearly

Five years of experience in ETL process using Informatica Power Center

8.x/7.x/6.x/5.x.

. Good knowledge of Data warehouse concepts and principles

(Kimball/Inman) - Star Schema, Snowflake, SCD, Surrogate keys,

Normalization/De normalization.

. Experience in integration of various data sources with Multiple

Relational Databases like Oracle, SQL Server, Sybase, DB2, Teradata,

Mainframe Files, COBOL files, XML Files and Worked on integrating data

from flat files like fixed width and delimited.

. Have extensively worked in developing ETL for supporting Data

Extraction, transformations and loading using Informatica Power

Center.

. Well acquainted with Informatica Designer Components - Source

Analyzer, Warehouse Designer, Transformation Developer, Mapplet and

Mapping Designer.

. Worked extensively with complex mappings using different

transformations like Source Qualifiers, Expressions, Filters, Joiners,

Routers, Union, Unconnected / Connected Lookups and Aggregators.

. Strong Experience in developing Sessions/tasks, Worklets, Workflows

using Workflow Manager Tools - Task Developer, Workflow & Worklet

Designer.

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

techniques.

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

. Experience with slowly changing dimension methodology and slowly

growing targets methodologies.

. Experience in using the Informatica command line utilities like pmcmd

to execute workflows in non-windows environments.

. Extensively used Informatica Repository Manager and Workflow Monitor.

. Experience in debugging mappings. Identified bugs in existing mappings

by analyzing the data flow and evaluating transformations.

. Hands on experience in Performance Tuning of sources, targets,

transformations and sessions.

. Good experience in documenting the ETL process flow for better

maintenance and analyzing the process flow.

. Worked with Stored Procedures, Triggers, Cursors, Indexes and

Functions.

. Experience with OLAP, ROLAP and other BI tools.

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

Reporting Services

. Experience in UNIX shell scripting, CRON, FTP and file management in

various UNIX environments.

. Excellent communication skills.

. Good team player with strong analytical skills.

EDUCATION/CERTIFICATIONS:

. Systems Engineering Certified

. Project Management Certified

. Oracle 9i Fundamentals II Certified Professional (OCP) (1Z0-032) -

Year 2009

. Oracle 9i PL/SQL Developer Certified Associate (OCA) (IZO-007) -

Year 2009

. Oracle 9i Fundamental I Certified Associate (IZO-031) - Year 2009

. Bachelor of Engineering, India

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:

PRUDENTIAL FINANCIAL, Paramus, New Jersey

May 2008 - Present

Senior Informatica Developer

Prudential Financial is a Fortune Global 500 and Fortune 500 company whose

subsidiaries provide insurance, investment management, and other financial

products and services to both retail and institutional customers throughout

the United States and in over 30 other countries. Principal products and

services provided include life insurance, annuities, mutual funds, pension-

and retirement-related investments, administration and asset management,

securities brokerage services, and commercial and residential real estate

in many states of the U.S. It provides these products and services to

individual and institutional customers through distribution networks in the

financial services industry

RESPONSIBILITIES:

. Involved in writing project documentation.

. Worked on Informatica tools, Source Analyzer, Warehouse Designer,

Mapping Designer, Workflow Manager and Workflow Monitor.

. Various kinds of transformations were used to implement simple and

complex business logic. The mappings involved extensive use of

transformations like Filter, Router, Expression, and Union.

. Used Lookup Transformation to access data from tables, which are not

the source for mapping.

. Build Session, Worklets/Workflow, Create/Modify Parameter File.

. Solely responsible for the daily loads and handling the reject data.

. Implemented Slowly Changing Dimensions - Type I & II in different

mappings as per the requirements.

. Changed some stored procedures in Oracle 9i as per the Business

Requirements.

. Used SQL tools like SQL Navigator to run SQL queries and validate the

data in warehouse.

. Used workflow manager and work flow monitor to run and monitor jobs.

. Extensively worked with flat files and excel sheet data sources. Wrote

batch scripts to automate flat file transportation local to

Informatica server.

. Worked on different tasks in Workflows like sessions, events raise,

event wait, decision, e-mail, command, Worklets, Assignment, Timer and

scheduling of the workflow.

. Analyzing the source data and deciding on appropriate extraction,

transformation and load strategy.

. Developed a number of complex ETL mappings, mapplet and reusable

transformations for daily data loads.

. Developed mapping parameters and variables to support SQL override.

. Created physical data models and procedures for DB2 and Oracle.

. Designed and developed table structures, stored procedures and

functions to implement business rules.

. Used Informatica Data Explorer and Informatica Data Quality for Data

Quality Management and Data profiling purposes.

. Used Informatica Data Quality to formulate data conversion and data

cleansing rules.

. Generated PL/SQL and Shell scripts for scheduling periodic load

processes.

. Generated Perl scripts for data validation, data collection and data

importing.

. Involved in ERWIN as data modeling tool.

. Used techniques like source query tuning, single pass reading and

caching lookups to achieve optimized performance.

. Used Teradata SQL Assistant to run the SQL queries and validate the

data.

. Associated with Production support team in various performances

related issues.

ENVIRONMENT: Informatica Power Center 8.6/8.1, Oracle9i/10g, IDQ,Perl

Scripting, Cognos, PL/SQL, SQL Navigator, DB2, ERWIN, Teradata SQL

Assistant, Teradata BTEQ.

SANOFI AVENTIS, Bridgewater, NJ

June 2006 - May 2008

Senior Informatica Developer

Sanofi-Aventis, headquartered in Paris, France, is a multinational

pharmaceutical company, the world's fourth-largest by prescription sales.

Sanofi-Aventis engages in the research and development, manufacturing and

marketing of pharmaceutical products for sale principally in the

prescription market, but the firm also develops over-the-counter

medication. Sanofi-Aventis covers 7 major therapeutic areas:

cardiovascular, central nervous system, diabetes, internal medicine,

oncology, thrombosis and vaccines (it is the world's largest producer of

the latter through its subsidiary Sanofi Pasteur). The company is a full

member of the European Federation of Pharmaceutical Industries and

Associations (EFPIA).

RESPONSIBILITIES:

. Gathered business scope and technical requirements.

. Extensively worked on Business Analysis and Data Analysis. Interacted

with the users to convert business logic into ETL specs.

. Extensively used Erwin for Logical / Physical data modeling and

Dimensional Data Modeling, and designed STAR Schemas.

. Designed and developed ETL mappings, enabling the extract, transport

and loading of the data into target tables.

. Worked with Power Center - Designer tool in developing mappings and

mapplets to extract and load the data from flat files, Oracle to

Oracle.

. Involved in the OLTP to OLAP transformation of data sources.

. Created different transformations for loading the data into targets

like Source Qualifier, Joiner transformation, Update Strategy, Lookup

transformation, Rank Transformations, Expressions, Aggregator,

Sequence Generator.

. Created reusable transformations and mapplets to use in multiple

mappings.

. Worked on repository to create and manage user profiles.

. Scheduled Sessions and Batches on the Informatica Server using

Informatica Server Manager.

. Implemented performance tuning logic on targets, sources, mappings,

sessions to provide maximum efficiency and performance.

. Written Data loading stored procedures, functions using PL/SQL from

Source systems into operational data storage.

. Used Master Data Management to align with key business process.

. Interacted with Management to identify key Dimensions and Measures for

business performance.

. Created views and altered some of the dimensional tables to satisfy

their reporting needs.

. Created reports like, Reports by Department, Reports by Supplier,

Reports by Period, Demographic reports and Comparative reports.

. Customized the dashboards and KPI's for better manageability of

overall performance.

. Created different types of reports, such as Master/Detail, Cross Tab

and Chart.

. Wrote UNIX Shell scripts for threshold check for the incoming files.

. Involved in testing the Data, ETL procedures, system testing and

integration testing.

ENVIRONMENT: Power Center 8.1/7.x, Informatica Power Connect, Work Flow

Monitor, Trillium, Erwin 4.1, Teradata V2R5, Oracle 10G, 9i, OLAP, Shell

Programming, Business Objects 6.0,, Autosys, Flat files, Toad 7.0, Unix

Shell Scripting.

AMERICAN EXPRESS, New York, NY

December 2005 - May 2006

Senior Informatica Developer

American Express Company (NYSE: AXP), sometimes known as AmEx, is a

diversified global financial services company headquartered in New York

City. Founded in 1850, it is one of the 30 components of the Dow Jones

Industrial Average. The company is best known for its credit card, charge

card, and traveler's cheque businesses. Amex cards account for

approximately 24% of the total dollar volume of credit card transactions in

the US, the highest of any card issuer

RESPONSIBILITIES:

. Used Informatica to extract, transform and load data from multiple

heterogeneous sources into Oracle

. Worked on Source Analyzer, Warehouse designer, Mapping & Designer and

Transformations, Informatica Repository Manager and Workflow Manager

and Monitor.

. Designed and developed an innovative, real-time transaction system

using Oracle 8i stored procedures, triggers.

. Developed Transformation Logic to cleanse the source data of

inconsistencies before loading the data into staging area which is the

source for stage loading.

. Created a generic email notification program in UNIX that sends the

emails to the production support team if there are Balancing

failures/Data validation in load process.

. Created Mappings, Mapplets and Transformations using the Designer and

developed Informatica sessions as per the business requirement

. Designed the change data capture logic in the Informatica mappings by

incremental extraction of source data.

. Used Workflow Manager to schedule and run batches, sessions and to

check session logs and other session related activities.

. Responsible for designing, coding and testing changes.

. Used the transformations like Source qualifier, Router, Filter,

Sequence Generators and Expression as per the Business requirement to

create the mappings.

. Loaded data into Teradata using Data Stage, FastLoad, BTEQ,

FastExport, MultiLoad.

. Analyzed business requirements, transformed data, and mapped source

data using the Teradata and SQL server 2000

. Tuned the Informatica mappings and the database SQL for better

performance of the application

. Developed and tested all the backend programs, Informatica mappings

and update processes.

. Developed and implemented Error Handling Strategies.

. 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.

. Involved in Unit testing, User Acceptance Testing to check whether the

data is loading into target, 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.

. Maintaining issue log of the issues during the UAT phase for future

reference.

. Migrating Folders, Mappings across repositories and folders.

. Actively involved in the production support and also transferred

knowledge to the other team members.

ENVIRONMENT: Informatica 7.1.2, Oracle 9i, DB 2,Windows NT 4,Sun

Solaris.8, Teradata Utilities (BTEQ, FastLoad, FastExport, MultiLoad

Update/Insert/Delete/Upsert), MS SQL server 2005

ERICSSION INC., Plano, TX

March 2004 - October 2005

Oracle / Informatica Developer

Ericsson is a leading Swedish-based provider of telecommunication and data

communication systems, and related services covering a range of

technologies, including handset technology platforms.

RESPONSIBILITIES:

. Involved in complete SDLC from Requirement gathering to test phase.

. Gathered Business requirements from the end users and reporting team

. Created design specifications based on the BRD and FIA for ETL

mappings.

. Created logical and physical design based on the requirements.

. Created Conformed Dimensions to for Generic Drug, Initiative, and

Prescriber Using Type -2 SCD.

. Extensively worked on transformations like Expression, Router, Dynamic

Lookup, Update Strategy, Sorter, Aggregator, Union, Source Qualifier

transformations.

. Created reusable mapplets and transformations to maintain row level

security across the clients

. Created mapping parameters and variables for delta loads.

. Provided technical Support in deployment of workflows, worklets and

sessions for weekly releases.

. Extensively worked on workflow manager to create session, E-mail

tasks, pre and post session stored procedures, worklets, creating,

editing Relational connections.

. Performance tuning of Informatica mappings.

. Created mapping parameters and variables to load the stage and Data

warehouse incrementally.

. Scheduled the work flows using PMCMD and Cron tab.

. Performed Unit testing of the mappings, integration testing for delta

loads full loads

. Created Informatica mappings to test the data consistency across old

and new servers during server migration.

. Involved in testing the Data, ETL procedures, system testing and

integration testing

ENVIRONMENT: Informatica Power center 4.7/1.7, Power Connect, Erwin 3.5,

Cognos 5.0, Oracle 8.1, PL/SQL, Mainframes, MS Access 2000, MS SQL Server

2000, Windows NT 4.0, Windows 2000, HP-UX,, UNIX Shell Scripts, Unix Sync

Sort.

HDFC (Housing Development Finance Corporation), India

October 2003 - February 2004

Oracle PL/SQL Developer

HDFC was incorporated in 1977 with the primary objective of meeting a

social need - that of promoting home ownership by providing long-term

finance to households for their housing needs.

RESPONSIBILITIES:

. Developed complex financial reports for Oracle SQL*Plus/UNIX

environment

. Developed PL/SQL stored procedures, packages, and triggers for data

load and transformation, and data extraction.

. Gathered Statistics and Analyzed Tables and Indexes

for Performance tuning

. Created, Tested and debugged the Stored Procedures, Functions,

Packages, Cursors and triggers using PL/SQL developer.

. Loaded data from the files got in different formats into the

development and production systems using SQL*Loader.

. Performed Shell Scripting using VI editor.

. Created External Tables to access data for interfaces from a data file

instead of loading the data into the database as per storage

consideration.

. Involved in writing complex stored procedures (Oracle) to facilitate

functionality. Wrote complex queries using SQL.

. Maintain all documentation and spreadsheets for Oracle Database

operations. Prepared documentation for database operations standards.

. Handled large volume of data, some tables having records of 100

million and some tables having an average of 50 million records.

. Troubleshooting development problems.

. Subsequent alterations of design based on user requirement changes.

. Implementation of coding and documentation standards to be followed

ENVIRONMENT: Oracle8i, Windows, UnixWare, Oracle 8i, BDE, Apache, COBOL,

SQL, PL/SQL, Putty, SQL*Plus, PL/SQL Developer

Contact this candidate