- Over 7 years of experience in software industry as a Data warehousing expert and ETL developer in designing, developing and implementing ETL processes for business applications using Informatica power center 7.1/8.1/8.5/9.1, Informatica Power Exchange 9.1
- Worked with various domains including Insurance, Banking, Finance, Healthcare and Travel
- Have Good understanding of Data Warehousing concepts(Kimball/Inman) and principles with prominence on ETL and Life Cycle Development including requirement analysis, design, development, testing and implementation
- Have good knowledge on Application oriented data(OLTP) and Subject oriented data(OLAP)
- Experience in dimensional data modeling concepts like Star Schema, Snow Flake Schema, Third Normal Form Schema, FACT tables, Dimensions tables and Surrogate keys
- Experience in integration of various data sources with multiple relational databases like Oracle, Teradata, SQL server and Netezza into the staging area
- Experience in working with Data management, Data synchronization, Data Migration, Data Conversion, and Extraction/Transformation/Loading usingInformaticaPower Center for development of Data warehouse
- Worked with various Power Center components like InformaticaServer and Client tools like Mapping Designer, Workflow Manager, and Repository Manager & Workflow Monitor
- Extensively worked on Informatica Designer Components like Source Analyzer, Warehousing Designer, Transformations Developer, Mapplet and Mapping Designer
- Worked on all the transformations like Connected and UnconnectedLookup, Aggregator, Expression, Filter, Router, Update Strategy, Connected and Unconnected Stored Procedure, Sequence Generator and Sorter
- Experience in Workflow Manager Tools like Task Developer, Workflow and Worklets Designer and Managing, Scheduling, and Monitoring of Informatica workflows
- Experience in migrating the workflows, sessions and mappings to different environments using Repository manager
- Experience in identifying bugs in mappings by analyzing data flow and evaluating transformations using debugging
- Experience in Informatica Performance Tuning of the ETL process and SQLTuning
- Experience in PL/SQL programming like developing Stored Procedures, Triggers, Functions and Packages using Oracle(SQL,PL/SQL) and SQL server and UNIX shell Scripting to perform Job scheduling
- Good Experience in creating Registration groups and Data Maps to Source the definition of VSAM files and performed Row Test using Informatica Power Exchange
- Experience in Data Modeling tools like Erwin r8, ER Studio.
- Excellent analytical and logical programming skills, communication skills, good organization skills hardworking, ability to contribute and be a productive team member and ability to implement business concepts quickly and efficiently
ETL Tools: Informatica Power Center 6.x/7.x/8.x/9.1, Informatica Power Exchange 9.1
Databases: Oracle 8.i/9.i/10g/11g, SQL Server 2000, 2005, Teradata V8,Netezza.
Data Modeling Tools: Erwin 4.1, ER Studio
Languages: SQL, PL/SQL, C, UNIX Shell script-K Shell
Development Tools: SQL*plus, TOAD
Operating Systems: Windows, Red Hat Linux, Sun Solaris.
Location: Boston, MA
Role; ETL Developer Apr\'12 -Aug\'12
Description: Grand Circle Travel offers distinctive international vacations for older Americans. Established in 1958 by Ethel Andrus, educator and founder of the American Association of Retired Persons (AARP), it views world travel as a powerful lever for lifelong learning. GCT make it accessible through trips that deliver unmatched discoveries, leisurely pacing, more travel choices, and comprehensive value.
- Participated in gathering requirements and understood the business rules, Source system, Target System, Operational Data and Deployment Process
- Designed and developed mapping at various levels like Staging, Enterprise data warehouse(EDW) and Data Mart according to the proposed ETL architecture
- Used various transformations like Expression, Filter, Router, Sorter, Sequence Generator, Aggregator, Lookup, Update Strategy, connected and unconnected Stored procedure, HTTP and Joiner
- Developed mappings to download a XML file from a web server using HTTP Transformation.
- Used SCD Type 1 and SCD Type 2 logics in the mappings and used a table to maintain the reject loads.
- Developed reject analysis mappings to store the reject records and reprocessed the reject load on daily bases
- Used both mapping variable and control table logic to extract only recent data from the staging tables and saved the session start time into control table using unconnected stored procedure
- Developed Mapplets and Reusable transformations to make them reusable where ever necessary to avoid metadata duplication
- Followed all standard rules according to the proposed architecture like Parameterized all connections in the session level instead of using hard coded connection names
- Developed Worklets to logically group dimension and facts session tasks
- Developed script to create a control file and also used event wait task for workflow dependencies
- Extensively worked on testing the developed mappings and fixed all kinds of data issues.
- Extensively worked on unit testing documents and code review documents for each and every mapping to keep a track of successful jobs
- Tuned Informatica mappings and sessions for better performance using various strategies based upon the analysis performed
- Developed a load analysis audit mapping to maintain the daily load status in Staging, EDW and DM for
- Created extraction maps in Informatica Power Exchange to source data from AS400 server.
- Created and reused the existing Unix Shell Scripts to schedule and automate the Informatica Mappings and also to automate the batch jobs. FTP the Files from different UNIX boxes.
- Work with the DBA and infrastructure team to implement the DDLs, refresh the data, create the DB links and refresh the materialized views, set up the environment and delete the log files in UNIX boxes
- Migrated and Modified other existing projects according to the new ETL architecture
Environment: Informatica power center 9.1, Informatica power exchange 9.1, Oracle 11g, Windows and UNIX.
Location: Albany, New York Apr'10-Feb\'12
Role: ETL Developer
Description: Capital District physicians collaborated to launch (CDPHP), a not-for-profit individual practice association (IPA) model health maintenance organization (HMO). More than 25 years later, CDPHP is still guided by its valued physician base and has evolved into an award-winning health benefits provider offering a variety of commercial and government-sponsored plans to the diverse communities it serves.
- Worked with Business Users to gather requirements and design data models for the Sales and Order Analysis marts.
- Used Data modeling tool ERWIN r8.0 for data requirement analysis and data modeling.
- Responsible for using Informatica parameters and variables in effectively developing mappings and change capture process.
- Worked with Informatica power center 8.6.1 to extract the data from SQL Server, Oracle into Data warehouse.
- Developed SQL queries and solved many debugging problems on the Oracle database side
- Worked with different information client tools like source designer warehouse designer, mapping designer, mapplet designer, Transformations, Informatica repository manager and Informatica server.
- Responsible for designing, developing and implementing the ETL process which include the incremental load strategy and full load.
- Responsible for creating user groups, privileges to the users using repository manager.
- Developed Informatica mappings for Type 1, Type 2 Slowly Changing Dimensions.
- Responsible for developing Mapplets to implement the reusable logic.
- Responsible for troubleshooting the Informatica tasks and performance related issues and data issues using the session logs, debugger andperformance indicators provided by Informatica.
- Responsible for creating shell scripts to invoke the Informatica workflows through command line.
- Extensively used transformations like Joiner, Expression,Connected/Unconnected lookups, and Filter, Aggregator, Rank, Update, Router, Stored Procedure, Normalizer and Sequence generator
- Created tables, synonyms, sequences, views, PL/SQL stored procedures and triggers.
- Mentoring other developers in technical and business aspects.
- Responsible providing all the documentation need for the user regarding ETL processes.
- Worked with reporting team in developing reports and dash boards using crystal reports.
Environment: Informatica 8.6.1, Oracle 10g, Sun Solaris, Rapid SQL, Windows and UNIX .
Location: Bellevue, Washington Feb'09-Mar'10
Role: ETL Developer
Description: T-Mobile USA is a national provider of wireless voice, messaging, and data services capable of reaching over 293 million Americans where they live, work, and play. Around the country, their Personal Coverage Checkgives customers a clear picture of their local coverage.
- Responsible for Business analysis and requirements gathering from the Business users.
- Involved in Designing Logical and Physical Data model using Erwin Tool.
- Involved in creating the design and Mapping specifications for the ETL process based on Functional specifications.
- Worked on Informatica power center client tools like Designer, Workflow Manager, Workflow Monitor, and Repository Manager.
- Extensively worked on Designer tools like Source Analyzer, Target designer, Mapping Designer, Mapplet Designer and Transformation Developer.
- Developed mappings using Informatica Power Center Designer to transform and load the data from source systems to Staging and Staging to target database.
- Worked on various types of transformations like Expression, Joiner, and Update strategy, Aggregator, Filter, Lookup and Router.
- Involved in designing, developing and testing of the ETL strategy to populate data from various source systems (Oracle, flat files, SQL Server) feeds using Informatica.
- Created Mapplets to reduce the development time and complexity of mappings and better maintenance.
- Developed Source to Staging and Staging to Dimension mappings.
- Developed Source to Staging and Staging to Fact mappings
- Developed staging jobs where in using data from different source formatted, Cleaned, Summarized, aggregated and transformed into data warehouse.
- Developed ETL design for initial and incremental loads.
- Implemented performance tuning logic on Targets, Sources, Mappings, Sessions to provide maximum efficiency and performance.
- Identifying and Removing Bottlenecks in order to improve the performance of Mappings and Workflows.
- Created stored procedures for dropping and recreating indexes on target tables.
- Involved in Enhancements and Maintenance activities of the data warehouse including performance tuning.
- Migrated mappings from Dev to Test and Test to Production.
- Performed Unit testing and created Unix Shell Scripts and provided on call support.
- Preparation of UTP (Unit Test Plan) with all required validations and test cases.
- Created sessions and workflows to run with the logic embedded in the mappings using Power center Designer.
- Created various tasks like sessions, decision, timer & control to design the workflows based on dependencies.
- Designed and developed UNIX shell scripts as part of the ETL process, automate the process of loading, pulling the data.
- Used workflow manager for session management, database connection management and scheduling of jobs.
- Involved in production support and done knowledge transfer to team members.
Environment: Informatica Power Center 8.6, SQL Server 2005/2008, Oracle 10g, Erwin 4.0, UNIX and Windows.
Location: Kansas City,Kansas Oct'07-Nov'08
Role: ETL Developer/Consultant
Description: Freight quote delivers a vast array of smartsolutionsfor a large and diverse customer base. These solutions enable customers to instantlyquote and comparefreight rates for hundreds of national and regional freight carriers. They currently have over 1,000 employee's nationwide and annualized revenues exceeding $500 million.
- Involved in analysis of source systems, business requirements and identification of business rules in building the Data marts/Data Warehouse using Kimball Methodologies.
- Interacted with users, documented user requirements, and translated requirements into system solutions and designed ETL processes and job schedule plans.
- Defined the technical requirements for ETL processes and created design document and source-to-target mapping document.
- Developed complex Mappings to load data from external sources like Flat Files, XML, Oracle, SQL Server, DB2 UDB using Informatica Power Center 7.1.4/6.2 designer to the Staging and Data Warehouse database.
- Created Mappings using transformations like Source Qualifier, XML Source Qualifier, Expression, Sorter, Aggregator, Filter, Lookup, Update Strategy, Sequence Generator, Normalizer, Stored Procedure, Joiner, Rank, Router and Transaction Control transformations.
- Used connected, unconnected lookup, static and dynamic cache to implement business logic and improve the performance.
- Used partitioning in Sessions to improve the performance of the database load time.
- Created Session Tasks, Worklets and Workflows comprising of different mappings to control the flow of data in to Data Warehouse based on the success of the previous session or worklets.
- Implemented Error Handling strategy for trapping errors from Date validation, Data type mismatch and Lookup failures.
- Implemented versioning by having date range fields and version flags to maintain full history data in Type II Slowly Changing Dimensions.
- Tested the existing mappings and redesigned the mappings to improve the performance and the efficiency of the design logic.
- Designed and developed various Analysis Reports using Business Objects with different type of charts.
- Performed various operations like scheduling, publishing and retrieving reports from corporate documents using the crystal reporting.
- Created sessions and used pre and post session properties to execute scripts and to handle errors.
Environment : Informatica Power Center 7.1.4/6.2, Oracle 9i, PL/SQL, SQL Server 2000, DB2 UDB, Teradata(Stored Procedures, Transact SQL), MS Access, Autosys, ERWIN, TOAD, Windows XP, Unix Shell Scripting.
Location: Chicago, IL. Oct'06-Aug'07
Role: ETL Developer
Description: ABN AMRO serves retail, private and commercial banking customers in the Netherlands and across the globe. Along with a comprehensive range of products and services, they offer in-depth financial expertise, extensive knowledge of numerous sectors and an international network supporting their customers' domestic and international operations.
- Creating ER diagrams & maintaining corresponding documentation for corporate data dictionary with all the attributes, table names and constraints using Erwin 3.5.5.
- Developing mappings using transformations like Source Qualifiers, Lookups (static/dynamic cache, connected/unconnected, persistent/non persistent cache), Stored Procedures (connected/unconnected, pre/post/normal), Routers, Filters, Aggregators, Normalizer, Unions, Update Strategies, Sorters.
- Extensively working with Mapplets, reusable transformations, mapping parameters, variables, parameter files for better coding standards developed in Mapping Designer.
- Creating workflows with sessions, worklets, event waits assignments, conditional flows, email and command tasks in Workflow Manager, maintained versioning in Visual Source Safe (VSS).
- Creating Checklists for coding, reviewing, bug logging, troubleshooting, testing and release for smooth functioning of the entire project.
- Working with various Oracle PL/SQL objects like Materialized views, Stored Procedures, Functions, Triggers, Sequence Generators and Partitions.
- Involved in performance tuning the ETL processes by monitoring the performance stats of the mappings and altering the designs accordingly.
- Maintaining the Repository Manager for creating Repositories, user groups, folders and migrating code from Dev to Test, Test to Prod environments.
Environment: Informatica PowerCenter 7.1, Oracle-9i, PL/SQL, SQL server, UNIX Shell Scripting, Erwin 3.5.5, TOAD.
Location: Noida, India May\'05-Aug\'06
Role: Oracle Developer
Description: Birla soft is a fast growing software consulting service where I was trained and worked on a project to implement customizable filtering engine for webmail. Electronic mail is popular and efficient communication medium. This filtering engine for web mail is a customizable application so that the user can enter the spam mail filter specifications like email id s, subject of email, keywords, size from which user can deny to receive emails. The main purpose of the project is to spam.
- Creating Data Structures. i.e., Tables & views and applying the referential integrity using SQL.
- Tested and debugged the modules along with the other modules to implement the business logic and involved in designing database schema.
- Tuned SQL queries for better performance.
- Managed oracle data base by adding, deleting and updating users and implemented user security by creating user profile roles..
- Created UML class diagrams based on the requirement.
Environment: Oracle 8i,SQL*PLUS, Windows.