Difference between Fact table and Dimension table?

128

When reading a book for business objects, I came across the term- fact table and dimension table.

I am trying to understand what is the different between Dimension table and Fact table?

I read couple of articles on the internet but I was not able to understand clearly..

Any simple example will help me to understand better?

This question is tagged with database data-warehouse business-intelligence fact-table

~ Asked on 2013-11-17 22:16:23

The Best Answer is


305

In Data Warehouse Modeling, a star schema and a snowflake schema consists of Fact and Dimension tables.

Fact Table:

  • It contains all the primary keys of the dimension and associated facts or measures(is a property on which calculations can be made) like quantity sold, amount sold and average sales.

Dimension Tables:

  • Dimension tables provides descriptive information for all the measurements recorded in fact table.
  • Dimensions are relatively very small as comparison of fact table.
  • Commonly used dimensions are people, products, place and time.

enter image description here

image source

~ Answered on 2015-11-17 06:32:36


121

This appears to be a very simple answer on how to differentiate between fact and dimension tables!

It may help to think of dimensions as things or objects. A thing such as a product can exist without ever being involved in a business event. A dimension is your noun. It is something that can exist independent of a business event, such as a sale. Products, employees, equipment, are all things that exist. A dimension either does something, or has something done to it.

Employees sell, customers buy. Employees and customers are examples of dimensions, they do.

Products are sold, they are also dimensions as they have something done to them.

Facts, are the verb. An entry in a fact table marks a discrete event that happens to something from the dimension table. A product sale would be recorded in a fact table. The event of the sale would be noted by what product was sold, which employee sold it, and which customer bought it. Product, Employee, and Customer are all dimensions that describe the event, the sale.

In addition fact tables also typically have some kind of quantitative data. The quantity sold, the price per item, total price, and so on.

Source: http://arcanecode.com/2007/07/23/dimensions-versus-facts-in-data-warehousing/

~ Answered on 2015-08-06 17:46:06


Most Viewed Questions: