When we think about dimensional modelling then two major and very important modeling schema comes into picture called the start schema and the snowflake schema.
So lets go into details on start and snowflake schema.
Star schema
What is star schema? The star schema architecture is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of fact table and the points of the star are the dimension tables. Usually the fact tables in a star schema are in third normal form(3NF) whereas dimensional tables are de-normalized. Despite the fact that the star schema is the simplest architecture, it is most commonly used nowadays and is recommended by Oracle.
Snowflake schema
What is snowflake schema? The snowflake schema architecture is a more complex variation of the star schema used in a data warehouse, because the tables which describe the dimensions are normalized. Here there can be two sets of dimensions one is called the primary dimension which is directly connected with the fact and secondary dimension which is indirectly connected with a fact through some primary dimensions.
Apart from star and snowflake we have one more schema when we try to connect two or more dimensional schema through fact and that is called as fact constellation schema.
Fact constellation schema
What is fact constellation schema? For each star schema it is possible to construct fact constellation schema(for example by splitting the original star schema into more star schemes each of them describes facts on another level of dimension hierarchies). The fact constellation architecture contains multiple fact tables that share many dimension tables.
The main shortcoming of the fact constellation schema is a more complicated design because many variants for particular kinds of aggregation must be considered and selected. Moreover, dimension tables are still large.
So lets go into details on start and snowflake schema.
Star schema
What is star schema? The star schema architecture is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of fact table and the points of the star are the dimension tables. Usually the fact tables in a star schema are in third normal form(3NF) whereas dimensional tables are de-normalized. Despite the fact that the star schema is the simplest architecture, it is most commonly used nowadays and is recommended by Oracle.
Snowflake schema
What is snowflake schema? The snowflake schema architecture is a more complex variation of the star schema used in a data warehouse, because the tables which describe the dimensions are normalized. Here there can be two sets of dimensions one is called the primary dimension which is directly connected with the fact and secondary dimension which is indirectly connected with a fact through some primary dimensions.
Apart from star and snowflake we have one more schema when we try to connect two or more dimensional schema through fact and that is called as fact constellation schema.
Fact constellation schema
What is fact constellation schema? For each star schema it is possible to construct fact constellation schema(for example by splitting the original star schema into more star schemes each of them describes facts on another level of dimension hierarchies). The fact constellation architecture contains multiple fact tables that share many dimension tables.
The main shortcoming of the fact constellation schema is a more complicated design because many variants for particular kinds of aggregation must be considered and selected. Moreover, dimension tables are still large.
No comments:
Post a Comment