MBN News | The Independent Business Blogging Community

MBN News: Business Intelligence | Databases | BigData

MBN International: English Flag Bulgarian Flag

29 Members | ~1,500 Unique Visitors with ~3,500 Visits Last Month | Last Update: 27 Apr 2017 | Google PageRank 5 (PR5)

MBN Most Popular

MBN Latest

Now Blogging » Business Intelligence (BI)

Business Intelligence Dictionary: Star Schema, Snowflake Schema | NSBI

|
<a href='http://morningbiznews.com/en/search/star'>star</a>, <a href='http://morningbiznews.com/en/search/snow+flake'>snow flake</a>, <a href='http://morningbiznews.com/en/search/schema'>schema</a>, <a href='http://morningbiznews.com/en/search/nsbi-dict'>nsbi-dict</a>

Relational database normalized schema is different from the architecture of a data warehouse. This article will explain what are the two most common design approaches when building a data warehouse - the Star Schema and the Snowflake Schema.

NSBI Tutorials aim to make Business Intelligence (BI) and Data Warehousing (DWH) attractive to non-technical people as well as to those who are now entering the field and are excited by the numerous ways data is changing our world. NSBI Tutorials are written and delivered by Nick Shopov, (BI Software Developer & DWH Consultant).

Star Schema | Definition

The DWH star schema features one fact table and multiple dimension tables directly linked to the fact table.

See visualization below as an example.

star-schema-nsbi

Star schema is designed for faster access to data (less joins) at the cost of further data redundancy.

Snowflake Schema | Definition

The snowflake schema is a hybrid between the star schema and the normalized relational schema where dimensions in the Star Schema are further normalized. This implies that dimensions are no longer directly linked to the Fact Table but may have other tables in between.

See a sample visualization of a Snowflake schema:

snowflake-schema-nsbi

Snowflake schema is reducing the effect from the star schema and is normally avoided.