What are Fact and Dimension tables?

Wednesday, January 27, 2010

Overview
• A multidimensional storage model involves two kind of tables:
1. Dimension tables
2. Fact tables
Fact Tables: A fact table contains the fact data of business like sales amount, quantity, customers and reference keys to dimension tables.



· Dimension Tables:
· A dimension table holds the data that we use to group the value to derive summary.
· A dimension table has primary key.
E.g. Customer table has information about city, state and zip code to summaries geographically sales. A dimension table contains the more detailed information of fact data like product details, customer details, store details etc.


· The fact table mainly holds transaction data.
· E.g. fact table data shows units amount and sold quantities.

Data Modelling for Data Warehousing

Overview
Data Cubes = A data which are available in multidimensional matrices is called Data Cubes.
· A excel sheet is a two dimensional matrix, it can have sales revenue of corresponding geographical regions.
· Change process of converting one dimensional to another in the data cube is called pivoting.
• In pivoting, the data cube can rotate to see the data in different orientation.
• So, this technique is equivalent to product sales table by region.
• There are two kinds of Hierarchical views of multidimensional models:
1. Roll-up display
In the Roll-up display the hierarchy will move up and it will group into the larger units.
E.g.: Weekly data summation by quarter and by then by year.
2. Drill-down display
In the Drill-down display it will have opposite view.
E.g. dividing the region sales into sub-region and then again dividing sub-region sales country specific sales.