---

7. Schemas for Multidimensional Databases

The schema defines the logical structure of the data warehouse. There are three primary types of schemas.

7.1 Star Schema

• Definition: The simplest and most common schema. It consists of one large central Fact Table surrounded by smaller Dimension Tables.
• Structure:
• The center is the Fact Table (contains Foreign Keys referencing Dimensions and numeric Measures).
• The points of the star are Dimension Tables.
• The Fact Table is in 3NF, but Dimension Tables are De-normalized (flattened).
• Characteristics:
• Simple queries (fewer joins).
• Fast query performance.
• Higher data redundancy in dimension tables.
• Easier for business users to understand.

7.2 Snowflake Schema

• Definition: A variation of the Star Schema where dimension tables are Normalized (split into multiple related tables).
• Structure: The Fact Table is still central, but dimension tables are broken down into sub-dimensions. This looks like a snowflake structure.
• Characteristics:
• Reduces data redundancy (saves storage space).
• Easier to maintain dimension data.
• Disadvantage: Queries require more joins, which can slow down performance.
• Complex for end-users to navigate.

7.3 Galaxy Schema (Fact Constellation Schema)

• Definition: A schema that contains multiple Fact Tables sharing common Dimension Tables.
• Structure: Two or more Fact Tables share dimension tables.
• Usage: Used when a business has multiple processes (e.g., Sales and Inventory) that need to be analyzed together.
• Characteristics:
• Most complex design.
• Allows for cross-functional analysis.
• Shared dimensions are called "Conformed Dimensions."

7.4 Comparison of Schemas