E-R Models to Dimensional Models

E-R Models to Dimensional Models

1. Dimensional models

   • Data warehouses
   • User-Friendly
   • Star Scheme
     • Fact table
       • All dimension tables are linked only to this table
     • Dimension table
   • Snowflake Scheme
     • Shows structure of each dimension
     • Reduces subquery performance
   • Advantages
     • Reduced complexity
     • Optimize query performance
   • Disadvantages
     • Lmits the queries that can be written

2. Why they work

   • Chunking
     • humans have limited capacity for processing information
   • Hierarchical structuring

3. Transformation process

   • 1. Classify entities
     • Transaction entities
     • Component entities
     • Classification entities
   • 2, Design high-level star schema
     • Identify star schemas required
     • Define level of summarization
     • Identify relevant dimensions
   • 3. Design detailed fact table
     • Define key
       • Composite key consisting of the keys of all dimension tables plus any degenerate dimensions
     • Define facts
       • non-key attributes of the fact table
   • 4. Design detailed dimension table
     • Define dimensional key
     • Collapse hierarchies
     • Replaces codes and abbreviation by descriptive text

Bilder und Dateien der Mindmap