/
Refreshing Analytics data marts after Mercury DWH stalled

Refreshing Analytics data marts after Mercury DWH stalled

Owned by Mariela Mihaleva
Last updated: 2022-06-09
3 min read

In rare cases when Mercury ETL stalls and no data enters MDWH, it is important for Analytics DWH to catch up after MDWH is up-to-date. The most affected Analytics ETLs are those using events , QC metrics and library data.

Highest priority are Covid related ETLs

  • Analytics.Covid_POD_Samples

  • Analytics.Covid_QPCR

Second in line are the rest of the dependent data marts:

  • BSP_PLATED_SAMPLES

  • SLXRE2_RG_ANCESTRY

  • SLXRE2_LIBRARY_QPCR

  • SLXRE2_LIBRARY_ANCESTOR

  • SAMPLE_QC_METRICS

  • EXTRACTION

  • SLXRE_LIBRARY_LCSET

  • SLXRE2_PICO_QUANTS

  • SLXRE2_DESIGNATION

  • SLXRE2_DESIGNATION_METADATA

  • ARRAY_MERCLOUD

 

ETLs that automatically pick up the data because they runs for a wider date range

  • SLXRE_LIBRARY_LCSET - refreshes last 30 days

  • SLXRE2_PICO_QUANTS - refreshes 1 year back

  • SLXRE2_DESIGNATION - refreshes last 30 days

  • SLXRE2_DESIGNATION_METADATA - refreshes last 30 days

  • ARRAY_MERCLOUD - refreshes last 30 days

 

Refreshing all other ETLs

  • Most of the ETLs need to have their property key moved back to a date / timestamp close to the time of the outage. Login to SQL Navigator as COGNOS.

  • Then, run the agent command in Linus shell on Analytics server.

  1. CovidPodSamples

    1. Set the property key

      UPDATE etl_property SET VALUE = '2022-Jun-3 16:00:00' WHERE KEY = 'analytics.tiger.agents.CovidPodSamples'; COMMIT;

    2. Run the agent

      $RUN3 Task task=etlObject agentName=analytics.tiger.agents.CovidPodSamples db=cognos 'task=sqlScript(fileName=Covid.conf,fieldName=CovidPodSamples,deltaInjection=text)' 'delta=MillisDelta.loadFromDb(2h,0h)'

  2. CovidQpcr - this ETL is being run manually because one of the queries tends to be very slow with large amounts of data. The query is in process of optimization.

  3. RgAncestry

    1. Set the property key

      UPDATE etl_property SET VALUE = '2022-Jun-3 16:00:00' WHERE KEY = 'analytics.tiger.agents.RgAncestry'; COMMIT;

    2. Run the agent

  4. LibraryQpcrEtl

    1. Set the property key

    2. Run the agent

  5. LibraryAncestry

    1. Set the property key

    2. Run the agent

  6. SampleQc

    1. Set the property key

    2. Run the agent

  7. Extractions

    1. Set the property key

    2. Run the agent

  8. BspPlatingRequest - This ETL is being run from BSP for backfilling from Mercury we only need to refresh the Mercury plating part. Run the query bellow when connected to seqprod as cognos.