Data Warehouse

Amin Hamid

Contact Details

*************@*****.***

Cell 404-***-****/ (Home) 404-***-****

Skills

ETL, Data Warehousing, Data Architect Erwin (4 years) Rational Rose (1 yr)

and

Database Modeling (10 years)

Informatica ( 8 years) Business Objects ( 1 year)

Relational Database Netezza, Hyperion Essbase ( 3 months)

Teradata,

Oracle, DB2 SQL Server (9 years)

Syncsort (1year) Brio( 1 year)

Unix, Linux ( 8 years), Perl ( 3 Cobol, JCL and VSAM ( 6 months)

months)

Summary

o Data Architect/ ETL Architect for Data mart and Data warehouse

Projects. Worked as Tech Lead for Projects from requirement gathering

to production deployment.

o Worked on large and complex data warehousing environment in all phases

Design, Development, Implementation and Production Support involving

more than 700 ETL jobs and 400 Scheduled reports. Worked on very large

data warehouse with size more than 120 TB. Used extensive partitioning

in Oracle for handling large volume of data.

o Data Architect for Data warehouse and Decision support

systems.Modeling of Data warehouses and OLTP applications. Worked with

OLTP applications in Oracle. Good understanding of Modeling

dimensional and relational database management systems using Erwin and

Rational Rose

o Design the ETL Architecture to maintain the staging database and load

the Enterprise Data warehouse/ Data marts. Set up the ETL architecture

by setting up servers for Informatica and Databases. Setting up and

maintaining the Development, Testing and Production Environment.

o Worked on Real Time ETL using oracle CDC and golden gate. Worked on

designing click stream data warehouse.

o Design, Develop, Test, Tune ETLs for loading ODS, Staging, Data

Warehouse and Aggregate tables using Metadata driven approach. Develop

of ETL using Informatica Power center/Power mart and Oracle PL/SQL.

Developed ETL using other languages like Syncsort, COBOL and Perl.

o Worked with multiple Databases such as Oracle, DB2, MS SQL Server,

Teradata, Netezza, Mainframes and flat files.

o Developed Business Objects Universe to meet the Reporting needs of the

users.

o Developed and maintain the reports using Brio. Helped in designing

Essbase outline.

o Worked on UNIX scripts for scheduling sessions, pre and post session

scripts.

o Knowledge of the entire Life cycle of Data warehouse and OLTP database

applications. Always been a Key member in defining standards for

Informatica implementation, testing and validating data. Worked in SEI

CMM level 4 organizations and followed SDLC processes for application

development and maintenance.

Professional Experience

Apr'10 to Date Employer : Capgemini

Projects:

Frontier Communications, NY Sr Consultant Apr'10 to Date

Frontier acquired Verizon infrastructure for state of WV. Verizon

maintained its Network inventory in mainframe based system. Frontier plans

to maintain inventory oracle based application. The project involved

migrating inventory from legacy system to Oracle based system. PL/SQL was

used for migration.

Platform: Oracle 10g, PL/SQL and UNIX.

Responsibilities:

. Understand different modules of the Network system inventory. Learn

the GUI interface for network inventory management.

. Developing and Modifying PL/SQL procedures for migration of the

inventory.

. Come up with the documentation for step by step order of code that

needs to be executed for migration.

. Build queries to test the migration validation. Build queries to

prepare a report of status of migration giving details of %

successfully migrated elements and fallouts with reasons.

Apr'06 to Apr'10 Employer : Comsys

Projects:

Autotrader.com, Atlanta Data warehouse Engineer Aug'08

to Apr'10

AutoTrader.com is a leading online automotive marketplace. It has inventory

of more than 3 million cars online and has millions of visitors per month.

The Enterprise data warehouse and data marts were built using diverse

sources from online web logs to master data bases. The reporting metrics of

clicks and impressions were at dealer and vehicle level. The size of stage,

EDW and data marts was nearly 30 TB in oracle.

Platform: Oracle 10g, Netezza 4.5.2, Erwin 7.2.0.1644, Informatica 8.6.1,

Linux and Perl.

Responsibilities:

. Data modeling for staging, EDW and Data Mart tables.

. Lead ETL designer leading a team of 3 ETL developers. The site

activity data of visitors, sessions, clicks and impressions was

captured and stored so that it can be reported from lowest grain of

car to aggregated level of region.

. Design, develop and implement ETL using Informatica, Oracle, Shell

script and Perl scripts.

. Design Metadata driven ETL solution so that maintenance and auditing

is easy.

. Maintain the web log parser scripts written in Perl to load the data

from weblogs into tables.

. Used Oracle partition swapping to load large volume tables quickly and

ease in reporting.

. Providing support to report developers to build report and tune the

reports.

. Working with team to incorporate new database Netezza. Utilize Push

Down Optimization in Informatica when developing mappings for Netezza.

Cingular Wireless, Atlanta (now AT&T) Sr Technical Architect

Apr'06 to Aug'08

Cingular Wireless is the leading Wireless Company in the USA with more

than 61 million subscribers who use the nation's largest digital voice and

data network. The Enterprise data warehouse had sources from various

Billing systems, CRM systems and other Sales and marketing sources. As a

part of Enterprise Data warehousing group, I was involved in designing,

building and maintaining the Data models and ETL Architecture to load the

data warehouse. The data volumes were very large with many tables have more

than Billions of records.

Platform: Oracle 9.2, Erwin 4, Informatica 7.2, 8.1.1, Teradata, UNIX and

Syncsort.

Responsibilities:

. Design the ETL Architecture to maintain the staging database and load

the Enterprise Data warehouse. The data warehouse is about 120 TB in

size and consist tables with more than 1 Billion records. The stage

database (Oracle is 4 TB)

. Data modeling for staging tables and Enterprise data warehouse.

. Played role of Application DBA to tune the database tables and

queries.

. Establishing the standards to develop the efficient ETLs for the

warehouse.

. Set up the ETL architecture by setting up servers for Informatica and

Databases. Setting up and maintaining the Development, Testing and

Production Environment.

. Maintain and tune the existing ETLs to meet the Data quality and meet

the timings to make the data available to the user community. Review

the ETL codes and specs.

. Lead for ETL development for a team of 7 members and provide guidance

to other ETL developers with Informatica.

. Develop the ETL for using the sources from various Billing systems on

Oracle and AS400.

. Worked on Oracle CDC process to load data in near real time.

. Write Mload and BTEQs to load the Teradata. Warehouse. Write extracts

using Fast exports to deliver data to downstream systems.

. Write Schedules in Maestro to load the data.

. Write Syncsort scripts to perform ETL on EBCDIC file extracts received

from AS400 sources.

Mar'04 to Mar'06 Employer : Netserv Applications Inc

American Cancer Society, Atlanta Data Architect Feb'05

to Mar'06

American Cancer Society (ACS) is a non profit organization which was

established to help people and Society fight against Cancer. It maintains a

huge database of constituents who wish to have knowledge about Cancer,

Donate for the cause or need help to fight Cancer.

As a part of Enterprise reporting team, I was involved in designing,

building and maintaining the reporting data marts for Siebel CRM, HR and

Distribution center (Warehouse Logistic)

Platform: Oracle 9.2, Erwin 4, Informatica 6.2, Business Objects 6.1.

Responsibilities:

. Develop Project Plan and track the progress of the project with

Project manager. Schedule the resources to optimize the load

distribution.

. Interact with Users and Business Sponsors to gather the Business

Requirements and give them regular updates on progress of the project.

. Analyzing different Source systems (Peoplesoft, Lawson and Siebel) to

identify the source tables for the Datamart.

. Modeling of Data Stores and Data warehousing tables. Designing the

Star Schema and De-Normalized tables.

. Designing the ETL strategy for the loading of Data Warehouse.

. Estimating the data size and data growth and estimating the space

requirements. Played and active role in determining the Hardware

requirements. Interaction with DBA's and Hardware Team, in finalizing

the hardware.

. Established Development, Test/QA and Production Environments.

. Designing and assisting in developing ETL Mappings in Informatica.

Mentoring of ETL developers and training them with ETL skills.

. Developing Test Plan for programmer testing. Assisted users for

developing User acceptance plan.

. Assist in Deployment of ETL in Production.

. Design of Business Objects Universe to meet the reporting needs. Also

set up the security to maintain the Restricted Access (row level) for

confidential data.

. Use SQL Server DTS to extract data from SQL server database to Oracle

database.

Projects:

Liberty Mutual Insurance Cincinnati ETL Architect

Mar'04 to Feb'05

Liberty Mutual is one of the leading Insurance providers. The project

involved designing, developing and populating the data store and data

warehouse for the different Personal and Commercial insurance lines of

business. The source system is relational transactional database. The data

was loaded into a data store before loading into the Enterprise data

warehouse.

I was involved in modeling the Data store and data warehouse, developing

ETL for loading data stores and data warehouse.

Platform: IBM DB2 7.2 and 8.0, Erwin 4, Informatica 6.2, UNIX Visio

Hyperion Essbase and Brio

Responsibilities:

. Analysis of Business requirements and developing ETL specifications

from requirements.

. Analysis of source systems and preparing source to target reference-

mapping document.

. Modeling of Data Stores and Data warehousing tables.

. Modeling the metadata tables for the tables and data loading.

. Designing the ETL strategy for the loading of Data Store, Data Staging

Layer and Data Warehouse.

. Designing and assisting in developing ETL Mappings in Informatica.

Implemented Informatica Partition strategy to get the write throughput

rate of more than 2000 rows per second.

. Designing Data Recovery strategies for ETL Loads.

. Reviewing load flows for new ETL using workflow designer.

. Tuning the sessions to optimize the performance. This was achieved by

identifying the bottle necks and resolving the resource contentions.

Some of the steps implemented were Tuning of Queries, creating

indexes, splitting of Mappings to eliminate massive Caches created

during the data load and using Partitions and Auto Loader scripts.

. Automating the loads by writing shell scripts.

. Testing the mappings, creating the test cases and automatic

balancing/Audit routines to validate the data after the ETL loads.

Aug'03 to Mar'04 Employer L&T Infotech Ltd, Mumbai and Phoenix (US)

Projects:

Motorola Inc Phoenix Data Warehouse Consultant Aug'03-mar'04

Motorola SPS is one of the leading manufacturers of semi conductor

products. The project involves supporting and maintaining the Data

warehouse for Motorola. The current database for data warehouse is more

than 400GB and consists of more than 750 loads scripts. The data sources

are also from wide range of OLTP applications such as MVS, SAP, Oracle and

even desktops in few cases.

Platform: Oracle 8.0, Erwin, Oracle PL/SQL, UNIX Visio, Hyperion and Brio.

Responsibilities:

. Data modeling for new tables for Staging and Data warehouse.

. Maintaining the Metadata tables

. Production Support and monitoring the ETL loads. Ensuring the

availability of data for the global end user community on 24 X 7

bases. Maintain the diverse source data feeds from SAP, Siebel and

Mainframe sources.

. Carrying out data recovery for ETL Load failures in a complex slowly

changing type-2 dimension tables.

. Design and developing ETL for new tables using PL/SQL.

. Unit testing and Regression testing of ETL.

. Tuning the ETL and scheduled reports.

. Maintenance and enhancements of Brio Reports.

. Help in designing Essbase outline for financial reporting.

. Writing and modifying Shell scripts for data loading for pre and post

data loading operations like dropping/ creating indexes and partitions

and calculating table statistics.

Sep'02- To Aug'03 Mastek Ltd, Mumbai and San Francisco as "Programmer

Analyst"

Projects:

GNX (now AGENTRICS) San Francisco Data warehouse Architect

Sep'02-Aug'03

Platform: Oracle 9.0, Rational Rose, Oracle PL/SQL, Unix.

Global NetXchange (GNX ) is the default collaborative supply chain platform

for its retail partners, who are looking at how to streamline operations

and improve the quality of their supply chain operations.

Collaborative Performance Management (CPM) is an application that will

provide a platform to the retailers, where they can view the performance of

their suppliers. The retailers will supply the fact measures as input to

CPM. These facts will be loaded into Data Warehouse (Oracle 9i). Based on

these facts the CPM will calculate the values for the predefined KPIs and

publish various reports. Retailers as well as suppliers can view these

reports.

Responsibilities:

. Design and Developing Data warehouse Data Model. Used the Bridge Table

concepts as explained in Notes of Ralph Kimball to implement variable

Hierarchy of dimensions

. Creating the logical and Physical Data Model.

. Designing Aggregation, Indexing and partitioning strategies for the

warehouse.

. Designing and developing the ETL methodology for the Data warehouse.

. Design; develop PL/SQL scripts for ETL.

. Tuning the ETL load process and Reports.

. Shell scripts for data loading and validating the input file.

. Testing the data laod and validating the data in fact tables and

aggregate tables.

Feb'00 - To August'02 Patni Computers Systems, Mumbai and Kansas (USA)

as "Senior Software Engineer"

Projects:

GE ERC Kansas Software Engineer feb'00-

Sep'02

GE-ERC is engaged in the Reinsurance business and is affiliated to GE

Capital.

Platform: Oracle 8.0, Erwin, Unix., Informatica and Business Objects

ISO REPORTING PROJECT

Westport Insurance is a primary Insurance company under GE-ERC. The

Westport Company is in process of reporting its database to ISO. The

current database of ERC is Oracle. The data to be reported to ISO is in

Flat file format.

The project involves extraction of data from Oracle. Building

transformation logic as per business rule, ISO specified manual and

generating Flat files in Mainframes format. Informatica mappings for

various lines of business were designed. Target copybooks were created

importing of source tables and extensive designing of transformation

objects were done using PowerCenter. For each line of Business in Insurance

a session is created in Power mart. The flat file was generated when these

sessions were run in the Server Manager.

Responsibilities:

Analyzing the present system and ISO reporting format as per different

Lines of Business of Insurance.

Modeling of the staging tables.

Developing Informatica Mappings, Setting up test database and populating

with test data.

Building Sessions and running them for the quarters to be reported

Quality checking of programs.

Writing Shell Scripts to Automate the running of sessions

The project had a sub module of generating reports of Automobile Insurance

for state of Texas for Westport. The reports are generated in mainframe. My

role includes

. Studying the rules for reporting Auto data

. Writing code in Cobol and generating reports in Mainframe.

. Testing and analysis of reports for elimination of errors in Data.

ETL PHOENIX RE

ERC was in the process of building a Data Warehouse in Oracle .The Data

Warehouse will be populated from the different OLTP systems of ERC. In

PHOENIX RE module, Phoenix Reinsurance Company's data will be moved into

ERC's database. Subsequently the ERC data will be moved into the Data

Warehouse. The extraction, transformation and loading activity (ETL) is

carried out using Informatica PowerCenter 1.7 and PowerMart 4.7.

As a Team Member, my responsibilities include:

. Requirement Analysis: Analyzing the requirement document sent by the

client.

. Designing of the mappings: Depending upon the requirements for a

particular company, the mapping is designed and the design document is

prepared considering the various guide lines given by the client. The

design considers the dataflow and the corresponding transformation

required to populate the destination tables using validations and

conditions as defined by the client.

. Development of the mappings: Based on the design document prepared for

a particular company the mapping is developed using Source analyzer,

Warehouse designer, transformation developer etc. The development may

also necessitate coding SQL statements, stored procedures and creation

of the mapplets as part of requirement. It also includes creation of

sessions/batches in the server manager for the mappings developed and

to run the sessions/batches to populate the data in the oracle

tables.

. Unit Testing : Preparation of Unit Test Cases/Condition for testing

purpose and to check the developed mapping against different test

conditions to ensure that the maping is as per the ERC standards.

. Writing pre session Shell Scripts for the sessions.

ERC Intranet Applcation.

Modeling the backend tables in Oracle for intranet application..

Developing the forms and reports in Lotus Notes.

Testing the application.

All the consulting companies were ISO 9001 certified SEI CMM level 5

organization. I have handled various roles as Quality Facilitator, Defect

Prevention Representative and SQA for various projects while working for

these companies.

Jun'99-Jan'00 Tata Infotech Ltd, Mumbai

Tata Infotech LTD Procurement Engineer

Jun'99-Jan'00

Platform: Oracle 8.0, Erwin 4, UNIX, D2k.

Tata Infotech has a manufacturing division producing document-processing

machines for Unisys USA. The organization has implemented Baan as its ERP

system. I was involved in the small module for Baan implementation. My my

role was

. Providing functional inputs to a separate Oracle module that will feed

data to Baan.

. Modeling of the Excise module tables.There were 40 tables external to

baan system and they would upload the data daily to central Baan data

. Developing forms using D2k.

. Testing the application.

Mar'97-May'99 Amoto Engineers Pvt Ltd, Thane,India

Designation Manufacturing Engineer

The company's business was manufacturing of textile machines like

Humidifiers and fluff collectors. I was involved in optimizing the

manufacturing process for the mechanical parts and developing vendors for

outsourcing the parts. Costing of the parts and setting up the Quality

Inspection plan were the key areas of responsibility for the outsourcing. I

was also involved in creating inventory management plan for production.

Aug'96-Feb'97 Grindwell Norton Ltd. Raigad, India.

Designation Graduate Engineer Trainee.

The organization is the leading manufacture of grinding wheels. I was

trained in the Grinding Technology for two months .My role was to carry out

trials at leading Auto Ancillary unit for optimizing the grades of grinding

wheel to be used. I had also designed the inventory plan for dies and tools

for a new plant set up at Nagpur.

Education

Mumbai University -India

Bachelor of Engineering

Year of Passing: 1996, 1st Class with honors.

Contact this candidate