0

I have populated my dimension tables (Oracle SQL Dev.) For the next step I created the facts table (F_Orders), I loaded quantity, price , the Order_ID's and surrogate keys into the facts table.

I want to Insert the latest Surrogate_ID's (customer, location, etc.) with the specific record. In SA_ORDERS I can see which Order_ID is connected with which Customer_ID. Using Customer_ID in D_CUSTOMERS I can find the Surrogate key.

To perform this task, I used this query:

INSERT INTO F_Orders 
(order_id
,quanity
,price
,cust_s_key)
       
SELECT
sa_order_items.order_id
,sa_order_items.quantity
,sa_order_items.unit_price
,d_customers.s_key
FROM
sa_order_items
JOIN sa_orders ON sa_order_items.order_id = sa_orders.order_id
JOIN d_customers ONd_customers.customer_id=sa_orders.customer_id
WHERE 
d_customers."Latest" = 'Y'
AND d_customers.flag = 'I'

Now I was wondering, can I perform update for Surrogate key (cust_s_key) as I have 2 joints to use? Unfortunately, I cant combine update with the join successfully. How could I combine both or may find another way to do it? Thanks a lot in advance. PS. I know that in the ETL we won't need this update often.

Deans
  • 3
  • 2
  • Please [edit] your question to include a [MRE] with: the `CREATE TABLE` statements for a minimal example; the `INSERT` statements for some sample data; your expected output; and an explanation of which columns you are talking about. – MT0 Sep 07 '21 at 12:23
  • Dear MT0, I tried my best to add language identifier and edit the text. Please let me know if I can do more. – Deans Sep 07 '21 at 13:19
  • 1
    Quick question, Why are you updating your fact table after loading it? are you not supposed to load the latest surrogate keys as part of the load itself once the dimensions are loaded? – Sunil Kumar Sep 07 '21 at 14:22
  • Hi Sunil, thank you very much for your quick answer. Yes absolutely correct, I do it this way. Sorry for the confusion, I changed the post. I was just wondering If I Initlitay didn't bring all surrogate keys, can I still update later even I have 2 joints? – Deans Sep 08 '21 at 08:17
  • Hi - what is/are the reason(s) for running updates? Because the customer wasn’t known when the fact record was created; because the fact was associated to the wrong customer; because the wrong record for the correct customer was used (customer records held as SCD)? – NickW Sep 08 '21 at 21:54
  • Hi Nick, it was just a request if it's possible to do that. Normally the record should stay untouched after it was inserted correctly. Thank you to everyone who helped to solve my question – Deans Sep 10 '21 at 08:42

1 Answers1

0

we can update the fact table using the same query

UPDATE F_Orders fo SET fo.cust_s_key = (SELECT
distinct d_customers.s_key
FROM
sa_order_items
JOIN sa_orders ON sa_order_items.order_id = sa_orders.order_id
JOIN d_customers ONd_customers.customer_id=sa_orders.customer_id
WHERE 
d_customers."Latest" = 'Y'
AND d_customers.flag = 'I'
AND sa_order_items.order_id = fo.order_id)
WHERE EXISTS (SELECT 1 FROM
sa_order_items
JOIN sa_orders ON sa_order_items.order_id = sa_orders.order_id
JOIN d_customers ONd_customers.customer_id=sa_orders.customer_id
WHERE 
d_customers."Latest" = 'Y'
AND d_customers.flag = 'I'
AND sa_order_items.order_id = fo.order_id)
Ramana
  • 36
  • 3