{"id":492,"date":"2024-11-10T09:27:13","date_gmt":"2024-11-10T09:27:13","guid":{"rendered":"https:\/\/datadandies.nl\/?p=492"},"modified":"2024-11-10T09:27:13","modified_gmt":"2024-11-10T09:27:13","slug":"3-types-of-fact-tables","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2024\/11\/10\/3-types-of-fact-tables\/","title":{"rendered":"3 Types of fact tables"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">I am rereading The Data Warehouse Toolkit by Kimball and Ross and would like to share something interesting concerning fact tables.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">According to the book, there are 3 main types of fact tables:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Transaction<\/li>\n\n\n\n<li>Periodic snapshot<\/li>\n\n\n\n<li>Accumulating snapshot<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">A transaction fact table inserts a new record for every transaction that takes place. An obvious example would be a sales fact table: for every sale, a new record is inserted in a table.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"268\" src=\"https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-2-1024x268.png\" alt=\"\" class=\"wp-image-493\" srcset=\"https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-2-1024x268.png 1024w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-2-300x79.png 300w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-2-768x201.png 768w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-2.png 1152w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">A periodic snapshot fact table inserts a new record with the most recent state at regular intervals. A classic example is a daily inventory snapshot fact table: everyday new records are inserted with the amount of product that is present in the inventory that day.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"228\" src=\"https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-3-1024x228.png\" alt=\"\" class=\"wp-image-494\" srcset=\"https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-3-1024x228.png 1024w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-3-300x67.png 300w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-3-768x171.png 768w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-3.png 1279w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">An accumulating snapshot fact table updates existing records according to changes in status of the record. An example of this could be an order fulfillment fact table. Whenever an order is placed, a new record is inserted with the \u201cOrderDate\u201d column filled with the order date. The \u201cShippedDate\u201d column is left empty, or filled with a default surrogate key like 99991231.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"195\" src=\"https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-4-1024x195.png\" alt=\"\" class=\"wp-image-495\" srcset=\"https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-4-1024x195.png 1024w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-4-300x57.png 300w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-4-768x146.png 768w, https:\/\/datadandies.nl\/wp-content\/uploads\/2024\/11\/image-4.png 1405w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">A lot of times, a single type of fact table does not suffice for the information needs from the business. Understanding the different types of fact tables could assist you in helping your stakeholders get the information they need.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am rereading The Data Warehouse Toolkit by Kimball and Ross and would like to share something interesting concerning fact tables. According to the book, there are 3 main types of fact tables: A transaction fact table inserts a new record for every transaction that takes place. An obvious example would be a sales fact&hellip;<\/p>\n<p class=\"more-link\"><a href=\"https:\/\/datadandies.nl\/index.php\/2024\/11\/10\/3-types-of-fact-tables\/\" class=\"themebutton\">Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[16,10,4],"class_list":["post-492","post","type-post","status-publish","format-standard","hentry","category-blog","tag-datamodeling","tag-dwh","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=492"}],"version-history":[{"count":1,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/492\/revisions"}],"predecessor-version":[{"id":496,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/492\/revisions\/496"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}