Page 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:

Consent withdrawal
- Participants may voluntarily request to withdraw their participation from specific ongoing research studies or other projects. Regulations indicate that any existing metadata must be updated to reflect such withdrawal request. These changes may not be readily available until the right steps are followed to update the metadata on the the withdrawal, which requires following the correct process to update our datamarts.
Fingerprinting and gender related changes
- Requests for fingerprinting reanalyses and gender related changes may occur for several reasons such as missing FP assays, sample swaps, and collaborator requests. These changes may not refresh automatically in the datamarts for which refresh pushes may be necessary.

The main steps a user may need to follow in these occasions includes include checking metadata changes and pushing datamart refreshes. The processes explained below are meant to help the user take step-by-step actions if and when required. A user may not need to take the steps in all processes.

...

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.6. Resolving DataChecker Errors - PK violations of Aggregation DMs

In cognos, check whether PK violators have already been picked up by a subsequent ETL
SELECT e.error_timestamp, la.TIMESTAMP etl_timestamp, e.*
FROM err$_slxre2_pagg_library e
LEFT JOIN slxre2_pagg_library la ON
la.project = e.project
AND la.SAMPLE = e.SAMPLE
AND la.LIBRARY = e.LIBRARY
AND la.data_type = e.data_type
WHERE e.resolution_timestamp IS NULL
If etl_timestamp is after the last error_timestamp , the error record can be resolved(use the query below). If etl_timestamp is missing or is before error_timestamp, then follow up with Pipeline team, send them specific (project, sample, librarym data type)
UPDATE err$_slxre2_pagg_library
SET resolution_timestamp = SYSDATE
WHERE resolution_timestamp IS NULL
If a subset of violators were picked up by the ETL, adjust the WHERE clause in the UPDATE statement to just those entries

2. PDO STAR, RGHQS, and Read Group Metadata Datamart Refresh Push

Tip

icon	false
title	Reference

Review Tiger3 ETL Shell Commands (refreshes, etc.) for more details on this section

...

Info
PDO STAR automatically refreshes samples that are not billed every two hours, and does a full refresh of 1-year data on Friday mornings at 2:55 am. If metadata change requests are for orders that are not billed or are less than a year old, a manual push may not be necessary. However, step 2.3 4 below should be followed regardless to check whether the metadata changes were made in PDO STAR.

2.1.

...

PDOSTAR refresh Option 1 - Using RunEtl tool (Also RGHQS refresh)

Instructions here

2.2. PDOSTAR refresh Option 2 - UNIX, using a query (using

...

"Old" Collaborator Sample IDs)

Note

icon	false

In order to follow For this process, you need to obtain the PDO_NAME of any records returned can bypass section 2.1 and use the "Old" Collaborator Sample IDs obtained in section 1.1.

Log in to your favorite SSH tool (e.g. SecureCRT)

connect to "analytics" server (command: ssh analytics)
enter the following: /home/unix/analytics/TigerETL3/runEtlShell.sh

paste & run the following etl-command separately for each PDO, changing PDO-12480 to the relevant PDO returned above.once, pasting the "Old" Collaborator Sample IDs in the query below

Code Block

language	scala

import analytics.tiger.agents.PdoStar._
AnalyticsEtlDB("analytics.tiger.agents.PdoStar.fromSQL", deltaFromSqlcollSamplesToPdoSamples(List("""SELECT DISTINCT a.pdo_name||','||a.pdo_sample_id FROM cognos.pdo_star5 a WHERE a.pdo_name = 'PDO-12480'"""oldcollabSMID1","oldcollabSMID2")) flatMap DataSetEtl() flatMap analytics.tiger.agents.PdoStar.pipeline)

The NOTE: These are supposed to be two lines (the first ends with an underscore). Once you hit enter, the command takes a little while. You'll know it's complete when you get the scala prompt again.

2.

...

3.

...

PDOSTAR refresh Option 3 - UNIX, refresh using a query (

...

using PDO_NAMEs)

Note

icon	false

For In order to follow this process, you can bypass section 2.1 and use the "Old" Collaborator Sample IDs obtained need to obtain the PDO_NAME of any records returned in section 1.1.