Schema
A schema is a collection of objects such as tables, views, indexes, synonyms. It simply explains the logically diagram of Database and relation between tables by using key attributes. It includes the name and description of records of all record types including all associated data-items and aggregates.
There are four types of schemas are available in data warehouse.
Star Schema
Snow Flake Schema
Extended Star Schema
Galaxy Schema
Star Schema with Diagram
Star Schema
· In Star Schema all dimensional tables are related with One Fact table.
· The dimension table contains the set of attributes. Each Dimension table has a one to many relationship with Fact table.
· Simple Star Schema contains only one Fact Table where as a complex star schema contains more than one Fact table.
· Star schemas are denormalized that reason it retrieve data faster than highly normalized schemas.
The above diagram explains Simple Star Schema. There is a fact table at the center of location and it contains the keys to four dimensions.
Snow Flake Schema with Diagram
Snow Flake Schema
· In snow Flake schema also same as star schema like all dimension tables related with Fact table and some of the Dimension table linked with Dimension tables only.
· One or more dimension tables are partially or completely normalized in Snowflake schema.
· Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table.
· The main Principle of snowflake is normalization of the dimension tables by removing redundancy of attributes and creating separate tables.
The above diagram explains that the dimension tables of Customer, Time, Product and Geography are connected to Fact table of FactInternetSales , Product Dimension table related with another dimension table ProductSubcategory.
Extended Star Schema with Diagram
Extended Star Schema
· In Star Schema Fact tables are connected to Dimension tables only. But Extended Star Schema Fact table is connected to Dimension table and the Dimension table is connected to SID (Surrogate ID) table and SID table is connected to the master data tables.
· One Fact table connected to 16 Dimension tables, one Dimension table can be assigned with maximum of 248 SID Tables in Extended Star schema.
· Main Advantage of Extended Star schema is keeping the master data outside of dimension tables , we are able to share the master data across the system there by reducing the need of redundancy of master table.
· It is mainly used in SAP BW projects for fast loading of data and fast access to reports.
Galaxy Schema with Diagram
Galaxy Schema
· The Galaxy schema is also known as fact constellation schema. It is combination of star schema and snow flake schema.
· Galaxy schema contains many fact tables with some common dimension (Confirmed Dimensions)
· This Schema is a combination of many data marts. One fact table may represent one data mart.
