DIMENSION TABLE HOW TO

A dimension table contains a surrogate key, natural key, and a set of attributes.A dimension table consists mainly of descriptive attributes that are textual fields. The fact table mainly consists of business facts and foreign keys that refer to primary keys in the dimension tables.Example for a fact table not violating 4NF:.Sales Fact (ID, BranchID, SalesPersonID, ItemID, Amount, TimeID) Example for a fact table violating 4NF:.Kimbal has a good forum you may also want to check out here: Kimball Forum.Įdit: To answer comment about examples for 4NF: Also, check this out to see an alternative view on the star schema model Inmon vs. You can find plenty of examples on Star Schema. In a Snow Flakes model, you could have 2 normalized tables for Geo information, namely: Content Table, Country Table. In the example of StoreSales, The Geography dimension could be composed of the columns (GeoID, ContenentName, CountryName, StateProvName, CityName, StartDate, EndDate) However, designers attempt to avoid this kind of design since it causes more joins that slow performance. It would be valid also for the dimension to have a parent, in which case the model is of type 'Snow Flake'. Each Dimension entry has 0,1 or more fact tables associated with it (Example of dimension tables: Geography, Item, Supplier, Customer, Time, etc.). On its simplest form, it contains a fact table (Example: StoreSales) and a one or more dimension tables. 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. 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.įact and dimension tables appear in a what is commonly known as a Star Schema. Also, fact tables may be aggregated, whereas Dimension tables are not aggregated. 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. The short answer (INMO) is No.That is because the 2 types of tables are created for different reasons. I was trying to understand whether dimension tables can be fact table The quantity sold, the price per item, total price, and so on. In addition fact tables also typically have some kind of quantitativeĭata. Product, Employee, and Customer are all dimensions Noted by what product was sold, which employee sold it, and whichĬustomer bought it. That happens to something from the dimension table. An entry in a fact table marks a discrete event Products are sold, they are also dimensions as they have somethingįacts, are the verb. A dimension either doesĮmployees sell, customers buy. Products, employees,Įquipment, are all things that exist.

Independent of a business event, such as a sale. A thing suchĪs a product can exist without ever being involved in a businessĮvent. It may help to think of dimensions as things or objects.

DIMENSION TABLE HOW TO

This appears to be a very simple answer on how to differentiate between fact and dimension tables!