Versions Compared

Version Old Version 35 New Version 36
Changes made by

Christina Gearin

Lucidchart Diagrams Connector

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Gliffy
nameAnalytics ETL Framework

 

Analytics ETL Framework 2
Gliffy
name
Lucidchart
autoSize1
macroId12429fd4-a2d7-46dc-af21-443e725b6935
pageCount1
instanceIdConfluence:8792679401
width1000
documentId9d330eff-c8ae-41f4-9dc8-85b6b8a3d064
alignleft
typerich
updated1533064724003
height1000

 

Lucidchart
autoSize1
macroId8041b405-27c8-4d39-97b7-03dd2833adb7
pageCount1
instanceIdConfluence:8792679401
width1000
documentIdda56ba42-0a89-4846-8c30-257e53a1b224
alignleft
typerich
updated1533064731700
height1000

 

ETL troubleshooting recipes (To make changes permanent in DB don't forget to issue COMMIT at the end.)

...

Issue/Error: Once a week (1am on Fri) PdoStar5 goes through 1y-refresh. Sometime you might want to trigger it earlier.

Diagnosis: 

Remedy:  Connect to "analytics" server using your favorite SSH tool and run the following line:

...

Issue: How to refresh Picard Aggregation DMs for a specific set of samples. Note, this will not refresh any metrics unless they are part of a new aggregation (new version). Only basecall metrics rolled up at library and sample level get updated

Remedy: Follow the instructions  here for Manual Refresh of Picard Aggregations for a list of samples.

 

Issue: PicardAggregator ETL fails with (COGNOS.SLXRE2_PAGG_SAMPLE_PK) violated error.

Cause: Most likely Is_latest flag is set for more than 1 aggregations.

Remedy: Find the sample that cause it (run the query below) and make a blocker PO ticket + notify somebody from Pipeline team to address it ASAP because the ETL won't recover until this is fixed.

SELECT DISTINCT SAMPLE FROM ( SELECT a.project, a.SAMPLE, a.LIBRARY, a.data_type FROM metrics.aggregation a WHERE nvl(a.modified_at, a.workflow_end_date) >= to_date(<START Date from ETL dashboard>, 'yyyy-Mon-dd hh24:mi:ss') AND nvl(a.modified_at, a.workflow_end_date) < to_date(<END Date from ETL dashboard>, 'yyyy-Mon-dd hh24:mi:ss') AND a.is_latest <>0 GROUP BY a.project, a.SAMPLE, a.LIBRARY, a.data_type HAVING count(*)>1 )
Code Block
 
Issue: Designation ETL fails with "ORA-30926: unable to get a stable set of rows in the source tables" error
Diagnosis: This is usually caused by a flowcell with 2 runs valid run (uncanceled), one of which should have been cancelled. Run the query below to determine the trouble flowcell:
SELECT r.flowcell_barcode, count(*)
FROM slxre2_organic_run r
WHERE r.is_cancelled =0
AND r.run_date >= '1-jan-2017'
AND r.run_type ='PRODUCTION'
GROUP BY r.flowcell_barcode
HAVING count(*)>1
 Code Block
Remedy: Contact Tammy, Andrew B. , Mike DaSilva or John W. to clear the bad run from their dashboard. Designation ETL will recover as soon as the run is cleared. 
 
 
Issue: Scala ETL ERROR: analytics.etl.DeckEventAgent$ (Most likely 'lcSet' tag is missing)
Diagnosis: Sometimes this Squid web-service returns no LcSet. Comment about this incident is automatically posted inIPI-61224and ticket reopened.
Remedy: Just follow up with LIMS team. OurDeckEventAgent will catch up as soon as LIMS service is fixed. No other intervention is needed.
 
 
Issue: analytics.etl.PkViolationWarning. unique constraint (COGNOS.RGHQS_METADATA_PK) violated
Diagnosis: This happens when 2+ readgroups having same (fc, lane, mol_index) try to get into datamart. Picard calls it "CLASH". RunMercuryAgent would ignore abusing readgroups and move on with the rest of 'good' ones. Email would give you detailed troubleshooting info.
Remedy: There isn't much for us to do. If upstream issues are resolved you can request a ReadgroupMetadata refresh for the whole run/fc (see above). You can search for the RG in GPINFOJIRA by searching the FC ID to see if it has been blacklisted and already handled upstream.
 
 
Issue/Error: ETL: DB error, server: (thorium or vorarestore), Error: ORA-12008: error in materialized view refresh path, ORA-25153: Temporary Tablespace is Empty
Diagnosis: This happens when the DB team is doing some kind of backup or transfer.
Remedy: These errors can be ignored. Everything is actually fine.
...