Resume of Sr ETL Developer

Title
Sr ETL Developer

Primary Skills
Informatica 7.1, Oracle

Location
US-OH-Columbus (will consider relocating)

Posted
Apr-09-07


SUMMARY:

• 7+ years of work experience in IT Industry in Analysis, Design, Development and Maintenance of various software applications in client-server environment.
• 4+ years of experience in providing Business Intelligence solutions in Data Warehousing using PowerCenter Informatica ETL Tools.
• Experience in Business Objects suite like BO reporter, Supervisor, Designer Web Intelligence, Info View and Broadcast Agent.
• Responsible for all activities related to the development, implementation, administration and support of ETL processes for large-scale data warehouses using PowerCenter and PowerMart.
• Hands on experience in tuning mappings, identifying and resolving performance bottlenecks in various levels like sources, targets, mappings, and sessions.
• Designed and developed efficient Error Handling method and implemented throughout the mappings.
• Strong knowledge in multidimensional analysis using Business objects 5.1
• Expertise in gathering the business requirements and creating Universes in the Designer.
• Experience in Study, Design, Analysis, Development, and Implementation of
E-Business Intelligence software applications under different environments.
• Experience in Full Life Cycle Implementation of Data warehouses.
• Practical understanding of the Data modeling (Dimensional & Relational) concepts like Star-Schema Modeling, Snowflakes Modeling, fact and dimension tables modeling of data at all the three levels: view, logical & physical.
• Good experience in UNIX and writing shell scripts for Informatica pre & post session operations and database administration activities.
• Self-Starter who can adapt and learn fast with excellent communication & interpersonal skills.
• Excellent Analytical and Problem Solving Skills.

TECHNICAL SKILLS:

ETL Tools : Informatica PowerCenter 7.1/6.2
Business Intelligence : Business Objects 5.1/5.0, Cognos
Data Modeling : Dimensional Data Modeling, Star Join Schema Modeling, Snow
Flake Modeling, FACT and Dimensions Tables, Physical
and Logical Data Modeling, Erwin 3.5.2/3.x, Microsoft Visio.
RDBMS : Oracle 9i/8i, SQL, PL/SQL, SQL*Plus, SQL*Loader,
IBM DB2 UDB 8.0/7.0, MS SQL Server 2000/7.0/6.5,
MS Access 7.0/2000
Tools : Toad, AQT, Quest Central for DB2
Languages : SQL, PL/SQL, UNIX Shell Scripting, C, Visual Basic.
Operating Systems : Windows 2000, UNIX AIX.



QUALIFICATION:

• Bachelor of Technology, India
• Certified Informatica Advanced Mapping Designer.


Client: WellsFargo PCS, Sanfrancisco, CA (Aug 2006 -- Till Date)
Sr. Data Warehouse Developer

Description:
Wells Fargo is one of the leading financial companies in USA specializing in banking and other financial services. My work at Wells Fargo was with the Private Client Services (PCS) department which maintains the accounts for company's wealthiest clients.

My work at the PCS was to play a crucial lead role in developing the data mart. All my work involved designing, developing, and maintaining the data in the Brokerage Data Mart - BDM. Involved with all the phases of the full software life cycle starting from gathering the requirements for designing DW system, data modeling involving the design of the logical and physical databases, Data warehouse design with decision support in mind, development (The ETL was done through Informatica PowerCenter 7.1, 7.2) and maintenance.

The ETL process involved extracting the data, temporarily placing the data in a ODS and then directing the data into the actual target Brokerage Data Mart.

Responsibilities:
• Worked with the PCS financial services staff and Business analysts for requirements gathering, business analysis, testing, and metrics and project coordination.
• Designed the 'Dimensional model' of the Data warehouse and used Erwin 4.0 to design the business process, grain of the data representation, dimensions and fact tables with measured facts.
• Identified all the conformed dimensions to be included in the target warehouse design and confirmed the granularity of the facts in the fact tables.
• Created various data marts for both kinds of applications, Credit-cards and Financial and used effective querying and formatting tools to present the data to the end users.
• Worked with huge tables that consisted of around 256 columns and 100 million records per each table.
• Used complex data transformations with around 25 transformations for each mapping
• Improved the performance of the mappings by moving the filter transformations early into the transformation pipeline, performing the filtering at Source Qualifier for relational databases and selecting the table with fewer rows as master table in joiner transformations.
• Identified and tracked the slowly changing dimensions/mini dimensions, heterogeneous Sources and determined the hierarchies in dimensions.
• Implemented various integrity constraints for data integrity like Referential integrity using primary key and foreign keys relationships.
• Used task developer in the Workflow manager to define sessions
• Created reusable worklets and mapplets and transformations.

• Involved in writing shell scripts on Unix (IBM - AIX) for Informatica ETL tool to run the Sessions.
• Created dimensions, hierarchies, mappings and complex mapplets using Informatica PowerCenter 7.1.
• Responsible for monitoring all the sessions that are scheduled, running completed and failed. Involved in debugging the Mappings that failed.
• Email and paging facilities are also utilized at key points in the process.
• Writing Oracle PL/SQL procedures for processing business logic in the database. Tuning of Database queries for better performance.
• Involved in designing and building of Universe, Classes and Objects.
• Developed objects in the universe using some calculations on existing columns in database
tables.
• Creation of application-specific reports like, Reports by customer, Reports by account, Reports by credit card.
• Documented processing times for each module, developed test cases and used them to run through each process. Tuned the matching parameters based on test results. Implemented and supported the Business Intelligence environment and User Interface.


Environment: Informatica PowerCenter/Powermart 7.1, UDB DB2, Oracle 9i SQL/PLSQL, Business Objects 6.5, UNIX Shell Programming, TOAD, Erwin 4.0, Unix (IBM - AIX), and Windows NT.

Client: Providian Financial, Oakland, CA (Jun 2005 -- Jul 2006)
Sr. Data Warehouse Developer

Description:
Providian Financial Corporation is a company specializing in financial services, credit cards etc?. A third party vendor called Total Access maintains the data of Providian. Efforts are on from the Providian side, to build its own Data warehouse from the DB2 mainframe database (12 terabytes) being maintained by Total Access. Especially the aim is to build two different types of data warehouses, EDW and OIS, while the latter maintains data for 6 months, the former maintains all the history spanning over several years.

My work at Providian was with the Financials groups, which is a core group. All my work involved designing, developing, and maintaining the data in the OIS data warehouse and then buliding the EDW using the data from OIS. Involved with all the phases of the life cycle starting from gathering the requirements for designing DS systems, data modeling involving the design of the logical and physical databases, Data warehouse design with decision support in mind, and development .

The ETL process involved extracting the data from DB2 mainframe as source, with the target data warehouse being in Oracle.

Responsibilities:

• Participated in the entire requirements engineering process right from requirements elicitation phase (where we gather the requirements from the retail business owners and marketing people) to the phase involving documenting of the requirements.

• Managed the entire “ETL process” involving the access, manipulation, analysis, interpretation and presentation of information from both internal and secondary data sources to customers in sales area.
• Developed complex mappings using Informatica PowerCenter Designer to transform and load the data from various source systems like Oracle, Teradata, Sybase into the Oracle target database.
• Analyzed and understood all data in the source databases and designed the overall data architecture and all the individual data marts in the data warehouse for each of the areas Finance, Credit Cards, Brokerage.
• Involved in the creation of oracle Tables, Table Partitions, and Indexes.
• Implemented various integrity constraints for data integrity like Referential integrity using primary key and foreign keys relationships.
• Handled alerting mechanisms, system utilization issues, performance statistics, capacity planning, integrity monitoring, population, maintenance, reorganization, security, and recovery of databases.
• Worked in Off-shore On-shore Co-ordination setting, delegating and managing a group in India's Accenture
• Involved in quality assurance of data, automation of processes.
• Involved in the development and testing of individual data marts, Informatica mappings and update processes.
• Identified and tracked the slowly changing dimensions/mini dimensions, heterogeneous Sources and determined the hierarchies in dimensions.
• Created synonyms for copies of time dimensions, used the sequence generator transformation type to create sequences for generalized dimension keys, stored procedure transformation type for encoding and decoding functions and Lookup transformation to identify slowly changing dimensions.
• Used Task developer in the Workflow manager to define sessions
• Assisted retailers in understanding consumer buying patterns and in creating consumer sets for marketing campaigns
• Created application-specific Data Marts so that users can access personalized dashboards of information that is specific to their department and business unit.
• Responsible for monitoring all the sessions that are running, scheduled, completed and failed.
• Worked on database connections, SQL joins, cardinalities, loops, aliases, views, aggregate conditions, parsing of objects and hierarchies.
• Defined the program specifications for the data migration programs, as well as the necessary test plans used to ensure the successful execution of the data loading processes.
• Created repository, users, groups and their privileges using Informatica Repository Manager
• Involved in writing UNIX shell scripts for Informatica ETL tool to run the Sessions.
• Connection management and scheduling of jobs to be run in the batch process
• Generated detailed reports from the data marts using Business Objects.

Environment: Informatica PowerCenter/Powermart 7.1, Business Objects, Oracle 9i, DB2, SQL/PLSQL, dynamic SQL, UNIX Shell Programming, Erwin 4.0, Unix (IBM - AIX), and Windows NT.

Center Point Energy, TX (Jan 2004-May 2005)
Cost Optimization Report Data Warehouse (CORDW)
Role: Sr.ETL Informatica Developer

Description: Center Point Energy is the third largest natural gas delivery company in the U.S. with nearly 3 million metered customers in six states. The scope of the project is to build Data Marts for business analysis departments, such as generation, transmission, distribution and customer satisfaction. The project involved extracting data from the different sources and loading into data Mart. The data mart has been designed using ERwin adopting Star Schema methodology. Models are created based on the dimensions, levels and measures required for analysis. It enables an insight in to the current and future customer service and helps in making business decisions based on the reports generated from data warehouse.

Responsibilities:
• Involved in the Data warehouse data modeling based on the client requirements
• Developed Logical and physical database designs for the transaction system.
• Used PMCMD command to start, stop and ping server from UNIX and created UNIX Shell scripts to automate the activities.
• Performance tuning on sources, targets, mappings and SQL queries in the transformations
• Created reusable transformations and mapplets based on the business rules to ease the development
• Used various Informatica Error handling technique to debug failed session
• Developed mappings to implement type 2 slowly changing dimensions
• Developed workflow tasks like reusable Email, Event wait, Timer, Command, Decision
• Used various debugging techniques to debug the mappings
• Creating Test cases for Unit Test, System Integration Test and UAT to check the data quality.
• Created Materialized view for summary data to improve the query performance
• Responsible for loading data into warehouse from different sources using Oracle loader to load millions of records and used Import/Export utility to load small tables.
• Involved in designing and building of Universe, Classes and Objects.
• Exported the universe in to the repository and maintained the domains regularly.
• Developed objects in the universe using some calculations on existing columns in database tables.
• Used drill analysis in Business Objects which will allow the user to view data at a more detail level, than current level
• Extensively interacted with the Business Analysts to create reports according to their requirements and specifications.

Environment: Informatica PowerCenter 7.1.1, Business Objects 5.1.1 ,Oracle 9i, SQL Server 2000, PL/SQL, Mainframe DB2, MS Visio, ERWIN Data Modeling tool, Advanced Query Tool, Windows 2000, UNIX AIX 5.1



Client: Wyeth Pharmaceuticals, Collegeville, PA (Sep 2002 -- Dec 2003)
ROLE: ETL Developer

Wyeth is one of the world's largest research based pharmaceutical companies. It is a global leader in pharmaceuticals, consumer health care products and animal health care products. The project deals with building a real time view of enterprise wide data. A decision support system was built to compare and analyze product prices, their quantities and patient profiles. IMS Health data is combined with data from other sources and is made available for ad hoc reporting. This Data Warehouse is used to deliver reports and information to sales and marketing management.

Responsibilities:
• Coordinating with the client and gathering the user requirements to create mapping specs.
• Handled the 2 Terabytes of data warehouse database.
• Performance tuning on sources, targets, mappings and SQL queries in the transformations. Worked on flat files as sources, targets and lookups.
• Responsible for migrating the folders or mappings/workflows from development to production environment, Creating shortcuts to share the repository objects
• Developed various optimization techniques in Aggregator, Lookup, Joiner transformation
• Designed mappings and scheduled workflows to Load data into Fact tables
• Developed mapping to implement type 2 slowly changing dimensions
• Developed Informatica parameter files to filter the daily data from the source system
• Used various debugging techniques to debug the mappings
• Creating Test cases for Unit Test, System Integration Test and UAT to check the data quality
• Created Oracle Stored Procedure to implement complex business logic for good performance
• Used various Oracle Index techniques to improve the query performance
• Created Materialized view for summary data to improve the query performance
• Responsible for loading data into warehouse from different sources using Oracle loader to load millions of records and used Import/Export utility to load small tables.
• Responsible for scheduling the workflow based on the nightly load.
• Supported Oracle 9i databases running mission critical 24*7 systems.
• Performed scheduling techniques with ETL jobs using scheduling tools, cron jobs through pmcmd commands, based on the business requirement.

Environment: Informatica Power Center 7.1.1, Business Objects 5.1.1, Oracle 9i & 10g, PL/SQL, Mainframe DB2, MS Visio, ERWIN Data Modeling tool, TOAD, Windows 2000, UNIX AIX 5.1

Syndicate Bank, Bangalore, India. (May 2001- Aug 2002)
Role: Developer

Description: The Banking Application was developed for Syndicate to meet the Requirements of bank transactions Such as deposits, loans and account maintenance. This Application had different modules like Master Maintenance, Deposit Maintenance, and Loan Maintenance. The Master Maintenance includes all the details of account holders, joint holders, nominee details, scheme details, reinvestment and closing of deposits. Deposits Maintenance includes all the details of the various types of deposits such as fixed deposits, Monthly income deposit, Short deposits and recurring deposits and al so reinvestment certificate. The loan maintenance includes loans such as housing, industrial and agricultural loans

Responsibilities:

• Created, monitored and maintained Oracle databases.
• Created Tablespaces for Data and Indexes.
• Created Tables, Indexes, Sequences, Clusters, Triggers, Procedures, Functions and Packages.
• Re-organization of databases and managing the databases for optimum performance levels.
• Writing database scripts for user management and roles.
• Configuring ODBC connectivity for new instances and clients.
• Formulating optimization strategies based on performance cost and rules.
• Performing SQL query optimization using hints, Indexes and Explain plan.
• Wrote PL/SQL Scripts for DDL operations such as to create alter and drop database objects likes tables, views, sequences, procedures and functions.
• Written stored procedures and functions to retrieve the data from database using PL/SQL.
• Different database triggers containing PL/SQL were created and stored in the database and fired off when contents of database were changed
• Created various reports based on the client requirements using Crystal Reports

Environment: Visual Basic 6.0, Oracle 8i, PL/SQL, Crystal Report 6, Erwin, Windows NT.


Cipla India Ltd. India (Aug 2000-- Apr 2001)
Programmer Analyst

The Inventory Management System (IMS) developed for the company involves tracking of various inventories with in the production cycles. Worked as a programmer in the Design and Development of the IMS for the information resources for the production departments within the organization.

Role and Responsibilities:

• Identified requirements, performed analysis and documented the specifications.
• Design - Database design, data modeling and application design.
• Created database objects like tables, views, synonyms, indexes and sequences.
• Extensively used Database Triggers, procedures, functions and packages.
• Oracle PL/SQL was used for generating checks reports and error reports.
• Used SQL Loader and Export, Import utilities to load the data.
• Documenting guidelines for support team.
• Involved in extensive testing of the applications and Involved in training the end users.

Environment: Oracle 8i, Informatica PowerCenter 5.1, Erwin, Windows 98

Certifications
Certified Informatica Advanced Mapping Designer.


You must be logged in and have a current resume access subscription. Login or Register »

View all resumes in US-OH-Columbus »
View all resumes in US-OH »

View other Sr ETL Developer resumes, Database Administrator resumes