Steps in designing Star Schema

                                                                                                                                                                      

Steps in designing Star Schema

• Identify a business process for analysis (like sales).

• Identify measures or facts (sales dollar).

• Identify dimensions for facts (product dimension, location dimension, time dimension, organization dimension).

• List the columns that describe each dimension.(region name, branch name, region name).

• Determine the lowest level of summary in a fact table(sales dollar).

Important aspects of Star Schema & Snow Flake Schema

• In a star schema every dimension will have a primary key.

• In a star schema, a dimension table will not have any parent table.

• Whereas in a snow flake schema, a dimension table will have one or more parent tables.

• Hierarchies for the dimensions are stored in the dimensional table itself in star schema.

• Whereas hierarchies are broken into separate tables in snow flake schema. These hierachies helps to drill down the data from topmost hierarchies to the lowermost hierarchies.