Snowflake Schema Design

If we did not de-normalize our dimensions into one table each, then the schema would look like Fig. 1

You can see, this looks like a snow flake, hence this type of schema is called Snowflake Schema. General rule of thumb is keep away from snow flake schemas as even though they may save you some space, they will cost a lot in terms of query time.

Star Schema

Let us discuss Star Schema with respect to an example: -

If you carefully look at our new dimensional modeled shcema, it will look like the one shown in Fig 1. You can easily tell, this looks like a STAR. Hence it is also known as Star Schema.

Advantages of Star Schema

1. Star Schema is very easy to unerstand, even for non-technical business managers.

2. Star Schema provides better performance and smaller query times.

3. Star Schema is easily extensible and will handle future changes easily.

A typical SQL Query Template for the Sales Schema will look like: -

----- Select the measurements that you want to aggregate unsing SUM Clause

SELECT P.Name, SUM(F.Sales)

--- JOIN the FACT table with Dimension Tables

FROM Sales F, Time T, Product P, Location L

WHERE F.TM_Dim_Id = T.Dim_Id AND F.PR_Dim_Id = P.Dim_Id AND F.LOC_Dim_Id = L.Dim_Id

--- Constrains the Dimension Attributes

AND T.Month ='Jan' AND T.Year='2003' AND L.Country_Name='USA'

--- finally the 'group by' clause Identifies the aggregation level. In this example you are aggregating all sales within a category

GROUP BY P.Category