Star schema is not a new concept. This is a very famous design
approach which was there even before SAP HANA came. The understanding of star
schema will help you to understand HANA analytic view.
Star Schema – a single object (the fact table) sits in the middle and is connected to other surrounding objects (dimension tables) like a star.
Each dimension is represented as a single table. The primary key in each dimension table is related to a foreign key in the fact table.
Each dimension table is joined to the fact table using a primary key to foreign key join, but the dimension tables are not joined to each other.
What is Star Schema?
The star schema (also
called multi-dimensional schema) is the simplest style of data warehouse
schema.Star Schema – a single object (the fact table) sits in the middle and is connected to other surrounding objects (dimension tables) like a star.
Each dimension is represented as a single table. The primary key in each dimension table is related to a foreign key in the fact table.
It is called a star schema because; the entity-relationship
diagram of this schema resembles a star, with points radiating from a central
table. The center of the star consists of a large fact table and the points of
the star are the dimension tables.
Facts and Dimension Tables:
The fact table holds
the main data i.e. the actual data. It includes a large amount of aggregated
data, such as sales, profit etc.
Dimension tables include the attributes that describe the data in different perspective.
For example : If sales is
the fact then Sales per Year, Sales per Country are the different perspective
of seeing data. Here Country and Year would be 2 different dimensions.Dimension tables include the attributes that describe the data in different perspective.
Each dimension table is joined to the fact table using a primary key to foreign key join, but the dimension tables are not joined to each other.
Fact table:
o Contain key numeric measures of the business
o Connected to dimensions
o Two types of columns:
o Facts or measures
o Foreign keys to dimension tables
o Primary key is formed by combining foreign
keys pointing to dimension tables
Dimension Tables:
o Contain information that represents attributes
or aspects of the business
o Contain relatively static data
o Are joined to fact table through a foreign key
reference
Advantages of Star Schema:
Performance
o Faster query operation
Data model is easy to understand
o Based on business process
o Support multi-dimensional analysis
Easy to define hierarchies
o Continent –> Country –> City
o Shift – Day – month – Year
Easy to navigate
o Number of table joins reduced
Maintainable and Scalable
o Can add new fact tables which use existing
dimensions
o Supported by many front-end tools
No comments:
Post a Comment