From ER Models to Dimensional Models

From ER Models to Dimensional Models

1. Dimensional Model

   • An ER model can be transformed into a set of dimensional models by a process of selective subsetting, denormalization, and summarization.
   • It's a database design technique developed specifically for designing data warehouse
   • The approach was first defined by Kimball (1996)
   • Produce database structures that are easy for end users to understand and write queries agains
   • Optimize query perfomance
   • Star SCHEMAS
     • The data is organized into facts and dimensions, a fact is an event that is counted or measures, such as a sale or login
     • This schema must respond where, when, who and what
   • Snowflake SCHEMAS
     • It's a star schema with a fully normalized dimension. Rather than having a regular structure, the 'arms' can grow to arbitrary lengths in each direction
     • The advantage is that shows the structure of each dimension rather than appearing as an unstructured collection of data
   • Theoretical Explanation
     • Principle 01 Chunking
       • Humans have a strictly limited capacity for processing information
       • If the amount of information received exceeds these limits, information overload ensues and comprehension degrades rapidly
     • Principle 02 Hierarchical Structuring
       • Is the most common way to organize the complexity for the purposes of human understanding
       • Each dimension in a star schema typically consist of one or more hierarchies
   • Exploding the Myths
     • It acts as a barrier for people trained in traditional database design techniques to learn dimensional design
     • Fact Table: which is in 3rd normal form
     • There are one or more entities called dimension tables, each of which is related to the fact table via one or more on-to-many relation-ships

2. Deriving Dimensional Models from ER Models

   • Data warehouse design is a highly constrained design problem limited by what data is available from operational systems
   • The transformation of an ER model to dimensional form takes place in four steps:
     • 1. Classify Entities by categories, transaction, component classification.
     • 2. High-level star schema: At a high level, transaction entities correspond to fact tables and component entities correspond to dimension tables
       • Identify Star Schemas
       • Define Level of Summarization
       • Identify relevantly dimensions
     • 3. Detailed Fact Table Design
       • Define key
       • Define Facts
     • 4. Detailed Dimension Table Design
       • Define Key
       • Hierarchies
       • Replace codes and abbrevations

Images and files in the map