Data Warehousing Basic

What is Data Warehousing?

Data warehouse is mainly used to store bulk data and generate analysis report. These analysis reports are very important for taking business decision.

Following are constraint to develop analysis report from the relational database:
• Performance is very poor
• Difficult to develop
• Put very heavy load on database

If you will go with data warehousing then you can easily use SQL server analysis service to generate analysis report.

Generally Data ware housing use two type of database structure:

• Star schema
• Snow flake schema

Key Terms:

Dimension: Dimensions are the entity using it you want to analysis the data. For an example, Product is an entity; it is dimension which is used to analysis the sales report product wise.

Fact: Fact is the entity on which you are analysis the data. For an example sales data is a fact. Fact data must be numeric data so you can analysis the report. For an example, you can calculate minimum/maximum/average of the data. You can use many function on that numeric data.

Dimension tables are joined with fact table directly or in-directly.

Star Schema
If all the dimension tables are joined directly to fact table then that schema is called star schema. Here I took an example of Sales management system.




Snow Flake Schema
If any dimension table is not directly joined with fact table but it is joined through the other dimension tables then that schema is called snow flake schema.



In above diagram, you can see “DimState” and “DimCountry” are dimension tables and connected to Fact table “FactSales” through the DimCity dimension table.