Star Schema

on 02/21/2014 - 05:17 pm

In the star schema design, a single object (the fact table) sits in the middle and is radically connected to other surrounding objects (dimension lookup tables) like a star. Each dimension is represented as a single table. The primary key in each dimension table is related to a foreign key in the fact table.

Star Schema

All measures in the fact table are related to all the dimensions that fact table is related to. In other words, they all have the same level of granularity.

A star schema can be simple or complex. A simple star consists of one fact table; a complex star can have more than one fact table.

Let's look at an example: Assume our data warehouse keeps store sales data, and the different dimensions are time, store, product, and customer. In this case, the figure on the left represents our star schema. The lines between two tables indicate that there is a primary key / foreign key relationship between the two tables. Note that different dimensions are not related to one another.

Benefit of Star Schema Design

Star schema provides a de-normalized design. Star schema is probably most popular schema in dimensional modeling because of its simplicity and flexibility. In a Star schema design, any information can be obtained just by traversing a single join, which means this type of schema will be ideal for information retrieval (faster query processing). Here, note that all the hierarchies (or levels) of the members of a dimension are stored in the single dimension table

Related Posts

data-warehousing-desi-cityguide

Learn data warehouse - step by step

on 02/21/2014 - 12:59 pm

dimension-model-desi-city-guide

What is Dimension data model?

on 02/21/2014 - 01:17 pm

Browse by Category more