Star Schema of OLAP

Star Schema Designing – Examples

What Is A Star Schema? If you carefully look at our new dimensional modeled schema, it will look like this:

OLAP Star Schema

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
<<< Fact Table SchemaMapping of Reports to Star Schema >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .