SCHEMAS:
Star Schema:
-
Star
Schema means Dimension Tables are directly linked to
the Fact Table.
Snow Flake Schema:
-
Dimension
Tables are indirectly
linked to the Fact Table OR
- Dimension table is linking to another dimension table.
|
|
Star
Schema |
|
Snow
Flake Schema |
|
1 |
Has redundant data and hence tough to maintain/change |
1 |
No redundancy and hence more easy to maintain/change |
|
2 |
Less complex queries and hence easy to understand |
2 |
More complex queries and hence less easy to understand |
|
3 |
Less foreign keys so faster execution time of cube. |
3 |
More foreign keys so take more execution of cube |
|
4 |
Has
De-Normalized Tables |
4 |
Has
Normalized Tables |
|
5 |
Good to
use for large database |
5 |
Good to
use for small database |
|
6 |
Less
number of joins |
6 |
More
number of joins |
|
7 |
When
dimension table has less
number of rows we can go for this. |
7 |
When
dimension table is relatively
big in size it is better as it reduces space. |
|
8 |
Recommended |
8 |
Not
Recommended |
|
|
|
|
|
|
|
ETL |
Cube Process |
|
Star Schema |
Slow |
Fast |
|
Snow Flake Schema |
Fast |
Slow |
Double Star Schema:
**Remember:
Even though
star schema is recommended but in real time scenario most of the companies are
using Snow flake schema due to the large amount of data.