Types of Schema | Star Schema | Snowflake schema

Types of Schema :

In Previous articles i explained about Business Intelligence,Data warehousing,OLAP vs OLTP;In this article I will explain about Database Schema used in business intelligece.Schema is logical description of whole database. Database schema is a skeleton or structure of the database which represents database logically. Same like a database Data warehouse also requires to maintain database schema. Data warehouse schema includes name of database objects with its relationship maintained in diagrammatic format. Database uses relational model where data warehouse uses following Types of Schema :1.Star Schema  2.Snowflake Schema  3.Fact Constellation schema which is also called as Galaxy Schema.

What you will find in this article?

1.What are Types of Schema in BI?

2.About Star Schema

3.About Snowflake Schema

What are Types of Schema in BI?

Star Schema :

In Star schema there is Fact table as a center and all dimension tables surrounded with that  fact table. It is called as Star schema because diagram resembles a star with points radiating from center. Star schema is used in simple data mart or data warehouse. Star schema is designed in such way that it will optimize the querying on large data sets. In Star schema multiple dimension tables joined with only one fact table in de-normalized form.

OBIEE BMM(Business model) Layer always follows star schema.

These all tables are joined in logical manner to meet specific business requirement. Star schemas are used to create report in BI reporting tool. Dimension table contains attributes and fact table contains foreign keys and measurements.

Real Life Example :

In above diagram you will able to see that table named transaction is fact table and all 5 are Dimension tables.

Snowflake Schema :

Snowflake schema is a form of dimensional modeling where dimensions are stored with multiple dimension tables. Snowflake schema is variation over star schema. The schema is diagramed as each fact is surrounded with dimensions; and some dimensions are further related to other dimensions which are branched in snowflake pattern. In snowflake schema multiple dimension tables are organized and joined with fact table. Only difference between star and snowflake schema is dimensions are normalized in snowflake schema.Normalization splits up data in to additional tables.

Real life Example :

In Diagram i shown the snowflake schema where sales table is a fact table and all are dimensions. Store table is further normalized in to different tables name city, state and region.

Snowflake schema is used in application layer of OBIEE.

Granularity :

Granularity in table represents the level of information stored in the table. In BI granularity is very important concept to check the table data. The granularity is high and low .High granularity data contains the data with high information or you can say it as transaction level data is high granularity data. Low granularity means data has low level information only. Fact table always have low granularity mean we need very low level data in fact table.

Following 2 points are important in defining granularity :

1.Determining the dimensions that are to be included

2.Determining location to place hierarchy of each dimension of information.

Real life Example :

Date Dimension Granularity level :

Year,month,quarter,period,week,day.

Normalization :

Database Normalization is organizing non structured data in to structured data. Database normalization is nothing but organizing the tables and columns of the tables in such way that it should reduce the data redundancy and complexity of data and improves the integrity of data.

Database Normalization is nothing but technique of designing the database in structured way to reduce redundancy and improve data integrity.

Database Normalization is used for following Purpose:

1. To Eliminate the redundant or useless data
2. To Reduce the complexity of the data
3. To Ensure the relationship between tables as well as data in the tables
4. To Ensure data dependencies and data is logically stored.

Hope you like this article on Types of Schema used in data warehousing. If you like this article kindly comment it in comment section.