Project Data

[pic]

RAKESH RANJAN

C: 724-***-**** Email: *********@*****.***

Summary:

. Over 6.5 years of experience in data warehousing, data modeling and

ETL - ELT process.

. Worked on all phases of data warehouse development lifecycle including

requirement gathering, design, development, testing, implementation

and support.

. Experienced in design and leading a team of developers in Data mart

development.

. Experienced in effort estimation for ETL projects and defined strategy

for ETL processes. Prepared high and low level design documents.

. Excellent knowledge of SCD, CDC and dimensional modeling (Star and

Snowflake schema). Designed and implemented data marts with bottom up

approach.

. Excellent knowledge of data modeling and normalization techniques.

. Expert level experience in Oracle Data Integrator (ODI). Excellent

knowledge of ODI master and work repository management. Expert in ODI

administration, tool setup and configuration.

. Expert in development of ODI interfaces and packages that populate

data-mart(s) and aggregate/summary tables and customizing ODI

knowledge modules.

. Expert level experience on Informatica Power Center 6.x/7.x/8.x.

Informatica Certified Professional.

. Passed certification exam for SQL from Oracle University.

. Clear understanding of OBIEE. Experienced in OBIEE administration,

deploying RPD, Catalog and Publisher.

. In-depth knowledge and experience in Oracle database (SQL and PL/SQL).

Good working experience in Informix, Teradata and SQL Server

databases.

. Excellent knowledge of modeling tool Erwin.

. Good knowledge of Business Intelligence and Analytics Software - SAS

and Data Stage.

. Command line Knowledge in UNIX (Sun Solaris).

. Attended many webinar conducted by DW guru Ralph Kimball and other

industry veterans on best practices in DWH. Attended online self

guided ODI training. Active member in ODI and Informatica online

forums.

. Self-starter with strong work ethics, good time management skills,

establish and maintain productive working relationship with

management, Clients and team members.

. Clear written, Analytical and oral perception of ideas and concepts.

Ability to articulate alternative solutions or workarounds. Ability to

listen to superiors and absorb new technological advances.

Technical Skills:

ETL: ODI 10.x, OWB 9/10g, Informatica 6.x/7.x/8.x, SAS

Data Integration Studio 3.3.

Reporting: OBIEE, BOXI, CE10

Modeling: Erwin 4.1/7.2

Scheduling: Redwood Cronacle 6.0

Operating System: UNIX (SunOS), Windows XP/NT/2000

RDBMS: Oracle 8i/9i/10g/11g, Teradata 6.2, MS SQL Server

2000, Informix.

Languages: SQL, PL/SQL, Teradata SQL, Base SAS 9.1

Professional Experience:

1. Citco ODI Specialist/Administrator

Oct 09 to till date

Project: Claris, CDD and CCore

Description: The Citco Group of Companies, a global organization of

financial services companies, is involved in providing niche financial

services in hedge fund administration, custody and fund trading, financial

products, and corporate and fiduciary solutions to its clients. Primary

objective of Claris is to provide real-time reports to the its world wide

client on their portfolios. Hence data was replicated from OLTP system to

DW almost immediately and it was available for reporting resulting.

Contribution:

. Discussed with business owners and analyzed requirements, and

made changes in existing design to meet business requirements.

. Designed the process to replicated real time data from OLTP to

DW. Made changes in LKM to optimize its performance.

. Written maintenance scripts to delete logs from ODI work

repository. Implemented error reporting thru email on failure.

. Assisted development teams in debugging developed code.

. Installed latest version of ODI in UNIX environment on Sun

Solaris operating system. Created master and work repositories.

Upgraded ODI to higher versions and upgraded work and master

repositories.

. Configured multiple agents as per specific project requirements

and prepared maintenance handbooks.

. Migrated ODI objects from one environment to another as per

request and prepared deployment scripts.

. Created new users and given requested privileges by creating

custom profiles.

. Trained and mentored resources on ODI.

. Additionally responsible for OBIEE and BOXI administration.

. Deployed RPD, Catalog and Publisher in UAT and PROD.

. Cleared cache in Production and UAT when ever required.

. Promoted BOXI and CE10 reports from one environment to another

and performed server setup and maintenance.

Environment: ODI 10.1.3.5.3, Oracle 10g/11g, Oracle SQL Developer,

OBIEE, BOXI, CE10, Sun Solaris 5.10

1. Accenture ETL (ODI) Specialist

Oct 08 to Sept 09

Project: Infrastructure Stabilization data warehouse (ISDW)

Description: This project was developed for United States

Department of Agriculture (USDA). Project was divided into two parts. Aim

of first part was to move existing data marts running on Informix database

which uses shell scripts and PL/SQL codes for ETL process to run on more

efficient infrastructure with Oracle database and to replace all the

scripts by writing ETL into ODI. Second part was aimed at developing an

enterprise data warehouse covering exiting data marts and new subject areas

as per client's requirements. I was involved in first part i.e. in

migration part.

Contribution:

. Did initial estimation of the effort required for migrating all

the data marts from Informix to Oracle by rewriting ETL process

in ODI.

. Performed initial tool setup and testing of ODI installation.

Configured agents at local and remote server. Created users and

assigned required privileges to them.

. Analyzed existing shell and PL/SQL scripts and created high and

low level design documents for developing ODI interfaces.

. Involved in discussion with data mart owner's to understand

current process and design to be process in Oracle.

. Defined strategy for ETL process keeping limitations of ODI in

mind.

. Developed and tested interfaces, procedures and packages in ODI

for historical data load and daily incremental load process.

. Developed ODI interfaces for extracting the data from files and

database tables and load them into Oracle database.

. Customized ODI knowledge modules for specific requirements.

. Created scenarios in ODI for scheduling purpose.

. Defined ETL strategy for handling large volume of data for

history load and ad-hoc data process.

. Mentored junior team members and trained team members on ODI.

. Identified bugs in ODI tool, identified limitations and

suggested areas of improvements to Oracle.

Environment: ODI 10.1.3.4, Oracle 11g, Informix, SQL Server, Toad

9.1.0, Sun Solaris

2. Deloitte Consulting DWH consultant/Data Modeler

Feb 08 to Oct 08

Project: California Court Management System (CCMS)

Description: This project is developed for Judicial system of Govt of

California to provide people with next generation court system. It is one

of the biggest judicial system project executed world-wide.

This project mainly aims to make all court related activities online,

starting from case filing till disposition/sentencing and after that

jail/probation thereby avoiding all the paper work. It connects all the

courts of California. Traffic related crimes are also addressed. State wide

data warehouse is being developed to deliver single version of truth about

pending cases and disposed case across the county courts. It will be used

to ascertain key performance of resources as well.

Contribution:

. Member of Enterprise data architecture team. This team is responsible

for designing data model for CCMS and designing a state wide data

warehouse.

. Discussion with user and functional team to understand business

requirements.

. Designing/Changing Erwin data model according to requirement.

. Defining strategy for ETL process. Preparing high and low level design

documents.

. Providing database details into mapping specification document for ODI

developers.

. Created ODI interfaces, packages and sceneries. Tested them according

to unit test cases.

. Written DDLs for creating database table and updating them according

to requirement.

. Written DMLs for inserting non-configurable data into database.

. Written/optimized complex queries for fetching data from database.

. Supporting development team with database related information at the

time of development and guiding ETL developers with best practices.

Environment: ODI 10.1.1, Oracle 10g, Erwin 7.2.3, Toad 9.1.0, Windows

XP Prof

3. Deutsche Bank Lead Developer

Mar 07 to Jan 08

Project: Fixed Income Prime Brokerage (FIPB)

Description: Deutsche Bank (DB) provides their clients a facility to

invest in the stock market and works as a broker. When investment orders

of more than available balance are received by the bank, the bank lends

money to the client and executes the transaction. This lending is based on

predefined rates for every grade of the stock, and DB charges interest to

the client. The aim of this project is to provide DB and the client

interest calculations, stock holdings, cash projection and other reports.

Contribution:

. Enhanced and maintained the existing Informatica and PL/SQL processes.

. Created schema objects to segregate FIPB.

. Validated reports for data issues, if any and fixed them

. Had discussions with business users for understanding their

requirements.

. Assigned requested reports for new parents.

. Defined strategies for the ETL process and converted some of the

Oracle PL/SQL

code into informatica mappings to meet client

requirements.

. Developed Mappings to load data into target with required business

logic.

. Prepared ETL specifications and Unit Test Cases.

. Tuned Mapping and Sessions for better performance

Environment: Informatica 7.1.1, Actuate, Oracle 9i, PL/SQL, Toad

9.0.1, Windows XP

4. Ingersoll Rand Senior Software Engineer

Nov 06 to Mar 07

Project: Service IQ (WMS)

Description: This was a Proof of Concept project for Ingersoll Rand

which was done using Sunopsis and SAS. Ingersoll Rand is one of the

world leaders in heavy machine manufacturing. This project was

developed to help them to answer complex business queries which

minimize their expenses on warranty claims by figuring out

rise/fall/total expense on warranty claims.

Contribution:

. Designed the target schema according to report requirement.

. Written Base SAS codes for extraction, transformation, loading

(ETL)

. Created and manipulated SAS Datasets.

. Written Base SAS codes by using PROC PRINT, PROC FREQ, PROC

MEANS, PROC REPORT etc for generating various reports.

. Created interfaces and packages in Sunopsis.

. Created cubes using SAS Data Integration studio.

Environment: SAS DI, Sunopsis, Base SAS 9.1, Oracle 9i, Toad, Windows

2000

5. Ameriquest Mortgage Company Senior Software

Engineer Jul 05 to Oct 06 Project 1: ASAP

Integration

Description: ASAP (Accelerated Sales Application Program) is a new

system that replaces the different systems of the loan origination

process where manual intervention is more. It is a single system which

supports all the steps of loan origination starting from Pre

Qualification to Funding without any manual intervention. The aim of

this project was to integrate the data from different modules in such

a way that it should be able to answer the query of business users.

This integrated data will be used later as a source for the ASAP data

mart.

Environment: Informatica 7.1.2, Oracle 9i, SQL Server 2000, Toad,

Windows 2000

Project 2: EDW-LSODS

Description: Aim of this project was to create an Operational Data

Store for the client from where immediate reporting can be done to

generate business related reports.

Client's OLTP system was running on SQL Server database. Their

reporting Database was also in SQL Server. Going forward they wanted

to build a Data Warehouse by using Oracle as Database to answer

complex business queries. First stage for this was to build an

Enterprise Data Warehouse and an ODS. In this project EDW and ODS was

developed for the client.

Environment: Informatica 7.1.2, Oracle 9i, SQL Server 2000, Toad,

Windows 2000, Beyond Compare.

6. General Electric (Health Care) Software Engineer

Nov 04 to Jul 05

Project: Service Global Parts Gen 1.2 and Gen 1.3

Description: Objectives of Gen 1.2 were to give visibility to the key

Parts Logistic metrics. All the reports were modified for critical and

non critical parts. Along with that critical and non critical back

orders and part availability were the new reports generated in this

generation. Objectives of Gen 1.3 were to add a new parameter

ORDER_TYPE (Planned/Special) in all the calculations like Part Span,

Parts Not on Shelf, Customer Back Orders etc. So that different trend

for special orders and planned orders can be obtained and analyzed It

was necessary for enabling the customers to run his business without

any break and thus to provide them better service.

Environment: Informatica 6.2, Oracle 9i, Teradata V2R5, Windows 2000,

Redwood Cronacle 6.0, Linux.

7. David Electronics Software Engineer

Jul 03 to Oct 04

Project: Asset Quality Demand Data

Description: The GPRS Asset Quality Demand Data is developed to

provide a consistent, single source of demand (Order) data that can be

accessed by anyone, globally. This project provides them facility to

analyze the defective parts coming from different vendors also. It

has improved the accuracy of metric reporting throughout the business

and eliminates need for multiple manipulations of data and endless

hours of work in Excel, Access etc. required to generate this data

throughout the business today.

Environment: Informatica 6.1, Oracle 8i, Windows 2000

Education:

. Bachelor of Engineering

Contact this candidate