Facts & Dimensions

This morning I went for a merchandise store to have some products of my daily use. Usually my mom gives me a list of products to get from the store and today also I had a list of products. I gave the list to the man and asked to deliver those items. But this time I was interested in seeing his calculation and the system of his sales maintenance.
I saw him entering my details such as my name, address, street, phone and other details related to me in one table and my sales information (all purchase related calculations e.g. OrderQuantity, UnitPrice, DiscountAmount, TaxAmount etc) corresponding to my details in another table.

This is the case of Datawarehouse design; all the information about the customer and its transaction is divided in 2 different tables. One contains the detailed information of the customer and the other related to its sales (additive values).The first table you can say as Dimension table which contains details of the customer.Second table, which contains all the additive figures and the corresponding foreign keys of all the dimensions, is called Fact table.
Example from AdventureworksDW database shows the Fact and Dimension tables below.

Fig1.Customer Dimension

Fig2. Fact Table

So we have a record in both the tables for a customer. Similarly for a Product and various dimension tables.

Hope this clears the idea of Fact and dimension tables in the warehouse.

Dimension: Dimension table contains all the detailed textual information of the dimensions.

Fact: A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.
In the next post we will discuss about the various schemas of datawarehouse projects.