Summary:

• Over 8 years of extensive experience in design, development and Implementation of ETL solutions/procedures using Informatica PowerCenter 8.6/8.5/8.1/7.1/6.1/5.1, PowerMart, PowerConnect for Oracle, DB2, Power Exchange, Power Analyzer, Power Plug, Datamart, OLAP, OLTP to extract the Organizational data into Enterprise Datawarehouse and Datamarts.
• Extensive working experience on Informatica PowerCenter Designer tools like Source Analyzer, Warehouse Designer, Mapping Designer, Mapplet Designer, Transformation Developer, Workflow Manager and Workflow Monitor.
• Extensive experience in creation of various Informatica transformations to facilitate Extract/Transform/Load of data from OLTP systems using PowerCenter client tools.
• Extensive experience in handling data from various source systems such as Flat Files, XML Source, Oracle, MS SQL Server, IBM DB2, Teradata and Excel Files.
• Experienced in tuning of Mappings and sessions to increase the throughput and to facilitate quick load times.
• Extensive experience in devising and implementing the workflow of jobs, job schedule/ status control process using Informatica Workflow Manager and UNIX Shell Scripts.
• Extensive experience in designing the data marts using dimensional modeling techniques such as Star Schema and Snow Flake Schema.
• Experienced in logical and physical data modeling of staging and warehouse environments using data modeling tools like Erwin, Rational Rose and Oracle Designer.
• Extensive experience in database programming using PL/SQL and SQL with emphasis on DDL, DML operations, creation of packages, stored procedures, functions, triggers, materialized views and cursors and performance tuning of SQL.
• Extensively worked on Data Cleansing and Data Profiling using IDQ and IDE
• Experienced in UNIX shell scripting with more emphasis on automation/control of Informatica Jobs.
• Extensively experienced on implementing CDC(Change Data Capture)
• Experienced Created WSDL and Web Services workflows.
• Expertise in designing and developing Dashboards with Xcelsius, Dashboard Manager of Business Objects
• Interfaced with Business Users / Subject Matter Experts to understand the business requirements and transform the business requirements into technical requirements.
• Experienced in creation of high level design, detail design, unit test plans System/Integration plans and traceability matrix based on business/technical requirements.
• Extensive experience in all phases of SDLC of large data warehouse project.
• Excellent documentation skills, analytical skills, problem identification and solving ability in timely manner.

Technical Summary:

Data Warehousing Informatica Power Center 8.6.1/8.5/7.1/6.2/5.1, Power Mart, Informatica Power Analyzer, Informatica Power Plug, Informatica Designer, Workflow Manager, Work flow Monitor, Datamarts, OLAP, OLTP, Autosys
BI Tools Business Objects 6.5, Cognos 8, MS Access Reports.
Data Modeling ERWIN 7.2/7.1, Dimensional Data Modeling, Data Modeling, Star Join Schema
Modeling, Snowflake Modeling, FACT and Dimensions Tables, Physical and Logical Data Modeling, Oracle Designer and Visio.
Databases Oracle 10g/9i/8i, Teradata V2R5/R4/R3, Trizetto Facets 4.6.1, IBM DB2, Sybase, MS SQL Server 2004, 2005, MS Access.
Programming SQL, PL/SQL, SQL Plus, Transact SQL, ANSI SQL, Java, Unix Shell Scripting, Kernel Programming, PERL
Environment Sun Solaris, HP-UX, IBM AIX, MS DOS, Win 98, Win NT, Red Hat Linux, UNIX, HP9000, Win 2000, Win XP

Professional Experience:

Confidential,Portland, OR Sep 2009 – Present
Sr. Informatica Consultant
Project: ICU Dashboard
Providence Health & Servicesis anot-for-profit health system committed to providing a comprehensive array of services to meet the needs of communities across five states, including Alaska, Washington, Montana, Oregon and California.
ICU Dashboard is project where doctors in ICU can analyze the condition of a patient based on the Apache Trends (Scores). This will also help clinicians to make critical care decisions at the bedside to improve patient outcomes, and to efficiently allocate critical care resources. This data will be made available on a real-time basis through a data repository containing clinical and financial information for all critical care or patients in ICU through a dashboard.
Role:

• Involved in design and development of requirement specification documents by coordinating with business users and Technical teams.
• Designed Sources to Targets mapping from DB2, Oracle10g to SQL Server2005 database using Informatica PowerCenter tools like Source Analyzer, Target Designer, Transformation Developer, Mapplets Designer, Mapping Designer, Workflow Manager, Workflow Monitor and PC Repository Manager.
• Extensively worked on converting complex scalar functions and stored procedure into Informatica mappings using various transformations like lookup, expression, update strategy, router, filter, sequence generator, joiner, etc.
• Various kinds of transformations were used to implement simple and complex business logic and configured the mappings to handle the updates to preserve the existing records using Update Strategy Transformation.
• Mapplets and Reusable Transformations were used to prevent redundancy of transformation usage and maintainability.
• Involved in the design of historical load and incremental load mappings to load data on Hourly, Shift basis and daily basis using SCD type2 methodology.
• Created and configured workflows, worklets & Sessions to transport the data to target warehouse SQL Server tables using Informatica Workflow Manager.
• Extensively worked on the performance tuning of the Informatica PowerCenter Mappings as well as the tuning of the sessions
• Configured and scheduled Pre and Post Session commands with Shell Scripts.
• Involved in performance tuning of the Informatica mappings and query.
• Involved in UAT Testing and Production Testing by identifying the bugs and also helped to resolve various Data issues and Load issues

Environment: Informatica PowerCenter 8.6.1, Business Objects XIR2, Business Objects Data Integrator, Oracle 10g, IBM DB2, SQL Server 2005, Excel and Unix scripting.

Confidential,San Francisco, CA Mar 2008 – Sep 2009
Sr. Informatica Designer/Developer
Blue Shield of California is among the leading providers of Health Insurance in the state of California. They are currently in a process of integrating a Trizetto Facets front end application as part of an overall IT project called Shield Advance. This project is about migrating and integrating data from a new 3NF database into existing legacy dimensional marts that involved designing an ETL environment that uses Facets data as its source and the current existing Rosetta Oracle Database as the target.
Role:

• Requirement gathering, Business Analysis and Documentation for business data flow from source to mart.
• Analyzed the functional specs provided by the architect and created technical specs documents for all the mappings.
• Analyzed, designed and developed an environment that would facilitate the use of Informatica to transform data coming from Trizetto Facets into Rosetta datamarts.
• In depth Analysis of the Claims and Membership subject areas. Worked on HMO/PPO, Claims Daily/Weekly, Oasis Claims data marts as well as the Oasis Membership and Membership Revenue marts. The analysis phase involved gaining a detailed knowledge of the Blue Shield Health Care system.
• Developed complex Informatica Mappings with transformations like lookup, router, aggregator, expression, update strategy, joiner and etc confirming to the business rules and documented.
• Extensively used Parameter files, mapping variables in the process of development of the mappings for all the dimension tables.
• Implemented Slowly Changing Dimension type2 methodology in Informatica.
• Worked on handling performance issues of Informatica Mappings, evaluating current logic for tuning possibilities.
• Created Adhoc reports using WEBI in Business Objects also worked on XCELSIUS.
• Created and executed unit test cases for various scenarios for all the mappings, workflows and scripts.
• Provided data to the reporting team for their daily, weekly and monthly reports.
• Involved in team weekly and by monthly status meetings.

Environment: Informatica PowerCenter 8.6, PowerExchange, Business ObjectsXIR3, Trizetto Facets 4.6, Oracle 10g, Teradata V2R5, IBM DB2, COBOL IBM Mainframe, Flat files, HP-UX 11.11, SQL Navigator, HIPAA, PVCS, Excel and Unix scripting.

Confidential,Irvine, CA Jan 2007 – Feb 2008
ETL/Informatica Developer
Science Applications International Corporation (SAIC)
SAIC is Fortune 500 scientific, engineering and technology applications Company that uses its deep domain knowledge to solve problems of vital importance to the nation and world, in national security, energy and environment, critical infrastructure, and health
Allergan, Inc.
Allergan Pharmaceutical Products Group aims to create a top tier global pharmaceutical business one that is dedicated to discovering, developing and marketing breakthrough drugs that improve patient health. Data warehouse tracks the information about the Allergan Laboratories customer details, sales management, and charge backs by maintaining the information in the oracle database as target database and then generating the reports based on the requirements.
Role:

• Reviewed functional specification document and developed technical specification document.
• Developed Mappings and Transformations (using Filter, Joiner, Lookup, Update Strategy, Expression and Aggregator) on the extracted data as per the Business requirements.
• Used UNIX shell scripts and called the shell script in the workflow manger through command task.
• Developed some reusable transformations in ETL Informatica.
• Involved in fixing invalid Mappings, testing of Stored Procedures and Functions, Unit and Integration Testing of Informatica Sessions and the Target Data.
• Developed PL/SQL procedures for processing business logic in the database.
• Configured and scheduled Pre and Post Session commands with Shell Scripts.
• Involved in scheduling Informatica workflows in Autosys to automate the update processes.
• Submitted the code promotion templates to the promotion management team for promoting the developed code, jobs and mappings from development to beta and beta to production.
• Wrote UNIX shell scripts.
• During Implementation phase, tuned Informatica Mappings and Sessions for optimum performance.

Environment: Informatica Power Center 8.6, SQL Server 2005, XML, SQL, Unix, Visio, JAVA, TOAD, SoapUI 2.5, Autosys, PVCS

Confidential,Carlsbad, CA Aug 05 – Dec 06
Informatica Developer
Project involved computerization of the entire warehouse so that all the operations and transactions pertaining to the inventory can be maintained online. It keeps the track of information of the all the inventory, which is being stored with the detailed report of the items in the agency and supplied to the department when requested. This deals with the maintenance of the entire stock in the firm. It mainly dealt with the information regarding the purchase, stocking and stores.
Role:

• Reviewed functional specification document and developed technical specification document.
• Designed and developed end-to-end ETL processes.
• Studied the existing OLTP system(s) and Created facts, dimensions and star schema representation for the Datamart.
• Analyzed and created Facts, Dimension tables. Modeled the Data Warehousing data marts in Star join schema
• Created reusable transformations inside a Mapplet.
• Extensively worked on the performance tuning of the Informatica PowerCenter Mappings as well as the tuning of the sessions
• Drilled out each of the data flow on a page to plan which tables to work on, and for sequencing the transformations within each data set
• Created Stored Procedures for data transformation and processing business logic in the database
• Created Dashboards using XCESIUS in Business Objects.
• Developed schedules to automate the updated processes and Informatica Sessions/Worklets
• Tuning of SQL queries for better performance. Working with database connections, SQL joins, views, aggregate conditions, parsing of objects and hierarchies
• Developed reports using the Business Objects for the Sales department and Marketing representatives. Maintained hierarchies in the designing of the Universe for drill down capabilities
• Generated Adhoc reports using the Business Objects free hand SQL.
• Extensively used Autosys for scheduling of the UNIX shell script jobs

Environment: Informatica PowerCenter 8.1, 7.1, IDQ, IDE, Business Objects XIR2, SQL, PL/SQL, Sybase, Oracle 10G, MS SQL server 2005, Korn shell scripts, Perl script, Flat Files, Windows NT/2000, Erwin 4.0, MS-Excel, MS-PowerPoint, MS-Access

Confidential,New York, NY July 2004 - July 2005
Informatica Consultant
FGIC (Financial Guaranty Insurance Company) is a leading insurer of municipal bonds, guaranteeing the timely payment of principal and interest on newly issued municipal bonds and bonds traded in the secondary market. The major information system involves the General Ledger. Oracle GL, one of the modules of Oracle Application is being implemented to cater all GL related functionality. The User Interface is developed using VB application and is used to run different feeds, which in turn runs, the Informatica sessions/batches containing the Mapping. The feature of the system includes post error processing, which enables to re-load the data after error correction.
Role:

• Major involvement in creating and tuning the mappings, to perform maximum efficiency and performance complete knowledge of session properties in Work flow monitor
• Designed and created complex source to target mappings using various transformations inclusive of but not limited to Aggregator, Look Up, Joiner, Source Qualifier, Expression, Sequence Generator and Router Transformations.
• Created update strategy and stored procedure transformations to populate targets based on business requirements.
• Implemented Slowly Changing dimension type2 methodology for accessing the full history of accounts and transaction information.
• Built-in mapping variable / parameters and created parameter files for imparting flexible runs of sessions / mappings based on changing variable values
• Used Informatica to create and organize various Metadata types, including legacy Source and Datawarehouse Target data definitions and ETL jobs.
• The Mapping Designer module used for ETL tasks to upload the data from various centers with data in different source systems to build Datamarts.
• Mapplets and Reusable Transformations were used to prevent redundancy of transformation usage and maintainability.
• Loaded operational data from Oracle, Sql server, flat files, Excel Worksheets into various data marts.
• Created Tasks and Workflows using Task Manager and Workflow Designer
• Also developed numerous Stored Procedures, Triggers and common functions using PL/SQL.

Environment: Informatica Power Center 7.1, ERWIN 4.0, Oracle 9i, PL/SQL, IBM DB2, TOAD, Visual Basic 6.0, VSS, Windows

Confidential,Jacksonville, FL Dec 2003 – Jun 2004
Informatica Developer
Performance Measurement Application (PMA) is a Data Warehouse application meant to measure the performance of portfolios and mutual funds. Involved in the design and development phases of the project.
Role:

• Extensively used Informatica client tools – Source Analyzer, Warehouse designer, Mapping designer, Mapplet Designer, Transformation Developer.
• Developed complex mapping using Source qualifier, Aggregators, Connected & unconnected lookups, Filters & Update strategy.
• Used Lookup Transformation to access data from tables, which are not the source for mapping and also used Unconnected Lookup to improve performance
• Participated in troubleshooting and facilitated transition to system test, UAT and production phase.
• Developed reusable Mapplets and Transformations.
• Create and Administer Repositories using Repository manager of Informatica.
• Also involved in moving the mappings from Test Repository to Production after duly testing all transformations.
• Responsible for testing the entire applications and transport the data to Target Warehouse Oracle tables on the server, Schedule, Run Extraction and Load process and Monitor sessions by using Informatica workflow manager and workflow monitor
• Involved in end-to-end Unit Testing of the mappings.
• Calculated the Cache requirements for Lookup, Joiner and Aggregator transformations and increased the memory on Informatica server to avoid the paging to the disc.

Environment: Informatica Power Center 6.1, Oracle 8i, UNIX, Sybase, MS SQL Server 2004, Windows 2000

Confidential,Boston, MA Dec 2001 – Nov 2003
Informatica Developer
The EIS project is a strategic initiative designed to improve the client reporting efforts of the various functional groups. The EIS is expected to create competitive advantage by improving the management decision making process with more accurate and timely information and improving the quality of the client interactions with better advance communication and coordination among the various teams.
Role:

• ETL tool Informatica was used to load strategic source data to build the data marts. An operational data store was created. Metadata Build up was designed for performing data mapping. Also involved in Mass data loads, refreshing the data in various applications, Performance evaluations, modifying the existing code to accommodate new features.
• Used various Transformations like Aggregator, Router, Expression, Source Qualifier, Filter, Lookup, Joiner, Sorter, XML Source qualifier, Stored Procedure and Update Strategy.
• Worked extensively on Flat Files, as the data from various Legacy Systems are flat files.
• Have setup Test and Production Environment for all mappings and sessions.
• Created and configured Sessions in Workflow Manager and Server Manager.
• Debugged the sessions using Debugger and monitored Workflows, Worklets and Tasks by Workflow Monitor.
• Extensively used SQL & PL/SQL for Transformations.
• Developed Oracle stored procedures, packages and triggers for data validations.

Environment: Informatica Power Center 5.2, Business Objects 5.1, Webi SDK, Oracle 8i, Windows 2000