SWATHI

SR ETL/INFORMATICA DEVELOPER Email - acg9qv@r.postjobfree.com

CAREER HIGHLIGHTS

. A seasoned ETL/ Informatica Developer with over 6 years of Technical

and Functional experience in Decision Support Systems - Data

warehousing implementing ETL (Extract, Transform and Load) using

Informatica 9.6x, 8.x, 7.x.

. Experience in Software Development Life Cycle (SDLC) which includes

requirement gathering, designing, implementing and testing.

. Good knowledge of Data warehouse concepts and principles

(Kimball/Inman) - dimensional data modeling, fact and dimension

tables, Star Schema, Snowflake, SCD (Type1, Type2 & Type3), Surrogate

keys, Normalization/De normalization.

. Experience in integration of various data sources with Multiple

Relational Databases like Oracle, SQL Server, Teradata and IBM DB2 and

worked on integrating data from flat files, excel and XML.

. Have extensively worked in developing ETL for supporting Data

Extraction, transformations and loading using Informatica Power

Center.

. Well acquainted with Informatica Designer Components - Source

Analyzer, Transformation Developer, Mapplet and Mapping Designer.

. Worked with complex mappings using different transformations like

Source Qualifiers, Expressions, Filters, Joiners, Routers, Rank,

Union, Unconnected / Connected Lookups, Normalizer, Sorter,

Aggregators, Update strategy, Stored procedure, mapping parameters and

variables.

. Strong Experience in developing Sessions/tasks, Worklets, Workflows

using Workflow Manager Tools - Task Developer, Workflow and Worklet

Designer, Workflow Monitor & and also extensively used Informatica

Repository Manager.

. Knowledge about Change Data Capture framework and Change Data Capture

implementation (CDC-incremental).

. Excellent knowledge in migration of repository objects from one

repository to other across various environments using export and

import option, copy the folder and by using deployment groups.

. Experience in using built-in command line utility PMCMD to interact

with the informatica features and perform various tasks like Start,

Stop, Abort workflow from specific tasks and session, Schedule the

workflow by scripting.

. Well Versed in debugging, Identify bugs in existing mappings by

analyzing the data flow and evaluating transformations.

. Hands on experience in performance tuning to optimize session

performance by eliminating performance bottlenecks occurrences at

source, target, mapping, session and system.

. Worked with SQL, PL/SQL, Stored Procedures, Triggers, Cursors, Indexes

and Functions.

. Highly motivated to take independent responsibility as well as ability

to contribute and be a productive team member and excellent customer

interfacing skills and excellent communication and interpersonal

skills.

Technical Skills

. ETL Tools: Informatica, PowerCenter 9.5.1/9.1/8.x/7.x

. Databases: MS SQL Server 2005/2008, Oracle 9i/10g/11g, Teradata, IBM

DB2

. Database Skills: Cursors, Stored Procedures, Functions, Views,

Triggers

. Client Side Skills: SQL, T-SQL, PL/SQL, UNIX shell scripting, HTML,

XML, CSS, JavaScript, C, C++, ASP.Net 2.0, MOSS 2007

. Version Control: Visual Source Safe 6.0, CVS

. Methodologies: Data Modeling Logical / Physical, Star/ Snowflake

Schema, FACT & Dimension Tables, ETL, OLAP, Agile, SDLC

. Operating Systems: Windows 2000/2003/XP/Vista/Windows 7, UNIX

. Tools: Erwin, Toad, SQL Developer, SQL*plus, SQL Loader, control-M,

Autosys, Informatica Power center Scheduler, putty, winscp, HP quality

center, JIRA, MS Visio.

WORK EXPERIENCE

Statestreet Bank, Boston, MA

Jan 2013 -Current

Sr ETL Informatica Developer

Statestreet Bank is the investment banking division and it provides

financing and risk management services to large companies, institutions and

government clients.

Project Description

This was part of the Market Risk IT suite of applications. It functions as

the enrichment and position data central store for risk for the Traded

Credit business systems. It provides aggregation, data enrichment including

static data, prices and analytics and data override functionality. It

generates feeds for downstream systems, regulatory reports and daily

reports for Risk Managers.

. Involved in gathering and analysis business requirements and identify

data sources and targets, create data mapping sheet, Low Level Design

Document as per business requirements.

. Translated Business Requirements into Informatica mappings to build

Data Warehouse by using Informatica Designer to populate the data into

the target Schema.

. Extracted data from various heterogeneous sources systems like Oracle,

SQL Server, Fixed Width and Delimited Flat Files and loaded into

relational data warehouse.

. Designed and developed active and passive transformations in complex

mappings from varied transformation logic like Unconnected and

Connected lookups, Router, Filter, Expression, Aggregator, Joiner,

Update Strategy, Stored Procedure.

. Implemented slowly changed dimensions (SCD) Type 2 for loading data

into Dimensions tables.

. Experience in using mapping parameter and variables and assigning the

parameter file globally/session/workflow and folder level in a

mapping.

. Modified the existing worklets and workflows to accommodate for these

new sessions and mappings.

. Worked with Shortcuts across Shared and Non Shared Folders.

. Testing the mapping and fixing the bugs using debuggers and

session/workflow logs.

. Involved in optimization and performance tuning of Informatica objects

and database objects to achieve better performance.

. Extensively involved in coding of the Business Rules through PL/SQL

using the Functions, Cursors and Stored Procedures.

. Coordinated between the onsite and offshore ETL teams for meeting the

scheduled project milestones and deadlines.

. Scheduling the sessions to extract, transform and load data in to

warehouse database on Business requirements. Created different jobs

using UNIX shell scripting to call the workflow. Created the schedule

using Autosys and ran the jobs.

. Work on 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.

. Created, updated and maintained ETL technical documentation for

Production Migration.

. Maintained a very good interaction with analysts, Project Managers,

architects and testers to have efficient and better results.

Environment: Informatica 9.1, Oracle 10g/11g, SQL Developer, SQL, PL/SQL,

UNIX, WINDOWS NT, Erwin, SQL Server 2008, Autosys, Visual Source Safe 6.0,

Putty, winscp.

Liberty Mutual Insurance, Philadelphia, Pennsylvania

Oct 2011 - Jan 2013

Informatica Developer

Liberty Mutual Insurance Company is a leading provider of auto, home, and

life insurance.

Project Description

The data warehouse was being designed for the Liberty Mutual Auto insurance

group to provide a household view of all their customers and this project

is to create extra layer named IDM integrate data just in between source

and data warehouse.

. Source system evaluation, standardizing received data format,

understanding business/data transformation rules, business structure

and hierarchy, relationships, data transformation through mapping

development, validation and testing of mappings.

. Designed ETL specification documents to load the data in target using

various transformations according to the business requirements.

. Informatica Mappings used to load the data from the Source files to

staging tables and from staging tables to DB2 tables.

. Wrote SQL overrides in source qualifiers for extracting only the

required rows for optimal performance. Created complex mapping to load

heterogeneous targets using connected/unconnected Lookup, incremental

update strategy, transformation control, Aggregate, Expression,

Filter, Sequence Generator, Joiner, and Stored procedure

transformations.

. Created Logical and Physical Modeling of Star and Snowflake schemas

based on the business rules in Erwin.

. Extensively used Informatica for loading the historical data using SCD

type1, SCD type2, SCD type 3 from various tables to dimension tables

and incremental load to fact tables using job-control table.

. Generating parameter file using normalizer transformation by

converting columns to rows.

. Created reusable transformations called mapplets and used them in

mappings in case of reuse of the transformations in different

mappings.

. Implemented optimization and performance tuning of mappings to achieve

high response times.

. Used Power center Workflow Manager to design workflow level task like

sessions, event wait/raise, and assignment, e-mail, command,

assignment, decision task and workflow variable to execute mappings.

. Created daily and weekly workflows and scheduled to run based on

business needs and offered production support for daily jobs.

. Created shell scripts to run daily jobs and extract the files from

remote location for data loads and Worked with UNIX commands and used

Unix shell scripting to automate jobs

. Worked with PMCMD to interact with Informatica Server from command

mode and execute the Shell scripts.

. Created the break points in the designer and worked with debugger to

trace errors in mappings, setup session properties to achieve database

error logging.

. Migration of repository objects from one repository to other using

static and dynamic deployment groups.

. Involved in developing test data/cases to verify accuracy and

completeness of ETL process.

Environment: Informatica 9.x/8.6, Oracle 9i, DB2, SQL, PL/SQL, Flat file,

XML, Erwin 4.0, UNIX, Shell scripts, TOAD, CVS, Windows XP, JIRA, HP

quality center.

Global Warranty Group, Saint James, New York

Sept 2010 - Oct 2011

ETL Developer

Global Warranty Group is a leading developer, marketer and administrator of

Product Warranty (OEM) and Extended Service Contract Programs (ESC) through

a wide variety of industries.

Project Description

The objective of this project was to enable marketing departments to view

the data at a lowest level using Informatica and BO. It has various

warehouses from where the products are distributed based on the orders

received from customers. The Database had huge historical data that was

transformed and loaded from various OLTP servers/databases into the

marketing data mart. Business Objects Reporting was used to analyze the

sales, demands and business in various dimensions

. Involved in gathering business requirements, logical modeling,

physical database design, data sourcing and data transformation and

data loading.

. Involved in the data analysis for source and target systems and good

understanding of Data Warehousing concepts, staging tables,

Dimensions, Facts and Star Schema.

. Extensively Used Informatica Designer as an ETL tool to extract data

from source systems to Target system. Source Systems are mainly

delimited flat files and relational tables.

. Developed Mappings using various transformations like

connected/unconnected Lookups, Filter, Router, Aggregator, Expression,

Stored procedure, Sequence Generator, Update strategy and Joiners etc.

. Designed SCD Type1 (overwriting, no history) and SCD Type2 (flag,

version, date) mappings to load dimension tables.

. Historical load to fact table by breaking of data load into different

cycles using mapping parameters.

. Written pre session and post session scripts in mappings, configured

ETL tool to read multiple source files at a time using indirect mode

in session properties.

. Used Repository Manager to create user groups and user profiles with

privileges of Administrator and Setting up the security for creating

user groups and assigning privileges.

. Used Workflow Manager for Creating, Validating, Testing and running

the sequential and concurrent Batches and Sessions and scheduling them

to run at specified time with required frequency.

. Extensively used debugger to find out errors in mappings and later

fixed them and changed session/workflow level override tracing value

property to None, Normal, Terse, Verbose initialization and Verbose

data thereby increasing log level.

. Performance tuning of the Informatica mappings using various

components like Parameter files, Variables and

Static/Dynamic/Persistant Cache as per business requirement. Tuned

existing mapping, targets, sources, sessions and Sql queries to

improve the performance of work units.

. Mapplets and Reusable Transformations were used to prevent redundancy

of transformation usage and maintainability.

. Migrated mappings, sessions, and workflows from Development to testing

and then to Production environments.

. Monitor; troubleshoot batches and sessions for weekly and Monthly

extracts from various data sources across all platforms to the target

database.

. Developed Exception programs to maintain and clean up data exceptions.

. Created, scheduled, and monitored the sessions and batches on the

basis of run on demand, run on time using Informatica Power Center.

. Experienced in partition of data for better performance of sessions.

. Developed UNIX shell scripts to automate the data warehouse loading

and Stored Procedures and Triggers.

Environment: Informatica 8.6, Oracle 8i, PL/SQL, SQL Server 2005, Windows

NT, Unix, Erwin, Fixed width and delimited flat file, SQL, Control-M, Sql *

loader.

Dell Services, Bangalore, India

July 2008 - Aug 2010

Jr. Developer

Project Description

This project is to create new system to maintain valid data for various

applications. Design Load control process to perform continuous batch

processes.

. Mapped business requirements to source systems to decide what data

elements will be extracted.

. Create Low Level Design Document.

. Worked extensively in the development of Extract, Transformation and

Load routines.

. Coordinated between the onsite and offshore ETL teams as a lead

offshore developer for meeting the scheduled project milestones and

deadlines.

. Developed transformations and mapping guidelines for loading different

targets

Used Informatica Power Center to create maps and transform data.

. Created mappings using various active and passive transformations like

source qualifier, lookup, router, procedure and aggregator, filter,

joiner, expression and reusable mappings in Informatica.

. Documented all the mappings and the transformations involved in ETL

process

Unit and integration tested Informatica Sessions, Batches, Workflows

and the Target data.

. Implemented optimization and performance tuning of mappings to achieve

high response times.

Environment: Informatica 8.x/7.x, SQL Server 2000, Windows XP, Unix.

EDUCATION

. Bachelors Degree, Information Science, Visvesvarayya Technological

University, Bangalore, India, 2008

CERTIFICATIONS

MCTS: Microsoft .Net Framework - Application Development Foundation

.

Contact this candidate