- Over 7+ years of professional IT experience in Analysis, Design, Development, Testing and Implementation of various Data Warehousing and Software Applications.
- Over 6years of extensive experience on Informatica Power Center with strong business understanding and knowledge of Extraction, Transformation and Loading of data from heterogeneous source systems like Flat files, Excel, XML, Oracle, Sybase, SQL Server.
- Experienced in logical and physical data modeling of staging and warehouse environments using Datamodeling tools like ERWin, Rational Rose and Oracle Designer.
- Extensively involved in ETL Data warehousing using Informatica PowerCenter 7.x/8.x/9 Designer tools like Source Analyzer, Target Designer, Mapping Designer, Mapplet Designer, Transformation Developer, Workflow Manager and Workflow Monitor.
- Extensively worked on transformations such as Source Qualifier, Joiner, Filter, Router, Expression, Lookup, Aggregator, Sorter, Normalizer, Update Strategy, Sequence Generator and Stored Procedure transformations.
- Hands on experience in identifying and resolving performance bottlenecks in various levels like sources, mappings and sessions. Hands on experience in optimizing SQL scripts and improving performance loading warehouse.
- Very strong in writing SQL Joins, Nested Queries, Unions, Multi-table joins.
- Experience in scheduling the batch jobs using workflow monitor, ESP, Tivoli, Autosys and FTP methods.
- Worked with Dimensional Data warehouses in Star and Snowflake Schemas, created slowly changing (SCD) Type1/2/3 dimension mappings.
- Expertise in using SQL*LOADER to load Data from external files to Oracle Database.
- Experience in Optimizing the Performance of SQL scripts and Oracle database/application tuning.
- Excellent in coding using SQL, SQL*Plus, PL/SQL, Procedures/Functions, Triggers and Packages.
- Developed Batch Jobs using UNIX Shell scripts to automate the process of loading, pushing and pulling data from different servers.
- Good experience in performing and supporting Unit testing, System Integration testing(SIT), UAT And production support for issues raised by application users.
- Diverse background with fast Learning and creative Analytical abilities with good Technical, Communication and Interpersonal skills.
ETL Tools: Informatica PowerCenter9/8.x/7.x, PowerExchange / PowerConnect.
RDBMS: Oracle11g/10g/9i, SQL Server 2000/2005/2008
BI Tools: Business Objects XI R2/R1/6.x
Operating System: UNIX,IBM AIX 4.3/4.2, Windows NT/2000/XP/7
Job Scheduling Tools: Tivoli 7x, Autosys 4.5
Defect Management tools: HP Quality Center 9x, Test Director 7x, Remedy 7x
Languages: SQL, PL/SQL, XML
Modeling: Tools ER Studio, Erwin 4.1,UML
Database frontend tools: Oracle SQL Developer, Aqua Data Studio 8.0.7, Quest Toad for Oracle.
Confidential, Buffalo, NY Jan 2011-TillDate
Role: Sr.Informatica Developer
Description: independent healths a health insurance company headquartered in buffalo,NY . It is the largest publicly-traded company focused exclusively on meeting the needs of those enrolled in publiclyfunded healthcare programs such as Medicaid and Medicare. A data warehouse has been built for cash reserve calculations, customer information sales tracking.The architecture involved moving data from legacy systems to relational tables in the staging area, moving staging data to the Data Mart and finally moving data to the Multi-Dimensional Data Warehouse which is stored in Oracle 10g.
- Involved in complete understanding of business requirement and involved in analyzing the sources to load in Oracle warehouse.
- Designed Sources to Targets mappings from SQL Server, Excel/Flat files to Oracle using Informatica Power Center.
- Responsible for working with DBA and Data Modellers for project coordination.
- Responsible for Detail design of each interface.
- Responsible for developing complex Informatica mappings using different types of transformations like UNION transformation, Connected and Unconnected LOOKUP transformations, Router, Filter, Aggregator, Expression and Update strategy transformations for Large volumes of Data.
- Developed SDE mappings to load large amount of data from different sources to staging area, SID mappings for load the data from staging area to target system
- Implemented slowly changing dimension Type 1 and Type 2 for Change data capture using Version control.
- Responsible for creating Workflows and sessions using Informatica workflow manager and monitor the workflow run and statistic properties on Informatica Workflow Monitor.
- Responsible for Defining Mapping parameters and variables and Session parameters according to the requirements and performance related issues.
- Created various tasks like Event wait, Event Raise and E-mail etc
- Created Shell scripts for Automating of Worklets, batch processes and Session schedule using PMCMD
- Created various Oracle database SQL, PL/SQL objects like Indexes, stored procedures, views and functions for Data Import/Export.
- tuning the Informatica mappings to increase the performance.
- Wrote test plans and executed it at UNIT testing and also supported for system testing, volume testing and User testing. Also provided production support by monitoring the processes running daily
- Extracted Data from FACETS Database.
- Worked on creating HIPAA files from SQL Server database.
Environment: Informatica PowerCenter 9.1/8.6, MS SQL server 2005, Oracle 10g, Data quality,Toad, PL/SQL (Stored Procedures, Triggers, Packages), Erwin, MS Visio, Windows XP
Confidential, Seattle, WA December 2009-November 2010
Role: ETL lead Analyst/ETL Developer
Description: Globally, T-Mobile has some 130 million subscribers, making it the world's tenth largest mobile phone service provider by subscribers and the third largest multinational after the United Kingdom's Vodafone . The Data arrives from different Source systems daily, which is sent into the tables of the staging area where it is transformed according to the business logic and finally loaded into the CDW. The data from the CDW is extracted for analysis and renewals by the Marketing team.
- Involved in Requirement Analysis, ETL Design and Development for extracting the data which is stored in different sources like Oracle, SQL Server, Sybase, and DB2.
- Analyzed, designed and developed an environment that would facilitate the use of Informatica to transform data coming from Igrasp oltp into Adim datamarts.
- Developed complex Informatica Mappings with transformations like lookup, router, aggregator, expression, update strategy, joiner and etc.
- Created Connected, Unconnected and Dynamic Lookup transformations for better performance.
- Created sessions, worklets, workflows for the mapping to run daily, biweekly based on the business requirements.
- 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.
- Tuned SQL, PL/SQL Queries in Source qualifier Transformation for better performance.
- Created and executed unit test cases for various scenarios for all the mappings, workflows and scripts.
- Involved in unit testing and fixing the bugs identified, provided data to the reporting team for their daily, weekly and monthly reports.
- Worked on modification of UNIX shell scripts.
- Involved in team weekly and by monthly status meetings.
Environment: Informatica PowerCenter8.5, Oracle10g, Toad, SQL, PL/SQL (Stored Procedure, Trigger, Packages), Erwin, MS Visio, Windows 2000, UNIX.
Confidential, Littleton, CO May 2008 - November 2009
Role: ETL/Informatica Consultant
Description Mortgage Data Integration
Aurora Bank (known as Aurora Loan Services) is one of the nation’s largest master servicers of residential mortgage-backed securities. Goal of MDI is to build a data warehouse with company’s mortgage data which is stored in different sources. Mortgage data like servicing, compliance, capital markets is stored in different databases and MDI is a common platform to pull in all data into one warehouse. Data is pulled from variety of sources like Oracle, SQL Server, flat files..
- Performed major role in understanding the business requirements and designing and loading the data into data warehouse (ETL)
- Used ETL (Informatica) to load data from source to DWH.
- Participated in Performance Tuning of ETL maps at Mapping, Session, Source and Target level as well as writing Complex SQL Queries from ABSTRACT Data model.
- Used techniques like source query tuning, single pass reading and caching lookups to achieve optimized performance in the existing sessions.
- Used Informatica client tools - Source Analyzer, Warehouse designer, Mapping Designer, Mapplet Designer, and Transformation Developer for defining Source & Target definitions and coded the process of data flow from source system to data warehouse.
- Used Informatica Workflow Manager and Workflow Monitor to schedule and monitor session status
- Used Informatica Data Migration to decrease the risk and minimize the errors
- Worked on creating HIPAA files from SQL Server database.
- Created complex Informatica mappings, reusable objects of Mapplets depending on client requirements
- Responsible for performance tuning for several ETL mappings, Mapplets, workflow session executions
- Used UNIX Shell scripting for automation of the process, invoking PL/SQL procedures, and Informatica sessions.
- Designed and Developed Oracle PL/SQL procedures, performed Data Import/Export, Data Conversions and Data Cleansing operations.
- Implemented performance tuning logic on Targets, Sources, mappings, sessions to provide maximum efficiency and performance.
- Involved in unit testing and identified new requirements/concerns. Scheduling and rerunning failed jobs as required.
Environment: Informatica PowerCenter 8.1/8.0, SQL, PL/SQL, Oracle 10G, MS SQL server 2005, TOAD Starschemas, Unix Scripting, Flat Files, Windows NT/2000.
Confidential, IL. May 2007-April 2008
Role: ETL Informatica Developer
Description: The Allstate Corporation Northbrook, IL The Allstate Corporation is the nation's largest publicly held personal lines insurer. The scope of the project is to build Data Marts for business analysis departments, such as generation, transmission, distribution and customer satisfaction. The project involved extracting data from the different sources and loading into data Mart. It enables an insight in to the current and future customer service and helps in making business decisions based on the reports generated from data warehouse.
- Developed Logical and physical database designs for the transaction system.
- Extract the XML file from source transform it with multiple transformation including XML Transformation.
- Performance tuning on sources, targets, mappings and SQL queries in the transformations.
- Used PL/SQL Views, Triggers, Procedures and Functions.
- Designed and developed complex ETL mappings making use of transformations like Source
- Qualifier, Expression, Joiner, Update Strategy, Connected Lookup and unconnected Lookup, Rank, Router, Filter, Aggregator and Sequence Generator transformations.
- Created reusable transformations and mapplets based on the business rules to ease the development.
- Developed mappings to implement type 2 slowly changing dimensions.
- Developed and implemented the Unix shell script for the start and stop procedures of the sessions.
- Used various Informatica Error handling technique to debug failed session.
- Developed workflow tasks like reusable Email, Event wait, Timer, Command, Decision Used various debugging techniques to debug the mappings.
- Created Test cases for Unit Test, System Integration Test and UAT to check the data quality.
- Created Materialized view for summary data to improve the query performance. Environment: PowerCenter8.1 ,Oracle9i, SQL Server 2000, SQL,PL/SQL, shell scripts, MS Visio, ERWIN Data Modeling tool, Toad 8.0, Windows 2000,XML,UNIX
Confidential, St.petersburg , FL October 2006 – April 2007
Role: Informatica Developer
Description: 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.
- 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
- Developed reusable Mapplets and Transformations.
- Good knowledge on fixedincome,Derivatives and foreign exchanges also.
- Responsible for 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
- Create and Administer Repositories using Repository manager of Informatica.
- Worked on creating HIPAA files from SQL Server database.
- Also involved in moving the mappings from Test Repository to Production after duly testing all transformations.
- 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 7.1, Oracle 9i, UNIX, Sybase, MS SQL Server 2004, Windows 2000
Confidential, Mumbai, India May 2004 – September 2006
Role: Data Warehouse/Database Consultant
Description: The Reliance project is a strategic initiative designed to improve the client reporting efforts of the various functional groups. Reliance 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.
- Involved in design & development of operational data source and data marts in Oracle
- Reviewed source data and recommend data acquisition and transformation strategy
- Designed ETL process using Informatica Designer to load the data from various source databases and flat files to target data warehouse in Oracle
- 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: Oracle 8i, SQL, PL/SQL, SQL*Loader, IBM DB2, TOAD, Windows 2000.