Kanhaiya Lal

Khatik

BI Data Architect / Data Modeler

Email: acdatr@r.postjobfree.com Phone Number:

203-***-****

PROFESSIONAL SUMMARY

. An Information technology professional with more than 9 years of

extensive experience in all phases of Software development life

cycle including System Analysis, Design, Data Analyst, Data

Architect, Data Modeling Implementation and Support of various web

based applications in OLTP, Data Warehousing, and OLAP

applications.

. Data Modeler with strong Conceptual and Logical Data Modeling

skills, has experience with JAD sessions for requirements

gathering, creating data mapping documents, writing functional

specifications, queries.

. Extensive experience in Relational and Dimensional Data modeling

for creating Data Analysis, Business requirements, Logical and

Physical Design of Database and ER Diagrams with all related

entities and relationship with each entity based on the rules

provided by the business manager using ER Studio

. Worked extensively in forward and reverse engineering processes.

Created DDL scripts for implementing Data Modeling changes.

Published Data model to acrobat PDF files, extensively created ER

Studio reports in HTML, RTF format depending upon the requirement,

Published Data model in model mart, created naming convention

files, co-ordinated with DBAs to apply the data model changes

. Extensive Experience in writing system specifications, translating

user requirements to technical specifications,

created/maintained/modified data base design document with detailed

description of logical entities and physical table.

. Experienced in developing Database Design Document including Data

Model: Conceptual, Logical and Physical Models using ER Studio.

General understanding of Database Management System and Data

Warehouse including their functional and technical architecture and

the design of Data Flow Diagram.

. Good Knowledge of ETL Processes such source, mapping,

transformation, staging areas and created various ETL documents

such as ETL Mapping documents, Data Mapping documents, ETL test

scripts etc.

. Good Knowledge of Informatica Data Virtualization tool.

. Good knowledge and experience in Data quality, Data Governance and

Master Data Management.

. Excellent knowledge of Software Development Life Cycle (SDLC),

Adhered to standards/guidelines through out the development life

cycle and maintenance of the models.

. Possess strong Documentation skill and knowledge sharing among

Team, conducted data modeling sessions for different user groups,

facilitated common data models between different applications,

participated in requirement sessions to identify logical entities

. Extensive Experience working with business users/SMEs as well as

senior management.

. Strong understanding of the principles of Datawarehousing using

Fact Tables, Dimension Tables, star and snowflake schema modeling.

. Strong experience with Database performance tuning and

optimization, query optimization, index tuning, caching and buffer

tuning.

. Extensive experience in backend programming including Database

table design, Stored procedures/BTEQs/Macros, Triggers, Indexes,

Performance tuning in Oracle 10g/9i/8i and Teradata 12.

. Good knowledge in analysis and reporting using various Business

Intelligence tools such Business Objects XI 3, OBIEE, Qlikview,

Spotfire

. Basic Knowledge of MISMO (Mortgage Industry Standards Maintenance

Organization)

. Basic Experience with No-SQL databases.

. Basic knowledge of Master data Management and data Quality.

. Good expierence in Troubleshooting techniques for drilling down the

complex issues raised by users.

. Brain Bench certification in Datawarehousing and data modeling.

. Good knowledge of extracting data from Sales CRM like Salesforce

and Siebel.

. Excellent verbal, presentation and written communication skills.

Able to communicate with technical, non-technical and senior

management audiences. Have played a significant role in

requirements planning, client interaction and documenting.

. Excellent problem solving skills with a strong technical background

and result oriented team player with excellent communication and

interpersonal skills.

. Knowledge of Oracle Apps Financial Services.

TECHNICAL SKILLS

Data modeling Embarcadero ER Studio, Erwin

Databases/Tools Teradata,12.0 Oracle 8i/9i,10g/11g MS SQL Server 2000,

MySQL, Sybase 11.5/11.2, MS Access, Mainframe

Files, DB2 UDB, XML, SQL Navigator, Teradata SQL,

PL/SQL

ETL Tools: Informatica Data Virtualization 9x, Informatica PowerCenter 9x,

8x, 7x/6.1

BI Tools Business Objects,OBIEE,Qlikview, Spotfire

Architectures OLAP, OLTP

Operating Systems Windows 95/98/2000/XP, UNIX

Programming Languages SQL, Shell Scripting, C, C++, XML, HTML

PROFESSIONAL EXPERIENCE

PBC (Pepsico Beverages Company), Somers,NY Jan-2014 to

Current

Sr. Data Modeler

Project - Enterprise Data Foundation V2.0:

This project is to build the enterprise Data warehouse layer for Pepsico

reporting. First phase of the project includes data modeling for entities

required for Canada, Mexico and Saudi countries and later phases would have

more entities required for other countries. This enterprise Data warehouse

layer will enable Pepsico reporting users to get holistic view of key

metrics like Net Sales across various regions by Product, Customer etc.

. Combine business and technical knowledge to determine the most

appropriate business needs.

. Analyzing the business requirements for building data marts and data

warehouse

. Design, developed, implement and maintained

conceptual/logical/physical data models for complex data warehouses

. Developing Snowflake schemas by normalizing the dimension tables

. Develop logical specifications and user requirements to design

solutions for the application environment

. Perform program analysis, design, code, test, implement, troubleshoot,

and write documentation

. Create functional specifications and document findings

. Identify data elements needed to support new requirements

. Responsible for creating and executing test cases to validate ETL and

report code changes made for break/fixes and new enhancements

. Responsible for implementing security for Data warehouse and reporting

applications.

. Responsible for reporting development with HTML along with JQuery,

ASP.Net and C#

. Other similar professional responsibilities as needed.

Environment: Teradata 14, Oracle 10g,Teradata SQL, PL/SQL for Oracle, ER

Studio, Informatica Powercenter 9.1, Business Objects

GE Capital Americas, Norwalk,CT

June 2013-Dec-2013

Data modeler/Data Analyst

HRSIC Project:

GE Capital is an financial services unit of US conglomerate General

Electric. GE Capital provides commercial lending and leasing, as well as a

range of financial services for health care, media, communications,

entertainment, consumers, real estate, and aviation. GE Capital focuses

primarily on loans and leases that it underwrites to hold on its own

balance sheet rather than on generating fees by originating loans and

leases, then selling them to third parties.

This project is built to provide data for GECA Sales Compensation process.I

was involved in maintaining Liaise with business subject matter experts in

analyzing business requirements and translating them into detailed logical

models, physical models and schema development in the database.

Role & Responsibilities:

. Taking Business requirement from users and performing data analysis.

. Analyzing and modeling of CRM systems like Salesforce and Siebel in

DW.

. Analyzing and modeling of Oracle Apps Financial Services tables.

. Data Architecture, Conceptual, logical and physical Modeling for OLAP

systems.

. ER Modeling - Developing Entity Relationship Diagrams (ERD).

. Normalizing the tables/relationships to arrive at effective Relational

Schemas.

. Identifying the facts & dimensions; grain of fact, aggregate tables

for Dimensional Models.

. Developing Snowflake Schemas by normalizing the dimension tables as

appropriate.

. Implementation of Business Rules in the Database using Constraints &

Triggers.

. Dimensional Data Modeling to deliver Multi-Dimensional STAR schemas.

. Requirements & Business Process Analysis; Rapid Audit of the

requirements and existing systems.

. Developed, Implemented & Maintained the Conceptual, Logical & Physical

Data models.

. Design & Implementation of Data Mart; DBA coordination; DDL & DML

Generation & usage.

. Metadata & Data Dictionary Management; Data Profiling; Data Mapping.

. Normalization techniques to arrive at effective Relational Schemas.

. Applied Data Governance rules (primary qualifier, Class words and

valid abbreviation in Table name and Column names).

. Involved in capturing data lineage, table and column data definitions,

valid values and others necessary information in the data models.

. Documented all the information of application and saving them for

future reference.

. Identifying the facts & dimensions; grain of the fact; aggregate

tables, etc for Dimensional Models.

. Developing Snowflake Schemas by normalizing the dimension tables as

appropriate.

. ETL Design & Implementation - Data Extraction, Transformation &

Loading using Informatica mappings and BTEQs.

. Performance Tuning (Database Tuning, Teradata SQL Tuning,

Application/ETL Tuning)

. Create and alter SQL statements before sending database change request

to DBA team.

. Maintained and documented all create and alter SQL statements for all

release.

. Designing Data Flows & System Interfaces.

. Architecting Work Flows, Activity Hierarchy & Process Flows;

Documenting using Interface Diagrams, Flow Charts & Specification

Documents.

. Coordinating with DBA team to implement physical models & to setup

development, test, staging & production environments for DDL & DML

Generation & usage.

. Extensively involved in various data modeling tasks including forward

engineering, reverse engineering, complete compare, creating DDL

scripts, creating subject areas.

. Defined dependencies of access layer components on all the source

databases or files.

Environment: Teradata 12, Oracle 10g,Teradata SQL, PL/SQL for Oracle, ER

Studio, Erwin, Informatica Powercenter 9.1

GE Capital EF, Norwalk & Danbury CT Aug 2009 -

June 2013

Data Modeler/Data Analyst

BI Touchless Origination:

Touchless application is new Origination Application for GE capital and GE

capital has planned to on-board all of small ticket business on this

application to get fast turn-around of the deal.

As a part of this project, we have worked on pulling the data in staging

layer, Core (3rd Normal form) and acess layer on top of 3rd Normal Core.

We provided single-stop reporting solution to Origination users of GECA.I

was involved in maintaining Liaise with business subject matter experts in

analyzing business requirements and translating them into detailed logical

models, physical models and schema development in the database.

Role & Responsibilities:

. Taking Business requirement from users and performing data analysis.

. Performing analytics on marketing data.

. Designed a Conceptual Data Model, Logical Data Model, and Physical

Data Model.

. Worked extensively in understanding business reporting requirements

for various releases Touchless for various GECA businesses.

. Designed the Stage environment as per the various sources involved for

this project.

. Extensively used Normalization Techniques to design Logical/Physical

Data Models, relational database design in EDW Core (3rd Normal form)

on basis of FSLDM model.

. Designed the access layer dimensional model for Touchless reporting.

. Conducted data modeling sessions for different user groups,

facilitated common data models between different applications,

participated in requirement sessions to identify logical entities.

. Extensively involved in various data modeling tasks including forward

engineering, reverse engineering, complete compare, creating DDL

scripts, creating subject areas, creating DDL scripts.

. Extensively worked with Business Objects developer in designing the

universe.

. Extensively worked with Developers and ETL team in enhancing the

models and co-ordinate the same with DBA in implementing those changes

into Applications orDatabases.

. Extensively involved in creating, maintaining, updating documents like

requirement document, database design document, System design

document, naming standard procedures, SOPs etc.

. Analyzed and optimized the existing business processes using

Conceptual Models and Data Flow Diagram. The process included

performance enhancement requirements assessment and mapping of

existing work flow and data flows.

. Participated in Requirement Gathering, Business Analysis, User

meetings, discussing requirements for new table design in existing

Data base and discussing about new columns for existing tables in

Database.

. Knowledge of breaking the xmltype Oracle object into flat view and

pull the same in Teradata.

. Worked on the Data Warehouse team analyzing data files that had to be

compiled from disparate non-production sources and readied them for

production. Tasks included: comparing data to requirements

documentation, creation of data layouts, data dictionary, and the pipe

delimited and fixed width files. In addition, was team lead for

managing the completion and loading of these files for the Data

Management group

. Adding Attributes related to MISMO in datawarehouse

. Extensively worked on creating,Altering and Deleting the Tables in

different Development Environments and also Production.

. Coordinating with DBA in implementing the Database changes and also

updating Data Models with changes implemented.

Environment: ER Studio DA 9.1, Erwin, Teradata 12, Informatica,

Teradata SQL, Microsoft Project Plan, Business Objects, Oracle 10g

GECA (GE Capital Americas), Mumbai India Oct 2006 - Aug

2009

Data Modeler/Analyst

BI Enterprise DW Reporting Project:

This project had 1000 users across the world for various GECA Sub-

Businesses.. This role involves direct interaction with users and it is

very challenging as it deals with multiple source applications and

knowledge of multiple tools and then doing enhancement to existing

environment to cater to user's needs. This project involves in all the

enhancement and support that can be identified by users and applied to

access layer,Siebel Analytics and business objects. Enhancements are

decided for every month and subsequently they are released to production

environment.

Responsibilities:

. Designed a Conceptual Data Model, Logical Data Model, and Physical

Data Model.

. Worked extensively in understanding business reporting requirements.

. Designed the Stage environment as per the various sources involved for

this project.

. Extensively used various Techniques to design Logical/Physical Data

Models, relational database design in Oracle DW.

. Designed the access layer dimensional model reporting.

. Conducted data modeling sessions for different user groups,

facilitated common data models between different applications,

participated in requirement sessions to identify logical entities.

. Extensively involved in various data modeling tasks including forward

engineering, reverse engineering, complete compare, creating DDL

scripts, creating subject areas, creating DDL scripts.

. Extensively worked with Business Objects developer in designing the

universe.

. Extensively worked with Developers and ETL team in enhancing the

models and co-ordinate the same with DBA in implementing those changes

into Applications orDatabases.

. Extensively involved in creating, maintaining, updating documents like

requirement document, database design document, System design

document, naming standard procedures, SOPs etc.

. Analyzed and optimized the existing business processes using

Conceptual Models and Data Flow Diagram. The process included

performance enhancement requirements assessment and mapping of

existing work flow and data flows.

. Participated in Requirement Gathering, Business Analysis, User

meetings, discussing requirements for new table design in existing

Data base and discussing about new columns for existing tables in

Database.

. Knowledge of breaking the xmltype Oracle object into flat view and

pull the same in Teradata.

. Worked on the Data Warehouse team analyzing data files that had to be

compiled from disparate non-production sources and readied them for

production. Tasks included: comparing data to requirements

documentation, creation of data layouts, data dictionary, and the pipe

delimited and fixed width files. In addition, was team lead for

managing the completion and loading of these files for the Data

Management group

. Extensively worked on creating,Altering and Deleting the Tables in

different Development Environments and also Production.

. Coordinating with DBA in implementing the Database changes and also

updating Data Models with changes implemented

Environment: Oracle 9i, Informatica 7, Oracle PL/SQL, Siebel Analytics/

OBIEE, Business Objects, Unix

GE Capital, Corporate Financial Services

Aug 2006 - Sep 2006

Data Modeler/Analyst & ETL Developer

Marketing Data Mart:

This project was to enhance the existing Marketing Data Mart by adding more

entities to it. Data Model changes were being done to add all the new

entities and then new mappings were being built to populate all the

entities.

Roles and Responsibilities:

. Designed a Conceptual Data Model, Logical Data Model, and Physical

Data Model.

. Participated in Requirement Gathering, Business Analysis, User

meetings, discussing the issues to be resolved and translating user

inputs into ETL design documents.

. Assess data quality requirements in terms of data completeness,

consistency, conformity, accuracy, referential integrity, duplication

and evaluate vendors (Informatica Data Explorer/Data Quality, identify

and measure data quality, design and implement data profiling and data

quality improvement solution to analyze, match, cleanse, and

consolidate data before loading into data warehouse.

. Developed mapping to load the relational data model for operational

data store and staging areas, Designed Dimension & Fact tables for

data marts.

. Developed Informatica ETL Mappings documents, created ETL staging area

framework, created data mapping documents, data flow diagram, ETL test

scripts etc.

. Analyzed and optimized the existing business processes using

Conceptual Models and Data Flow Diagram. The process included

performance enhancement requirements assessment and mapping of

existing work flow and data flows.

. Extensively worked with Business Objects developer in designing the

universe.

Environment: Oracle 9i, Informatica 7, Oracle PL/SQL, Siebel Analytics/

OBIEE, Business Objects, Unix

GE Commercial Finance UK (GLM), Mumbai, India Jan 2006-

Jul 2006

Data Analyst, ETL and Business Objects Developer

This project is to create Operational reports (Point in time reports) in

Business Objects from Oracle Applications database. A Business Objects

Universe is designed with these tables to cater the reporting requirements.

Reports are created from this Universe and published to the Corporate

documents. I worked as a BO developer to build the universe and reports in

this project.

Roles and Responsibilities:

. Analyzed/Profiled the source data to help DA to define the grain of

the data

. Developed Analytical applications that can analyze large amounts of

online and offline data.

. Extensively used Data Stage ETL tool in designing & implementing

Extract Transformation & Load processes.

. Performed tuning on database queries, ETL mappings and end-user

queries.

. Developed Reporting Design doc and reports.

. Developed BO reports and Universe

. Used Appworx to schedule and running the solution and used Informatica

components for testing and debugging and monitoring the ETL jobs.

. Wrote shell scripts to automate file manipulation and data loading

procedures.

. Unit tested and deployed the jobs to Production.

. Used Type2 mapping to update a slowly changing dimension table to keep

full history.

Environment: Informatica PowerCenter 6.1 (Work Flow Manager, Work Flow

Monitor, Designer), Oracle,PL/SQL, Unix, Business Objects

GE Commercial Finance VFS, Mumbai, India

Aug 2005- Dec 2005

ETL & Business Objects Developer

The project involved migration of the existing hierarchical Database based

on FOCUS to Oracle

I have built extraction process, BO universe and 105 BO reports on top of

it.

Roles and Responsibilities:

. Involved in analysis, requirements gathering, functional/technical

specification, development, deploying and testing.

. Used Informatica as an ETL tool to extract data from sources like DB2

and flat files and loaded to target tables..

. Developed DataStage Parallel jobs for various components and strings

involved in the calculation of quotes and rates.

. Developed parameter driven ETL process to map source systems to target

data systems with DataStage complete source system profiling

. Developed BO reports and Universe

. Wrote Release notes, Deployment documents and scheduled the jobs.

. Project Life Cycle - from analysis to production implementation, with

emphasis on identifying the source and source data validation,

developing particular logic and transformation as per the requirement

and creating jobs and loading the data into different targets.

Environment: Informatica PowerCenter/PowerMart 6.1 (Work Flow Manager, Work

Flow Monitor, Designer), Oracle, Unix, power designer, Business Objects

EDUCATIONAL QUALIFICATION

. Bachelor's Of Technology from NIT Jamshedpur, India

[pic]

Contact this candidate