Advanced Data Modeling and Architecture

Session Title:  Advanced Data Modeling and Architecture

Pre-requisites

ü  Should be aware of DBMS Concepts

ü  Should be aware of Data warehouse concepts

ü  Should be aware of Data base concepts (any database)

ü  Should have minimun of 2+ years of experience on any ETL Tool

Table of Content

1.       Data Model Overview

1.1   Prerequisites for this Course

1.2   Expectations from this training

1.3   Scope of the training

1.4   Introduction to Data Model s

o   Fundamentals of database Systems

o   What is Data Modeling

o   Why use Data Modeling

o   What Makes a Good Data Model

1.5   Sample Data Models

1.6   Challenges, Benefits &  opportunities

1.7   Why Data Modeling Tool

1.8   Requirements for a good data modeling tool

1.9   Overview Data Model Tools

2.       Data Models

2.1   What is a Data Model

2.2   Types of Data Models

2.3   Data modeling stages and deliverables

2.4   Main Phases of Database Design

2.5   Data Modeling In DWH Environment

2.6   Data Modeling Approaches

2.7   Data Modeling Life Cycle

2.8   Zachman Framework

3.        How to Model Data

3.1   Identify entity types

3.2   Identify attributes

3.3   Identify relationships

3.4   Apply naming conventions

3.5   Assign keys

4.       Data Modeling

4.1   Conceptual Data Modeling

4.1.1          Introduction to Conceptual Data Model

4.1.2          Stages in Conceptual Modeling

4.1.3          Components of a Conceptual Data Model

4.1.4          ER Modeling Basic Concepts

4.1.5          Enhanced ER Modeling

4.1.6          Guidelines for ER Modeling

4.2   Relational Data Modeling

4.2.1          Relational Data Model Concepts

4.2.2          ER to Relational Mapping Algorithm

4.2.3          Relational Database Design

4.2.3.1                Relational Database Design Approach

4.2.3.2                Normalization

4.2.4          Relational Data Modeling Case Study

4.3   Dimensional Data Modeling  

4.3.1          Steps in Dimensional Modeling

4.3.2          Star Schema & Snowflake Schema

4.3.3          The Implementation Approach (Bill & Ralph)

4.3.4          Dimensional Modeling Fundamentals

4.3.4.1    Dimensional Modeling Overview

4.3.4.2    Dimensional Modeling Steps

4.3.4.3    Dimensional Model Design Life Cycle

4.3.4.4    Dimensional model - Case Study

4.3.4.5    Hierarchies in Dimensions

4.3.5          Dimensional Modeling Beyond Fundamentals

                                                                         4.3.5.1      Supply chain, Value Chain & Value Circle

                                                                         4.3.5.2      Dimensional Modeling for Value Chain

                                                                         4.3.5.3      Confirmed Dimensions

                                                                         4.3.5.4      Slowly Changing Dimension

                                                                         4.3.5.5      Additivity of Measures

                                                                         4.3.5.6      Dimensional Modeling framework

                                                                         4.3.5.7      Transactions and Snapshot Schemas

                                                                         4.3.5.8      Accumulating Snapshot Schemas

                                                                         4.3.5.9      Factless Fact tables

                                                                      4.3.5.10      Big Dimensions

                                                                               4.3.5.11      Dirty Dimensions

4.3.6          Dimensional Modeling Advanced Concepts

                                                                                  4.3.5.1      Extended Dimension Table Design

                                                                                  4.3.5.2      Extended Fact Table Design

                                                                                  4.3.5.3      Advanced ROLAP Querying and Reporting

4.4   Logical Data Modeling

4.4.1          Introduction to Logical Model

4.4.2          Characteristics of a Logical Model

4.4.3          Conventions for Notations

4.4.4          Sample Logical Data Model

4.4.5          Summary

4.5   Physical Data Modeling

4.5.1          Physical Database Design

4.5.2          Physical Database Design Consideration

5.       Data Warehouse Database Administration and Performance Improvements

5.1   DBA Checklist

5.2   Raid

5.3   Backup and Recovery

5.4   Archiving, Purging and Retrieval

5.5   Materialized views

5.6   Indexing

5.7   Aggregates

5.8   MDBMS VS RDBMS

5.9   Partitioning

5.10                        Performance Tuning

6.       Data Modeling for Data Warehouse Environment

6.1   Data Warehouse Architectures

6.2   Building An Enterprise Data Model

6.3   ER to Dimensional Model Conversion

6.4   Modeling the Staging area

6.5   Dimensional Modeling Tips

6.6   Data Quality of Data Models

7.       Introduction to ERwin

7.1   Introduction to ERwin Modeling Suite

7.2   Reverse Engineering

7.3   Entity and Attributes

7.4   Relationship

7.5   Display Level

7.6   Subject Area

7.7   Forward Engineering