Procedure to refresh Analytics DWH when Product Names change

Update SLXRE_Readgroup_Metadata

MERGE INTO slxre_readgroup_metadata m
USING mercurydw.product p
ON (m.product_part_number = p.part_number 
AND p.part_number  IN (<list of Part Numbers>)
)
WHEN MATCHED THEN UPDATE SET
m.product = p.product_name

HQS Family

MERGE INTO slxre2_rghqs m
USING mercurydw.product p
ON (m.product_part_number = p.part_number 
AND p.part_number = 'P-WG-0047')
WHEN MATCHED THEN UPDATE SET
m.product = p.product_name
;

MERGE INTO slxre2_lane_hqs m
USING mercurydw.product p
ON (m.product_part_number = p.part_number 
AND p.part_number = 'P-WG-0047')
WHEN MATCHED THEN UPDATE SET
m.product = p.product_name
;

-- Run HQS does not have PDO metadata, no need to refresh

Aggregation DMs

MERGE INTO slxre2_pagg_library m
USING mercurydw.product p
ON (m.product_part_number = p.part_number 
AND p.part_number = 'P-WG-0047')
WHEN MATCHED THEN UPDATE SET
m.product = p.product_name
;
-- Update Sample Agg for those with a single product
MERGE INTO slxre2_pagg_sample m
USING mercurydw.product p
ON (m.product_part_number = p.part_number 
AND p.part_number = 'P-WG-0047')
WHEN MATCHED THEN UPDATE SET
m.product = p.product_name
;
-- Update Sample Agg for those with combination of products:
--1. get a list of samples
SELECT DISTINCT '"'||a.SAMPLE||'",'
FROM slxre2_pagg_sample a 
WHERE  instr(a.product_part_number , ',') >0 AND instr(a.product_part_number, 'P-WG-0047')>0
;
--2. Manual Agg ETL with the list of samples from the above query (make sure to remove the comma after the last sample in the list
CognosDB("analytics.tiger.agents.PicardAggregator.Manual", deltaETL(Set("NWD804135","NWD410904","NWD196533","NWD634014","NWD721067","NWD323914","NWD782097","NWD840180",...)) flatMap DataSetEtl() flatMap analytics.tiger.agents.PicardAggregator.Regular.pipeline)