{"id":467,"date":"2024-02-20T13:46:48","date_gmt":"2024-02-20T12:46:48","guid":{"rendered":"https:\/\/bitwise.exposed\/?p=467"},"modified":"2026-03-16T09:38:47","modified_gmt":"2026-03-16T08:38:47","slug":"populate-a-fact-table-with-surrogate-keys-from-dimensions","status":"publish","type":"post","link":"https:\/\/bitwise.exposed\/index.php\/2024\/02\/20\/populate-a-fact-table-with-surrogate-keys-from-dimensions\/","title":{"rendered":"Populate a fact table with surrogate keys from dimensions"},"content":{"rendered":"\n

Populating a fact table with surrogate keys<\/a> from dimension tables involves connecting the dimension tables to the fact table through foreign key relationships and inserting the corresponding surrogate keys into the fact table.<\/p>\n\n\n\n

Let’s start with some constraints:<\/p>\n\n\n\n

SQL<\/span><\/path><\/path><\/svg><\/span>
CREATE<\/span> <\/span>TABLE<\/span> <\/span>Dim<\/span>.Customer (<\/span><\/span>\n    DimCustomer_SK <\/span>INT<\/span> <\/span>IDENTITY<\/span>(<\/span>1<\/span>,<\/span>1<\/span>) <\/span>NOT NULL<\/span> <\/span>PRIMARY KEY<\/span>,<\/span><\/span>\n    DimCustomer_BK <\/span>INT<\/span> <\/span>NOT NULL<\/span> <\/span>UNIQUE<\/span>, <\/span>-- e.g. CustomerNumber<\/span><\/span>\n    CustomerName <\/span>VARCHAR<\/span>(<\/span>255<\/span>)<\/span><\/span>\n);<\/span><\/span>\n<\/span>\n"""<\/span><\/span>\nNotes about: UNIQUE<\/span><\/span>\n<\/span>\nEnforcing a UNIQUE constraint on our BusinessKey (=BK) not only to ensure that we have a unique key.<\/span><\/span>\n<\/span>\nThe SQL engine will also create a unique index, this can speed up queries.<\/span><\/span>\n<\/span>\nI omit that for our SurrogateKey (=SK), assuming that this will be an auto-increment field, then it is implicitly unique, because auto-increment fields generate a unique value for every record. In such a case, explictly addind UNIQUE would be redundant.<\/span><\/span>\n"""<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Reinforce the relationships between the tables:<\/p>\n\n\n\n
SQL<\/span><\/path><\/path><\/svg><\/span>
CREATE<\/span> <\/span>TABLE<\/span> <\/span>Dim<\/span>.Customer (<\/span><\/span>\n    DimCustomer_SK <\/span>INT<\/span> <\/span>IDENTITY<\/span>(<\/span>1<\/span>,<\/span>1<\/span>) <\/span>NOT NULL<\/span> <\/span>PRIMARY KEY<\/span>,<\/span><\/span>\n    DimCustomer_BK <\/span>INT<\/span> <\/span>NOT NULL<\/span> <\/span>UNIQUE<\/span>, <\/span>-- e.g. CustomerNumber<\/span><\/span>\n    CustomerName <\/span>VARCHAR<\/span>(<\/span>255<\/span>)<\/span><\/span>\n);<\/span><\/span>\n<\/span>\nCREATE<\/span> <\/span>TABLE<\/span> <\/span>Fact<\/span>.Sales (<\/span><\/span>\n    DimCustomer_SK <\/span>INT<\/span> <\/span>REFERENCES<\/span> Dim.Customer(DimCustomer_SK),<\/span><\/span>\n    DimCustomer_BK <\/span>INT<\/span>, <\/span>-- e.g. CustomerNumber<\/span><\/span>\n    Amount <\/span>DECIMAL<\/span>,<\/span><\/span>\n    Quantity <\/span>INT<\/span><\/span>\n);<\/span><\/span>\n<\/span>\n"""<\/span><\/span>\nDimCustomer_SK INT REFERENCES Dim.Customer(DimCustomer_SK) <\/span><\/span>\n<\/span>\nenforces that the values in Fact.Sales.DimCustomer_SK must match values in Dim.Customer.DimCustomer_SK. <\/span><\/span>\n<\/span>\nThis means that for any value entered in Fact.Sales.DimCustomer_SK, there must be a corresponding value in the Dim.Customer.DimCustomer_SK.<\/span><\/span>\n<\/span>\nThis is used for ensuring validity of the data in the Fact.Sales.DimCustomer_SK column as it guarantees that an actual corresponding record exists in the Dim.Customer table. <\/span><\/span>\n<\/span>\nIf you try to enter a DimCustomer_SK in Fact.Sales that does not exist in Dim.Customer.DimCustomer_SK, the DBMS would throw an error.<\/span><\/span>\n"""<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Load DimTable:<\/p>\n\n\n\n
SQL<\/span><\/path><\/path><\/svg><\/span>
INSERT INTO<\/span> Dim.Customer (DimCustomer_BK, CustomerName)<\/span><\/span>\nSELECT<\/span> <\/span><\/span>\n    CI.CustomerNumber,   <\/span>-- Assuming CustomerNumber is unique <\/span><\/span>\n    CI.CustomerName      <\/span><\/span>\nFROM<\/span> <\/span><\/span>\n    CustomerInfo CI      <\/span>-- Table from source system<\/span><\/span>\nWHERE<\/span> <\/span><\/span>\n    <\/span>NOT<\/span> <\/span>EXISTS<\/span> (         <\/span>-- Check for duplicate<\/span><\/span>\n        <\/span>SELECT<\/span> <\/span>1<\/span><\/span>\n        <\/span>FROM<\/span> Dim.Customer DC<\/span><\/span>\n        <\/span>WHERE<\/span> DC.DimCustomer_BK = CI.CustomerNumber<\/span><\/span>\n    );<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
Note:
NOT EXISTS<\/code> Subquery<\/strong>: This condition is crucial for dimension tables to avoid inserting duplicate entries. It checks if the customer already exists in the dimension table based on the business key (CustomerNumber<\/code>). If not, it proceeds with the insert. This helps maintain the uniqueness of entries in the dimension table.<\/p>\n\n\n\n
Load FactTable:<\/p>\n\n\n\n
SQL<\/span><\/path><\/path><\/svg><\/span>
INSERT INTO<\/span> Fact.Sales (DimCustomer_SK, DimCustomer_BK, Amount, Quantity)<\/span><\/span>\nSELECT<\/span> <\/span><\/span>\n    DC.DimCustomer_SK,   <\/span>-- Surrogate key from dimension table<\/span><\/span>\n    T.CustomerNumber,    <\/span>-- Business key from transactional data<\/span><\/span>\n    T.SaleAmount,        <\/span>-- Sale amount from transactional data<\/span><\/span>\n    T.SaleQuantity       <\/span>-- Sale quantity from transactional data<\/span><\/span>\nFROM<\/span> <\/span><\/span>\n    Transactions T<\/span><\/span>\nJOIN<\/span> <\/span><\/span>\n    Dim.Customer DC <\/span><\/span>\nON<\/span> <\/span><\/span>\n    T.CustomerNumber = DC.DimCustomer_BK;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"
Populating a fact table with surrogate keys from dimension tables involves connecting the dimension tables to the fact table through foreign key relationships and inserting the corresponding surrogate keys into the fact table. Let’s start with some constraints: Reinforce the relationships between the tables: Load DimTable: Note:NOT EXISTS Subquery: This condition is crucial for dimension […]<\/p>\n","protected":false},"author":1,"featured_media":476,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-467","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-allgemein"],"_links":{"self":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/467","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/comments?post=467"}],"version-history":[{"count":11,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/467\/revisions"}],"predecessor-version":[{"id":632,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/467\/revisions\/632"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media\/476"}],"wp:attachment":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media?parent=467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/categories?post=467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/tags?post=467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}