Difference between Fact table and Dimension table

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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      image source

User · Answer

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  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  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  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  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

User · Answer

From my point of view   Dimension table   Master Data Fact table   Transactional Data

User · Answer

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

User · Answer

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

[database] Difference between Fact table and Dimension table?

Examples related to database

Examples related to data-warehouse

Examples related to business-intelligence

Examples related to fact-table