Dimensional Modeling

1
Dimensional Modeling
2
Entity-Relationship Models

• Goal of entity-relationship (normalized) data
  models is to eliminate data redundancy
• Optimized for transaction processing because
  transactions are very simple and deterministic
• Models the intricate relationships among the data
  elements in an organization

3
Entity-Relationship Model
E-R model includes allthe processes of an
organization includingmany that do not
intersect in the real world
4
E-R models and Data Warehouses

• Relational databases can consist of hundreds or
  even thousands of entities (tables)
• Difficult for end users to understand the E-R
  model
• Queries in E-R databases can become very complex
• Although transactions are efficient querying
  multiple tables can be very inefficient

5
Dimensional Modeling

• The goal of dimensional modeling is to create an
  intuitive representation of the data that is
  optimized for high-performance access
• Essentially an E-R model with some restrictions
• A single E-R model breaks down into multiple
  dimensional models

6
Dimensional Modeling

• Every dimensional model consists of
• a single fact table with a multipart primary key
• Fact table usually contain one or more numerical
  facts
• one or more dimension tables with single part
  primary keys that relates to one part of the fact
  table key
• Dimensions consists of textual descriptors
  associated with the facts in the fact table

7
Advantages of Dimensional Models

• Dimensional models can be modified gracefully
• Attributes added without breaking existing
  queries and reports
• Standard structure makes querying efficiency
  predictable
• Standard structure is easy to understand and
  query
• Well understood approaches to common situations

8
Retail sales processdimensional
model One-to-manyrelationships
betweendimensions and fact table 1,000s
records indimension tables106 to 109 in
facttables Diagram makes itobvious why
themodel is calledthe Star Schemaor Star Join
9
Big Bang or Data Marts

• Big Bang (Enterprise Wide) Approach
• Build an enterprise data warehouse containing
  dimensional models for entire enterprise
• Very difficult and time consuming
• Data Mart Approach
• Build subject specific data marts as needs arise
• Leads to incompatible and duplicated effort

10
Data Warehouse Bus Architecture

• Create a data warehouse architecture
• Define conformed dimensions
• Define conformed fact tables
• Create data marts in conformance with the
  architecture
• Each data mart should reuse conformed dimensions
  and (less often) fact tables when possible
• Conformed dimensions form the data warehouse buss

11
Data Mart Dimensions
Bus Architecture Matrix
12
(No Transcript)
13
Conformed Dimensions

• A conformed dimension is a dimension that means
  the same thing with every possible fact table
• Customer, product, location, time
• Architecture defines and reconciles the
  attributes associated with each conformed
  dimension
• Conformed dimensions tie data marts together
• Often as much a political issue to create these
  as a technical issue

14
Partial Customer Dimension
Customer Dimension
15
Conformed Facts

• Facts represent the numeric data either in the
  database or that is derived from this data
• Revenue, profit, prices, quantities, costs
• Occasionally there are textual facts but they are
  rare
• Conformed fact definitions should include
• Organization wide definitions
• Equations used to derive them
• Units of measure
• Reporting periods
• Granularity

16
Fact Table
Fact tables are narrowbut very long (few
fieldsbut many records)