- Result - driven IT Professional with referable 12+ years of expertise in Analysis, Design, Development, Testing and Implementation of business application systems for Airline/Financial Sectors using Data Warehouse/Data Mart Design, ETL, OLAP, BI, Client/Server applications.
- Proficient in all phases of the Software Development Life Cycle (SDLC) In Data Warehousing.
- Good knowledge on web services like SOAP and REST based API’s
- Expert in coding SQL, PL/SQL Procedures, Functions, Triggers and Packages.
- Experience in ETL Design, Entity-Relationship Modelling and Dimensional Modelling.
- Good at developing Slowly Changing Dimension Mappings of Type I, and II.
- Extensive experience in creating Complex Mappings and reusable components like reusable Transformations, Mapplets, Worklets and control tasks to implement reusable business logic.
- Effective in collaborating with various business and technical teams to gather requirements around data quality rules and propose the optimization of these rules if applicable, then design and develop these rules with IDQ.
- Expertise in designing the mappings using Source qualifier, Joiner, Aggregator, Expression, Lookup, Router, Filter, and Update Strategy transformations and Mapplets to load data into the target involving slowly changing dimensions.
- Proficient in performance analysis, monitoring and SQL query tuning using EXPLAIN PLAN, Collect Statistics, Hints and SQL Trace both in Teradata as well as Oracle.
- Strong experience in ETL workflows for multiple data sources like flat files, XML, Teradata, Oracle, SQL Server, MySQL.
- Experience with tools like SQL Server Management Studio and SQL Server Reporting Services(SSRS).
- Good at performance and tuning at source, Target levels using Indexes, Hints and Partitioning in DB2, ORACLE and Informatica.
- Proficient in building Dimensions, Bridges, Facts, and Star Schemas, Snow Flake (Extended Star) Schemas.
- Expertise in Unit testing, System testing to check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.
- Expert in using Erwin, MS Visio, Oracle Designer to design process flow diagrams.
- Extensive experience in Administration/maintenance of Informatica Power Center including Installation, upgrading and patching.
- Experience in Master Data Management concepts, methodologies and ability to apply this knowledge in building MDM solutions.
ETL Tools: Informatica Power Center 9.6/9.5/9.1/8.6/8.5/8.1/7.1
Data Quality Tools: Informatica Data Quality 9.6/9.5, Informatica MDM
Operating Systems: Windows - NT/2000/XP/7/10, UNIX
Reporting Tools: SQL Server Reporting Services (SSRS), BI Publisher
DBMS: Oracle 12c, 11g, 10g, 9i, SQL Server 2005/2008, Teradata 13, 12, 11
Data Modelling Tools: Erwin, Oracle Designer 10g, MS Visio 2010
Database Tools: SQL*Loader, Toad, SQL Developer, PL/SQL Developer
Languages: SQL, PL/SQL, UNIX Shell Scripting
Confidential - Lake Forest, CA
Sr. ETL Informatica Developer
- Design, develop and maintain Enterprise Data warehouse (EDW), ODS and MDM systems.
- Enhanced processes by applying Informatica performance improvement techniques to maximize utilization and performance.
- Design, develop and prototyping new system requirements utilizing Informatica, Oracle, EDQ, UNIX Shell Scripts and other tools.
- Develop review UNIX shell scripts, automate ETL processes, perform database modelling, design and implement data warehousing, data marts, Star, Snowflake Schema Modeling, dimensional modeling on OLTP and OLAP concepts.
- Write Pre SQL, Post SQL scripts/commands at session level, write queries, stored procedures and functions.
- Perform daily project functions with a focus on meeting the business objectives on time in rapidly changing work environment.
- Enhance/re-architect existing components to satisfy new performance and scalability requirements.
- Design &Develop Informatica mappings, sessions, workflows and ETL loads, build PLSQL code, UNIX Shell scripts for the requirements received as part of value adds.
- Automate/ Tune existing code base of ETL objects for performance improvement.
- Performance Tune SQL Queries and information code base to improve overall application performance.
- Formulate, define scope &objectives through fact-finding by interfacing between the business and the development team, store, retrieve as well as manipulate data for analysis of system capabilities and requirements.
- Review prototypes develop iterative revisions, make recommendations &design ETL architecture.
- Interact with multiple teams combined with translate reported problems/issues into JIRA items to implement to meet enterprise standards.
- Maintain highest levels of software quality along with stability through continuous improvement of the SDLC.
- Built maintain design standards along with best practices to ensure the team adhere to these standards.
- Designed and developed Custom PL/SQL Packages to accommodate complex solutions within the warehouse.
- Extensive experience in Oracle, DB2, Teradata, MySQL, SQL Server to be able to write complex queries to evaluate data.
- Developed BI Publisher reports as per the business needs and provided guidance to end users.
- Developed test cases and performed Unit Testing and Integration Testing of the dashboards.
- Used using performance tuning methods like Explain Plan, Trace, DBMS PROFILIER. Managed the Metadata associated with the ETL processes used to populate the Data Warehouse.
- Implemented complex business rules in Informatica Power Center by creating re-usable transformations, and robust Mapplets.
- Used various transformations like Source Qualifier, Joiner, Lookup, SQL router, Filter, Expression and Update Strategy.
- Implemented slowly changing dimensions (SCD) for some of the Tables as per requirements.
- Implemented performance tuning of Sources, Targets, Mappings and Sessions by identifying bottlenecks and used Debugger to debug the complex mappings and fix them.
- Improved session Performance by enabling property incremental aggregation to load incremental data into target table.
- Worked with Functional team to make sure required data has been extracted and loaded and performed the Unit Testing and fixed the errors to meet the requirements.
- Copied/Exported/Imported the mappings/sessions/ Worklets /workflows from development to Test Repository and promoted to Production.
- Used Session parameters, Mapping variable/parameters and created Parameter files for imparting flexible runs of workflows based on changing variable values.
- Developed metadata repository using OBIEE Administration tool in Physical, Business Model and Mapping, and Presentation Layer.
- Involved in Dimensional modelling (Star Schema) of the Data warehouse and used Erwin to design the business process, dimensions and measured facts.
- Worked with Static, Dynamic and Persistent Cache in lookup transformation for better throughput of Sessions.
- Used PMCMD command to automate the Power Center sessions and workflows through UNIX.
- Converted old discoverer reports to SSRS and Oracle BI reports.
- Apart from Projects and other activities, I worked on support tickets which was the primary objective of the role.
Environment: : Informatica PowerCenter 9.6.1, 9.1.1, Oracle 12c, 11g, Teradata 15.10, SQL Server, SQL, PL/SQL, SQL Server Reporting Services (SSRS), Oracle BI Publisher, Informatica IDQ
Confidential - Danbury, CT
ETL Informatica Developer
- Analyze client’s business requirements and processes through document analysis, interviews, workshops, and workflow analysis.
- Used Informatica ETL to load data from flat files, which includes fixed-length as well as delimited files and SQL Server to the Data mart on Oracle database.
- Used reverse engineering in Erwin 4.x to understand the existing data model of the data warehouse.
- Worked with creating Dimensions and Fact tables for the data mart.
- Created Informatica mappings, sessions, workflows, etc., for loading fact and dimension tables for data mart presentation layer.
- Implemented SCD (Slowly Changing Dimensions) Type I and II for data load.
- Developed mapping using parameters and variables.
- Created complex workflows, with multiple sessions, Worklets with consecutive or concurrent sessions.
- Used Timer, Event Raise, Event Wait, Decisions, and Email tasks in Informatica Workflow Manager.
- Used Workflow Manager for creating validating, testing and running sequential and concurrent batches.
- Implemented source and target based partitioning for existing workflows in production to improve performance so as to cut back the running time.
- Analyzed workflow, session, event and error logs for trouble shooting Informatica ETL process.
- Worked with Informatica Debugger to debug the mappings in Informatica Designer.
- Involved in creating test plans, test cases to unit test Informatica mappings, sessions and workflows.
- Involved in migrating Informatica ETL application and Database objects through various
- Communicate client’s business requirements by constructing easy-to-understand data and process models.
- Draft and maintain business requirements document(BRD) and align them with functional and technical requirements document (FSD and TDD).
- Worked on Informatica PowerCenter tools- Designer, Repository Manager, Workflow Manager, and Workflow Monitor.
- Parsed high-level design specification to simple ETL coding and mapping standards.
- Involved in building the ETL architecture and Source to Target mapping to load data into Data warehouse.
- Extracted the data from the flat files and other RDBMS databases into staging area and populated onto Data warehouse.
- Involved in migrating Informatica ETL application and Database objects through various environments such as Development, Testing, UAT and Production environments.
- Developed SQL Server Stored Procedures, Tuned SQL Queries (using Indexes and Execution Plan).
Environment: : Informatica PowerCenter 8.5/8.1, Oracle 10g/9i, MS SQL Server 2008/2005, Business Objects XI r2/6.x, SQL, PL/SQL, UNIX, Shell Scripts
Confidential - Tulsa, OK
ETL Informatica Developer
- Involved different phases of SDLC from requirement, design, development, testing.
- Identified performance issues in existing sources, targets and mappings by analyzing the data flow, evaluating transformations and tuned accordingly for better performance.
- Extraction, Transformation and Loading of data were carried out from different sources like Flat files, SQL Server Involved in creating and designing mappings and Mapplets using Expression, Filter, Router, Joiner, Lookup, Update Strategy, Stored Procedure, Union and other transformations
- Developed the mappings as per the ETL specifications.
- Execute the workflow as per the business rules.
- Prepared UNIX Shell Scripts and these shell scripts will be scheduled in AUTOSYS for automatic execution at the specific timings.
- Coordinate with deployment and support resources to ensure that solutions gets implemented smoothly into QA and production environments
- User support during application stabilization.
- Perform project management and status reporting related activities for onsite/offsite project team.
Environment: : Informatica PowerCenter 7.1 Oracle 9i, Teradata 10, SQL, Teradata SQL Assistant, Unix, Erwin