Datawarehouse Design (3)

Designing conceptual models of data warehouses is the next stage which should be implemented after the phase determination of measure and dimension . At this stage, created a model that can describe any data or tables that will be stored in a data warehouse, the following connectedness among others.

The data or tables in the data warehouse can be modeled by using modeling tools such as ER diagramstar schemaSnowflake schema , or FCO-IMFully Communication Oriented Information Modelling ). But in general, are tools used star schema or Snowflake schema .

The star schema is the simplest style of data warehouse schema. The star schema consists of a few fact tables (possibly only one) referencing any number of dimension tables.

Star schema will describe the fact table , the table that represents the measure , as a “data center”. This table will be connected with the tables that describe the dimensions to measurethe ( dimension table ). For example, the star schema for data warehouse sale is:

Star Schema


Snowflake schema architecture is a more complex variation of a star schema design. The main difference is that dimensional tables in a snowflake schema are normalized. one or more of the existing hierarchy in the dimension tables are normalized (decomposition) into several smaller table, so they have a typical relational database design.

Snowflake Schema

Snowflake Schema



Tagged: , ,

2 thoughts on “Datawarehouse Design (3)

  1. rohit May 2, 2012 at 11:14 am Reply

    why we go for snowflake design when we can build datawarehouse design with simple arcitecture like star schema

    • jokondo June 23, 2012 at 7:34 am Reply

      If a dimension is very sparse (i.e. most of the possible values for the dimension have no data) and/or a dimension has a very long list of attributes which may be used in a query, the dimension table may occupy a significant proportion of the database and snowflaking may be appropriate.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: