Data Project

CAREER PROFILE

A PMP certified, IT professional with outstanding 23 years track record in

Project Lead, Data Architect, Application Development, Database Programming

and Consulting with hands-on experience in building and managing large

complex systems. Over 17 years of solid RDBMS and Data-warehouse along with

BI project experience.

SKILLS SUMMARY

Professional Strength Industry Soft Skill

Project Management Data Modeler Telecommunicatio Consulting

Oracle Expert n Project Management

IT Leadership ETL Expert Banking & Leadership

Solution Provider Data Migration Finance Communication and

Quality Management Software Insurance Interpersonal

Development Travel Relationship

Data Warehousing Business Analysis Other (Purolator

Data Architecture Standards and etc.)

Data Analysis SLAs.

> DataWarehouse Solution Architect with over 8 years of experience. Able to

deliver data management vision, goals, priorities, design principles, and

operating policies in support of the business goals of the organization:

9 Yrs.

> Extensive hands on experience using Oracle 11g, Oracle 10g, Oracle 9i,

DB2, Sybase and Teradata 12 databases and several of its tools: 20 Yrs.

> Management and implementation of database models, data flow diagrams,

database schemas, DB scripts, DTD schemas, XML Schemas, structures and

data standards to support a robust data management infrastructure

> Solid experience translating business requirements into conceptual,

logical, and physical data models using Erwin 3.5.8, 4.1.4 & 7.3: 8 Yrs.

> Implement best practices and methodologies in data analysis, design and

modeling

o 3NF modeling with Parent-child relationship for OLTP

o Dimensional modeling with Dimension and Fact in star Schema or

Snowflakes for Data-warehousing

o Data Vault modeling with HUB, Satellite and Link for Data-

warehousing

> Experience on Meta data definition, implementation and maintenance, new

business rules identification and implementation to data rules,

transformation program library maintenance, XML file generation and data

quality.

> Experience in implementation of Master Data Management (MDM) and Data

Governance.

> Design standards for using normalized data structures, de-normalized

structures, and dimensional structures. Defines common design patterns

for modeling various types of relationships.

> Vast experience on Extraction, Transformation and Load (ETL) strategy,

design and implementation. Includes Informatica 7.x, 8.x & 9.x, Datastage

and Business Object Data Integrator (BODI): 8 Yrs.

> In-depth knowledge and experience in SQL, PL/SQL and UNIX Shell

Scripting: 20 Yrs.

> Worked in Windows, UNIX, HP-UX, Solaris, Linux

> Project Management and Team Lead: 7 Yrs.

CAREER HIGHLIGHTS

Client: Intact Insurance, Ontario, Canada Jun 2012 - Till

Now

Project: HCAI & GuideWire Jun 2012 - Till

Now

Role: Data Architecture, Implementing Data Vault,

Solution Design for Loading Data into Final Datamart

Health Claims for Auto Insurance (HCAI) is an IBC mandated electronic

system for transmitting Auto Insurance claim forms between insurers and

health care facilities in Ontario. HCAI data, which includes Insurer,

Facility and Payment information, will be stored in the OPUS system.

The native HCAI system provides none to very limited reporting

capabilities. As a result, the main objective of this project is to provide

the Intact AB claims business with an enhanced reporting ability. HCAI data

will be loaded into the OPUS system and linked to OPUS.

HCAI project has been initiated to modify already running ETL process and

add new load processes to migrate data from one of the INTACT front end

OPUS into Intact Enterprise Information System (EIS).

The EIS Guidewire Claim Center - Claims CL Property Project introduces a

new operational Claim System at Intact which will feed claim data to EIS.

The Guidewire Claim Center (GWCC) application will initially handle only

Commercial Line Property claims (select products), with subsequent phases

to handle other business lines. GuideWire is completely new project to

bring data from GuideWire Application into Intact EIS.

In this project, new DW Architecture call Data Vault (HUB, Link and

Satellite table) concept has been adopted.

My Role is

. Updating the Erwin Model EIS tables in INTACT EIS Staging, Dimension

and Layer 2 (final data-mart) Area as per requirement from HCAI using

Erwin 7.3

. Generate DDL and implementing changes corresponding tables in EIS

database in Oracle 10g.

. Generating View as well as Materialized View.

. Based on Source Data, defined new composite natural key.

. Established new load sequence for loading data into EIS tables in

staging, Dimensional and L2 area.

. Develop new data logical as well as physical model of staging, Working

Area and L2 tables required for Guidewire Data load using Erwin 7.3.

. Generate DDL and implementing changes corresponding tables in EIS

database.

. Based on Source Data, defined new composite natural key.

. Develop XML schema.

. Interaction with Business Analyst, Quality Control Team and other

Project Team (DBAs).

. New Implementation of Data Vault (HUB, Link and Satellite table)

. Documenting Solution Design Document, Process Flow Diagram and Data

Flow Diagram.

Environment: Oracle 10g, Toad 10, Erwin 7.3, HP, Informatica 8.6

Client: Rogers Communications, Ontario, Canada Oct 2006 - May

2012

Project: Blue and Maestro Reporting Jan 2011 - May

2012

Role: Solution Architect

Two simultaneous DW and Reporting Projects Blue and Maestro 2 have been

initiated by Rogers BI/DW Department.

Blue Project is for incorporating loyalty Point System for the customer.

The OLTP database is called ELMS.

BI/DW team of Rogers is working towards building a data-mart to support

Blue and Maestro Reporting and Analytic query by Marketing, Sales and

Revenue Assurance department.

My Role is predominantly as data- architect and ETL Designer with following

deliverables:

. Analyzing the source Data in Toad, SQL Developer and Aqua data Studio.

. Used Technical and Business Metadata.

. Estimation of number required Databases and their sizing based on

business requirement.

. Designing Database based on business requirement using the modeling

tool Erwin 7.3

> to load data in Landing Area, Staging and finally ODS

> 7 different Star Schema for Data-mart to satisfy Reporting and

analytical Query in BLUE

. Dimensional Modeling for EDW using the modeling tool Erwin 7.3:

Created Star schema for Maestro in following Module

> Customer and Account

> Billing & AR

> Order and Service

. Generated DDL script from Erwin and physicalize all those objects in

Teradata.

. Implementation of MDM in Teradata Data Mart using Consolidation

Method, to be used by downstream system for Analytic and Reporting

purpose, Where sources are Legacy Mainframe for Cable, Vision21 for

Wireless Database and RCI database for Home Phone.

. Used Process and Business Metadata.

. Prepared HLD, Solution design document and Process flow Diagram for

ETL required to load data into Landing, Staging and different

Datamarts using Informatica 9.1.

. Interaction with different Business Group, Development Team and other

Project Team (DBAs).

. Leading a team of Developer (External and Internal).

. Documenting Solution Design Document, Process Flow Diagram and Data

Flow Diagram.

Environment: Oracle 10g, Toad 10, Teradata 12, HP, Erwin 7.3, Informatica

9.1

Project: Business Transformation (later on renamed as Jul 2008 - Dec

Maestro) 2010

Role: Data Architect, ETL Architect and Development

team Lead

As soon as ICM Project finished, an initiative by Rogers to integrate all

customers from different LOB into one Database, finished, Rogers got

engaged with new endeavor, called Business Transformation (later on

Maestro).

I was involved in migrating data from legacy system (Mainframe for cable

and Oracle database for wireless - V21) into BT environment. I worked in

following Modules:

. Contact Management & CRM

. AR, Billing & Collection

. Order Management & Service Management

. ARM & MDU

My Role in above Conversion Project were as follows

. Prepare HLD for System requirement and Database Estimate.

. Estimation of Database Size based of source data and objects to be

created.

. Database designing (Logical and Physical Data Models) to load data

from Different Sources (Mainframe, Oracle OLTP Wireless DB, XML and

Flat files) into PreLanding, Landing, Staging and target area using

Erwin 4.1.4.

. Generated DDL script from Erwin and physicalize all those objects in

Oracle 10g.

. Added new View and Materialized View in Oracle 10g using Toad.

. Used Technical and Business Metadata.

. Implementation of MDM using Consolidation Method Where sources are

Legacy Mainframe for Cable, Vision21 for Wireless Database and RCI

database for Home Phone.

. Defining required Indexes and Partitioning of tables.

. Prepare HLD, Data Flow and Process Flow Diagram.

. Prepared Solution design document and Source-to-Target document for

ETL required to load data into Landing, Staging and Target using

Informatica 8.6

. Followed Agile Development Methodology (Incremental Development)

. Installation of Informatica 8.6.

. Built the Migration Team (11 members' team) from scratch.

. Interaction with different Business Group, Development Team and other

Project Team (DBAs).

. Documenting Solution Design Document, Process Flow Diagram and Data

Flow Diagram.

Environment: Oracle 10g, Toad 10, HP, Erwin 4.1.4, Informatica 8.6 and

9.1

Project: ICM (Integrated Customer management) Dec 2006 - Jun

2008

Role: Data Architect and ETL Architect

ICM is a new initiative by Rogers to integrate all customers from different

LOB into one Database.

Whole ICM project has been divided into different sub-projects with

different deliverables. I have worked in following Sub-Projects:

. ICM Conversion

. RCIS Conversion

. ICM Incremental Conversion

. B2B Conversion

. Media Conversion

My Role in above Conversion Project were as follows

. Analyzing the source Data in Toad, SQL Developer and Aqua data Studio.

. Estimation of Database Size based of source data and objects to be

created.

. Prepare HLD for System requirement and Database Estimate.

. Database Design: Logical and Physical Models for Landing, Staging and

RCIS Database using Erwin 4.1.4.

. Generated DDL script from Erwin and physicalize all those objects in

Oracle 10g.

. Added required Indexes and Partitioning of tables

. Added new View and Materialized View in Oracle 10g using Toad.

. Generate DDL for physicalizing all database objects.

. Prepare HLD, Solution Design Document, Process Flow Diagram and Data

Flow Diagram.

. Used Technical and Business Metadata.

. Implementation of MDM in RCIS as Transactional as well as Registry

HUB, using Consolidation Where sources are Legacy Mainframe for Cable,

Vision21 for Wireless Database and RCI database for Home Phone.

. Prepared Solution design document and Source-to-Target document for

ETL required loading data into Landing, Staging and Target using

Informatica 8.6 and SQL, PL/SQL.

. Followed Agile Development Methodology (Incremental Development)

. Leading a team of 6 developers

. Developed Own Process Scheduler using UNIX Shell Script

Environment: Oracle 10g, Toad 8, HP UNIX, Erwin 4.1.4

LNP (Local Number Portability)

Another Initiative by Rogers in 2007, to introduce Local Number

Portability.

My Role in this project was as follows:

. Analyzing the source Data in Toad.

. Estimation of Database Size based of source data and objects to be

created.

. Prepare HLD for Database Estimate.

. Database designing in Rogers EDW using Erwin 4.1.4

. Dimensional Modeling using Erwin 4.1.4.

. Generated DDL script from Erwin and physicalize all those objects in

Oracle 10g.

. Added required Indexes and Partitioning of tables

. Added new View and Materialized View in Oracle 10g using Toad.

. Design and Documentation of Source-to-Target mapping for ETL to be

developed in Informatica 7.3.2.

. Development Team Lead with 3 developer

Bank of Nova Scotia (BNS), Canada, Ont. Dec 2005 - Oct 2006

Contract (Data and ETL Architect, Project

Leader)

Enterprise Data-warehouse Services (EDS)

RML Load

EDS receives Data from Customer-service and other sources through INTEK in

ASCII and EBCDIC files or in VSAM format. Loads those data into staging

table using Shell Scripts and COBOL program then data gets loaded into the

Enterprise Data-warehouse (Star Schema). Because of complexity in Shell

Scripts and COBOL programs it becomes very difficult to make any required

changes into those programs.

Main objective of this project is to replace all pre-existing COBOL and

Shell Scripts into Informatica Mappings.

. Create Logical and Physical Data Model for required staging tables

. Create Data-Flow-Diagram and Structured Diagram.

. Create Requirement Document.

. Create HLD and Detail Design Document.

UNLIMITED ACCOUNT

The project is to deliver data for new customers with SCOTIA1 account

(UNLIMITED ACCOUNT) and FTP to third party vendor.

Delivered data will have customer demographic detail as well as account

detail. Data will be extracted from two EBCDIC files with Customer details

and one ASCII file from transaction detail.

. Create Business Requirement Document.

. Create Logical and Physical Data Model for required staging tables

using Erwin 4.1.4

. Create HLD and Detail Design Document.

. Shell Scripting, ETL Design

. Encryption and FTP software Evaluation

Sales-Builder

Sales-builder is a web-based application running on AIX servers, with a DB2

database, delivers a fully scalable customer relationship management system

to the branch sales force.

The project is to import and integrate Sales-builder data into the EDW. It

is the foundation for designing the detailed requirements, data models

(logical and physical) and developing the ETL processes required to

implement this data.

Sales-builder data is required in the EDW to enhance current and future

reporting, enhance central opportunity downloads and business analysis

needs.

. Create Business Requirement Document.

. Create Logical and Physical Data Model for required staging table

using Erwin 4.1.4

. Create Data Flow and Process Flow Diagram

. Shell Scripting, ETL Development and Testing (UAT, SIT)

Environment: Db2 8.1.6, Erwin 4.1.4, Squirrel, Informatica 7.1.3, SUN

Solaris, PVCS for version control

Canadian Imperial Bank of Commerce (CIBC), Canada, Jun 2005 - Nov

Ont. 2005

Contract (ETL Developer)

OTIS Data Access Service Layer (DASL)

DASL represents the common application and technical basis on which OTIS

and other GOT and CIBC applications Send and Receive data to and from ADP

or other external systems.

. Analyzed the Source data (Cobol, Mainframe and Oracle)

. Implementation of CVS into Informatica 6.2

. Develop Informatica Architecture and Mapping Document

. Develop the Informatica Mappings as well as Mapplets to perform the

required data migration.

. Conduct data migration process integration testing to confirm the

target tables are loaded correctly.

. Other than Session task, other tasks used: E-mail Task, Command Task,

Decision Task and Timer etc.

. Using Striva Details for Binary and COBOL Source.

. Using Tidal as Scheduler for running Workflows.

. PL/SQL, SQL Coding, Using SQL Loader and Materialized Views.

. Performance Tuning at Source, Mappings and Session level.

. Working towards Migration from 6.2.2 to 7.1

Environment: Oracle 9i, Toad, Informatica 6.2 & 7.1, UNIX Shell Script,

Tidal

MFXchange Holdings, Inc. Canada, Nov 2004 - May 2005

Ontario

Contract ETL Developer

PMSC-to-WINS conversion

The project was to create a data conversion of mainframe to Oracle Staging

Area and then to DB2 Data-warehouse at AS400.

. Analyzed the Source data (Cobol, DB2, Mainframe and Oracle)

. Created Staging table in Oracle 9i corresponding to source.

. Developed Mapping Document

. Developed the Informatica Mappings as well as Mapplets to perform the

required data migration from Mainframe to Staging Area and then

staging Area to DB2.

. Conducted data migration process integration testing to confirm the

target tables are loaded correctly.

. Other than Session task, other tasks used: E-mail Task, Command Task

etc.

. Used Striva Details for Binary and COBOL Source.

. Used Worklets and Scheduler in Workflows.

. PL/SQL, SQL Coding, Used SQL Loader and Materialized Views.

. Performance Tuning at Source, Mappings and Session level.

. Migration of Mappings and workflows from 6.2.2 to 7.1

Environment: Oracle 9i, AS400, DB2, Toad, Informatica 6.2.2 & 7.0, UNIX

Shell Script

Teranet Project (5 months)

The project was to create a data conversion of mainframe to Oracle Data

Warehouse and generate reports.

. Analyzed the Source data and designed the Data-warehouse schema.

. Develop the Informatica mappings to perform the required data

migration.

. Develop Informatica statistical reports that will confirm the target

tables are loaded correctly.

. Conduct data migration process integration testing to confirm the

target tables are loaded correctly.

. PL/SQL, SQL Coding

. Conducted Informatica 7.1.1 Training

Environment: Oracle 9i, Linux, Informatica 7.1.1

Allstream Ltd. Canada, Ontario May 1998 - July 2004

Sr. Consultant (May 2000 - July

2004)

Consultant (May 1998 - April

2000)

Bank of Nova Scotia (10 months)

The project was to create a Data Warehouse and generate reports for Scotia

bank's Global Risk Management (GRM).

. Analyzed the Business Requirement.

. Analyzed the Source Data and develop the architectural Design document

for the new Multi-dimensional Data Mart.

. Developed a new Data warehouse in Sybase for OLAP purpose.

. Wrote procedure in SQL, Trans-SQL

. Analyzed and interpreted high priority SAS code to uncover key metrics

and dimensions as well as embedded business logic and developed report

specification document

. Worked closely with management to collect requirements and prepared a

showcase presentation of Business Objects for bank executives.

. Used Business Object BI Tool for analyzing and reporting data from the

Data-warehouse.

. Installed, developed, and deployed the Business Object's environment

e.g. Repository in Sybase database, Universe with required classes and

complex objects and reports to enable bank to meet their business

intelligence objectives.

. Used Supervisor to set up user and user groups for Business Object.

. Reviewed the Technical Documentation developed for the reports.

Environment: Business Object 5.1 and 6.0, UNIX, Windows 2000 and Sybase.

Allstream Ltd, Canada (16 months)

This was a Data Warehousing projects named Sales Targeting and Incentive

Reporting Systems. Building a central enterprise Database to store sales

data from different region captured already in form of flat file, excel

file or local database and leading a team to develop enterprise Web based

Application to maintain the Sales Hierarchy and sales of product at

national level.

. Performed System Analysis and Designing and generated High level

Design Document

. Developed Database Architectural document

. Developed Database design document.

. Used Erwin for Data Modeling

. Develop the Informatica Mappings as well as Mapplets to perform the

required data migration.

. Conduct data migration process integration testing to confirm the

target tables are loaded correctly.

. Using PL/SQL, SQL and SQL Loader.

Environment: Erwin 3.4, Informatica 5.1 & 6.2, Window 2000 and Sun

Solaris

Nortel, Canada (10 months)

This was a Data Warehousing project named Electronic Contract Management

System (ECMS).

. Analyzed two existing cottage applications (one for Key performance

measures and the other for Regional sales reporting) in order to

decommission them and bring similar functionalities into the existing

Global Data Warehouse (GDW).

. Determined the gaps in the GDW and coordinating with the internal IT

group to bring the required data into GDW

. Designed the new schemas for new subject areas

. Designed and developed the ETL procedures to source the new data

. Developed ETL job using Business Object Data-Integrator to transfer

data from different sources to GDW.

. PL/SQL, SQL procedure Coding.

. Performance tuning of the warehouse

. Designed and Developed Business Object Reports using universe as well

as procedures.

. Developed web query and reports using WebIntelligence

. Fine-tuned the Business Objects reports for optimal perform.

Environment: Business Object 5.1, BODI, AIX, Oracle 8i

Canadian Imperial Bank of Commerce (CIBC), Canada (6 months)

This was a Data Warehousing project named CRISP, which involved the

migration and conversion of 10 years of data related to Credit Risk, from

an existing mainframe data warehouse into a new Oracle based solution.

. Analyzed the Source Data

. Staging of source data.

. PL/SQL, SQL procedure Coding.

. Conduct data migration process integration testing to confirm the

target tables are loaded correctly.

. Documenting and confirming business data requirements and data

definitions.

. Successful migration of 300 Gb of historical data

Environment: Mainframe System, Window2000, Oracle 8i

Purolator Canada (18 Months)

This was a Data Warehousing projects named ABC & Profitability Management.

DataStage was use to handle the extract, transformation and loading (ETL)

of data, including the calculation of the unit cost. The data comes from a

variety of sources from within Purolator, including other database systems,

spreadsheets, and some manually maintained data that is entered in

spreadsheets.

. Used Erwin 3.4 for Data Modeling.

. Used Data Stage for data extraction, cleansing, analyzing and

uploading data into Data Mart.

. Commonly used stages are Aggregator, Transformer, Hashed File, ODBC,

Sequential File, Folder, Shared Container and Local Container

. Wrote Unix Shell Scripts to execute the Data Stage jobs in production

environment.

. Wrote complex Data Stage jobs to load the data from flat files, Excel

Sheets, COBOL files and other data sources using advance features like

UDF (User Defined Function).

. Developed complex SQL procedures to improve application and database

performance.

. Developed Data Stage Sequence to execute complex Data Stage jobs.

. Created test plan and conversion testing reports using SQL Reports and

test development scripts and Data Stage job on development machine.

. Wrote technical document to submit shell script and Data stage job on

Mestro Production Server.

Environment: DataStage XE from Ascential Software, HP UNIX and Windows

2000, Informix.

Allstream Ltd, Canada

The application has been developed for accepting order of different network

related products from the client through Sales Agents or Sales

Representative, validating and finally executing those orders.

. Performed System Analysis and Designing and generated High level

Design Document

. Developed Technical Architecture document using UML.

. Performed Development of the application

Environment: Struts 1.1, Websphere 5.0., Solaris and Window 2000, WSAD 5.0,

Oracle 8.1.7, and XML

Daleen Ltd, Canada (US Based E-Business Firm)

The project was to create J2EE application name KARIBU for Customer service

department of a telecommunication company. Whole application has several

components like personalization, Security, Reporting etc.

. Evaluated different reporting tools and reporting server including:

Crystal Report 8.0 from Seagate; Seagate Enterprise; Oracle Report

From Oracle Corporation; JReport from Jinfonet; Actuate 5.0.

. Developed proof of concept for a reporting infrastructure, part of the

core eCare product and Generated high-level architectural designing

and analysis document for building a reporting Infrastructure.

. Created interface between eCare Karibu System and the reporting

infrastructure to interact with different components in distributed

networking environment.

. Created interface between LDAP server and the Reporting server for the

user Authentication.

Environment: Weblogic 6.0 Application Server under Sun Solaris,

Allstream Ltd, Canada

The project was to create application name WEBPMU a project management

utility.

. Produced proof of concept of various new ideas like Generic EJB for

single tables, JTA implementation using Data Source, Implementation of

Connection Pooling using Data Source, Distributed Transaction

infrastructure, utilization of XA interface like XA Datasource, XA

Connection and XA resource.

. Installed WebSphere 3.5 along with DB2 data-source as well as Oracle

data Source.

Environment: IBM Websphere 3.5 Application server

BMO Nesbitt Burns, Canada

The project was to develop an application name Sudden Impact to get Real-

Time Quotes Information from third party vendors named StarData and EMS and

display them in BMO website.

. Prepared requirement analysis, documentation and architecture design.

. Developed proof of concept of using JNI interface as well as Socket.

. Coded and implemented in UAT and production environment.

Environment: Windows NT, Sun Solaris, HP-UNIX, Oracle 8.1.6, Java 1.2,

WebSphere

John View, Canada

The project was to develop a Travel application to cater all travel related

aspects national as well as international. Flight Booking, Hotel Booking,

and Tour Booking Cancellation etc.

. Interacted with user to understand the business and analyzed the

company's business logic.

. Designed the back-end system to incorporate the business logic.

. Performed development using PL/SQL, SQL Procedure.

. Application and Database Tuning.

Environment: Windows NT, Windows95, Oracle 7.3.2

Toronto Stock Exchange, Canada

The project was to bring the required changes in existing application and

databases to make them 2000 compliance.

. Performed application assessment for Year 2000 compliance and analysis

of application code.

. Set-up and provided technical support for testing environments.

. Performed database administration, including table and index creation,

data extraction, data migration, (public) synonym management, object-

level security, and production installation tasks.

. Performed development and application tuning using PL/SQL, SQL

Procedure.

Environment: IBM HDS/44 mainframe, Unix Server, Oracle versions 7.1.6,

7.3.3 and 7.3.4

Koshika Telecom Ltd., India December 1997 - April 1998

Sr. System Administrator (Oracle

DBA)

DSS Mobile Communications Ltd, July 1996 - December 1997

India

Assistant Manager (Oracle DBA)

Fujitsu ICIM Ltd., India September 1993 - July 1996

Software Engineer

Pertech Computers Ltd, India March 1989 - August 1993

System Executive

EDUCATION/PROFESSIONAL DEVELOPMENT

. PMP Certification

. In-house training in Informatica Power Center 6.2

. Object Oriented Analysis & Design using UML

. Java for Enterprise Systems Development: Hands On

. Oracle8: DBA Administration

. Oracle 7.x Advanced DBA, SQL Star International, India

. Unix & C, CMC Limited, India

. Post-Graduate Diploma in Computer Applications, Institute of Modern

Management

. Bachelor of Science (Honours), University of Calcutta

Contact this candidate