{"id":325,"date":"2012-01-10T05:29:00","date_gmt":"2012-01-10T05:29:00","guid":{"rendered":"http:\/\/etlguru.com\/blog\/?p=325"},"modified":"2012-01-12T00:20:09","modified_gmt":"2012-01-12T00:20:09","slug":"type-2ii-dimension-or-a-fake-fact-table","status":"publish","type":"post","link":"https:\/\/etlguru.com\/?p=325","title":{"rendered":"Type II Dimension or a Fake Fact Table!"},"content":{"rendered":"

You are sales manager of North-East region in 2010 to Oct 23 2011 and did total sales of 1 million. The table that stores your information looks like\u00e2\u20ac\u00a6.
\nDim Sales Person<\/strong>
\nSales Person Key =99
\nSales Person Name = Tony Peterson
\nSales Person Designation = Sales Manager
\nSales Region = North-East
\nPhone = 203 203 9999
\nDate Of Birth = 9\/9\/1977<\/p>\n

Sales fact<\/strong>
\nTransaction Date Key
\nSales Person Key
\nProduct Key
\nProduct Qty
\nProduct $Amount<\/p>\n

As of Oct 23 management asks for the sales amount by sales person and region. The result would look like: Tony Peterson, North-East, and 1 Million.<\/strong>
\nFrom 24th Oct 2012 there is change and you have been transferred to South-East region. So some IT guy updated the sales region in Sales Person table to South-East.
\nSales Person<\/strong>
\nSales Person Key =99
\nSales Person Name = Tony Peterson
\nSales Person Designation = Sales Manager
\nSales Region = South-East
\nPhone = 203 203 9999
\nDate Of Birth = 9\/9\/1977<\/p>\n

As you do your sales next few month you did a sales of .4 million in South-East. On Jan 2012 management again asked for the sales figure by employee and region. What will the report look like?
\nTony Peterson, South-East, and 1.4 Million.<\/strong>
\nIs the report correct? The answer is obvious total sales figure is incorrect for South-East as it will show 1.4 millions. You only did .4 million in the South-East and not 1.4. The reason it is incorrect is due to the fact that somebody updated the dimension record with the current region. <\/p>\n

Now that we know the problem and exact cause of it, we need to look for a solution. By this time most you will say \u00e2\u20ac\u00a6this is no big deal it\u00e2\u20ac\u2122s a classic issue of slowly changing dimension. Let me create a type II dimension\u00e2\u20ac\u00a6 So I can preserve history of transfer.
\nDim Sales Person II<\/strong>
\nSales Person Key =99, 100
\nSales Person SSN=000-00-9999, => 000-00-9999
\nSales Person Name = Tony Peterson, => Tony Peterson,
\nSales Person Designation= Sales Manager, => Sales Manager
\nSales Region = North-East, => South-East
\nPhone = 203 203 9999,=> 203 203 9999
\nDate Of Birth = 9\/9\/1977, => 9\/9\/1977<\/p>\n

Now since there are two records for same sales person you can join with sales facts and you can separate the regions. The correct facts are as follows.
\nTony Peterson, North-East, and 1.0 Million
\nTony Peterson, South-East, and 0.4 Million
\n<\/strong>
\nObserve the Type II dimension carefully. You will note that
\n–\tThe key identifier (PK) for the Sales person will keep changing. Note it changed from 99 to 100.
\n–\tInstead of having 1 one row per salesman now it has two or even more with time.
\n–\tThe Sales Region is time variant fact about the sales person
\n–\tWe are indeed storing time variant facts in type II dimension.
\nSo Type II dimension is really more of a fact table. So is storing time variants facts a good design at all? Why not store the facts in a fact table? Read other articles on pitfalls of Type II dimension.<\/p>\n","protected":false},"excerpt":{"rendered":"

You are sales manager of North-East region in 2010 to Oct 23 2011 and did total sales of 1 million. The table that stores your information looks like\u00e2\u20ac\u00a6. Dim Sales Person Sales Person Key =99 Sales Person Name = Tony Peterson Sales Person Designation = Sales Manager Sales Region = North-East Phone = 203 203 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-325","post","type-post","status-publish","format-standard","hentry","category-data-modeling"],"_links":{"self":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts\/325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=325"}],"version-history":[{"count":2,"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts\/325\/revisions"}],"predecessor-version":[{"id":328,"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts\/325\/revisions\/328"}],"wp:attachment":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}