Lorena Araque

ETL / INFORMATICA DEVELOPER

acfgeh@r.postjobfree.com

219-***-****

PROFESSIONAL SUMMARY:

• Seven (7) years of IT experience in the Analysis, Design, Development, Testing and

Implementation of business application systems for Health care, Financial, Telecom and

Manufacturing Sectors.

• Strong experience in the Analysis, design, development, testing and Implementation of Business

Intelligence solutions using Data Warehouse/Data Mart Design, ETL, OLAP, BI, Client/Server applications.

• Strong Data Warehousing ETL experience of using Informatica 9.1/8.6.1/8.5/8.1/7.1 PowerCenter Client

tools - Mapping Designer, Repository manager, Workflow Manager/Monitor and Server tools – Informatica

Server, Repository Server manager.

• Expertise in Data Warehouse/Data mart, ODS, OLTP and OLAP implementations .

• Analysis, requirements gathering, data modeling, ETL Design, development, System testing,

Implementation and production support.

• Extensive ETL testing experience using Informatica 9.1/8.6.1/8.58.1/7.1 (Power Center/ Power Mart)

(Designer, Workflow Manager, Workflow Monitor and Server Manager).

• Worked with Dimensional Modeling using Star and Snow Flake Schema, Identifying Facts and

Dimensions, Physical and logical data modeling using ERwin and ER-Studio.

• Expertise in working with relational databases such as Oracle 11g/10g/9i/8x, SQL Server

2012/2008/2005, DB2, MS Access and Teradata.

• Strong experience in extracting data from different source systems.

• Extensive experience in developing Stored Procedures, Functions, Views and Triggers, Complex

SQL queries using SQL Server, TSQL and Oracle PL/SQL .

• Experience in resolving on-going maintenance issues and bug fixes; monitoring Informatica sessions

as well as performance tuning of mappings and sessions.

• Well Experienced in doing Error Handling and Troubleshooting using various log files.

• Experience in all phases of Data warehouse development from requirements gathering for the data

warehouse to develop the code, Unit Testing and Documenting.

• Experience in writing UNIX shell scripts and automation of the ETL processes using UNIX shell scripting.

• Proficient in the Integration of various data sources with multiple relational databases like

Oracle11g /Oracle10g/9i, MS SQL Server, Teradata and Flat Files into the staging area, ODS,

Data Warehouse and Data Mart.

• Experience in using Automation Scheduling tools like Autosys and Control-M.

• Worked extensively with slowly changing dimensions.

• Hands-on experience across all stages of Software Development Life Cycle (SDLC) including business

requirement analysis, data mapping, build, unit testing, systems integration and user acceptance testing.

• Experience in handling initial and incremental loads in target database using mapping

variables.

• Developed effective working relationships with client team to understand support requirements,

develop tactical and strategic plans to implement technology solutions, and effectively manage

client expectation.

• Excellent written, communication skills and possessed analytic problem skills in evaluating

business and technical processes and issues, develops and implements system enhancements

to facilitate overall operations.

ACCOMPLISHMENTS AND PROFESSIONAL STRENGTHS:

• Extremely dedicated employee, who sets and exceeds goals within time-frames

• Accept complicated responsibilities with superior results in ever-changing workplaces adhering to

guidelines

• Detail-oriented, precise and meticulous employee – maintain public relations and provides best

service possible

• Plan, develop and follow through with appropriate, workable company procedures in busy work

environments

• Problem solver and decision maker, who identifies issues, understands processes and creates

timely solutions

Education:

Bachelor of Electronics Engineering, Saint Thomas University, Tunja, Colombia.

Technical Skills:

Operating Systems: Windows 7/2008/2007/2005/NT/XP, UNIX, MS-DOS

ETL Tools: Informatica Power Center 9.1/8.6/8.5/8.1/7.1(Designer, Workflow

Manager, Workflow Monitor, Repository manager and Informatica

Server)

Databases Oracle 11g/10g/9i/8i, MS SQL Server 2012/2008/2005, DB2,

Teradata.

Data Modeling tools Erwin, MS Visio, SmartDraw, ER-Studio, TOAD 9

OLAP Tools Cognos 8.0/8.1/8.2/8.4/7.0/, Business Objects XI r2/6.x/5.x,

OBIEE 10.1.3.4/ 10.1.3.3

Languages SQL, PL/SQL, T-SQL, UNIX, Shell scripts, Perl Scripting, C++,

Java, VisualBasic.NET, PHP, XML, HTML, CSS.

Scheduling Tools Autosys,Control-M

Testing Tools QTP, WinRunner, LoadRunner, Quality Center, Test Director

PROFESSIONAL EXPERIENCE:

Wells Fargo, Des Moines, IA Nov 2012 – till date

ETL Developer

Wells Fargo is one the largest banking and mortgage lending companies. The CRM operations of the

company involved channeling of various offers. The customer data is imported from various sources into

Customer management system (CMS), which is a data warehousing system where information is brought

into and processed. CMS is used by reporting and marketing teams in collecting relevant customer

information, and creating the selective offers for the customers. Informatica Power Center tool was used

to load strategic source data into CMS.

Responsibilities:

• Used Informatica PowerCenter 9.1 and its all features extensively in migrating data from OLTP to

Enterprise Data warehouse.

• Design and develop mapping, transformation logic in Informatica powercenter for implementing

business rules and standardization of source data.

• Designed and developed mappings with optimal performance using Aggregator, Joiner,

Normalizer, Rank, Sequence Generator, SQL, Transaction Control,Un-Cached & various

kinds of Cached Lookup, Connected-Unconnected-Source Target pre and post load

Stored Procedure Transformations,Update Strategy, Union, XML Transformations,etc.

• Designing and developing Workflow process for the complete system data flow process, using

subsets of mappings/Reusable Objects/Mapplet /worklets for easy system breakpoints and

maintenance.

• Developing UNIX shell scripts and PL/SQL procedures where required.

• Involved in scheduling the workflows through Informatica scheduler using UNIX scripts.

• Used Workflow Manager for session and database connection management

• Created parameter files and developed pre-session and post sessions routines

• Developed Technical Specifications for ETL processes

• Documented the approach in the Technical Design Document.

• Scheduled jobs to be run in the batch process

• Responsible for requirement gathering by direct interaction with users

• Responsible for analyzing of the existing OLTP design and designed OLAP

• Created various active and passive transformations to support voluminous loading of data target

table using Informatica PowerCenter

Environment: Informatica PowerCenter 9.1, PowerExchange, Oracle11g, Flat file, SQL Server, WinSCP,

Windows 7, Unix, Toad.

Fedex, Collierville, TN Jun 2011 – Aug 2012

ETL Developer/ Informatica Developer

Fedex is a company that provides shipping services worldwide. The goal of the project is to improve the

system performance. It is required to design a performance plan, test it and then implement it.

Performance tuning and SQL tuning are needed at different stages on the informatica application.

Responsibilities:

• Design and extract data from different source systems, transforming the data, then mapping and

loading the data into the corporate data warehouse.

• Ensure optimal performance tuning at various levels such as source, mappings, target and

sessions.

• Analyze and validate complex system requirements and existing business processes and

information schemes.

• Design, develop and implement new programs and modify existing applications.

• Translate the business requirements into detailed technical design.

• Use advanced performance optimization techniques

• Find the Informatica mappings bottlenecks and optimize the mappings to get the best

performance and tuning the SQL queries as well.

• Used Informatica debugging techniques to debug the mappings and used session log files and

bad files to trace errors occurred while loading

• Describe performance counters and used Thread Statistics.

• Involved in performance tuning of mappings, transformations and (workflow) sessions to optimize

session performance.

• Enabled the Integration Service to write to the targets, read source data and transform and move

data efficiently.

• Developed workflow tasks like reusable Email, Event wait, Timer, Command, Decision.

• Enabled PowerCenter service processes to obtain a faster performance.

• SQL Tuning, rewrite SQL code to improve performance

• Wrote PL/SQL stored Procedures and Functions for Stored Procedure Transformations.

• Develop efficient ETL mappings by applying standard procedures and unit testing.

• Implement ETL routines and prepare support documentation.

Environment: Informatica PowerCenter 8.6, PowerExchange, Oracle11g, DB2, MS SQL Server 2008,

Flat file, XML, Linux, Toad.

Great-West Healthcare, Denver, CO Jul 2010 – May 2011

ETL Developer/ Informatica Developer

Great-west is one of the nation’s leading Insurance providers. The flow of data was from different sources

and types, which encompassed premiums, claims, expenses and contracts. The primary objective of this

project is to get data from different sources (SQL Server, Oracle, Flat file) and perform the necessary

operations on the data as per the user requirement and load into the data warehouse for analysis.

Responsibilities:

• Involved in Dimensional modeling to Design and develop STAR Schemas, used ER-win 4.0,

identifying Fact and Dimension Tables.

• Worked with different Sources such as Oracle, MS SQL Server and Flat file.

• Used Informatica to extract data into Data Warehouse.

• Extensively used several transformations such as Source Qualifier, Router, Lookup

(connected & unconnected), Update Strategy, Joiner, Expression, Aggregator and

Sequence generator transformations.

• Identified and tracked the slowly changing dimensions, heterogeneous Sources and

determined the hierarchies in dimensions.

• Created reusable transformations and Mapplets and used them in mappings.

• Used Informatica Repository Manager to maintain all the repositories of various applications.

• Developed number of Complex Informatica Mappings, Mapplets and Reusable Transformations to

facilitate Daily, Monthly and yearly Loading of Data.

• Involved in fixing invalid Mappings, testing of Stored Procedures and Functions, Testing of

Informatica Sessions, and the Target Data.

• Worked on SQL tools like TOAD to run SQL queries to validate the data.

• Design and Development of pre-session, post-session and batch execution routines to run

Informatica sessions using Informatica Server.

• Created and Scheduled sessions & Worklets using workflow Manager to load the data into the

Target Database

• Wrote PL/SQL stored Procedures and Functions for Stored Procedure Transformations.

• Used Control –M scheduler to automate the process.

Environment: Informatica PowerCenter 8.6, PowerExchange, Oracle11g, MS SQL Server 2008, Flat file,

XML, Control-M (scheduler), Linux, Toad.

Blue Cross Blue Shield, Rochester, NJ Feb 2009 – May 2010

Informatica Developer

The client is into the Health and Medical care vertical. The prime responsibility of the team is to develop a

Patient Care Data Warehouse (PCDW).This data warehouse is used to access easily the detailed clinical

data on a platform and also to facilitate the enterprise-wide data analysis within the health care

environment. The PCDW is up and running with incremental growth starting from 20 Terabytes of data.

This Data Warehouse is build using Informatica Power Center 8.6 for extracting data from various

sources.

Responsibilities:

• Analyzed the requirements and framed the business logic for the ETL process.

• Involved in the ETL design and its documentation.

• Interpreted logical and physical data models for business users to determine common data

definitions and establish referential integrity of the system using ERWIN.

• Followed Star Schema to design dimension and fact tables.

• Used Informatica Designer to create mappings using different transformations to move data to a

Data Warehouse from SQL server and Oracle.

• Developed mappings in Informatica to load the data from various sources into the Data

Warehouse, using different transformations like Source Qualifier, Expression, Lookup,

Aggregate, Update Strategy and Joiner.

• Experienced in handling slowly changing dimensions.

• Optimized the performance of the mappings by various tests on sources, targets and

transformations.

• Scheduling the sessions to extract, transform and load data in to warehouse database on

Business requirements.

• Scheduled the tasks using Autosys.

• Loaded the flat files data using Informatica to the staging area.

• Created SHELL SCRIPTS for generic use.

• Developed Test cases for Unit Testing of the Mappings, and also was involved in the Integration

Testing.

Environment: Informatica Powercenter 8.6, UNIX, SQL Server 2000/2008, Oracle 10g, SQL, PL/SQL,

SQL Developer, MS VISIO,ERWIN, OBIEE,IBM.

Nokia, Irving, TX Oct 2007 – Dec 2008

Informatica Developer

Nokia is one of the major wireless companies in U.S.A and the foremost in providing G.S.M. phones in

U.S. market. The company has huge volumes of data coming from different sources which were

generated from multiple and cross platform applications. The Project involved design and development of

data warehouse for the sales department.

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.

• 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.

• Used workflow manager for session management, database connection management and

scheduled the jobs to run in the batch process.

• Developed Slowly Changing Dimensions for Type 2 SCD.

• Troubleshoot the issues by checking sessions and workflow logs.

• Involved in analyzing the bugs, performance of PL/SQL Queries and provided solutions to improve

the same.

• Written and used UNIX shell scripts extensively for scheduling and pre/post session management

• Involved in the performance tuning process by identifying and optimizing source, target, mapping

and session bottlenecks.

• Involved 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.

• Prepared the Standard Operating Procedure (Knowledge Transfer) document, which provides

necessary information, required for the Maintenance and Operation of the application.

• Provided data loading, monitoring, system support and general trouble shooting necessary for all the

workflows involved in the application during its production support phase.

• Actively participated in data base testing like checking the constraints, correctness of the data,

stored procedures, field size validation, etc.

Environment: Informatica Power Center 8.6, Oracle 11g, PL/SQL, SQL Server 2005, T-SQL, Erwin 4.0,

Win 2008, Perl scripts,, Flat files, Toad, Autosys, ODBC.

Abbott Labs, Chicago, IL Aug 2006 – Jun 2007

ETL Developer

This project responsibility is to develop an Enterprise DataWarehouse (EDW). The responsibility of this

project was to completely integrate the business to single environment. This data warehouse is used to

access easily the detailed data on a single platform and also to facilitate the enterprise-wide data analysis

to Reporting within the business environment. This Data Warehouse is build using Informatica Power

Center 8.6 for extracting data from various sources including flat-files, Teradata and Oracle.

Responsibilities:

• Analyzed the requirements and framed the business logic for the ETL process.

• Extracted data from Oracle as one of the source databases.

• Involved in the ETL design and its documentation.

• Interpreted logical and physical data models for business users to determine common data

definitions and establish referential integrity of the system using ER-STUDIO.

• Followed Star Schema to design dimension and fact tables.

• Experienced in handling slowly changing dimensions.

• Collect and link metadata from diverse sources, including relational databases Oracle, XML and

flat files.

• Responsible for the development, implementation and support of the databases.

• Extensive experience with PL/SQL in designing, developing functions, procedures, triggers

and packages.

• Developed mappings in Informatica to load the data including facts and dimensions from various

sources into the Data Warehouse, using different transformations like Source Qualifier,

Expression, Lookup, Aggregate, Update Strategy and Joiner.

• Developed reusable Mapplets and Transformations.

• Used data integrator tool to support batch and for real time integration and worked on staging and

integration layer.

• Extracted DTS (Data Transformation Services) packages from the SQL server 2000.

• Identified the transformations used in each DTS packages.

• Converted the DTS packages into Informatica mappings and loaded the target into the Oracle

database.

• Optimized the performance of the mappings by various tests on sources, targets and

transformations

• Design, develop and Informatica mappings and workflows; Identify and Remove Bottlenecks in

order to improve the performance of mappings and workflows

• Scheduling the sessions to extract, transform and load data in to warehouse database on

Business requirements.

• Scheduled the tasks using Autosys.

• Loaded the flat files data using Informatica to the staging area.

• Created SHELL SCRIPTS for generic use.

• Created high level design documents, technical specifications, coding, unit testing and resolved

the defects using Quality Center 9.0.

• Developed unit/assembly test cases and UNIX shell scripts to run along with

daily/weekly/monthly batches to reduce or eliminate manual testing effort.

Environment: Windows XP/NT, Informatica Powercenter 8.6, UNIX, Teradata, Oracle 10g, Oracle Data

Integrator, SQL, PL/SQL,SQL Developer, ER-win, Oracle Designer, MS VISIO, Autosys, Korn Shell.

CitiBank, Tunja, Colombia Mar 2006 – May 2006

Informatica Developer

City Bank is a leading Investment and wealth management banking company involved in financial

services. City Bank is a full-service center, offering all general services and specific option such as Asset

Management, Mortgage Loans, Internet banking and executive services such as Private Banking.

Cash Management Department collectively implemented an end-to-end global financial solution to

strengthen and integrate internal controls and reporting, reduce overall costs of financial systems, create

operational efficiencies and global standards for Finance and Human Resources. Project was executed in

phases building up from extraction, computation logic, dimension models and populating data using

Informatica.

Responsibilities:

• Extensively worked with the data modelers to implement logical and physical data modeling to

create an enterprise level data warehousing.

• Extensively worked on creating Facts and Dimensions tables

• Designed and Developed Oracle PL/SQL Procedures and UNIX Shell Scripts for Data

Import/Export and Data Conversions.

• Created and Modified T-SQL stored procedures for data retrieval from MS SQL SERVER database.

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

• Extensively used Informatica Power Center 7.1 to extract data from various sources and load in to

staging database.

• Extensively worked with Informatica Tools – Source Analyzer, Warehouse Designer, Transformation

developer, Mapplet Designer, Mapping Designer, Repository manager, Workflow Manager, Workflow

Monitor, Repository server and Informatica server to load data from flat files, legacy data.

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

Router, Filter, Update Strategy, Joiner, Transaction Control and Stored Procedure .

• Designed the mappings between sources (external files and databases) to operational staging

targets.

• Involved in data cleansing, mapping transformations and loading activities.

• Developed Informatica mappings and Mapplets and also tuned them for Optimum performance,

Dependencies and Batch Design.

• Implemented Slowly Changing Dimension methodology for Historical data.

• Fine-tuning of PL/SQL Stored Procedures.

• Designing mapping templates to specify high level approach.

• Provided production support for Business Users and documented problems and solutions for running

the work flow.

• Extensively worked on unit testing and implemented on transformations, mappings.

• Extensively involved in documentation of the project.

• Reports were generated using Business Objects for analysis.

Environment: Informatica Power Center 7.1, Teradata, Oracle 10g, PL/SQL, SQL SERVER 2005, UNIX

Shell scripting, Business Objects, Control-M, TOAD,.

Contact this candidate