Dimensional Model

Dimensional Model

1. ER Model to dimensional

   • 1. Classify entities
     • Transaction Entities
       • Order
       • Order Item
       • Stock Level
     • Component Entities
       • Customer
       • Employee
       • Retail Outlet
       • Product
       • Warehouse
       • Delivery Method
     • Classification Entities
   • 2. Design high-level star schema
     • 2.1 Identify Star Schemas Required
     • 2.2 Define Level of Summarization
     • 2.3 Identify Relevant Dimensions
   • 3. Design detailed fact table
     • 3.1 Define Key
     • 3.2 Define Facts
   • 4. Design detailed dimension table
     • 4.1 Define Dimensional Key
     • 4.2 Collapse Hierarchies
     • 4.3 Replace Codes and Abbreviations by Descriptive Text
       • All tables in the star schema denormalized to at least some extent
       • All dimension tables in 2NF
       • Date Dimension is a new table

2. Produce easy database structures

3. Optimize query performance

4. Schemas

   • Star Schema
   • Snowflake Schema

5. Dimensions

   • ”WHAT” Dimension
   • ”WHO” Dimension
   • ”WHERE” Dimension
   • ”WHEN” Dimension

6. Why it works?

   • Principle #1: “Chunking”
   • Principle #2: Hierarchical Structuring