[database] Difference between Fact table and Dimension table?

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?

The answer is

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

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/

Super simple explanation:

Fact table: a data table that maps lookup IDs together. Is usually one of the main tables central to your application.

Dimension table: a lookup table used to store values (such as city names or states) that are repeated frequently in the fact table.

Dimension table Dimension table is a table which contain attributes of measurements stored in fact tables. This table consists of hierarchies, categories and logic that can be used to traverse in nodes.

Fact table contains the measurement of business processes, and it contains foreign keys for the dimension tables.

Example – If the business process is manufacturing of bricks

Average number of bricks produced by one person/machine – measure of the business process

In the simplest form, I think a dimension table is something like a 'Master' table - that keeps a list of all 'items', so to say.

A fact table is a transaction table which describes all the transactions. In addition, aggregated (grouped) data like total sales by sales person, total sales by branch - such kinds of tables also might exist as independent fact tables.

  1. The fact table mainly consists of business facts and foreign keys that refer to primary keys in the dimension tables. A dimension table consists mainly of descriptive attributes that are textual fields.
  2. A dimension table contains a surrogate key, natural key, and a set of attributes. On the contrary, a fact table contains a foreign key, measurements, and degenerated dimensions.
  3. Dimension tables provide descriptive or contextual information for the measurement of a fact table. On the other hand, fact tables provide the measurements of an enterprise.
  4. When comparing the size of the two tables, a fact table is bigger than a dimensional table. In a comparison table, more dimensions are presented than the fact tables. In a fact table, less numbers of facts are observed.
  5. The dimension table has to be loaded first. While loading the fact tables, one should have to look at the dimension table. This is because the fact table has measures, facts, and foreign keys that are the primary keys in the dimension table.

Read more: Dimension Table and Fact Table | Difference Between | Dimension Table vs Fact Table http://www.differencebetween.net/technology/hardware-technology/dimension-table-and-fact-table/#ixzz3SBp8kPzo

From my point of view,

  • Dimension table : Master Data
  • Fact table : Transactional Data

Dimension table : It is nothing but we can maintains information about the characterized date called as Dimension table.

Example : Time Dimension , Product Dimension.

Fact Table : It is nothing but we can maintains information about the metrics or precalculation data.

Example : Sales Fact, Order Fact.

Star schema : one fact table link with dimension table form as a Start Schema.

enter image description here

Questions with database tag:

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str IN vs ANY operator in PostgreSQL How to execute raw queries with Laravel 5.1? How should I tackle --secure-file-priv in MySQL? How to use multiple databases in Laravel Why does Oracle not find oci.dll? How do I create a local database inside of Microsoft SQL Server 2014? How to change the default port of mysql from 3306 to 3360 Laravel 5 error SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES) How to change Oracle default data pump directory to import dumpfile? Using COALESCE to handle NULL values in PostgreSQL What is Hash and Range Primary Key? How to get item count from DynamoDB? Find duplicate records in MongoDB Django Model() vs Model.objects.create() Mongodb: Failed to connect to, reason: errno:10061 How can I rename column in laravel using migration? There is already an object named in the database How to list all databases in the mongo shell? Failed to connect to mysql at with user root access denied for user 'root'@'localhost'(using password:YES) Partial Dependency (Databases) Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given... what I do wrong? ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend? Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL Warning: mysqli_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES) The backend version is not supported to design database diagrams or tables Difference between partition key, composite key and clustering key in Cassandra? Backup/Restore a dockerized PostgreSQL database Can't connect to MySQL server on '' (10061) (2003) copy from one database to another using oracle sql developer - connection failed Best way to check for "empty or null value" ORA-12528: TNS Listener: all appropriate instances are blocking new connections. Instance "CLRExtProc", status UNKNOWN getting error HTTP Status 405 - HTTP method GET is not supported by this URL but not used `get` ever? How do I to insert data into an SQL table using C# as well as implement an upload function? Postgres - Transpose Rows to Columns How to update multiple columns in single update statement in DB2 How to increase MySQL connections(max_connections)? What are OLTP and OLAP. What is the difference between them? The ALTER TABLE statement conflicted with the FOREIGN KEY constraint Reset identity seed after deleting records in SQL Server How can I get all sequences in an Oracle database?

Questions with data-warehouse tag:

Difference between Fact table and Dimension table? What is the difference between a database and a data warehouse?

Questions with business-intelligence tag:

Difference between Fact table and Dimension table? Installation of SQL Server Business Intelligence Development Studio How to change pivot table data source in Excel?

Questions with fact-table tag:

Difference between Fact table and Dimension table?