DIMENSIONAL MODELING

DIMENSIONAL MODELING

1. Designing data warehouses

2. Database structures

3. ER -> DIMENSIONAL

   • OLTP OnLine Transaction Processing
   • OLAP On-Line Analytical Processing
   • ER need normalization
   • Step by step
     • Classify entities
       • As orders, shipments, payments
     • Design high-level star schema
       • Transaction entities correspond to fact tables and component entities correspond to dimension tables
     • Design detailed fact table
       • complete the detailed design for fact tables in each star schema
     • Design detailed dimension table
       • Now complete the detailed design for dimension tables in each star schema (attribute level design).

4. OBJECTIVES

   • Easy for end users to understand and write queries against.
   • Optimize query performance

5. SCHEMAS

   • Star schema
     • Have a central table by a number of dimensions tables
       • WHAT, WHO, WHEN, WHERE questions
   • Snowflake schema
     • Reduce query performance and reduce storage space

6. Issues examples

   • Alternative dimensional structures: stars, snowflakes, and starflakes
   • Slowly changing dimensions
   • Heterogeneous star schemas