About
A star schema is a relational schema that is a dimensional:
- with a single fact table
- and many foreign key relationships with several dimension tables.
The characteristic star-like structure is often called a star join schema. This term dates back to the earliest days of relational databases.
The main advantages of star schemas are :
- The data is easier to understand and navigate. User agree immediately that the dimensional model is their business
- Provide highly optimized performance for typical star queries (fewer join, very strong assumption about first constraining to attack the fact table, …)
- extensible to accommodate change. (You don't need to change this schema for future business need)
- Are widely supported by a large number of business intelligence tools, which may anticipate or even require that the data warehouse schema contain dimension tables.
A star join is a primary key to foreign key join :
- of the dimension tables
- to a fact_table.
Star schemas are used generally in the data marts to deliver business data to specific business units for drill down, analysis and other OLAP operations.
Dimension or Measure Attribute ?
It's some time difficult to make the choice between measure and dimensional descriptif attributes, especially when the value is a number. You must so answer to this question :
Where the value is more used ?
| Measures are | Descriptif Attributes are |
|---|---|
| additional | used as report constraint |
| semi-additive | used as row header |
| number that behaves like a textual descriptor | |
| discrete and constant |
Accommodate change
We can :
- add completely new dimension
- add new fact to the fact table assuming that the level of detail is consistent with the existent fact row.
- supplement pre-existing dimension with new unanticipated attributes
- …