Content Comparison

In some occasions, the analytics team may need to take specific actions to check or update changes in the datamarts. Examples in which this may happen may include the following:

...

1. Log in to REPORTING@SEQPROD on SQL Navigator and run the following query:

   Code Block
   language sql
   SELECT DISTINCT s.bsp_collaborator_sample_id, s.collaborator_participant_id, s.aggregation_project, s.last_aggregation_date, s.external_sample_id, s.pdo_name, s.pdo_sample_id, s.sample_delivery_status, s.sample_is_billed FROM cognos.pdo_star5 s WHERE s.bsp_collaborator_sample_id IN ('oldcollabSMID1','oldcollabSMID2')

   
   

2. If no records are returned, then no further action is needed (datamarts have already been refreshed)
3. If records are returned, then either there are supposed to be some samples remaining (this is less frequent) or datamarts need refreshing.
4. Before refreshing PDO STAR, first check to see if SLXRE_READGROUP_METADATA is updated (section 1.2 below).
5. If readgroup metadata is updated, then the PDO STAR datamart needs a refresh (see sections 2.1-2.3 below). Note the PDO_NAME of any records returned.

...

1. Log in to REPORTING@SEQPROD on SQL Navigator and run the following query:

   
   

   Code Block
   language sql
   SELECT DISTINCT a.collaborator_sample_id, a.sample_id bsp_sample_id, a.project, a.research_project_id, a.product_order_key, a.product_order_sample, a.collaborator_participant_id, a.flowcell_barcode, a.run_name FROM cognos.slxre_readgroup_metadata a WHERE a.collaborator_sample_id  IN ('oldcollabSMID1','oldcollabSMID2')

...

1. 
   

2. If no records are returned, then no further action is needed (datamarts have already been refreshed)

3. If records are returned, then the datamart needs a refresh (section 2.4 below). Note the FLOWCELL_BARCODE of any records returned.

1.3. Check whether PDO Interesting Products list is updated

...

Log in to COGNOS@SEQPROD on SQL Navigator and run the following query:

SELECT 	ip.product_part_number interesting_product, pdos.*
FROM 	cognos.mercurydw_pdo_samples pdos ,
		cognos.pdo_interesting_products ip
WHERE pdos.pdo_sample_id  IN (
	'SM-ID1',
	'SM-ID2',
	)
AND pdos.product_part_number = ip.product_part_number(+)

If the PRODUCT_FAMILY_NAME for one of these products is Whole Genome Genotyping, log in to REPORTING@SEQPROD on SQL Navigator and run the following query:

SELECT DISTINCT "Collab. sample ID",
                "PTID",
                "Collab. PTID",
                "PDO",
                "PDO sample ID"
FROM arrays_mercloud_w_unmarked_rw
WHERE "PDO sample ID" IN ('SM-ID1','SM-ID2')

...

     2.1.3. Check whether there's updated data to be added

1.  Check for metrics in DB with the following queries:

   Code Block
   language sql
   SELECT * FROM analytics.bsp_sample WHERE collaborator_sample_id IN 'oldcollabSMID1, oldcollabSMID2' ; SELECT * FROM metrics.basecalling_analysis ba WHERE ba.flowcell_barcode IN ('HTH3KBBXX') AND ba.metrics_type IN ('Indexed', 'Unindexed') ; SELECT * FROM metrics.picard_analysis ba WHERE ba.flowcell_barcode IN ('HTH3KBBXX') AND ba.metrics_type IN ('Indexed', 'Unindexed') ; --Understand when flowcells were updated in Analytics DM SELECT * FROM slxre_rghqs_targets t WHERE t.flowcell_barcode = --'HTH3KBBXX' ;

   
   

1.

...

4. Check aggregations

1.  Check for aggregations in DB with the following queries:

...

NOTE: is_latest should be 2 for anything with consent withdrawn (2 signifies BAM is deleted)

1.5.

...

Check whether Sample datamarts have been refreshed

1. If refreshes aren't showing updated data, it may be because the sample ETL is down. If LIMS team has done their part, but the bsp.analytics_sample and analytics.bsp_sample DMs are not updated, check the ETL agent here: http://analytics:8090/etl_runs?status=Succeeded&status=Failed&status=Running&status=Abandoned&agent=analytics.tiger.agents.BspSample&action=Refresh

...

1. Rerun the PDO_STAR query above. This refresh is immediate, so you can run the query right away.
2. If it returns nothing, the DM is all set.
3. If it still returns records, ensure that that is unexpected behavior and try the refresh again. (occasionally, there are other samples that need to remain with the "old" IDs, as is usually the case in sample swaps)
   1. If it still returns unexpected records, talk with Nasko.

2.4. Refreshing the ReadgroupMetadata DM in SEQPROD Database

...