Difference between Fact table and Dimension table?
Asked Answered
Q

11

197

What is the difference between fact tables and dimension tables? An example could be very helpful.

Quamash answered 17/11, 2013 at 22:16 Comment(3)
The concept is rather long to describe in good detail, if you have a specific problem beyond the basic definition please tell us about it.Chalcis
Basically, I was trying to understand whether dimension tables can be fact table as well or not?Quamash
edureka.co/blog/dimension-table-in-data-warehousingAdduce
C
62

This is to answer the part:

I was trying to understand whether dimension tables can be fact table as well or not?

The short answer (INMO) is No.That is because the 2 types of tables are created for different reasons. However, from a database design perspective, a dimension table could have a parent table as the case with the fact table which always has a dimension table (or more) as a parent. Also, fact tables may be aggregated, whereas Dimension tables are not aggregated. Another reason is that fact tables are not supposed to be updated in place whereas Dimension tables could be updated in place in some cases.

More details:

Fact and dimension tables appear in a what is commonly known as a Star Schema. A primary purpose of star schema is to simplify a complex normalized set of tables and consolidate data (possibly from different systems) into one database structure that can be queried in a very efficient way.

On its simplest form, it contains a fact table (Example: StoreSales) and a one or more dimension tables. Each Dimension entry has 0,1 or more fact tables associated with it (Example of dimension tables: Geography, Item, Supplier, Customer, Time, etc.). It would be valid also for the dimension to have a parent, in which case the model is of type "Snow Flake". However, designers attempt to avoid this kind of design since it causes more joins that slow performance. In the example of StoreSales, The Geography dimension could be composed of the columns (GeoID, ContenentName, CountryName, StateProvName, CityName, StartDate, EndDate)

In a Snow Flakes model, you could have 2 normalized tables for Geo information, namely: Content Table, Country Table.

You can find plenty of examples on Star Schema. Also, check this out to see an alternative view on the star schema model Inmon vs. Kimball. Kimbal has a good forum you may also want to check out here: Kimball Forum.

Edit: To answer comment about examples for 4NF:

  • Example for a fact table violating 4NF:

Sales Fact (ID, BranchID, SalesPersonID, ItemID, Amount, TimeID)

  • Example for a fact table not violating 4NF:

AggregatedSales (BranchID, TotalAmount)

Here the relation is in 4NF

The last example is rather uncommon.

Chalcis answered 17/11, 2013 at 23:36 Comment(1)
Some fact tables reflect transaction level data. Some reflect aggregated data. A fact table in a Star Schema does not have to be even in 3NF. For example Sales Fact could contain data such as (ID, BranchID, Amount, SalesPerson, Time) - This violates 3NF, BCNF and 4NF because the SalesPerson and Branch dependency.Accordingly, it is not true that a typical fact table is in 4NF.Chalcis
S
468

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

Stoup answered 17/11, 2015 at 6:32 Comment(5)
Dimensions look relatively large as compared to fact table in the diagram as it has more descriptive data. And they are more in numbers tooBarrera
@Blue Clouds: You have to realize that the Fact-Table contains one entry for every possible combination of the for dim´s (if there is data, at least). While the Location Dimension will at max contain one entry for every possible location (say 50 points-of-sale) and will grow rarely, when new pos are added, the Facts table will probably grow for each day by location x items x branches. So, the facts will get large in number of records pretty fast.Spies
@Stoup Can fact table exist without primary key?Byproduct
@Kalana, Yes a fact table can exists without a primary key. For example, a sales table with columns, cust_id, date_ordered, qty, time, location can have all records same if he/she ordered in the same day / same location / same qty twice.Pleiad
Good explanation of concept. Can you give some database scenarios where we use this Star schema(fact/dim tables).Carnatic
G
163

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/

Glossal answered 6/8, 2015 at 17:46 Comment(2)
To summarise: dimensions are attributes of fact-events. DAFE. What are you doing, DAFE?Someplace
Yes, that's how I remember them. It's the reverse of what you'd think. You'd think facts are set in stone and dimensions are dynamic, based on the words themselves. But, it's the opposite: a basic dim table is a fairly static lookup list, and a basic fact table is living data that's being entered.Mudlark
C
62

This is to answer the part:

I was trying to understand whether dimension tables can be fact table as well or not?

The short answer (INMO) is No.That is because the 2 types of tables are created for different reasons. However, from a database design perspective, a dimension table could have a parent table as the case with the fact table which always has a dimension table (or more) as a parent. Also, fact tables may be aggregated, whereas Dimension tables are not aggregated. Another reason is that fact tables are not supposed to be updated in place whereas Dimension tables could be updated in place in some cases.

More details:

Fact and dimension tables appear in a what is commonly known as a Star Schema. A primary purpose of star schema is to simplify a complex normalized set of tables and consolidate data (possibly from different systems) into one database structure that can be queried in a very efficient way.

On its simplest form, it contains a fact table (Example: StoreSales) and a one or more dimension tables. Each Dimension entry has 0,1 or more fact tables associated with it (Example of dimension tables: Geography, Item, Supplier, Customer, Time, etc.). It would be valid also for the dimension to have a parent, in which case the model is of type "Snow Flake". However, designers attempt to avoid this kind of design since it causes more joins that slow performance. In the example of StoreSales, The Geography dimension could be composed of the columns (GeoID, ContenentName, CountryName, StateProvName, CityName, StartDate, EndDate)

In a Snow Flakes model, you could have 2 normalized tables for Geo information, namely: Content Table, Country Table.

You can find plenty of examples on Star Schema. Also, check this out to see an alternative view on the star schema model Inmon vs. Kimball. Kimbal has a good forum you may also want to check out here: Kimball Forum.

Edit: To answer comment about examples for 4NF:

  • Example for a fact table violating 4NF:

Sales Fact (ID, BranchID, SalesPersonID, ItemID, Amount, TimeID)

  • Example for a fact table not violating 4NF:

AggregatedSales (BranchID, TotalAmount)

Here the relation is in 4NF

The last example is rather uncommon.

Chalcis answered 17/11, 2013 at 23:36 Comment(1)
Some fact tables reflect transaction level data. Some reflect aggregated data. A fact table in a Star Schema does not have to be even in 3NF. For example Sales Fact could contain data such as (ID, BranchID, Amount, SalesPerson, Time) - This violates 3NF, BCNF and 4NF because the SalesPerson and Branch dependency.Accordingly, it is not true that a typical fact table is in 4NF.Chalcis
A
14

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.

Astonied answered 8/2, 2019 at 20:28 Comment(0)
N
10
  • a Fact = an action: a sale, a transaction, an access
  • a Dimension = an object: a seller, a customer, a date, a price

Then...

  • Facts references dimensions for: when, where, what, who, how

The real interesting thing is deciding whether an attribute should be a dimension or a fact. For example, the price of each item in an order, or, the maximum amount of a insurance recorded in a contract. There are no generally correct way to approach these, only ones that make sense in the context.

PS: If I were to create those jargons I would prefer Log table and Object table.

Nevski answered 21/3, 2022 at 8:36 Comment(3)
I would rather say that a fact is a "measurement". Of course, that could be measuring the result of an action (probably often is), but it doesn't seem to really hit the nail to say it is an action.Alphonsa
@SamuelLampa This is probably a bit metaphysical, but take this example. I consider a transaction an action, and in a table named "Transactions", I would expect it to store data about an action (amount, date, etc.). The result of the action (e.g. added amount in one account and subtracted in another), is not captured by the table. That being said, technically, the table stores not actions, but the data that describe actions, but I think this is metaphysical and does not help understanding the differences.Nevski
Ok, I see your point. Well, I was thinking that facts could also be measurements of other kinds (say, the current weather). That is, that they might not always need to represent actions per se, but I think I can see how in the majority of cases, actions might be a good way to think about it.Alphonsa
V
8

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

Volturno answered 5/12, 2019 at 10:18 Comment(0)
P
5

From my point of view,

  • Dimension table : Master Data
  • Fact table : Transactional Data
Pontifex answered 31/12, 2020 at 6:51 Comment(1)
I have used "master data" and "transactional data" for years as well, and I think that they are linguistically more evident regarding what the words entail.Heavily
W
4

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.

Wehrle answered 30/12, 2015 at 5:19 Comment(0)
F
3
  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

Florist answered 19/2, 2015 at 11:32 Comment(0)
H
0

For Relation database users, Dimension is equivalent to Master Table. Fact is equivalent to Transaction table.

Hod answered 16/3, 2022 at 6:37 Comment(1)
As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.Burford
M
-3

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

Mitch answered 15/6, 2018 at 7:45 Comment(1)
(This post does not seem to provide a quality answer to the question. Please either edit your answer, or just post it as a comment to the question).Bride

© 2022 - 2024 — McMap. All rights reserved.