How to join dimension tables while populating a fact table if business key of one dimension table is not in the other joining table?

Question

I have an OLTP database having some tables. I want to build a DW star schema from this database. I extracted three tables--Customer, Store, SalesPerson--from source to staging area and put a surrogate key as primary key in all three tables. These three tables also have respective primary keys (business keys) from OLTP but don't have relationship with each other through foreign keys. After that I populated dimension tables from staging area. Now i want to populate fact table from these dimension tables. The strategy is that I have to join dimension tables based on their business keys and put surrogate key of each dimension to fact table along with some fact values. But the problem is that the dimensions don't have business keys on which I could join them. And if I join dimensions on surrogate keys then how to keep track to the OLTP in case of SCD type 2? enter image description here

The image is attached. This is the source OLTP. I want Customers, Products, Offices and Employees as dimension tables and "Price each","quantity ordered" as facts. Can you please how can I build a data warehouse from these mentioned tables and fact. Thank you

Answer 1

Say your source OLTP system has a sale record that looks like this:

customer_id	product_id	sale_date	amount
ABCD	X1234	2022-07-26	13.67

In your data warehouse you have 3 dimension tables:

Customer_dim:

customer_sk	customer_bk
9876	ABCD

Product_dim:

product_sk	product_bk
5432	X1234

Date_dim:

date_sk	date_bk
20220726	2022-07-26

From your source record, you take each relevant column, join it to the BK in the relevant dimension table to look up the corresponding SK and then write the record to your fact table. So your fact table would look like this:

customer_sk	product_sk	date_sk	amount
9876	5432	20220726	13.67