Experience Summary:

• Over 7+ years Experience with Informatica 8.6x/7.x/6.1 (Source Analyzer, Mapping Designer, Mapplet Designer, Transformations Designer, Warehouse Designer, Repository Manager, and Workflow Manager/Server Manager).
• Implementation of full lifecycle in Data warehouses and Business Data marts with Star Schemas, Snowflake Schemas, SCD & Dimensional Modeling.
• Strong Experience in developing Sessions/tasks, Worklets, Workflows using Workflow Manager Tools - Task Developer, Workflow & Worklet Designer.
• Extensively worked with different data sources non-relational databases such as Flat files, XML files, and other relational sources such as Oracle, Sql Server, DB2.
• Well adept in planning, building, and managing successful large-scale Data Warehouse and decision support systems.
• Comfortable in both technical and functional applications of RDBMS, Data Mapping, Data management, Data transportation and Data Staging.
• Experience in data mart life cycle development, performed ETL procedure to load data from different sources into data marts and data warehouse using Informatica Power Center.
• Extensively worked in Extraction, Transformation and Loading of data from multiple sources into Data Warehouse.
• Expertise in creating mappings, mapplets and reusable Transformations using Informatica Designer.
• Vast experience in Designing and developing complex mappings from varied transformation logic like Unconnected and Connected lookups, Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, Update Strategy etc.
• Good knowledge in tuning the performance of SQL queries and ETL process.
• Worked on UNIX-shell Scripting for Pre-Session, Post-Sessions tasks and also automated the scripts using a scheduling tool.
• Hands on experience in creating Indexes and partitioning tables for performance.
• Significant Experience in PL/SQL, Procedures/Functions, Triggers and Packages.
• Expertise in doing Unit Testing, Integration Testing, System Testing and Data Validation for Developed Informatica Mappings.
• Excellent Analytical, Communication skills, working in a team and ability to communicate effectively at all levels of the development process.
  

Technical Summary:

Languages: C/C++, HTML, XML, SQL, PL/SQL, Unix Shell Script, COBOL, JCL, PL1

ETL: Informatica 8.6x/7.x/6.x(Power Center/Power Mart) (Designer, Workflow Manager, Workflow Monitor, Server Manager)

Databases: Oracle 8i/9i/10g, Sql Server 2008/2005, DB2.

OLAP: Business objects XIR2, BI Publisher, XML Publisher

Operating Systems: Windows 2000/ NT/ XP/ Vista/98/ 95, UNIX, DOS, Windows 2003 server.

Tools: SQL Loader, SQL*Plus, TOAD, SQL Developer

Professional Summary:

Confidential,
Bloomington, IL
Role: Sr. ETL Developer Mar 10 - Present

Project Description: ECRM - Agent List Creation

This project formally defines and recognizes the ECRM - Household Marketing - Additional Queries project. It serves as a formal agreement with applicable management, Business Partners, and Stakeholders in identifying the scope of the project. It also provides the Project Manager with the authority to manage and apply resources to the project.
This framework allows agents to create lists of clients using an expanded menu of predefined search criteria. The customer lists that are produced from the searches will be loaded into the Leads Management System List management and letter creation.

Responsibilities:

• Exclusively Worked on Informatica - Source Analyzer, Data warehousing designer, Mapping Designer & Mapplet and Transformations.
• Extracted data from flat files, SQL Server 05, Oracle using Informatica Power Connect and performed massive data cleansing applying all the business rules prior to loading them into Oracle staging tables.
• Hands-On experience in developing Transformations like Joiner, Aggregate, Expression, Lookup, Filter, Union, Update Strategy, Stored Procedures, and Router etc. using best practices.
• Extensively used Informaticato load data from MS SQL Server andDB2, into the target.
• Used PMCMD, PMREP and UNIX shell scripts for workflow automation and repository administration.
• Implemented Slowly Change DimensionType I & II to capture history and created process flow chart for insert/update using effective end date while loading data into the data marts.
• Developed shell scripts, Stored Procedures for regular Maintenance and Production Support to load the warehouse in regular intervals and to perform Pre/Post Actions.
• Performed Bulk Load of large volume of data by creating pre- and post-load scripts to drop indexes and key constraints before session and rebuild those indexes and key constraints after the session completes.
• Implemented Performance Tuning at various levels such as Source, Target, Mapping, Session, and System.
• Developed Test Cases for Unit Testing, also involved in Integration, system, and performance testing levels.

Environment: Informatica Power Center 8.1/8.5/8.6, Oracle 10g, DB2, SQL Server 05, Autosys, Toad 9.0.1, Unix, SQL Developer, SQL, PL/SQL.

Confidential,
Bloomington, IL
Role: Sr. ETL Developer Apr 09 - Feb 10

Project Description: ECRM - Multi-Wave Campaign
Multi-Wave campaign is a series of related marketing touch points directed towards a specific group of customers of a large insurance company in US. The waves may consist of a combination of marketing mailings launched successively over a specified period of time such as a brochure, followed by a postcard followed by a letter etc
Agents create an Agent-mail list using ALC application

System provides opportunities to the Agent to

• View the List
• Revise the List criteria
• Delete the list
• System provides the information for each marketing list.
• List Type

List criteria and other information are updated in List Manager Database. A new campaign type will be created in LMS and necessary tables to accommodate multi-wave campaigns. Agents can creates waves to the existing list (except for the list status: In processing, rejected & lead count zero) using Multi-wave screens. The Multi-wave details of those waves will be updated in Wave & Wave-list tables of List manager database. A new batch process is written to query the multi-wave campaign table for lists to be loaded into LMS.

Responsibilities:

Designed and Developed mappings needed for enhancement of project. Worked with Informatica Power center 8.6 Mapping Designer, Workflow Manager, Workflow Monitor and Admin Console. Created Informatica Mappings to populate the data into dimension and Fact tables. Exported and Imported mappings between different Informatica folders and also Repositories. Responsible for Administration of Informatica Environment. Created users and groups, configured profiles and privileges. Used different transformations like Aggregator, Lookup, Filter, Expression, Router, Update Strategy and Sequence Generator. Created mappings for various sources like DB2, Oracle, etc to load and integrate the instrument level details to ware house. Informatica Debugger is used to test the mappings and fixed bugs. Provided Production Support for IDS DSS and created Unix Shell Scripts to delete data that is older than 3 years from fact tables. Developed UNIX shell scripts to get data from external systems flat files to EDW stage area, and to schedule various workflows to load the data into target system. Familiar with Data Warehouse Architecture, design methodologies and Best practices. Experience with server log files, editing UNIX scripts, FTP files and checking space on server. Implemented performance tuning techniques on Targets, Sources, Mappings and Sessions. Scheduled Informatica sessions and workflows using Informatica Scheduler. Performance and fine tuning of Informatica mappings. Loaded data into Datamarts on a daily basis. Created new repositories and new folders within the repositories using Repository Manager. Experience with Designer, Workflow Manager, Workflow Monitor and Repository Manager. Involved in System testing and User Acceptance Testing. Involved in Production support and trained the other developers to handle issues.

Environment: Informatica Power Center 8.1, Oracle 9i Windows Server 2003, PL/SQL, SQL*Plus, XML, PL/SQL Developer, DB2, Windows NT/2000.

Confidential,
Bloomington, IL
Role: Sr. ETL Developer Jan 08 - Mar 09

Project Description: Business Support System (BSS) - MARS

The Business Support System - Meta Data Access Repository Service (MARS) project consists of several web-enabled tools used to view and manage meta-data. MARS application provides access to meta-data which helps promote data sharing and reuse. It also provides a way to integrate the information from multiple meta-data creation sources into a single easy to use delivery mechanism. Meta-Data is the who, what, when, where, why and how of the data which one is interested to know. The description of the different tools of MARS is as follows:

MetaMart - Allows the user to browse meta-data related to production datastores.
Data Dictionary - A quick search for users to reference data elements.
Allowed Values - Allows the user to create and manage allowed values.
Meta Map - Allows the user to document relationships and business rules between data elements.
Admin Tool - Allows the user to perform administrative activities.

Responsibilities:

• Involved in study of existing operational systems, data modeling, analysis and translated business requirements into data mart design.
• Define the entity-relations - ER Diagrams and designed the physical databases for OLTP and OLAP (data warehouse).
• Created and maintained the centralized repository using Informatica power center.
• Created mappings with heterogeneous targets in Informatica Mapping Designer which are not possible in earlier versions.
• Used Pre and Post session SQL in Source Qualifier Transformation of Informatica to drop and recreating the Indexes of database tables.
• Used Informatica repository manager to backup and migrate metadata in development, test and production environments.
• Worked with mapping wizards for slowly changing dimensions and slowly growing dimensions by using Informatica mapping designer.
• Designed and developed complex aggregate, join, lookup transformation rules (business rules) to generate consolidated (fact/summary) data using Informatica Power center.
• Designed and developed mappings using Source qualifier, Aggregator, Joiner, Lookup, Sequence generator, stored procedure, Expression, Filter and Rank transformations.
• Created the PL/SQL stored procedures for extracting the data from source tables and load the target tables of Data mart.
• Created session variables to build sessions more effectively by using Informatica server manager.
• Created stored procedures to validate the data before loading data into data marts.
• Used triggers to validate data before inserting or updating data into tables.
• Created Joins, Cardinalities, Aliases and Contexts for resolving Loops and checked the Integrity of the Universe.
• Created different kinds of objects like Dimension, Detail and measure objects.

Environment: Windows NT, Oracle 8i, Informatica Power center 7.1.2

Confidential,
Bloomington, IL
Role: ETL Developer Oct 06 - Dec 07

Project Description: Data Information & Strategies - SAMS Authorization
SAMS Authorization was divided into two phases Drop I and Drop II. SAMS Drop 1 implemented multiple data stores to house associate data and processes to source this information. This Drop 1 created the foundation for Enterprise Operational Data Stores. The SAMS program built on that foundation by creating a web application to manage sales offices, associates with sales roles and policy assignment information.

Agents, AFOs, Canada Agency Resources and various corporate departments are using this new functionality to manage this data by creating and maintaining sales roles, offices and all supporting personal, business and assignment information. Work efforts include automated workflow system and a user-friendly reporting solution as part of web-based Sales and Assignment Management System.

The project is to help product areas who requested the ability to view the actual licensing information that is available. For example, there are associates who hold insurance licenses who are not authorized by virtue of their current job role or lack of proper contract agreements. In reality, the real business need to know, at a particular point in time, whether or not an associate is authorized to solicit and service the insurance, bank, financial service products offered by State Farm in a particular state or province. This effort provided the enterprise with viewing and management capabilities around summary product line authorizations for associates in sales roles and detailed insurance licensing, appointment and securities registration information for all associates including those in sales roles. The summary authorization and detailed information will be available for resident and non-resident states.

I worked on design document and coding the mappings for this work effort. All the target data is loaded to DB2 tables. These tables are used to create the reports in Business objects for different scenarios for the end users. I have developed and implemented part of this project, which is now in production.

Responsibilities:

• Interacted actively with Business Analysts and Data Modelers on Mapping documents and Design process for various Sources and Targets.
• Extensively worked on schema creations (Star and Snow flake schemas) in Data warehouse.
• Extracted data from wide variety of data Sources like Legacy Systems databases, DB2, Flat files, Relational Databases and Heterogeneous sources.
• Extensively worked on Informatica commissions process.
• Created mapping documents for data loading into all Staging, ODS, Dimensions and Fact tables.
• Created mappings using various Transformations such as Source qualifier, Aggregator, Expression, Router, Filter, Rank, Look up, Joiner, Sequence Generator, and Update
• Designed Incremental loading process to load data into staging tables.
• Implemented Type1 and Type2 methodologies in ODS tables loading, to keep historical data in data warehouse.
• Extensively worked on creating the new set of composite primary keys on ODS tables to handle Type2 changes efficiently.
• Implemented unconnected lookups in ETL mappings to create foreign key relationship between Dimensions and Fact tables.
• Extensively worked on Performance tuning to increase the throughput of the data load (like read the data from flat file & write the data into target flat files to identify the bottlenecks).
• Debugged all expression and data validation problems in the mappings with debugger.
• Extensively used Re-usable Objects like Sources, Targets in all mappings to handle metadata changes efficiently.
• Created and tested AppWorx scripts to automate Job scheduling.
• Created Worklets, Workflow and Tasks to schedule the loads at required frequency using Workflow Manager.
• Created reusable email tasks in Workflow manager for process exception handling.
• Collecting the statistics for each load and implementing the exception handling as part of the load process.
• Maintenance/Support of the application, trouble shooting and Enhancements

Environment: Informatica Power Center 6.1.1, Oracle 8i, PL/SQL, SQL*Plus, XML, PL/SQL Developer, Windows NT/2000.

Confidential
India
Role: ETL Developer Jun 04 - Sep 06

Project Description: Data Information & Strategies - Customer Access

State Farm Insurance is a leading insurance company in North America that services policies in Auto, Home, Health, Fire and Life. The current project builds the applications to assess marketing and business needs of the company in reporting, monitoring and tailoring the company's services from a strategic and competitive point of view.

The current project, which is creating a reporting tool, that provides information on Company's Policy bind and issued policy process. There are 3 main access points (Agent, Internet and After Hours Quote & Bind System) that the project solution aims to track and report on. One of the main purposes for this information of the project is to understand how customers are shopping for company's policies today, through what access points, the volume of customers requesting quotes and showing interest in a company's Auto Policy and the rate at which Agents can follow up and bind them to new business. Other goals for this project include: Ad Hoc reporting for end users, Policy Gain, Policies in Force, Lapse/Cancel information, Demographics, etc. This reporting will be done at Enterprise, Zone, State, VPA & AFO and Agent levels and the end users using this application are company's Enterprise and Zone users.

The whole project is classified into two different cases based on access points -

• AHQB (After Hours Quote and Bind) - Closed access point is Agent office/AHQB rep.
• Online - Closed access point is Internet.

Responsibilities:

• Defined Source and Targets in Informatica for ETL and created source to target mappings.
• Created reusable Transformations for modifying data before loading into target tables.
• Created mapplets in the Informatica Designer which are generalized and useful to any number of mappings for ETL jobs.
• Created Transformations using the SQL script to modify the data before loading into tables.
• Used Informatica Power center to load the data into data warehouse.
• Created and used mapping parameters, mapping variables using Informatica mappingdesigner to simplify the mappings.
• Worked with Informatica server manager to tune the session performance.
• Developing different styles of Business objects documents
• Used the Business objects features Slice and Dice and Drill Down for multi dimensional analysis.
• Inserted Objects, Conditions, classes, subclasses and user objects according to client's requirement.

Environment: Windows NT, Oracle 8i, Informatica Power mart 5.1, Informatica power center 5.1