Please enable JavaScript.

Coggle requires JavaScript to display documents.

DIMENSIONAL MODELS (DM) (Deriving DM from ER (Step 1: Classify entities…

- - - - Consists of a central table called the fact table, surrounded by a number of dimension tables.
      - Fact tables
        
        Correspond to particular types of business events.
        
        Usually contain numerical values, which may be analyzed using statistical functions.
      - Dimensions
        
        Answer “who”, “what”, “when", “where”, “how”, and “why” questions about the business events stored in the fact table.
        
        The hierarchical structure of dimensions provides the basis for analyzing data at different levels of detail
    - - The “arms” of the snowflake can grow to arbitrary lengths in each direction.
      - It explicitly shows the structure of each dimension rather than appearing as an unstructured collection of data items
- - - - Record details of business events
      - Decision support applications focus on these events to identify patterns, trends, and potential problems and opportunities
    - - Related to a transaction entity by a one-to-many relationship
      - Answer “who”, “what”, “when", “where”, “how”, and “why” questions about the business events stored in the fact table.
    - - Component entity by a chain of one-tomany relationships
      - Define embedded hierarchies in the data model and are used to classify component entities.
  - - - Not all transactions will be important for decision-making purposes
      - Multiple star schemas at different levels of detail may be required for a particular transaction
      - When transaction entities are connected in a master-detail structure, they should be combined into a single star schema
    - - Unsummarized
      - Summarized
    - - Not all component entities may be relevant for purposes of analysis.
      - If a component entity has no dependent attributes, its key will be included in the fact table but it will not have its own dimension table.
      - Explicit dimensions are required to represent dates and times.
  - - - The key of a fact table includes all dimensional keys, and will therefore not be minimal in most cases.
    - - Fully additive facts: these are facts that can be meaningfully added across all dimensions.
      - Semi-additive facts: these are facts that can be meaningfully added across some dimensions but not others (usually time).
      - Non-additive facts: these are facts that cannot be meaningfully added across any dimensions
      - The key of the fact table consists of the keys of all the dimension tables.
      - The non-key attributes of the fact table are all summarized facts.
  - - - The key of each dimension table should be a simple (single attribute) numeric key.
    - - The resulting dimension table consists of the union of all attributes in the original entities—as many descriptive attributes should be included as possible to support slicing and dicing of data in different ways.
    - - All of the tables in the star schema are denormalized to at least some extent: each table corresponds to multiple entities in the original normalized ER model.
      - All of the dimension tables are in 2NF: they have simple keys and no repeating attributes.
      - The Date Dimension is a new table which does not correspond to any entity in the original ER model.