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.
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.
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.
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:
- Contain key numeric measures of the business
- Connected to dimensions
- Two types of columns:
- Facts or measures
- Foreign keys to dimension tables
- Primary key is formed by combining foreign keys pointing to dimension tables
Dimension Tables:
- Contain information that represents attributes or aspects of the business
- Contain relatively static data
- Are joined to fact table through a foreign key reference
Advantages of Star Schema:
Performance
- Faster query operation
Data model is easy to understand
- Based on business process
- Support multi-dimensional analysis
Easy to define hierarchies
- Continent –> Country –> City
- Shift – Day – month – Year
Easy to navigate
- Number of table joins reduced
Maintainable and Scalable
- Can add new fact tables which use existing dimensions
- Supported by many front-end tools
No comments:
Post a Comment