Data warehousing is new emerging trend to store all operations data into single, consistent and complete data store which is optimized for analysis. Data warehouse consists of many data marts having common dimension tables. While data mart is collection of fact tables and related dimension tables which are specific to one business.
Data mart can be designed with:
- Star schema: What is star schema? it is simplest and recommended architecture schema for data warehouse. Generally fact
table is in third normal form (3NF) while dimension tables are not normalized. If we draw a diagram it looks like fact able in middle as center of star and all dimension tables as ray out of it hence the name star schema. Following are few traits of star
- Most simple structure and easy to understand
- Query is very fast as simple joins
- Loading dimension tables with data may be slow and data size large as they are not normalized
- Widely used
- Snow flake schema is similar to star schema except the dimension tables are normalized with related tables. Hence the total shape around fact tables look likes snow flake.
- Its more complex than star schema.
- Its good for sophisticated queries