I am not a big user of DI Studio, but find myself in a scenario where I need to use the SQL Merge Transformation, to update and insert records in an SCD2 Oracle Table. I have worked my way through the user guide and have really not found much information on this transformation. I have read this article http://databobjr.blogspot.com/2011/04/load-slowly-changing-dimension-type-2.html, which has given me a lot of insight into how the code should be structured, but having taken a number of stabs at replicating the logic in DI, I have failed miserably.
I have searched Google (of course), but I am yet to find anything that addresses this topic at length. Does anyone have good resources around how to use this transformation at all?
Thanks in advance for any assistance offered.
Edit: This is what I have been able to come up with so far. It appears the problem I am having is getting the subqueries to nest correctly. This is the code I have generated so far:
MERGE INTO dim_product p USING ( SELECT DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid, PRODUCT_ID, PRODUCT_CATEGORY_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRICE, PRICE_EFFECTIVE_DATE, LAST_MODIFIED_DATE, CREATED_DATE, m.scd_row_type_id FROM (SELECT dp.product_sid, sp.PRODUCT_ID, sp.PRODUCT_CATEGORY_ID, sp.PRODUCT_NAME, sp.PRODUCT_DESCRIPTION, sp.PRICE, sp.PRICE_EFFECTIVE_DATE, sp.LAST_MODIFIED_DATE, sp.CREATED_DATE, CASE WHEN dp.product_id IS NULL THEN 1 WHEN (dp.product_category_id != sp.product_category_id OR dp.product_name != sp.product_name OR DP.PRODUCT_DESCRIPTION != sp.product_description OR dp.price != sp.price OR dp.price_effective_date != sp.price_effective_date) THEN 2 ELSE 0 END AS scd_row_type_id FROM stg_product sp LEFT JOIN Dim_product dp ON (sp.product_id = dp.product_id and dp.is_current_row = 'Y') ) m JOIN scd_row_type s ON (s.scd_row_type_id <= m.scd_row_type_id) ) mp ON (p.product_sid = mp.product_sid) when matched then update set P.EFFECTIVE_END_DATE = mp.LAST_MODIFIED_DATE, is_current_row = 'N', updated_date = sysdate commit;
I haven't used the Merge transformation that much, but it's quite straight forward - but it's' linked to the syntax of SQL MERGE statement, so you need to know what you want to do.
Why do you have to use the Merge transformation?
if you are updating a SCD Type 2 table, there's a transformation for that...
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.