Data Warehousing Community Forum
February 08, 2012, 11:25:56 pm

Pages: [1]   Go Down
  Print  
Author Topic: DW Schemas  (Read 2104 times)
Sipra
Founder
*****

Reputation: +59/-0
Offline Offline

Posts: 323


Am the King...


View Profile WWW
« on: May 22, 2007, 03:26:17 pm »

The different Data Warehousing schemas are as follows:

Star Schema:

Definition: The star schema is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center.

A single Fact table (center of the star) surrounded by multiple dimensional tables(the points of the star).
Advantages:
  •    Simplest DW schema  (EDIT)
  •    Easy to understand
  •    Easy to Navigate between the tables due to less number of joins.
  •    Most suitable for Query processing (EDIT)

Disadvantages:
  •    Occupies more space
  •    Highly Denormalized (EDIT)

Snowflake schema:

Definition: A Snowflake schema is a Data warehouse Schema which consists of a single Fact table and multiple dimensional tables. These Dimensional tables are normalized.
Advantages:
  •    These tables are easier to maintain
  •    Saves the storage space.
Disadvantages:
  •    Due to large number of joins, it is complex to navigate

Constellation Schema:

Definition:
A Constellation Schema in which one dimension table is accessed by one more than one Fact table.
In this type of schema, there may be one or more than one number of Fact tables and also Dimension Tables.
It sometimes also called as Galaxy Schema.
« Last Edit: September 19, 2007, 11:19:08 am by Sipra » Logged

Whoever
Administrator
*****

Reputation: +34/-0
Offline Offline

Posts: 124



View Profile WWW
« Reply #1 on: May 22, 2007, 03:49:26 pm »

I heard about Star-flake schema ! Is there any schema like this ?

If so what is the difference between all these following schema -
  • Star Schema
  • Star-flake Schema
  • Snow-flake Schema
Logged

If most people said what’s on their minds, they’d be speechless.
Sipra
Founder
*****

Reputation: +59/-0
Offline Offline

Posts: 323


Am the King...


View Profile WWW
« Reply #2 on: June 05, 2007, 01:11:02 pm »

I heard about Star-flake schema ! Is there any schema like this ?

If so what is the difference between all these following schema -
  • Star Schema
  • Star-flake Schema
  • Snow-flake Schema

The Differences between those three are as follows: (wrt Definition)

Star Schema - A logical structure that has a fact table (containing factual data) in the center, surrounded by dimension tables (containing reference data)

Snowflake schema - A variant of the star schema where each dimension can have its own dimensions.

Starflake schema - Hybrid structure that contains a mixture of (denormalized) STAR and (normalized) SNOWFLAKE schemas.
« Last Edit: September 19, 2007, 11:21:47 am by Sipra » Logged

Raj2007
DW Apprentice
**

Reputation: +3/-0
Offline Offline

Posts: 25


Lightning for DW Forum


View Profile
« Reply #3 on: July 07, 2007, 05:44:14 pm »

Star Schema - A Normalized multi-dimensional model in which each disjoint dimension represented by a single table.

Snow-flake Schema - A Normalized multi-dimensional model in which at least one dimension represented by two or more heirarchically tables.
Logged
Pages: [1]   Go Up
  Print  
 
Jump to: