Data Oracle

Ajai Mangari

[514-***-**** - ********@*****.*** ]

Summary of Professional Experience:

Over 15 years of professional experience in Software industry, working with

Oracle, Sql Server, Mysql, DB2, Informatica PowerCenter, Data Stage, SAP

Business Objects, SAP Xcelsius, WEBI, Microsoft Analysis Services, ERWIN,

SQL, PL SQL, T-SQL, Oracle Designer, Pro*C, Oracle Warehouse Builder,

Oracle ProcessFlow, Cognos, C, Unix Shell Scripting, Perl, UNIX, .Net,

AJAX, ASP, XML, XSLT, Powerbuilder, Visual Basic, JDA, Java, and .Net

platforms.

. Extensive experience in designing and developing data warehouse and

data marts.

. Extensive experience in Business users meets for analysis, Production

support, training, documenting, analytical and problem solving skills

in both Agile and waterfall methods.

. Experience in Sarbanes Oxley (SOX) Compliance.

. Experience in Project leading and project management.

. Experience in using both Inmon and Kimball data warehouse design

methods.

. Experience in Logical and Physical database design with ERWIN and

Oracle Designer.

. Extensive experience building Business object Universes and WEBI

reporting tool.

. Experience in Informatica PowerCenter & Data Stage ETL mapping.

. Experience is building Cubes using Cognos and Analysis Services

. Extensive experience in Oracle Warehouse Builder and Oracle Workflow.

. Extensive experience in designing Star, Snow-flex and hybrid schemas

for Data warehouse projects.

. Designed Fact tables, conformed Dimensions and junk Lookup tables for

Data warehouse projects.

. Experience in loading data into Data warehouse using ETL tools like

Consterller, OWB and SQL

. Expert in developing Database Triggers, Stored Procedures, Functions

with PL/SQL & T-SQL.

. Extensive Unix Shell Scripting, Perl, Pro*C experience in writing

batch and automated daily jobs.

. Extensive Experience in Database and query tuning (Generating Plan

Table, Explain Plan, and Optimizer with Hints, Analyze, and indexing

strategy).

. Experience in defining and designing interfaces and upload download

data from Legacy System to RDBMS through views, UTL_FILE, SQL*Loader,

Export/Import, PL/SQL Procedures, Shell scripting, Pro*C, Materialized

views and other Replication techniques etc.

. Experience in DBA activity includes daily backup, User Roles,

Privileges, Grant, creating users, maintaining users, Database sizing,

planning etc.

. Professional experience in developing object oriented programs.

. Team player and designed and developed systems in full cycle.

. Expert in Oracle Form, Reports, Powerbuilder, Visual basic,, .Net

etc.

Technical Skills

HARDWARE : Sun SPARCstation, IBM Compatibles, and Windows File

Servers

OPEARTING SYSTEM : IBM Unix - AIX 5.2, SCO Unix v5, HP-UX 10.20, Win

2003 Srv, Win 2000 Svr.

DATABASE : Oracle 11g, 10g, 9i, 8i, 7x, SQL Server 2000,2005 and

2008, Mysql 4,5, DB2

LANGUAGES : SQL, PL/SQL, T-SQL, C, Pro*C, Perl, Unix Shell

Scripting, Asp, C#, vb.net, JavaScript, Ajax, Html, XML,

XSLT, .Net, Powerbuilder, Visual Basic 6.0, Java, Developer

2000, J2EE

TOOLS :Analysis Services, Informatica PowerCenter, SAP Business

Objects, SAP Xcelsius, TOAD, ERWIN, SQL*Loader, Oracle

Designer, Oracle Enterprise Manager, Consterller, Cognos

Business Objects, Oracle Warehouse Builder, Oracle

Workflow, Export/Import Utility, Query Analyzer, Patrol,

Visual Source Safe, Citrix, Visual Studio, IBM-DB2, Data-

junction

Industry Experience: Insurance, Telecommunication, Retail, Manufacturing,

Construction, Inventory Management Systems, Warehouse Management Systems,

Financial, Sales and Distribution, etc.

PROFESSIONAL EXPERIENCES:

Bell, Montreal April-12 - Current

Network Data warehouse Lead

I'm working for Bell as a data warehouse technical lead on multiple

initiatives.

Projects completed or in progress:

a. Pair bonding.

b. TIM & ACUT and Calrs ETL.

c. SI TOOL Reporting tool (Backend)

d. Audit Qualification Error Reporting.

Responsibilities:

. Develop technical feasibility and design for NDW projects

. Work with team, management and vendors on project costs, schedule and

resources planning.

. Work with BA on various requirements and feasibilities studies

. Logical and physical database modeling

. Work with external vendors on code development and testing.

. Create ETL mapping documents for the ETL developers.

. Advice technical issues with ETL and reporting developers

. Code walk thru.

. Performance tuning on complex SQL Statements.

. Additional database management responsibilities.

. Create SRS document for the projects.

1. Environment: Oracle, Informatica PowerCenter, ErWin, Unix, Toad,

Teradata

Sunlife Financial, Montreal July-09 - Apr 2012

Sr. BI Architect

I worked as a Sr. data warehouse Architect for Sun life financial.

Recently completed design and development of a new data warehouse for

Canadian government employee contracts which involved Data extract from

multiple sources including mainframe, designing multiple fact, factless and

dimension tables, data load, design various datamarts to support internal

and external clients, design business object universes and other data

warehouse design and ETL mapping tasks.

Currently working on design and development of ETL framework using SQL and

other tools to create and manage corporate master data management (part of

Enterprise data warehouse). This involves design and development of

acquisition, Integration and presentation data layers

Responsibilities :

. Design Conformed dimensions and fact for the data warehouse and data

marts.

. Work with upper management on project costs, schedule and resources

planning.

. Work with BA on various requirements and feasibilities studies

. Design Physical data model and create script for database objects.

. Engage in discussion with other corporate Data warehouse Architects

directions and strategies.

. Create ETL mapping documents for the ETL developers.

. Advice technical issue with ETL and reporting developers.

. Create OLAP Cubes using Analysis Services.

. Create BO Universes for WEBI reports.

. Design Cubes using Analysis Services

. Assist developers in Performance tuning.

. Work with auditors to enhance database security.

. Design and develop dashboards using SAP Xcelsius.

. Performance tuning on complex SQL Statements

1. Environment: Oracle, DB2, SQL Server, Informatica PowerCenter, SAP

Business Objects, SAP Xcelsius, ErWin, Microsoft Analysis Services, BOXI,

Webi report designer, Crystal Report Designer

Standard Life, Montreal Dec-07- June-09

Data Specialist

I worked as a database specialist for StandardLife insurance company In

charge of dimension modeling and development of ETL mappings. Analyze

aggregations and summarized tables for the data marts.

Responsibilities:

. Provide technical advice and guide on database related issue to

developers and cost center mangers.

. Manage data synch across multiple data sources and data warehouse

schema.

. Design, Analyze and document all technical aspects of the Oracle and

all ETL process.

. Lead in maintaining and improving ETL processing efficiency.

. Assist in developing feeds to Data Marts.

. Assist developers in oracle database performance tuning.

. Work with auditors to enhance database security.

. Advice cost center managers on project deliverables and process

improvement.

. Logical and Physical model design

. Extensive backend coding in SQL, PL Sql, Pro*C and shell scripts

. Design, Map and Deployment of ETL activities using data junction.

. Map ETL Process flow using Data-junction.

2. Environment: ORACLE 10G, UNIX AIX 5.3, BusinessObject, ErWin, Pro *C,

Toad, PL Sql, Data-Stage, DB Visualizer, J2EE, Eclipse, IBM- websphere,

IBM-DB2.

TekSystem, Montreal,.

Dec-06 - Dec-07

Senior Consultant

I worked as a senior consultant for a Vehicle auction firm from United

States.

Supporting role for AMS system which auction, register and sell vehicle and

send data transmission to Car manufacturers like GM, Ford etc. System runs

at multiple locations simultaneously (over 50) in North America with over

2500 users.

Responsibilities:

. Make sure development requests (DR) for the month are completed on

schedule.

. Work with BA and other client resources to identify critical issues

and set priorities.

. Performance tuning, query optimization, data integrity and statistics.

. Develop PL-Sql & T-SQL- stored procedures, triggers, functions,

tables, views, indexes and relational database models.

. Develop code in C sharp and ASP. Maintain VB 6 Code.

. Developed AJAX enabled Web Pages.

. Develop SQL queries and re-write SQLs to optimize performance.

. Working with Business Analysts on new modules and trouble shoot

existing development request.

. Design and maintain GTS - (Generic table structure) for the system.

. Conduct technical interview of new recruits on various customer

technologies for the consulting firm.

. Actively involved in developing strategies for powerbuilder to .Net

conversion.

Environment: Oracle, Pl Sql, SQL Server 2000, T-SQL, Query Analyzer,

ErWin.Powerbuilder, .net. asp, javascript, Clearcase, Clearquest, VSS,

AJAX

BT(British Telecom) Radianz., NJ

April-05- Dec-06

Oracle Data warehouse - ETL Consultant

BT Radianz provides the industry standard platform for secure, reliable,

and scalable connectivity to the global financial community.BT Radianz is

an industry leader in secure telecommunication.

Responsibilities :

. Extract data from source systems.

. Transform data via standardization, cleaning, data repair, matching,

and duplicating.

. Keep data in synch across multiple data sources including internal,

external, and 3rd party data sources.

. Document and Design all technical aspects of the Oracle and all ETL

processes.

. Assist in maintaining and improving ETL processing efficiency.

. Assist in developing feeds to departmental Data Marts.

. Ensure data can be provided to support the MIS OLAP reports.

. Designing and defining Entity Relationship changes through ERWIN

. Help develop Business Objects Universe and reports.

. Logical and Physical model design

. Extensive backend coding in SQL, PL Sql, Pro*C and shell scripts

. Design, Map and Deployment of ETL activities using Oracle Warehouse

Builder.

. Map ETL Process flow using Oracle Processflow.

3. Environment: Oralce Warehouse Builder 10.x, ORACLE 9.i, 8.1.7, UNIX AIX

5.2, Pro *C,,ErWin, TOAD, Business Object, PL Sql, Oracle Workflow

FootStar Inc., NJ May 99

- April -05

Warehouse Management System (Project Lead)

Functional aspects: Primary object of the system is to receive cases at

distribution centers and ship cases to retail stores or wholesale

locations. System supports multiple divisions and distribution centers.

System handles both manual as well as automated distribution centers.

System also handles inventory management. Receipt transactions are forward

to other systems like JDA and POMS. System calculates complex nightly

shipment for the stores efficiently to minimize open cases. (Full case

picks, roundup cases, round down cases etc.) . System handles both

wholesale and retail shipments. Shipment transactions then forward to

sales, store management and Shipment tracking system for further

processing.

System also receives ASN (EDI) information from external vendors. Also send

EDI850, EDI855, EDI856, and EDI211 to clients like Federated and Wal-Mart.

Other functionalities like dc to dc transfer of goods, Cross docking,

vendor drop ship, Store cut for the week, holiday cut, split waves, merge

waves, inventory adjustment, inventory balance and many other

functionalities of distribution and warehouse management.

Technical Overview: System utilizes Oracle stored packages, procedures and

triggers to tackles complex calculations on huge volume of data and slice

data set or rollup data for reporting purpose. 80 percent of process done

at backend. Multiple cron jobs are running on AIX server which call shell

scripts for search files, split file, validate files, Sqlload, send Ftp

files, Archive files, send mail to user communities etc. Edit checking is

done at Shell script level to make sure files are complete and basic data

structure is valid. Data pumped into temp tables from flat files using Sql

loader and then further processed by Oracle Warehouse Builder and Oracle PL

SQL Packages to load into transaction tables. UNIX crones driven nightly

batch runs to rollup or summarize data or distribution center wise into

smaller data sets for faster retrieval. Using oracle parallel query

option, bulk reading and bulk inserts for performance enhancement.

4. Engaged in designing and analyzing the system.

5. Designing and defining Entity Relationship changes through ERWIN

6. Logical and Physical model design

7. Extensive backend coding in SQL, PL Sql, Pro*C and shell scripts

8. Loading Data into SIS Schema using Oracle Warehouse Builder (OWB).

9. Interaction with JDA (retail packaged Software) for data transfers.

10. Extensive coding in Pro*C to load data from text files to Oracle

Database.

11. Written UNIX Shell scripts to run batch process to receive and send

data from/to external systems.

12. Performed Sql tuning, Indexing strategies, de-normalization techniques

to improve database performance.

13. Responsibilities include assisting other programmers on technical and

designing issues.

14. Utilized extensively object oriented programming techniques. Extensive

coding in .Net, Asp, C#, VBScript, Powerbuilder and visual basic. System

support 24/7 (system monitored by users at dc)

15. Environment: ORACLE 9.i, 8.1.7, OWB, UNIX AIX 4.2, 5.2, C, Pro *C,

Net, C#, asp, JavaScript, VBscript, HTML, DHTML, XML Powerbuilder 6.5,

9.0, SQL Server 2000, Visual basic 6.0, Citrix, TOAD, AutoSys

StarTrak

StarTrak system keep track of goods from distribution centers through pool

points and until stores receive goods. Users scan cases each location which

automatically updated internet based system. Utilized Perl scripting to

validate received ftp files and send them to multiple locations. All

reports are done in ASP at pool points to make easy access to information.

16. Involved in designing and analyzing the system from the beginning.

17. Designing and defining Entity Relationship changes through VISIO

18. Logical and Physical model design

19. Written or assisted programmers major parts codes.

20. Integrated system with other applications. Major coding visual basic,

.Net, Sql tuning to optimize performance. Backend coding in PL Sql, Pro*C

and shell scripts

21. Environment: ORACLE 9.i, 8.1.7, UNIX AIX 4.2, 5.2, Pro *C, ASP,HTM,

Visual Basic 6.0,

Amp Inc, Harrisburg - PA, December 1997 -

May- 1999

Database Administrator for Data warehouse apps

. Was actively involved in developing a data warehouse project for Amp,

a manufacturer of fiber optic connectors. Designed Fact tables,

Dimensions and Lookup tables. These were implemented in Star and Snow

Fakes schemas.

. Data being populated to Amp Data warehouse from legacy system as well

as SAP. Data goes through Consterller where data being cleaned and

scrubbed into ODS (Operation Data Store) from there to the Data

warehouse.

. Build OLAP cubes.

. Integrated SAP and data warehousing. This includes logical designing,

data sharing physical structure designing, normalization, de-

normalization etc.

. Physical designing includes creating physical structure from logical

data structures, sizing table spaces sizing tables, sizing indexes,

database tuning creating all database objects, implementing

distributed database, performance monitoring, database replication,

backup and recovery. SQL turning and other database maintenance

activities. Also sync ORACLE database objects with ORACLE case

repository.

. Created automated shell scripts for file size information, grow rate,

grep log files and send emails to get first hand trouble notification

etc..

. Environment: ORACLE 8.0, 7.3,Consteller, Cognos, Patrol, Enterprise

Manager, Precise, PL SQL, SQL, Designer 2000, UNIX, Pro *C.

Fuller Corporation, Bethlehem PA, December 1996 -

December 1997

Database Administrator

. Fuller Corporation is a major construction company with various

projects around the world.

. Created and maintained database and database objects.

. Developed logical and physical designs from ORACLE case repository,

planning database sizing, performance monitoring, help application

developers for SQL tuning.

. Developed standards for naming database objects and data sharing

strategies.

. Was responsible include Database backup, recovery, import, export,

database loading and monitoring system activities.

. Environment: ORACLE 7.3x, Spy, Designer 2000, SQL*NET, Novell 4.1,

Windows 3.1.

General Electric, Aircraft Division, March 1996 - December 1996

Module Leader

1. Metallography Lab Systems

The General Electric Aircraft Engines (GE-AE) turbine blade and vane

repair process involves several furnace based processes. To review

process quality, expendable parts similar to the ones that are being

repair are included in these runs. These test pieces are then

examined for flaws. The MLS system keeps track of these items for

process, and maintains the history of each process. It produces many

detailed reports, e.g. process completed items, status of the items,

etc.

. Migrated from ORACLE 6.0 with SQL*FORMS 2.x. Re-engineering and

designed the system to run on UNIX based servers with ORACLE

7.0, Developer 2000.

. Develop the system to take advantage of newer technologies (e.g.

stored procedures and defined primary foreign keys).

. Designed, coded, tested, prepared unit test plans and documented

the system.

. Developed common objects for the system. Generated application

ER Diagrams using Erwin. Wrote the database packages for the

system.

. Environment: ORACLE 7.0/6.0, Developer 2000, Designer 2000,

Erwin, Sun Solaris, UNIX, Windows 3.1.

2. Training and Educational Development System

The system was implemented to achieve this goal. TEDS is a conversion

of a character based system to a GUI system. This system is being used

by GE-AE at all its sites. TEDS contains various modules called

catalogs, through which it perform the tasks of tracking training

programs of employees. TEDS is referred to as a Notebook and is

maintained for every individual.

. TEDS consists of modules like training plan, training history

information, course details, overviews, course classification,

course assignment, session information and privileges. The

system was developed using ORACLE 7.1. There are no constraints

specified at the database level. All the relations are drawn at

the application level.

. Developed using ORACLE FORMS 4.5 and ORACLE REPORTS 2.5 as the

front end.

. Coded, tested, prepared unit test plans, dud QA for the other

group members. Also used Help Magician to generate context

sensitive windows Help for the system.

. Environment: ORACLE 7.1, Developer 2000, UNIX, Windows 3.1, Help

Magician.

Shani Exporters, Bombay India, November 1995 - February 1996

Project Leader

Diamonds Purchase and Sales System

The system was designed to support the purchase and sales needs Shani

Enterprises. Various kinds of purchase orders such as Local PO's, Cash

PO's, Contract PO's, etc are handled by the system. It also handles volume

and quantity based price rebates, tracking of advance Payments, vendor

rating and evaluation and maintenance of price history with breakups of

excise duty, sales tax, insurance, freight, exchange history, custom duty,

etc. The accounts receivable system handled posting of JV's at month end

for consolidated collection into General Ledger. The system was developed

using ORACLE 7.0 as back end and FORMS 4.5 and REPORTS 2.5 at front end.

. As leader for the purchase module, responsibilities included design of

the purchase modules, creating database tables and indexes and other

constraints for the Purchase Module along with the DBA.

. Was responsible for coding, testing, implementing, documenting and

design ER diagrams using Erwin.

. Environment: ORACLE 7.0, SQL*FORMS 4.5, SQL*PLUS, SQL*LOADER, PL*SQL,

REPORTS 2.5, Erwin.

LOK Construction, April 1992 - October 1995

Project Leader

1. Inventory Control System

The system consists of modules of material received, material rejection

and material used. It also handles transfer of materials from one site to

another site and consumption returns. The system transfers data to the

head office every day through modem communications, and receives data

from the head office to update new materials entries.

. Developed the system using PowerBuilder 4.0 and ORACLE 7.0 as

back end. Database level Stored Procedures were written to

update the head office tables. Used the Power Frame Class

Libraries, Object Navigator and tab Folder for the development.

. Designed database tables, indexes and other constraints with

DBA, designed and developed application common libraries,

coding, testing, interaction with users for analyzing the

system, documenting, preparing test plans, and implemented the

software.

. Environment: ORACLE 7, Powerbuilder 4, Power Frame, Object

Navigator, Tab Folder.

2. Integrated Purchase and Inventory Control System (IPICS)

The IPICS system consists of requisition, indenting, purchase order,

material received, material rejected, material used, bill booking,

transfer to accounts and payment details from accounts. This system also

handles data transfer to all the sites. IPICS has sub-modules such as

Open Man, Material Transfer, etc. The system keeps two types of units,

slandered and alt-unit. The alt-unit is decided by the company and the

system will give reports on both units. All conversion of the units is

done by the system. The system also allows the user to enter non-standard

items and from description given by the user, the system calculates the

units.

. Developed the system using PowerBuilder 3.0 and ORACLE 6.0.

Developed a common object library for master details,

maintenance windows, list window, drag and drop, search, etc.

. Wrote database level procedures to update site databases. As

module leader, was responsible for developing various modules of

IPICS system, interaction with users, designing modules with the

Team Leaders, coding, testing, integrated testing and

implementation of the modules.

. Environment: ORACLE 6.0, PowerBuilder 3.0, Power Frame, UNIX.

. Developed the Integrated Purchase and Inventory Control System

using C and Clipper and consists of modules of indent, purchase

order, material received note, material rejection, material

issue and bill booking.

. Environment: Novell NetWare (3.1, 2.2), Clipper, Turbo C.

Education

Post Graduate Diploma in Computer Science, India 1992.

BS India, 1990

Contact this candidate