What is the Star Schema
for Data Warehouse Design

A Star Schema

Most business intelligence data warehouses use what is called a dimensional model, where a basic fact table of data e.g. sales or support calls is surrounded and linked with other tables holding the dimensions of the fact table.

This particular fact table has four main dimensions - Customer, Time, Product and Staff.

These dimensions are then linked to the fact table through indexes (highlighted in yellow)to enable tables to be joined to permit fast queries, reports and data consolidations to be carried out.

For example, how many transactions for product x have we had this quarter?

This data model or schema is simple, allows fast retrieval, can be readily extended without changing all the existing standard reports and queries. The disadvantage is that there is some data redundancy which could cause inconsistency if not all of the redundant data is kept up to date.

A Snowflake Schema

The next kind or model is called a snowflake model and is very similar to the above schema except that some of the redundancy in the dimensions is removed by using what is called data normalised tables.

See the following example of a snowflake schema. Primary and foreign keys (primary keys are highlighted in yellow and foreign (linking) keys in green) are used to join up the tables to the central fact table and other dimension tables.

Remember, data normalisation is a three step process to ensure a that every piece of data is uniquely identified and there optimum data redundancy. For example, in the Location table the value of a country would be repeated many times. Now by introducing a Region dimension table the value of a particular country would be repeated less often.

To remove all country redundancy completely you would need a further table for Country. Often the Snowflake model does not go all this way to normalise the data fully.

These structures can easily be built with popular database management software systems.

Return to the Top of What is a Star Schema

Return to Data Warehouse Basics

Return to What is Business Intelligence?