Data Modeling & Dimensional Modeling
Datawarehousing Training Course duration
4 Days
Datawarehousing Training Course outline
Data Modeling
1. Concepts & architecture
- Introduction to DB Model Development
- When to perform Data Modeling Task
- Problem Analysis & Scope
- Entity – Relationship Diagram or Model
- Basic Construct of E-R Modeling
- Understanding Entities, Attributes and Relations
- Normalization
- DB Architectural Model (E-R Notations)
- Enterprise views on Data Modeling
- Introduction to the Modeling Tools
2. Development life cycle
- Gathering Business Requirements
- Initial Design Phase (CDM )
- Logical Design phase (LDM)
- Physical Design phase (PDM)
- Database Script
- Database Creation and Maintenance
3. Conceptual Data Model
- What is CDM & its Overview
- Outline or Blue print for Database Design
- Advantages of CDM
4. Logical Data Model
- Overview
- Design Framework
- Defining entities, Attributes, Key Groups & Relationships
- Defining the Business Process
5. Physical Data Model
- Overview
- Generating Script
- Generating tables, Columns, Relationships and its properties
- Applying Normalization Rule
- Logical vs Physical Models
6. Steps In Building the Data Model
- Identification of data objects and relationships
- Drafting the initial ER diagram with entities and relationships
- Refining the ER diagram
- Add key attributes to the diagram
- Adding non-key attributes
- Diagramming Generalization Hierarchies
- Validating the model through normalization
- Adding business and integrity rules to the Model
7. Entities
- What is an Entity?
- Identifying Entity
- Types of Entities
- Naming Entities
- Describing Entities
- Identifying & Applying Key Columns
- Common Modeling mistakes with Entities & Keys
8. Attributes
- What is an Attribute?
- Analyzing & Defining Attribute Characteristic
- Naming Attributes
- Describing Attributes
- Common Mistakes with Attributes
9. Understanding Relationship between Objects
- What is a Relationship?
- Relationship types
- Dependency & Non-dependency
- Relationship Cardinality
- Developing Schema
- Common Mistakes
10. Normalization Rules
- Basic Concepts
- Overview
- Apply Normalization on the Model
- Functional Dependency
- First Normal Form
- Second Normal Form
- Third Normal Form
- Boyce-Codd Normal Form
- Forth Normal Form
- Fifth Normal Form
Dimensional Modeling
1. Concepts & architecture
- Overview
- Defining Dimensional Model
- What makes differ from the Data Model?
- Uses of Dimensional Data Model
- Dimensional Model Frame work/Architecture
- Dimensional Model types
- Dimensional Schema types
2. De-Normalization
- What is a De-normalize? Overview
- Why to De-normalize?
- What supports De-normalize?
- How it is useful in the Business Analysis?
3. OLAP Architecture
- Overview
- Theory of Analysis
- Multi-dimensional architectural Support
- Creation of Cubes
- Multi-Dimensional Reports
4. Facts and Dimension tables
- Different types of tables
- What is a Dimension table?
- What is a Fact table?
- Creating Dimension table
- Create Fact table
- What is a slowly changing Dimension?
- Where & When SCD is used
5. Schema and it’s types
- Overview
- What is a schema?
- Schema Rules
- Different types of Schema
- What is a Star Schema?
- What is a Star Snowflake Schema?
6. Difference between Data Model and Dimensional Model
7. Why we need different models for database and data warehouse?
