- 8 years of experience in Data Architecture, Data Integration, Data Warehousing using ETL tool INFORMATICA Power Center 10.1/9.6.1/ 9.5.1/9.1.1 /8.6/8.1/7.1 (Source Analyzer, Warehouse Designer, Mapping/Mapplet Designer, Sessions/tasks, Worklets/Workflow Manager).
- Worked with integrating most of the databases such as Oracle, Teradata, My Sql, Sql server, DB2, Sybase and flat file, XML both Confidential source & target.
- Excellent knowledge in data warehouse development life cycle including Software Development Life Cycle (SDLC) on various platforms like WINDOWS /XP/2000, UNIX.
- Extensive knowledge in dimensional data modeling, star schema, snowflake schema, fact and dimension tables and process mapping using the top - down and bottom-up approach.
- Extensively worked in the Performance tuning of the SQL and ETL processes.
- Experience in Designing and developing mappings from various transformation logics like, Source Qualifier, Unconnected and Connected lookups, Router, Filter, Expression, Aggregator, Union, Joiner, java, Transaction Control and Update Strategy.
- Proficient in implementing Complex business rules by creating re-usable transformations, workflows/worklets and Mappings/Mapplets.
- Hands on experience in identifying and resolving performance bottlenecks in various levels like sources, targets, mappings, transformations and sessions
- Experienced in using Teradata Utilities like BTEQ, M-Load, F-Load, T-Pump, F-Export & TPT.
- Experience in doing fine tuning of Teradata scripts and writing Teradata scripts using BTEQ.
- Experience with Teradata Admin related work while supporting also using Explain plan effectively for performance related query tuning.
- Good experience on Teradata Data Recovery Options and Releasing different utility failures M-Load, F-Load & T-Pump).
- Good Experience with Teradata Index options, PPIs & Join Index.
- Worked with Different tables in Teradata - Multi Set, Set, Volatile, & Global tables.
- Developed slowly changing dimension (SCD) mappings of Type1, Type2 and Type3 (version, flag and time stamp).
- Implemented Change Data Capture (CDC) for extracting Delta data.
- Worked with Informatica Mapping Variables, Mapping Parameters and Parameter Files
- Scheduling the sessions to extract, transform and load data into warehouse database depending on the business requirements.
- Wrote SQL, PL/SQL, stored procedures for implementing business rules for adhoc data.
- Responsible for Unit testing of mappings and workflows.
- Experience in working with Unit testing & QA team for system testing.
- Good experience in working with several scheduling tools Maestro, Cron Tab, Autosys, Control M & Informatica Schedular.
- Extensively worked on Code Migration from Development, UAT, and Production.
- Involved in Code Deployment, Release Management & Change Management process and documentation.
- Heavily Involved in On Call Production Support for monitoring & supporting ETL Production jobs also involved in solving trouble tickets raised by End Users/Customers.
- Experience in Both Waterfall & Agile Methodology in implementing DWH projects and good experience with scrum process.
ETL Tools: Informatica Power Center 10.2/9.6.1/9.5.1 /9.1.1/8.6.1/8.1.1
Databases: Oracle 12c/11g/10g, MS Sql Server, Teradata 15/14/13/12, My Sql, DB2, Sybase, Flat File, XML.
Programming: Languages/ Scripting
Sql, PL/SQL, Unix Shell, Perl, C, Java: Tools: Toad, MS Office, SQL*PLUS, Sql Assistant, SQL Developer, AOTS, Service Now, Rally, Jira
Reporting Tools: Business Objects, Cognos, Dash Board Reporting
Operating Systems: Windows, Unix, Linux
Confidential, Deerfield, IL
Sr.ETL Informatica Developer/Data Analyst
- Worked on the requirements with Business Analyst and business users also involved in working with data modellers.
- Worked closely with data population developers, multiple business units and a data solutions engineer to identify key information for implementing the Data warehouses.
- Analyzed logical and physical data models for Business users to determine common data definitions and establish referential integrity of the system.
- Parsed high-level design spec to simple ETL coding and mapping standards .
- Used Informatica power center as an ETL tool to create source/target definitions, mappings and sessions to extract, transform and load data into staging tables from various sources .
- Written Teradata BTEQs & as well Informatica mappings using TPT to load data from Staging to base.
- Fine-tuned Teradata BTEQs as necessary using explain plan and collecting statistics.
- Written different loader scripts to laod data from Teradata to Teradata.
- Involved in Teradata Admin work like creating various tables dropping error tables while releasing loader locks and maintaining the space.
- Effectively used Primary Indexes, PPIs & Join Indexes as necessary .
- Developed ETL procedures to ensure conformity, compliance with standards and lack of redundancy, translating business rules and functionality requirements into ETL procedures.
- Developed and tested all the backend programs, Informatica mappings and update processes
- Populated the Staging tables with various Sources like Flat files (Fixed Width and Delimited), Oracle, My Sql, and Informix .
- Created mappings using various Transformations such as Source qualifier, Aggregator, Expression, lookup, Router, Filter, Rank, Look up, Sequence Generator, java and Update Strategy .
- Created and used the Normalizer Transformation to normalize the flat files in the source data.
- Extensively built mappings with SCD1, SCD2 implementations as per requirement of the project to load Dimension and Fact tables.
- Used Evaluate expression options to validate and fix the code using Debugger tool while testing Informatica code.
- Handled initial (i.e. history) and incremental loads in to target database using mapping variables.
- Used Debugger to debug mappings and created breakpoints for better analysis of mappings.
- Worked with Workflow Manager for maintaining Sessions by performing tasks such as monitoring, editing, scheduling, copying, aborting, and deleting.
- Worked on performance tuning Confidential both the Informatica level and Database as well by finding the bottlenecks.
- Worked on Maestro job scheduling and Unix Scripting.
- Developed UNIX shell scripts to run the pmcmd functionality to start and stop sessions, batches and scheduling workflows.
- Involved in migrating the ETL Code to different environments from Dev to UAT and then to Produciton with ETL Admins.
- Performed Unit testing and created unit test plan of the code developed and involved in System testing and Integration testing as well. Coordinated with the testers and helped in the process of integration testing.
- Heavily involved in Production support on rotational basis and supported DWH system using the ticketing tool for the issues raised by Business users.
- Solved Different Severity Tickets based on SLAs for data issues raised by Customers using trouble ticket system
- Experience in working with reporting team in building collection layer for reporting purpose.
Environment: Informatica Power Center 10.2/9.6.1/9.5.1, Oracle, My Sql, Informix, Teradata 14/13/12, Flat File, Cognos, Rally, Putty, Maestro/Informatica Scheduler, UNIX, Windows.
Confidential, Richardson, TX
- Involved in Business analysis and requirements gathering along with Business Analyst.
- Extensively worked with Upstream and Downstream teams to coordinate the process and work.
- Creating the design and technical specifications for the ETL process of the project.
- Managed offshore team with 4 resources for development/Support in Informatica.
- Assisted in creating fact and dimension table implementation in Star Schema model based on requirements.
- Worked on Informatica Power Center tool - Source Analyzer, Target designer, Mapping & Mapplet Designer and Transformation Developer along with Workflow Manager & Monitor.
- Extracted data from heterogeneous sources like Oracle, My Sql, Sybase and loaded in Teradata.
- Developed complex mappings using Informatica Power Center Designer to transform and load data from different sources and loaded in to Teradata using Teradata Utilities in combination with Informatica.
- Extensively used various types of transformations such as Expression, Joiner, Update strategy, Aggregator, Filter, Expression, Router, Sequence Generator and Lookup.
- Developed several Mappings and Mapplets using corresponding Sources, Targets and Transformations.
- Implemented ETL performance tuning logic on targets, sources, mappings, sessions to provide maximum efficiency and performance.
- Implemented complex mappings such as Slowly Changing Dimensions (SCD - Type 1 & Type 2).
- Implemented Change data capture (CDC) using Mapping Variable Concept.
- Involved in Teradata performance tuning for jobs running in Production for various failures while supporting.
- Written and did performance tuning of Teradata scripts - BTEQ for stage to base loads.
- Used Teradata utilities extensively - BTEQ, M-LOAD, F-LOAD, F-Export & T-PUMP.
- Used Collect Stats extensively to improve the performance of heavy and regular loads.
- Involved in code migration & deployment activities for both newly developed, enhanced code also production fixes.
- Extensively worked with Cron tab scheduler for scheduling Informatica & Teradata jobs.
Environment: Informatica Power Center 9.5.1/9.1.1, Oracle, My Sql, Flat File, Sybase, Teradata 14, AOTS, Cron Tab, Unix, Windows.
Confidential, Charlotte, NC
- Prepared a detailed Technical Design Document after analyzing the Functional Specifications and Architectural Diagram. Technical Design Document captured all functional and technical requirements.
- Worked with different sources like Oracle, SQL Server, My Sql, DB2 UDB and flat files.
- Performed Data Cleansing before loading it on data warehouse.
- Developed numerous Complex Informatica Mappings, Mapplets and reusable Transformations.
- Designed and created complex source to target mappings using various transformations inclusive of but not limited to Aggregator, Joiner, Filter, Source Qualifier, Expression and Router Transformations.
- Extensively used Lookup Transformation and Update Strategy Transformation while working with Slowly Changing Dimensions.
- Created PL/SQL Procedures for data extractions, transformation and loading.
- Developed Workflows with various Tasks and Parameter files.
- Involved in Performance Tuning by determining bottlenecks Confidential various points like targets, sources, mappings and sessions. This led to better session performance.
- Profiled and cleansed data by using tools i.e. Informatica Data Quality (IDQ) and Informatica Data Explorer (IDE).
- Loaded data into Teradata by using BTEQ batch scripts.
- Used Target Load Order Plan and Constraint based loading for loading data appropriately into multiple Target Tables.
- Modified pre-existing scripts and used them to automate scheduling process using Autosys.
- Prepared Test Cases for testing interfaces which were used by QA team.
- Extensively used Informatica Metadata Reporter to analyze and manage repository efficiently.
- Performed Code review to ensure that ETL development was done according to client’s ETL standard and ensured that ETL best practices were followed.
Environment: Informatica Power Center 9.5,1, Oracle 10g, SQL Server 2005, Teradata 12, Toad, PL/SQL, Linux, Erwin and Windows.
Senior Informatica Developer
- Gathered business scope and Technical requirements.
- Worked with Power Center Designer tools in developing mappings and mapplets to extract and load the data from flat files and Oracle (source) to Oracle (target).
- Extensively used All Fusion-Erwin for Logical / Physical data modeling and Dimensional data modeling, and designed Star join schemas.
- Created different transformations for loading the data into targets using various transformations like Source Qualifier, Joiner transformation, Update Strategy, Lookup transformation, Rank Transformations, Expressions, Aggregator, and Sequence Generator
- Created reusable transformations and mapplets to use the transformation logic in multiple mappings
- Worked on Repository manager to create and manage user profiles.
- Scheduled Sessions on the Informatica Server using Informatica workflow Manager
- Implemented performance tuning logic on targets, sources, mappings, sessions to provide maximum efficiency and performance.
- Extensively used Source Qualifier, Aggregator, Filter, Expression, Lookup, Update Strategy, Router and Joiner transformation.
- Used Procedure Transformations to invoke Oracle PL/SQL Procedures.
- Implemented slowly changing dimensions according to the requirements, Partitioned sessions, modified cache/buffers and tuned transformations for better performance.
- Wrote Data loading stored procedures, functions using PL/SQL from Source systems into operational data storage
- Involved in fine Tuning queries for better performance (includes indexing and pre-joining and materializing views).
- Created views and altered some of the Dimensional tables to satisfy their Reporting needs.
Environment: Informatica Power Center 9.1.1/9.5.1 (Repository Manager, Admin Console, Designer, Workflow Manager, Workflow Monitor), Oracle, Teradata 14, Sql Assistant, Toad, UNIX, Windows
ETL Informatica Developer
- Worked with various active transformations in Informatica Power Center like Filter Transformation, Aggregator Transformation, Joiner Transformation, Rank Transformation, Router Transformation, Sorter Transformation, Source Qualifier, and Update Strategy Transformation
- Extensively worked with various Passive transformations in Informatica Power Center like Expression Transformation, Sequence Generator, and Lookup Transformation
- Worked with Informatica Power Center client tools like Repository Manager, Designer, Workflow Manager and Workflow Monitor
- Worked with Source Analyzer, Transformation Developer and Mapping Designer
- Worked with Task Developer, Worklet Designer and Workflow Designer in the Workflow Manager
- Responsible for extracting data from Oracle DB and Flat files
- Responsible for the Data Cleansing of Source Data using LTRIM,LAST DAY and RTRIM operations of the Expression Transformation
- Responsible for Performance in Informatica Power Center Confidential the Target Level, Source level, Mapping Level, Session Level, and System Level
- Extensively worked with both Connected and Un-Connected Lookups
- Extensively worked with Look up Caches like Static Cache, and Dynamic Cache to improve the performance of the lookup transformations
- Extensively used Normal Join, Full Outer Join, Detail Outer Join, and Master Outer Join in the Joiner Transformation
- Extensively worked with Incremental Loading using Parameter Files, Mapping Variables and Mapping Parameters
Environment: Informatica Power Center 9.1, SQL, Flat Files, Oracle, Toad & UNIX.
- Imported data from various Sources transformed and loaded into Data Warehouse Targets using Informatica Power Center 7x/8x
- Was involved in Identifying slowly changing dimensions (SCD) and creating mapping using wizards in Designer.
- Transformation is done using Informatica tool from MS-Access, Oracle database To Oracle database.
- Sophisticated transformations rules are implemented using Informatica features like Filter, Aggregation, Joiner, Expression, Lookup, Update Strategy, Stored Procedure and Source Qualifier. Wrote PL/SQL packages, Stored Procedures to invoke several ETL functionalities for aggregation and summation.
- Developed several Mappings and Mapplets using corresponding Source, Targets and Transformations.
- Responsible for documenting the entire process.
- Prepared the design specifications, detailed design document for technical specifications.
Environment: Informatica 8x, ORACLE 9i, Windows.