databasedata-warehousebusiness-intelligencefact-table

Difference between Fact table and Dimension table?

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

Solution

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

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

    AggregatedSales (BranchID, TotalAmount)

    Here the relation is in 4NF

    The last example is rather uncommon.