The table below describes the Analytics data marts related to each step in the Lab.
Lab Process | Table Name and Primary key | Description |
|---|---|---|
Sample Receipt (informatical) | analytics.bsp_sample PK: sample_barcode | Metadata about the samples registered in BSP. |
Sample Receipt (informatical) | No Analytics DM. mercurydw.sample_fact PK: sample_barcode | Metadata about samples registered in Mercury. Data may overlap with analytics.bsp_sample for some controls and clinical samples. |
Sample Receipt (physical accessioning) | No Analytics DM. mercurydw.event_fact where lab_event_type = ‘SampleReceipt’ ? | |
Extraction | analytics.extraction PK: (pdo, a.source_sample, a.extracted_sample) | Extracted samples related to certain events from mercurydw.event_fact. |
Quantification | No Analytics DM. mercurydw.lab_metric quant types: INITIAL_PICO, XL20_VOLUME | |
Plating | cognos.bsp_plated_samples PK: (PDO, PDO_sample_id, Plated_Sampel_id) | Includes plating information for Sequencing and Arrays from Mercury (work_request_item_id = -1, work_request_id = -1). The work_request_item_type is ‘Mercury Seq Import’ for Seq plating. The work_request_item_type = ‘Mercury Array Import’ for Array plating. This table includes legacy data for plating done in BSP (work_request_id points to a BSP work request for plating, and work_request_item_id - BSP work request item id for plating WR) The work_request_item_type = ‘SEQ_PLATING_REQUEST’ for Seq plating The work_request_item_type = ‘GAP_PLATING_REQUEST’ for Array plating |
LC | cognos.slxre_library_lcset PK: (library_name, plated_sample_id) | Metadata of all libraries generated - type, plated sample (aliquot), PDO, PDO sample, LCSET, etc. |
Library QC | cognos.sample_qc_metrics PK: (content_name, Plated_sample_id, quant_type | QC metrics and metadata for libraries of all types (pond, pools) that went through some sort of QC - Pico, qPCR, etc. content_name corresponds to cognos.slxre_library_lcset.library_name quant_type is converted to a user-friendly label but matches the mercurydw.lab_metric.quant_type. |
Library QC | cognos.slxre2_pico_quants PK: (library_name, quant_type) | Only Pico QC metrics and metadata. Libraries that contain only 1 sample usually go through Pico. |
Library QC | cognos.slxre2_library_qpcr PK: library_name | qPCR QC metrics and metadata for the libraries that were qPCR’d. In the past only pools were qPCR’d but now days some processes run qPCR QC on single sample libraries. |