14

When reading a book for business objects, I came across the term- fact table and dimension table. Is this the standard thing for all the database that they all have fact table and dimension table or is it just for business object design? I am looking for an explanation which differentiates between two and how they are related.

Edited:

Why cannot a query just get the required data from the fact table? What happens if all the information are stored in one fact table alone? What advantages we get by creating a separate fact and dimension table and joining it?

Sorry for too many questions at a time but I would like to know about the inter-relations and whys.

Tommi
  • 8,550
  • 5
  • 32
  • 51
Jack_of_All_Trades
  • 10,942
  • 18
  • 58
  • 88
  • See also https://stackoverflow.com/questions/20036905/difference-between-fact-table-and-dimension-table – Rory Mar 22 '22 at 19:14

2 Answers2

11

Dimension and Fact are key terms in OLAP database design.

  • Fact table contains data that can be aggregate.
  • Measures are aggregated data expressions (e. Sum of costs, Count of calls, ...)
  • Dimension contains data that is use to generate groups and filters.
  • Fact table without dimension data is useless. A sample: "the sum of orders is 1M" is not information but "the sum of orders from 2005 to 2009" it is.

They are a lot of BI tools that work with these concepts (e.g. Microsft SSAS, Tableau Software) and languages (e. MDX).

Some times is not easy to know if a data is a measure or a dimension. For example, we are analyzing revenue, both scenarios are possibles:

  • 3 measures: net profit , overheads , interest
  • 1 measure: profit and 1 dimension: profit type (with 3 elements: net, overhead, interest )

The BI analyst is who determines what is the best design for each solution.

EDITED due to the question also being edited:

An OLAP solution usually has a semantic layer. This layer provides to the OLAP tool information about: which elements are fact data, which elements are dimension data and the table relationships. Unlike OLTP systems, it is not required that an OLAP database is properly normalized. For this reason, you can take dimension data from several tables including fact tables. A dimension that takes data from a fact table is named Fact Dimension or Degenerate dimension.

They are a lot of concepts that you should keep in mind when designing OLAP databases: "STAR Schema", "SNOWFLAKE Schema", "Surrogate keys", "parent-child hierarchies", ...

Sohaib
  • 4,556
  • 8
  • 40
  • 68
dani herrera
  • 48,760
  • 8
  • 117
  • 177
1

That's a standard in a datawarehouse to have fact tables and dimension tables. A fact table contains the data that you are measuring, for instance what you are summing. A dimension table is a table containing data that you don't want to constantly repeat in the fact table, for example, product data, statuses, customers etc. They are related by keys: in a star schema, each row in the fact table contains a the key of a row in the dimension table.

stevie_c
  • 990
  • 1
  • 8
  • 15
  • Does this mean that the database designer should create both-fact table and dimension table separately when designing a database? – Jack_of_All_Trades Feb 20 '12 at 15:42
  • 3
    Yes, create them separately. If you have all your dimension data in the fact table then the fact table would be much larger than it needs to be. Although data warehouses should be de-normalised, you probably ought not to de-normalise it to such an extent that you're left with only one table. – stevie_c Feb 24 '12 at 13:37