- 9 + Years of experience in IT industry including expertise in design and development of Relational Database Systems, Design and Development of Data Warehousing using Informatica Power Center ETL tool.
- Extensively worked on data warehousing concepts using various ETL tools like Informatica and SSIS.
- Experience in ETL tool, INFORMATICA (V 7.x, V 8.x, V 9.x & V 10.x). Experience in Using Repository Manager, Designer and Workflow Manger, Workflow Monitor Tools in Informatica Power Center and Informatica IDQ, MDM and other Data tools.
- Extensive understanding of Transactional and Dimensional Data Modeling, Data Warehouse concepts and Designing Star schemas & Snowflake Schemas for OLAP Systems.
- Developed Slowly Changing Dimension mappings of Type1, Type2 and Type3 (version, flag and time).
- Experience in Trouble shooting and implementing Performance tuning at various levels such as Source, Target, Mapping, Session and System in ETL Process.
- Proficient in IDQ development around data profiling, cleansing, parsing, standardization, validation, matching and data quality exception monitoring and handling.
- Strong experience in developing complex mappings using transformations like Unconnected and Connected Lookups, Router, Aggregator, Sorter, Joiner, Transaction Control, Filter and Update Strategy.
- Expertise in data extraction from Flat Files (XML, excel, csv, fixed width, delimited, txt), and Oracle server.
- Good experience in handling terabytes of data and good at analyzing the data related issues.
- Extensively worked on oracle warehouse builder tool. Supported customizations and testing.
- Created various tasks like sessions, and workflows in the workflow manager to test the mapping during development.
- Hands on experience on SQL and PL SQL concepts. Working experience on oracle server.
- Extensively worked on performance tuning areas. Good knowledge on partitions, indexes, table space related issues.
- Good at analyzing AWR (Automatic Workload Repository) report and good in providing suggestions on SQL tuning techniques using explain plans.
- Worked with Teradata including loading targets, executing SQL and troubleshooting and optimization of ETL components.
- Good working experience on partition exchange load process (offline data loads) in oracle.
- Strong experience working with Microsoft business intelligence including SSIS, SSAS, SSRS.
- Implement UNIX shell scripts for scheduling various tasks.
- Strong Knowledge in ETL design document and mapping sheet creation.
- Good in scheduling jobs and administration activities.
- Proficient in understanding Functional Requirements and Design documents.
- Good in creating HLD and other design documents as needed.
- Extensive expertise in designing, developing and executing test scenarios and test cases.
- Possess good communication skills and effective client communication.
- Strong problem solving & technical skills coupled with confident decision making for enabling effective solutionsleading to high customer satisfaction.
- Self - learner and highly self-motivated attitude.
- Knowledge of Change Data Capture (CDC) using Oracle 9i and Informatica Power Exchange.
- Followed effectively industry standards of HIPAA, ANSI837 and PHI concepts
ETL/DW Tools: Informatica Power Center 10.2/10.1/9.6/9.5.1/9.1/9.0/8.6.1/8.6/8.5.1/8.1/ 7.1.3/7.1.1/6.2 (Source Analyzer, Target Designer, Mapping Designer, Mapplet Designer, Transformation Developer, Workflow Manager, Workflow Monitor), IDQ, MDM, SQL Server Integration services.
Data Modeling Tools and Reporting tools: TOAD, Rapid SQL, Teradata, SSRS, Tableau server, Crystal Reports 9.0/8.5.
DBMS: Oracle 11g/10g/9i/8i, MS SQL Server 2008/2008R2/2012/2014, Teradata.
SQL, PL/SQL, T: SQL, HTML, XML, C, C#, ASP.NET
Operating Systems: Windows Server 2003/2008/2012 , UNIX, Linux.
Confidential, Seattle, WA
- Worked heavily with Business Analyst to understand the business and to design the architecture of the dataflow.
- Code, test, modify, debug, document, and implement ETL workflows/mappings using Informatica PowerCenter tools.
- Designed the metadata tables and created mappings to populate the same.
- Created mappings to perform the tasks such as cleaning the data and populate that into staging tables, populating the Enterprise Data Warehouse by transforming the data into business needs & populating the Data Mart with only required information.
- Created reusable transformations to clean the data, which were used in several mappings.
- Tested stored procedures, functions and packages in PL/SQL
- Extracted data from various sources like MS SQL Server 2008, DB2, flat files, Excel spreadsheets, Oracle and XML files and loaded into the oracle database.
- Worked on Data Profiling using IDE- Informatica Data Explorer and IDQ - Informatica Data Quality to examine different patterns of source data. Proficient in developing Informatica IDQ transformations like Parser, Classifier, Standardizer and Decision.
- Experience with creating profiles, rules, scorecards for data profiling and quality using IDQ.
- Involved in Production Support by performing Normal Loads, Bulk Loads, Initial Loads, Loads, Bulk Loads, Initial Loads, Incremental Loads, Daily loads and Monthly loads.
- Responsible for monitoring scheduled, running, completed and failed sessions.
- Involved in debugging the failed mappings and developing Error Handling method. Worked with Informatica Administrator to setup project folders in development, test and production environments.
- Extensively used various Informatica Power center and Data quality transformations such as - source qualifier, aggregator, update strategy, expression, joiner, lookup, router, sorter, filter, web services consumer transformation, XML Parser, address validator, comparison, consolidation, decision, parser, standardizer, match, merge to perform various data loading and cleansing activities.
- Worked in the UNIX Shell Scripts for Informatica Pre-Session, Post-Session and Autosys scripts for scheduling the jobs (workflows)
- Worked with the Quality Assurance team to build the test cases to perform unit, Integration, functional and performance Testing.
- Used Informatica Version Control for checking in all versions of the objects used in creating the in creating the mappings, workflows to keep track of the changes in the development, test and production environment.
Environment: Informatica Power Center 8.6/9.1/10.1/10.2 , MDM, IDQ Autosys, Teradata, Oracle 10g,SQL Assistant, SQL Server 2008, Postgres, PL/SQL, SQL, Oracle, UNIX, Shell scripts.
Confidential, Dallas, TX
- Created mappings and sessions to implement technical enhancements for data warehouse by extracting data from sources like Delimited Flat files.
- Development of ETL using Informatica 8.6, 9.1, 9.5.
- Applied slowly changing dimensions like Type 1 and 2 effectively to handle the delta Loads.
- Prepared various mappings to load the data into different stages like Landing, Staging and Target tables.
- Used various transformations like Source Qualifier, Expression, Aggregator, Joiner, Filter, Lookup, Update Strategy Designing and optimizing the Mapping.
- Proficient in Data Warehousing clustered environment using Informatica PowerCenter Client tools - Mapping Designer, Repository manager, Workflow Manager/Monitor and Server tools - Informatica Server, Repository Server manager.
- Developed Workflows using task developer, worklet designer, and workflow designer in Workflow manager and monitored the results using workflow monitor.
- Created various tasks like Session, Command, Timer and Event wait.
- Modified several of the existing mappings based on the user requirements and maintained existing mappings, sessions and workflows.
- Created mappings and mapplets to transform the data according to the business rules using Informatica PowerCenter.
- Tuned the performance of mappings by following Informatica best practices and applied several methods to get best performance by decreasing the run time of workflows.
- Prepared SQL Queries to validate the data in both source and target databases.
- Worked on TOAD and Oracle SQL Developer to develop queries and create procedures and packages in Oracle.
- Worked extensively on PL/SQL as part of the process to develop several scripts to handle different scenarios.
- Created Test cases for the mappings developed and then created integration Testing Document.
- Prepared the error handling document to maintain the error handling process.
- Automated the Informatica jobs using UNIX shell scripting.
- Closely worked with the reporting team to ensure that correct data is presented in the reports.
- Interaction with the offshore team daily on the development activities.
Environment: Informatica Power Center 8.6.1, Oracle 10g/ 9i, MS-SQL Server, Toad, HP Quality Center, Windows XP and MS Office Suite, SSIS
Confidential, Brentwood, NY
- Worked extensively with Informatica 9.6 for creating workflows and ETL Processes
- Worked heavily on Financial data and managing financial workflows
- Developed Daily Files Load Mechanism that we are receiving from different Clients and loaded into our Transactional Database.
- Responsible for data analysis and data modeling based on the business Process/performance Improvement of the transactional Database.
- Created ETL Mappings, sessions and workflows using Informatica Power Center to move Data from multiple sources like XML, Flat files, SQL Server, and Oracle into a common target such as Data Marts and Enterprise Data warehouses. Used direct and indirect flat files (delimited and fix width) as a source of data.
- Write UNIX Shell scripts and automate/monitor daily/weekly/monthly scheduled jobs.
- Extensively involved in debugging the Existing GreenPlum Process.
- Directly Involved in Data Migration from GreenPlum to SQL Server.
- Implemented Slowly Changing Dimensions - Type I & II in different mappings as per the requirements.
- Worked with all Power center tools like Mapping Designer, Workflow Manager and workflow monitor.
- Extensively worked with flat file and data validation.
- Worked on different tasks like events raise, event wait, decision, e-mail, command, Work lets, Assignment, Timer and scheduling of the workflow.
- Created strong SQL Queries and created stored procedures and packages
- Worked on performance tuning of SQL query and workflow process.
- Extensively used transformation like Expression, filter, router, joiner, union, update strategy, Seq Generator, etc.
- Parameterized the process for the dynamic loads.
- Designed and developed the DDL and DML statements, store procedure as per the business requirements.
- Used Scheduling tool like Autosys.
- Worked directly with Business and UI team for data validation and data population.
Environment: SQL Server-2012\2014, Putty, Auto Sys, Informatica Power Center, GreenPlum 1.16, HP Quality Center, UNIX, Outlook
Confidential, Seattle, WA
- Member of warehouse design team assisted in creating fact and dimension tables based on specifications provided by managers.
- Load operational data from Oracle, SQL Server, flat files, Excel Worksheets into various data marts.
- Designed and created complex source to target mappings using various transformations inclusive of but not limited to Aggregator, Look Up, Joiner, Source Qualifier, Expression, Rank, Union, Normalizer, Sequence Generator, and Router Transformations
- Created complex mappings that involved implementation of Business Logic to load data in to staging area
- Used Informatica reusability at various levels of development.
- Developed mappings/sessions using Informatica Power Center 8.6 for data loading.
- Building Reports according to user Requirement by using SSRS.
- Implemented effective date range mapping (Slowly Changing dimension type2) methodology for accessing the full history of accounts and transaction information.
- Design complex mappings involving constraint-based loading, target load order.
- Use debugger in identifying bugs in existing mappings by analyzing data flow, evaluating transformations and Create mapplets that provides reusability in mappings.
- Involve in enhancements and maintenance activities of the data warehouse including performance tuning, rewriting of stored procedures for code enhancements.
- Designed workflows with many sessions with decision, assignment task, event wait, and event raise tasks, used Informatica scheduler to schedule jobs.
- Used workflow monitor to monitor the jobs, reviewed error logs that were generated for each session, and rectified any cause of failure.
- Worked on Informatica Schedulers to schedule the workflows.
Environment: Informatica Power Center 6.2, Oracle, Business Objects 6.x, Windows 2000, SQL Server 2000, Microsoft Excel, SQL * Plus, MS SQL, SSRS
Confidential, Portland, OR
ETL Informatica Developer
- Designed and developed Complex Mappings, to store the data in EDW on SQL Server in Normalized form, Error handling, and implementation of business rules.
- Extensively Used Informatica Power center as an ETL tool to extract data from legacy source systems to Target system. Extracted data from multiple operational sources for loading into staging area, Data warehouse, Data Marts using SCD's (Type 1 and Type 2) loads.
- Implemented CDC for mappings to capture the changes and preserve history.
- Involved in ETL load process and control framework for the warehouse.
- Extensive experience in creation of ETL mappings and transformations to reflect business rules using Informatica Power Center to move data from multiple sources into target area, source systems are mainly relational tables, and flat files.
- Worked with (Fact and Dimensional tables) OLAP (Star schema, Snowflake schema).
- Extensively worked with Debugger using breakpoints and various test conditions to test the logic and the validity of the data moving through the mappings.
- Worked with Variables and Parameters in the mappings to pass the values between sessions.
- Used various transformations like Source Qualifier, Lookup (connect, unconnected), Update Strategy, Router, Filter, Sequence Generator, and Joiner on the extracted source data according to the business rules and technical specifications.
- Performance Tuning of SQL Queries and ETL Mappings.
- Responsible for monitoring all the sessions that are running, scheduled, completed and failed Debugged the mapping of the failed session.
- Migration of mappings, sessions and workflows from Development to Stage and Production environments.
- Code validation of the mappings migrated to QA and production environment.
- Performed Unit tests on the sessions and recommended measures to improve the performance of sessions.
- Responsible for performance tuning ETL process to optimize load and query Performance.
- Experience in writing Batch/ Shell scripts handle files and informatica jobs from AIX UNIX.
- Proactive team player with the demonstrated ability to multi-task and prioritize in a fast-paced professional environment.
Environment: Informatica Power center 9.5.1, SQL Server 2008, 2012, flat files, UNIX, AIX, ERwin 4.0.