...

ETL troubleshooting recipes

Issue: ¿epatienting Repatienting (rename externalID well after work on sample has started)

...

Remedy: Refresh Sample_Attributes datamart. In GAP_PROD.ANALYTICS schema issue SQL statement like this:

INSERT INTO sample_attr_special_refreshes

SELECT 'SM-' || a.sample_id, SYSDATE 

FROM sample_attributes a

WHERE a.collaborator_sample_id IN ('old_externalID_1','old_externalID_2')

Issue: When upstream data change, some samples in PDO_STAR5 might need to be refreshed.

System: GAP

Remedy: Request a refresh for PdoStar5Agent by issuing SQL like this in SEQPROD.COGNOS:

INSERT INTO pdo_star_special_refreshes VALUES('PDO-1', 'SM-12345', sysdate)

Issue: When upstream data change, some samples in ReadgroupMetadata might need to be refreshed.

Remedy: Request a refresh for RunMercuryAgent by issuing SQL like this in SEQPROD.COGNOS:

call cognos.data_warehouse_api.request_refresh(P_FLOWCELL_BARCODE=>'XYZ', P_REQUESTED_BY=>'JohnDoe', P_COMMENTS=>'Test', P_DEFERRAL_ANALYSIS_DATE=>null)