Star Schema

Star Schemas

A star schema is a database design where there is one central fact table, that fact table  participates in  one-to-many relationships with dimension tables.

1)     Dimension tables are de normalized structure.

2)     There are no hierarchies between dimensions.

3)     Required less joins to fetch the data from star schema because it fact table directly joining with dimension. The most natural way to model a data warehouse is as a star schema, only one join establishes the relationship between the fact table and any one of the dimension tables.

4)     A star schema optimizes performance by keeping queries simple and providing fast response time. All the information about each level is stored in one row.

The star schema is the simplest data warehouse schema.

Figure 2-1 Star Schema

Text description of the illustration dwhsg007.gif

Snow flake Schema

If a dimension is normalized, we say it is a snowflaked design. 

Snowflake schema, which is a star schema with normalized dimensions in a tree structure.

Snowflake schema is a more complex data warehouse model than a star schema, Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table.

 For example, a product dimension table in a star schema might be normalized into a products table, a product_category table, and a product_manufacturer table in a snowflake schema. While this saves space, it increases the number of dimension tables and requires more foreign key joins. The result is more complex queries and reduced query performance.

Figure 17-3 presents a graphical representation of a snowflake schema.

Figure 17-3 Snowflake Schema

Below is the simple data model

Below is the sq for project dim

1.ACW – Logical Design

2.ACW – Physical Design

3. ACW - Data Flow Diagram