Unit 2. -- Schema Types

Variations of Star Schema

• Star Schema

• All dimensions are denormalized
• Wide dimensions and deep facts

• Snowflake Schema

• All dimensions are normalized into 3NF

• Starflake Schema

• Combinations of star and snowflake schema

• Multi Star Schema

• A star schema where the concatenated FKs might not provide a PK
• A fact table contains a degenerated dimension (e.g., line_num)

• Multi Fact tables

• Many fact tables with one or more dimensions in common
• Use conformed dimensions
• Conformed dimensions support drill-across operations

The Data Warehouse Bus Architecture

• The architected step-by-step approach to building a DW
• Each data mart should be based on the most granular data
• Two steps:

• Create an architecture that defines the scope of the complete DW
• Oversee the construction of each data mart:

• Use conformed dimensions and conformed facts

Conformed Dimensions and Standard Fact Definitions

• A conformed dimension is the same dimension in all data marts

• A conformed customer dimension is a master table of customers with a clean customer key and many well-maintained attributes
• A conformed product dimension: the enterprise’s agreed-upon master list of products
• Location, Time, …

• When developing conformed dimensions:
  • Take time to design the conformed dimensions
  • Define at the most granular level possible
  • Use a surrogate (system-generated) PK, different from production systems because production key may be reused or merged with other system keys causing them to change

• Creating a set of master conformed dimensions takes 80% of the up-front data architecture effort

• Advantages of conformed dimensions:
  • A single dimension can be used against multiple fact tables
  • User interfaces and data contents are consistent
  • Rollups across data marts

• What are Conformed Facts
  • The same measure data used in many data marts
  • Ex: revenue, profit, price, cost