Data Integration Informatica

Krishnaveni Murugaiah

**********@*****.***/******.*******@*****.***

LinkedIn: https://www.linkedin.com/in/krishnaveni-murugaiah-3098771a/

Mobile: +1-302-***-****

PROFESSIONAL SUMMARY

** ***** of IT experience with extensive Data Modelling and Data Warehousing implementations across various industries.

Develop, and maintain ETL processes using Informatica PowerCenter to meet business requirements.

Collaborate with data architects and business analysts to understand data integration needs and translate them into technical specifications.

Perform data profiling, data cleansing, and transformation tasks to ensure data accuracy and consistency.

Optimize and tune ETL processes for performance and scalability.

Troubleshoot and resolve issues related to data integration, data quality, and performance.

Work closely with database developers and administrators to ensure seamless integration between ETL processes and databases.

Create and maintain documentation for ETL processes, including technical specifications, data mappings, and process flow diagrams.

Involved in developing ETL mappings and scripts using Informatica Power Center 10.x/9.x/8.x, IBM Cognos, UNIX Shell scripting.

Good experience in gathering the requirement by interacting with Business and documenting the same as User Stories in Jira.

Experience in preparing End-to-End process flow diagram.

Good exposure in Agile Development Project and Agile Processes.

Good exposure in preparing process flow diagrams and business flows in MS Office Tools.

Experience in all the phases of Data warehouse life cycle involving Requirement Analysis, Design, Coding, Testing, and Deployment.

Expertise in Business Model development with Dimensions, Hierarchies, Measures, Partitioning, Aggregation Rules, Time Series, Cache Management.

Extensively worked on the ETL mappings, analysis and documentation of OLAP reports requirements. Solid understanding of OLAP concepts and challenges, especially with large data sets.

Strong knowledge of Entity-Relationship concept, Facts and dimensions tables, slowly changing dimensions and Dimensional Modeling (Star Schema and Snowflake Schema) using Kimball Methodology.

Experience in integration of various data sources like Oracle, Teradata, DB2, SQL server and MS access and non-relational sources like flat files into staging area.

Expertise in full life cycle Business Intelligence implementations and understand all aspects of an implementation project using OBIEE.

Experience in creating Reusable Transformations (Joiner, Sorter, Aggregator, Expression, Lookup, Router, Filter, Update Strategy, Sequence Generator, Normalizer and Rank) and Mappings using Informatica Designer and processing tasks using Workflow Manager to move data from multiple sources into targets.

Experience in creating Reusable Tasks (Sessions, Command, Email) and Non-Reusable Tasks (Decision, Assignment, Worklets, Control).

Experienced in UNIX work environment, file transfers, job scheduling and error handling.

Worked on Performance Tuning, identifying, and resolving performance bottlenecks in various levels like sources, targets, mappings and sessions.

Experienced in developing Dimensional Hierarchies, Level Based Measures, and adding multiple sources to business model objects.

Experienced in configuring and set up of Biosecurity using LDAP and External Database Tables and configuring object level and database level security.

Experience in writing, testing and implementation of the PL/SQL triggers, stored procedures, functions, packages.

Involved in Unit testing, System testing to check whether the data loads into target are accurate.

Proficient in interaction with the business users by conducting meetings with the clients in Requirements Analysis phase.

Extensive functional and technical exposure. Experience working on high-visibility projects.

Assign work and provide technical oversight to onshore and offshore developers

Excellent analytical/ communication skills and good team player.

EDUCATION

M. Tech in VLSI Design, Sathyabhama University, Tamil Nādu, India, April 2007

Bachelors in engineering, B.E., EEE, Anna University, Tamil Nādu, India April 2005

PROFESSIONAL EXPERIENCE

Company: Qlytix, Inc

Client: IBM

Role: Sr.ETL Developer-Informatica

Feb 2022 – April 2023

Project Objective:

The goal of this project was to facilitate a seamless transition for our Telecom client from a legacy platform to a state-of-the-art New Platform. The project primarily focused on migrating critical data encompassing Account, client, and product level details, consolidating them into a Unified platform. The initiative aimed to enhance operational efficiency, improve data accessibility, and position the client for future scalability and innovation within their data ecosystem.

Project Scope and Complexity:

The data migration project presented a multifaceted challenge due to the diverse nature of the information to be migrated and the intricacies associated with telecom data. The scope encompassed a comprehensive analysis of existing data structures, careful planning, and meticulous execution to ensure minimal disruption to ongoing operations. With a large volume of data and the need for precision, the project demanded a strategic approach to mitigate risks and address potential complications during the migration process.

Role Played:

ETL Development and Informatica PowerCenter:

Developed ETL mappings and scripts using Informatica PowerCenter 10.x, ensuring efficient data integration and transformation.

Led the end-to-end process, encompassing Requirement Analysis, Design, Coding, Testing, and Deployment phases.

Spearheaded the creation of scripts for loading data into base tables and orchestrating the movement from source to staging and staging area to target tables.

Advanced SQL Query Development:

Crafted numerous SQL queries, ranging from simple to complex, incorporating joins, Common Table Expressions (CTEs), and correlated subqueries to fulfill diverse business requirements.

Tuned and optimized SQL queries by modifying query options and implementing indexing strategies, aligning them with specific business needs.

Oracle 11g and PL/SQL Expertise :

Demonstrated proficiency in Oracle 11g, PL/SQL, Functions, Stored Procedures, Packages, Triggers, and Materialized Views.

Leveraged expertise in Oracle to perform SQL queries and execute stored procedures, ensuring the rapid extraction of data to resolve issues promptly.

Collaboration with Business Analysts and Technical Design:

Collaborated closely with business analysts to identify and comprehend requirements, playing a pivotal role in crafting Technical Design Documents.

Ensured a seamless bridge between technical implementations and business goals, contributing to the alignment of solutions with user needs.

Leadership and Innovation:

Held a leadership role within the Data Engineering Team, overseeing and fostering innovation in expert rules systems.

Played a key role in Solution and Migration Strategy, contributing strategic insights to ensure successful project outcomes.

Unix Shell Scripting and ETL Automation :

Applied Unix Shell scripting to automate various ETL processes, streamlining workflows and enhancing operational efficiency.

Environment: Informatica Power Center 10.4 (Designer, Workflow Manager, Monitor, Repository

Manager), Unix Shell Script, Stored Procedures, Oracle 12g SQL

Company: Qlytix, Inc

Client: Premier Inc

Role: Sr.ETL Developer-Informatica

Nov 2020 – Jan 2022

Project Description:

The INsights platform, developed by Premier, serves as a cornerstone for transforming data into a valuable product. The project's primary objective is to fortify the INsights platform by facilitating the acquisition, standardization, and integration of Premier data sets. A pivotal component of this initiative involves incorporating Quality Metrics and Regulatory (QMR) data, a crucial regulatory dataset. This strategic integration not only enriches the INsights data but also serves as a catalyst for migrating Premier Customer Engagement (PCE) customers onto the advanced INsights platform.

Interacted with the Business users to identify the process metrics and gathered requirements and documented the same in JIRA as user stories.

Create and managing schema objects such as tables, views, indexes, stored procedures, and triggers & maintaining Referential Integrity.

Involved in creating Data Model using Erwin Tool

Written Hive queries for data analysis to meet the business requirements.

Creating Hive tables and working on them using Hive QL.

Involved in creating Hive tables loading with data and writing hive queries which will run internally in map reduce way.

Importing and exporting data into HDFS and Hive using Sqoop.

Executed query using Impala and used BI tools to run ad-hoc queries directly on Hadoop.

Involved in various NOSQL databases like HBase, Cassandra in implementing and integration.

Installed and configured Hive and written Hive UDFs and Used Map Reduce and Junit for unit testing.

Load and transform large sets of structured, semi structured, and unstructured data.

Environment: Apache Hadoop (MRv1, MRv2), Hive, HBase, Hue, Impala, Spark, Erwin Data model, PostgreSQL

Company: Cognizant Technology, Chennai

Client: Health Net Inc, Los Angeles, CA/

Role: ETL Developer

Feb 2015 – March 2017

Extensive experience in developing ETL mappings, scripts and data integration using Informatica Power Center 9.x

Experience in all the phases of Data warehouse life cycle involving Requirement Analysis, Design, Coding, Testing, and Deployment.

Experience in working with business analysts to identify and understand requirements in order to create Technical Design Documents.

In-charge of Data Engineering Team, leading Innovation in expert rules system and involved in Solution and Migration Strategy.

Developed solutions for the complex business requirements that include developing new systems, migration/reverse engineering of existing systems and other non-functional application improvements.

Migrate the data from legacy system to Oracle Database and perform ETL Operations using Informatica 9.6.1.

Created numerous simple to complex SQL queries involving joins, CTEs, correlated sub queries for diverse business requirements.

Tuned and optimized SQL queries to fit the business requirements by altering query options and indexing strategies.

Gathered requirements from Business Analyst’s in understanding the Data Profile requirements for claims processing system.

Developed Advanced SQL queries and Stored Procedures to generate various reports as per Business requirements.

Expertise in Oracle 11g, PL/SQL, Functions, Stored Procedure, Packages, Triggers and Materialized Views.

Expertise in Data Warehousing Concepts like Star Schema, Snow Flake Schema, Fact Table, Dimension Table, OLAP/OLTP, Logical Data Modeling, Physical Modeling, Dimension Data Modeling, multidimensional modeling, Data profiling and data cleansing.

Strong knowledge of Entity-Relationship concept, Facts and dimensions tables, slowly changing dimensions and Dimensional Modeling (Star Schema and Snowflake Schema) using Kimball Methodology.

Interacted with the Business users to identify the process metrics and involved in the complete lifecycle of the project.

Implementing end-to-end data masking project, including strategy, requirement analysis, design, and development and testing phases.

Widely used different features of Teradata such as BTEQ, Fast load, Multiload, SQL Assistant, DDL and DML commands and very good understanding of Teradata UPI and NUPI, secondary indexes and join indexes.

Very good understanding of Teradata’s MPP architecture such as Shared Nothing, Nodes, AMPs, BYNET, Partitioning, Primary Indexes etc.

Extensively created and used various Teradata Set Tables, Multi - Set table, global tables, volatile tables, temp tables.

Designed and developed change data capture solutions (CDC) for the project which captures and analyses changes from daily feeds to maintain history tables.

Developed complex Informatica TDM masking rules for PHI attributes & followed hybrid approach in masking data utilizing both TDM and Informatica Power Center.

Development of scripts for loading the data into the base tables in EDW and to load the data from source to staging and staging area to target tables using Fast Load, Multiload and BTEQ utilities of Teradata.

Created entity relationship diagrams and dimensional modeling for the Data warehousing systems.

Worked with Erwin for effective model management of sharing, dividing and reusing model information and design for productivity improvement.

Tuning of Teradata SQL statements using Explain analyzing the data distribution among AMPs and index usage, collect statistics, definition of indexes, revision of correlated sub queries, usage of Hash functions, etc.

Worked on developing the Slowing Changing Dimensions Type1, Type2, Type 3 as per the business criteria.

Worked on Unix Schell scripting to automate several ETL processes.

Used BTEQ and SQL Assistant (Query man) front-end tools to issue SQL commands matching business requirements to Teradata RDBMS.

Used FastExport utility to extract large volumes of data at high speed from Teradata RDBMS.

Incorporated the star schema dimensional modeling due to the faster retrieval of data so the user can generate ad hoc reports in data warehousing.

Worked on Performance Tuning, identifying and resolving performance bottlenecks in various levels like sources, targets, mappings and sessions.

Wrote numerous BTEQ scripts to run complex queries on the Teradata database.

Extensively worked on the partitioning the pipeline for parallel data processing in the Informatica Power Center.

Implemented Teradata MERGE statements in order to update huge tables thereby improving the performance of the application.

Worked on Error handling and performance tuning in Teradata queries and utilities.

Create and managing schema objects such as tables, views, indexes, stored procedures, and triggers & maintaining Referential Integrity.

Automated the Batch job process by scheduling the workflows using the PMCMD commands in Informatica.

Alert the user during the batch process by sending out email notification from Informatica.

Work on the Technical design documentation with the detail aspects of the data flow.

Prepared Logical Mapping Document (LMD) which depicts the Project level scope & type of data masking applied to the individual attributes with detailed information.

Environment: Informatica Power Center 9.6.1 (Designer, Workflow Manager, Monitor, Repository

Manager), Unix Shell Script, Stored Procedures, Oracle 11g SQL,Teradata 14.10/14

Express Scripts Inc, Cognizant, Chennai, India

ETL Developer.

May 2011 – Feb 2015

Responsibilities:

The Program involved application transformation from the Legacy platform to the new platform with

enhancement to include additional volume and improved client experience.

Involved in understanding the Requirements of the End Users/Business Analysts and Developed Strategies for ETL processes.

The program involved in migrating large data from legacy mainframe application to latest databases Teradata, SQL.

Very good understanding of Teradata’s MPP architecture such as Shared Nothing, Nodes, AMPs, BYNET, Partitioning, Primary Indexes etc.

Expertise and well versed with various Ab Initio Transform, Partition, Departition, Dataset Database components, sort, validate and compress.

Interacted with the Business users to identify the process metrics and gathered requirements and documented the same in JIRA as user stories.

Prepared End-to-End process flow diagrams to trace the requirement change and received Business Signoff.

Developed number of Ab Initio Graphs based on business requirements using various Ab Initio Components such as Partition by Key, Partition by round robin, reformat, and rollup, join, scan, normalize, gather, merge etc.

Worked on improving the performance of Ab Initio graphs by using various Ab Initio performance technique’s like using looks instead of Join’s etc.

Implemented Lookup’s, lookup local, in-Memory Joins, and rollups to speed up various Ab Initio Graphs.

Designed ETL high level structure on how to retrieve & load the data from 25 different partner system.

Implemented the parallel concurrent execution of the session to process the data.

Worked with SQL, PL/SQL procedures and functions, stored procedures, and packages within the Informatica Designer mappings.

Extensively used Informatica to extract, transform data from heterogeneous source systems and

load the data into the Target database.

Implemented slowly changing dimensions Type 1 & Type 2 Temporal Dimension & facts according to the requirements.

Involved in Unit testing and UAT testing after coding the Ab Initio object.

Extensively worked on Partitioning tables for better handling of huge volume of data.

Used Informatica file watch events to pole the FTP sites for the external files.

Prepared ETL mapping documents in data warehouse for every mapping and Data Migration document for smooth transfer of project from development to testing environment and then to production environment.

Creation of shell scripts to perform end to end testing on the load.

Extensively worked on Performance tuning to increase the throughput of the data load (like read the data from flat file & write the data into target flat files to identify the bottlenecks).

Worked on Error handling and performance tuning in Teradata queries and utilities.

Used Teradata utilities FAST LOAD, MULTI LOAD, TPUMP to load data.

Expertise in using Teradata Utilities BTEQ, M-Load, F-Load, TPT in combination with Informatica for better Load into Teradata Warehouse.

Built several BTEQ to load data from Stage to Base after considering several performance techniques in Teradata sql.

Involved in SQL tuning on Teradata joins.

Extensively worked in the performance tuning of Teradata SQL, ETL and other processes to optimize session performance.

Strong knowledge of Entity-Relationship concept, Facts and dimensions tables, slowly changing dimensions and Dimensional Modeling (Star Schema and Snowflake Schema) using Kimball Methodology.

Wrote, tested, and implemented Teradata Fast load, Multiload and BTEQ scripts, DML and DDL.

Worked extensively with Teradata versions 15/14/13, strong knowledge on Teradata utilities - Fast load, Multi load, Tpump and Teradata Parallel Transporter (TPT) to load huge amounts of data from flat files into Teradata database.

Involved in Unit & System testing as Regression Testing to check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.

Experienced with Teradata utilities Fast Load, Multi Load, BTEQ scripting, Fast Export, SQL Assistant.

Generated BTEQ scripts to invoke various load utilities transform the data and query against Teradata database.

Performed high volume maintenance on large Teradata tables using Multiload loader utility.

Implemented Teradata MERGE statements in order to update huge tables thereby improving the performance of the application.

Created TPT to transfer the data Oracle system to Teradata.

Used Fast Export utility to extract large volumes of data at high speed from Teradata RDBMS.

Developed TPump scripts to load low volume data into Teradata RDBMS at near real-time.

Collected statistics periodically on tables to improve system performance. Performed tuning and optimization of application SQL using Query analyzing tools.

Extensive experience in troubleshooting and solving migration issues and production Issues.

Created Mapplets, reusable transformations and used them in different mappings.

Tuned Informatica mappings and sessions for optimum performance.

Assisted the other ETL developers in solving complex scenarios and coordinated with source

systems owners with day-to-day ETL progress monitoring.

Performed Unit testing and maintained test logs and test cases for all the mappings.

Perform to review and apply Structured Query Language (SQL) fixes to the data in the Oracle Production Databases.

Highly skilled in Performance tuning - analyzing query plans and SQL tuning using EXPLAIN PLAN and hints.

Extensively worked on Reference Tables (RT) change Implementation projects, where validated the records to make sure the temp functionality is intact.

Ensured the feasibility of the logical and physical design models.

Created UNIX shell scripts to automate and schedule the jobs.

Created UNIX shell scripts for Informatica ETL tool to automate sessions.

As part of the SQL review extensively worked on SQL tuning / Optimization and drastically

changed the response time using appropriate hints when needed.

Environment: Informatica Power Center 8.6.1 (Designer, Workflow Manager, Monitor, Repository

Manager), Ab initio GDE 3.15, Oracle R12c / 11g, SQL/PLSQL, UNIX, Teradata 12,14, TPT, SQL Assistant

The Hanover Insurance Group, Inc,Cognizant,Chennai, India

ETL Developer

Sep 2007 – Apr 2011

Responsibilities:

Interacted with Business and documented the requirement as User Stories and Received signoff.

Performed the gapping exercise to identify the Gaps.

Extensively involved in Data Extraction, Transformation and Loading (ETL process) from Source to target systems using Informatica.

Worked with heterogeneous source to Extracted data from Oracle database, XML and flat files and loaded to a relational Oracle warehouse.

Created data mappings to extract data from different source files, transform the data using Filter, Update Strategy, Aggregator, Expression, Joiner Transformations and then loaded into data warehouse.

Worked on SQL Server Integration Services (SSIS) to integrate and analyze data from multiple homogeneous and heterogeneous information sources.

Set and follow Informatica best practices, such as creating shared objects in shared for reusability and standard naming convention of ETL objects, design complex Informatica transformations, Mapplets, mappings, reusable sessions, worklets and workflows.

Evaluate business requirements to come up with Informatica mapping design that adheres to Informatica standards.

Implemented Slowly Changing dimension type2 methodology for accessing the full history of accounts and transaction information.

Used Update Strategy Transformation to update the Target Dimension tables, type2 updates where we insert the new record and update the old record in the target so we can track the changes in the future.

Developed various mapplets that were then included into the mappings as part of data warehouse.

Used Workflow Manager to read data from sources, and write data to target databases and manage sessions.

Develop SSIS packages to Extract, Transform and load data using different transformations such as Lookup, Derived Columns, Condition Split, Aggregate, and Pivot Transformation.

Developed mappings, sessions and workflows in Informatica Power Center.

Strong knowledge of Entity-Relationship concept, Facts and dimensions tables, slowly changing dimensions and Dimensional Modeling (Star Schema and Snowflake Schema) using Kimball Methodology.

Identified performance issues in existing sources, targets and mappings by analyzing the data flow, evaluating transformations and tuned accordingly for better performance.

Developed standard and reusable mappings and mapplets using various transformations like Expression, Aggregator, Joiner, Router, Lookup (Connected and Unconnected) and Filter.

Performed tuning of SQL queries and Stored Procedure for speedy extraction of data to resolve and troubleshoot issues in OLTP environment.

Troubleshooting of long running sessions and fixing the issues related to it.

Worked with Variables and Parameters in the mappings to pass the values between sessions.

Involved in the development of PL/SQL stored procedures, functions and packages to process business data in OLTP system.

Worked with the testing team to resolve bugs related to day one ETL mappings before production.

Creating the weekly project status reports, tracking the progress of tasks according to schedule and reporting any risks and contingency plan to management and business users.

Involved in meetings with production team for issues related to Deployment, maintenance, future enhancements, backup and crisis management of DW.

Worked with production team to resolve data issues in Production database of OLAP and OLTP systems.

Resolved issues related to Enterprise data warehouse (EDW), stored procedures in OLTP system and analyzed, design and develop ETL strategies.

Environment: Informatica Power Center 7.2 (Designer, Workflow Manager, Monitor, Repository Manager), SSIS, SSMS, SQL/ PLSQL, SQL Server, Oracle 11g, Windows Server 2003, TOAD, ERWIN Data Modeler, WINSCP, Shell Scripting, Putty.

Technical Skills:

ERP

Health Care and Insurance Product Claims Processing system and Policy Servicing systems

ETL / BI

Ab initio, Informatica Power Center (Designer, Workflow Manager, Workflow Monitor, Repository manager), Test Data Management (TDM), OBIEE 11.x/10.x, Dashboards, Answers, Delivers, BI Publisher.

Databases

Oracle RDBMS,,Teradata 13/14 Oracle 10g/12C, SQL Server, DB2, My SQL, MS-Access. Editors (SQL Navigator, Toad).

Data Modeling

ERWIN, Visio.

Languages

SQL, PL/SQL, PERL, UNIX Shell Scripting (K-Shell, C-Shell) Power Shell Scripting, JAVA Script, J2EE.

Web Technologies

Seibel Analytics, HTML, JAVASCRIPT, ASP and PHP

Tools

OBIEE 10g/11g (Admin/Desktop/Plus), Forms 9i/10g, Reports 9i/10g, Toad, SQL Navigator, SQL*Plus, SQL*Load, JDeveloper, Discoverer 9i/10g, Developer 2000, XML Publisher, SVN, TFS, Perforce, Panayi, Clear Quest, Clear Case, Perforce, More4apps, Kintana, PVCS, Quality Center.

Operating System

UNIX, Linux (Red hat enterprise) and WINDOWS family.

Contact this candidate