Tutorials -> Dimensional Modeling Tutorials

Star Schema Designing - Examples

If you carefully look at our new dimensional modeled schema, it will look like this:






You can easily tell this looks like a STAR. Hence it is also known as Star Schema.

Advantages of Star Schema

• Star Schema is very easy to understand, even for non technical business managers
• Star Schema provides better performance and smaller query times
• 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 using 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 product category.

GROUP BY P.Category

Next>> Report Mapping