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