Data Warehouse Overview
Schema à It is a collection of tables in Data ware house is called as schema.
Types of Schema:
In data warehousing a star schema is the simplest form of a dimensional model, in which data is organized into facts and dimensions.
A fact table is surrounded by multiple dimensions is called as star schema.It contains De-normalized data; having less joins; performance is high.
· Every dimension in a star schema is represented with the only one-dimension table.
· The dimension table should contain the set of attributes.
· The dimension table is joined to the fact table using a foreign key.
· The dimension table are not joined to each other.
· Fact table would contain key and measure.
· The Star schema is easy to understand and provides optimal disk usage.
· The dimension tables are not normalized. For instance, in the above figure, Country_ID does not have Country lookup table as an OLTP design would have.
· The schema is widely supported by BI Tools.
Snow Flake Schema:
It is an enhancement of star schema by adding additional dimensions is called as snow flake schema. It consists of Normalized data; having more joins; Performance is low.
· The main benefit of the snowflake schema it uses smaller disk space.
· Easier to implement a dimension is added to the Schema.
· Due to multiple tables query performance is reduced.
· The primary challenge that you will face while using the snowflake Schema is that you need to perform more maintenance efforts because of the more lookup tables.
Multi-star schema (or) Galaxy Schema: If a dimension table has more than 2 fact tables is called as Multi-star schema or Galaxy Schema. The dimensions in this schema are separated into separate dimensions based on the various levels of hierarchy.
For example, if geography has four levels of hierarchy like region, country, state, and city then Galaxy schema should have four dimensions. Moreover, it is possible to build this type of schema by splitting the one-star schema into more Star schemes. The dimensions are large in this schema which is needed to build based on the levels of hierarchy.
This schema is helpful for aggregating fact tables for better understanding.