Sunil Hegde

Senior Informatica Developer

Brooklyn, NY

acd3hk@r.postjobfree.com

• 7+ years of experience in Information Technology with a strong background in Data warehousing and ETL

process.

• Extensively worked on Informatica Designer components Source Analyzer, Warehouse Designer,

Transformation Developer, Mapping Designer and Mapplet Designer.

• Data Processing experience in designing and implementing Data Mart applications, mainly transformation

processes using Informatica PowerCenter9.5/8.6/8.0/7.1.

• Expertise in Dimensional and Relational Physical & logical data modeling using Erwin and ER/Studio.

• Extensively worked on Informatica Components like, Admin console, Repository manager, Designer,

Workflow manager, Workflow monitor.

• Experience in identifying Bottlenecks in ETL Processes, improving the Performance of the production

applications using Database Tuning, Partitioning, Index Usage, Aggregate Tables, and Normalization /

Denormalization strategies.

• Strong in Data Warehousing concepts, dimensional star schema and Snowflake Schema methodologies.

• Experience in Performance tuning of the Informatica and Oracle.

• Strong expertise in Relational data base systems like Oracle, SQL Server, MS Access, DB2 and Teradata.

• Worked with XML and Flat file sources coming from various legacy source systems and residing on Mainframe

and UNIX.

• Created personalized version of reports as well as statements for customers using the data from Informatica

metadata and then generated Business Objects reports using slice and dice capabilities.

• Created Stored Procedures to transform the Data and worked extensively in T-SQL, PL/SQL for various

needs of the transformations while loading the data into SQL Server database.

• Extensively worked with SQL loader for bulk loading into the oracle database.

• Proficient in understanding business processes / requirements and translating them into technical

requirements.

• Highly adept at creation, execution, testing and debugging of Informatica mappings, mapplets, sessions,

tasks, worklets and workflows in UNIX and Windows environment.

• Excellent technical and professional client interaction skills. Interacted with both Technical, functional and

business audiences across different phases of the project life cycle.

• Team Player with excellent communication, analytical, verbal, writing and interpersonal skills.

WORK EXPERIENCE

Senior Informatica Developer

HCA Parallon - Franklin, TN - May 2012 to Present

Responsibilities:

• Worked closely with client in understanding the Business requirements, data analysis and deliver the client

expectation

• Used Informatica PowerCenter 9.5/8.6 and its all features extensively in migrating data from OLTP to

Enterprise Data warehouse.

• Extensively used Erwin for Logical and Physical data modeling and designed Star Schemas.

• Extracted data from different sources like Oracle, flat files, XML, DB2 and SQL Server loaded into DWH.

• Created complex mappings in PowerCenter Designer using Aggregate, Expression, Filter, and Sequence

Generator, Update Strategy, Union, Lookup, Joiner, XML Source Qualifier and Stored procedure

transformations.

• Developed mappings/Reusable Objects/mapplets by using mapping designer, transformation developer and

mapplet designer in Informatica PowerCenter 9.5/8.6

• Used Informatica PowerCenter9.5/8.6 for extraction, loading and transformation (ETL) of data in the data

warehouse.

• Handle slowly changing dimensions (Type I, Type II and Type III) based on the business requirements.

• Used Informatica Power Center Workflow manager to create sessions, batches to run with the logic embedded

in the mappings.

• Involved in creation of Folders, Users, Repositories and Deployment Groups using Repository Manager.

• Developed PL/SQL and UNIX Shell Scripts for scheduling the sessions in Informatica.

• Involved in scheduling the Informatica workflows using Autosys.

• Migrated mappings, sessions, and workflows from development to testing and then to Production

environments.

• Involved in Performance tuning for sources, targets, mappings and sessions.

• Performed unit testing on the Informatica code by running it in the Debugger and writing simple test scripts

in the database thereby tuning it by identifying and eliminating the bottlenecks for optimum performance.

• Worked extensively with different caches such as Index cache, Data cache and Lookup cache (Static,

Dynamic, Persistence and Shared).

• Created deployment groups, migrated the code into different environments.

• Worked closely with reporting team to generate various reports.

Environment: Informatica PowerCenter 9.5/8.6(Power Center Repository Manager, Designer, Workflow

Manager, and Workflow Monitor), SQL Server 2005, UDB DB2 8.1, XML, Perl, Autosys, Oracle 10g, TOAD

6.0, SQL, PL/SQL, UNIX, Cognos 8.0, Erwin.

Informatica Developer

Humana Inc - Louisville, KY - January 2011 to April 2012

Responsibilities:

• Extensively used Informatica Power center for extracting, transforming and loading data from relational

sources and non-relational sources.

• Extensively used various transformations such as Source Qualifier, Expression, Lookup, Sequence

Generator, aggregator, Update Strategy, and Joiner while migrating data from various heterogeneous sources

like Oracle, DB2, XML and Flat files to Oracle.

• Developed Informatica mappings, re-usable transformations, re-usable mappings and Mapplets for data load

to data warehouse.

• Developed Mappings using Designer to extract, transform data according to the requirements and loaded

into database.

• Designed complex UNIX scripts and automated it to run the workflows daily, weekly and Monthly.

• Setting up Batches and sessions to schedule the loads at required frequency using Power Center Workflow

manager.

• Created sessions, database connections and batches using Informatica Workflow Manager. Developed

mappings to load data in slowly changing dimension.

• Created connected and Un-Connected transformations to lookup the data from Source and Target Tables.

• Scheduled Sessions and Batches on the Informatica Server using Informatica workflow Manager.

• Monitoring the ETL jobs and fixing the Bugs

• Worked along with the DBA to resolve performance and tuning issues.

• Provided reliable, timely support of integration, performance and user acceptance testing processes.

• Involved in doing Unit Testing, Integration Testing and System Testing.

• Identified and created various classes and objects for report development.

Environment: Informatica PowerCenter 8.6, Oracle 10g/9i, DB2, XML, Flat files, SQL, PL/SQL, TOAD, Perl,

SQL*Plus, Windows, UNIX, Business Objects 6.0.

Informatica Developer

Quest Communications - Denver, CO - 2009 to December 2010

Responsibilities:

• Extensively used ETL to load data from multiple sources to Staging area (Oracle 9i) using Informatica

PowerCenter 7.1

• Worked with pre and post sessions, and extracted data from Transaction System into Staging Area.

Knowledge of Identifying Fact and Dimension tables.

• Created reusable transformations and mapplets and used them in mappings

• Extensively used transformations such as Source Qualifier, Aggregator, Expression, Lookup, Router, Filter,

Update Strategy, Joiner, Transaction Control and Stored Procedure.

• Tuned sources, targets, mappings and sessions to improve the performance of data load

• Designed Logical and Physical Modeling using Erwin Data Modeler tool.

• Created Several Informatica Mappings to populate the data into dimension and fact tables.

• Involved in Performance tuning for sources, targets, mappings, and sessions.

• Performed unit testing on the Informatica code by running it in the Debugger and writing simple test scripts

in the database thereby tuning it by identifying and eliminating the bottlenecks for optimum performance.

• Automated the load process using UNIX shell scripts.

• Created sessions and batches for incremental load and schedule them to run daily.

• Implemented Type II slowly changing Dimension to maintain all historical information in Dimension Tables.

• Optimized and Tuned SQL queries and PL/SQL blocks to eliminate Full Table scans to reduce Disk I/O and

Sorts.

• Investigated and fixed problems encountered in the production environment on a day to day basis.

• Actively involved in the production support and also transferred knowledge to the other team members.

• Co-ordinate between different teams across circle and organization to resolve release related issues.

Environment: Informatica PowerCenter 7.1/8.1, Oracle 10g/9i, SQL, PL/SQL, TOAD, SQL*Plus, Autosys,

Erwin, Windows, UNIX.

ETL Developer

BNY Mellon - Boston, MA - September 2008 to May 2009

Responsibilities:

• Evaluated and documented Business processes to accurately reflect the current and future state of the

process

• Analyzed new applications as they relate to Business and existing applications

• Developed prototypes of a new application to be used with customers to confirm requirements.

• Identified and tracked the slowly changing dimensions from heterogeneous sources and determined the

hierarchies in dimensions.

• Implemented weekly error tracking and correction process using Informatica.

• Implemented audit process to ensure Data warehouse is matching with the source systems in all reporting

perspectives.

• Experience with Client/Account data management systems

• Identified performance issues in existing sources, targets and mappings by analyzing the data flow, evaluating

transformations and tuned accordingly for better performance.

• Extensively used Stored Procedures, Functions and Packages using PL/SQL.

• Created maestro schedules/jobs for automation of ETL load process.

• Prepared ETL mapping Documents for every mapping and Data Migration document for smooth transfer of

project from development to testing environment and then to production environment.

• Involved in Unit testing, User Acceptance testing to check whether the data loads into target are accurate,

which was extracted from different source systems according to the user requirements.

• Involved in developing test data/cases to verify accuracy and completeness of ETL process.

• Written UNIX shell Scripts for getting data from systems to Data Warehousing system.

• Actively involved in the production support and also transferred knowledge to the other team members.

• Co-ordinate between different teams across circle and organization to resolve release related issues.

Environment:Informatica PowerCenter 8.1, PowerExchange, Power Analyzer, Erwin, Oracle 9i, SQL, PL/SQL,

SQL Server 2005, Windows XP, MS Access, UNIX Shell Scripting, Maestro, Sql*Loader, TOAD 8.6.1.0

ETL Developer

WEA Trust - Madison, WI - October 2007 to September 2008

Responsibilities:

• Actively involved in gathering requirements and acquiring application knowledge from Business Managers

& Application Owners.

• Prepared the High-level Design document to provide an overview of the technical design specification system

required for Application Enhancements.

• Involved in designing the process flow for extracting the data across various systems interacting.

• Extensively worked with ETL tools to extract data from various source including Oracle, Flat files, XML

sources.

• Developed ETL routines using Informatica Power Center and created mappings involving transformations

like Lookup, Aggregator, Ranking, Expressions, Mapplets, connected and unconnected stored procedures,

SQL overrides usage in Lookups and source filter usage in Source qualifiers and data flow management into

multiple targets using Routers.

• Created Mapping and Session Variables.

• Optimized the existing applications at the mapping level, session level and database level for a better

performance.

• Responsible for daily internal and external SLA, communicating in advance of any SLA miss to both technical

and functional audience.

• Maintaining issue log of the issues during the UAT phase for future reference

• Preparing and using test data/cases to verify accuracy and completeness of ETL process.

• Lead the team to recover from the Data warehouse corruption. Put the schedule and developed the plan

to recover in a short time.

• Actively involved in the production support and also transferred knowledge to the other team members.

• Co-ordinate between different teams across circle and organization to resolve release related issues

Environment: Informatica 7.1, Teradata, Oracle10g/9i, Autosys, UNIX, Erwin, Windows XP.

ETL Developer

AIG, NY - December 2006 to July 2007

Responsibilities:

• Involved in design and development of data warehouse environment, liaison to business users and/or

technical teams gathering requirement specification documents and presenting and identifying data sources,

targets and report generation.

• Worked on Informatica Power Center client tools like - Source Analyzer, warehouse designer,

MappingDesigner, WorkflowManager, Mapplets, and ReusableTransformations.

• Using Informatica Designer designed Mappings, which populated the Data into the Target Star Schema on

Oracle 8i Instance.

• OptimizedQueryPerformance, Session Performance and Reliability.

• Extensively used Normalizer (for COBOL source files from S-390 Mainframe system),

• Router, Lookup, Aggregator, Expression and UpdateStrategy Transformations.

• Setup the data mart on Oracle database by running the SQL scripts from ERWIN designer.

• Translation of Business processes into Informatica mappings for building Data marts.

• Involved in the Migration process from Development, Test and Production Environments.

• Report generation using PERL and Microsoft Excel spreadsheet

• Used SQL tools like TOAD to run SQL queries and validate the data.

• Wrote stored procedures and triggers in Oracle 8i for managing consistency and referential integrity across

data mart.

• Tuning the Mappings for Optimum Performance, Dependencies and Batch Design.

• Schedule and Run Extraction and Load process and monitor sessions using Informatica Workflow Manager.

• Scheduled the batches to be run using the Workflow Manager.

• Involved in identifying bugs in existing mappings by analyzing the data flow, evaluating transformations and

fixing the bugs so that they conform to the business needs.

Environment: Informatica Power Center 7.1, Repository Manager, Oracle 8i, Teradata V2R3, UNIX (Solaris),

Erwin 4.0/3.5.x, MS SQL Server 2000, Cobol, PL/SQL, MS Access 2000, Shell Programming, SQL * Loader,

Toad 8.6.1.0, SQL Navigator

SKILLS

: Informatica PowerCenter 9.5/8.6(Power Center Repository Manager, Designer, Workflow Manager, and

Workflow Monitor), SQL Server 2005, UDB DB2 8.1,XML, Perl, Autosys, Oracle 10g, TOAD 6.0, SQL, PL/

SQL, UNIX, Cognos 8.0, Erwin,Informatica PowerCenter 8.6, Oracle 10g/9i, DB2, XML, Flat files, SQL,

PL/SQL, TOAD, Perl, SQL*Plus, Windows, UNIX, Business Objects 6.0,Informatica PowerCenter 7.1/8.1,

Oracle 10g/9i, SQL, PL/SQL, TOAD, SQL*Plus, Autosys, Erwin, Windows, UNIX.Informatica PowerCenter 8.1,

PowerExchange, Power Analyzer, Erwin,Oracle 9i, SQL, PL/SQL, SQL Server 2005, Windows XP,MS Access,

UNIX Shell Scripting, Maestro, Sql*Loader, TOAD 8.6.1.0,Informatica 7.1, Teradata, Oracle10g/9i, Autosys,

UNIX, Erwin, Windows XP,Informatica Power Center 7.1, Repository Manager, Oracle 8i, Teradata V2R3,

UNIX (Solaris), Erwin 4.0/3.5.x, MS SQL Server 2000, Cobol, PL/SQL, MS Access 2000, Shell Programming,

SQL * Loader, Toad 8.6.1.0, SQL Navigator

ADDITIONAL INFORMATION

Data Warehousing ETL Informatica PowerCenter 9.x/8.x/7.x/6.x/5.x, (Source Analyzer, Warehouse Designer,

Transformation Developer, Mapplet Designer, Mapping Designer, Repository Manager, Workflow Manager,

Workflow Monitor and Informatica Server) ETL, Power Exchange 8.0, Datastage, Repository, Metadata, Data

Mart, OLAP, OLTP.

Data Modeling MSVisio, ERWIN 4.x/3.x, Ralph-Kimball Methodology, Bill-Inman Methodology,

Star Schema, Snow Flake Schema, Extended Star Schema, Physical And

Logical Modeling.

Databases Oracle 10g/9i/8.0, MS SQL Server 7.0/2000/2005/2008, DB2 8.1, OLTP, Teradata

Programming SQL, T-SQL, PL/SQL, SQL*Loader, Unix, Shell Scripting, SQL Tuning/Optimization, C,C++,

VC++, VB, Java, SAS 9.1.3, HTML, Perl, PHP, JavaScript

Tools TOAD, SQL*Plus, Excel, Word, Autosys, Control-M, Erwin,Dream weaver

Reporting Tools Oracle BusinessIntelligence, Business Objects XI r2/r1/6.5/6.1/5.1, Cognos, SSRS(SQL

Reporting Services), Oracle 10g/9i/8i/8.0/7.x, IBM DB2 UDB 8.0/7.0, Teradata V2R4 V2R5, MS SQL

2005/2000/7.0/6.0, MS Access

Environment Linux.UNIX Sun Solaris 2.x, Win 95/NT/98/2000/XP

Contact this candidate