As per Querry Performance which type of schema is best? and Why?
Printable View
As per Querry Performance which type of schema is best? and Why?
[QUOTE=raghuladdu;11999]As per Querry Performance which type of schema is best? and Why?[/QUOTE]
For performance wise, Its better to go for a Star Schema rather than going for Snow-flake schema.
A [B]Star Schema[/B] is simple data warehouse schema which consists of a single Fact table surronded by multiple dimensional tables.
[B]Advantages:[/B][LIST][*]Easy to understand[*]Easy to Navigate b/n the tables due to less number of joins.[/LIST]
[B]Disadvantages: [/B][LIST][*]Occupies more space[/LIST]
A [B]Snowflake schema[/B] is a Data warehouse Schema which consists of a single Fact table and multiple dimensional tables. These Dimensional tables are normalized.
[B]Advantages: [/B][LIST][*]These tables are easier to maintain[*]Saves the storage space.[/LIST]
[B]Disadvantages: [/B][LIST][*]Due to large number of joins, it is complex to navigate[/LIST]
There is another popular schema called [B]Constellation Schema[/B] in which one dimension table is accessed by one more than one Fact table.
In this type of schema, there may be one or more than one number of Fact tables and also Dimension Tables.
It sometimes also called as [B]Galaxy Schema..[/B]
[B]Constellation Schema :[/B]
This schema is used according to the requiremnt of the client.
Ex: Banking, Insurance, Travel & Hospitality etc.,