General DQ metrics

Metrics on this tab

• Visit ID Failure Rate (condition, procedure)
• Route Concept Failure Rate
• Unit Concept Failure Rate

General DQ metrics

Visit ID Failure Rate (condition)

This metric assesses the percentage of rows in the condition table where the visit_occurrence_id:

• is null

• is 0

• does not exist in the visit_occurrence table

SQL template

SELECT COUNT(DISTINCT vo.visit_occurrence_id) AS total
FROM `visit_occurrence` vo;

SELECT COUNT(DISTINCT co.condition_occurrence_id) as condition_rows_w_no_valid_vo
FROM `condition_occurrence` co
LEFT JOIN `visit_occurrence` vo on vo.visit_occurrence_id = co.visit_occurrence_id
WHERE 
    co.visit_occurrence_id NOT IN (vo.visit_occurrence_id)
    OR co.visit_occurrence_id = 0
    OR co.visit_occurrence_id IS NULL

failure rate = condition_rows_w_no_valid_vo / total

Visit ID Failure Rate (procedure)

This metric assesses the percentage of rows in the procedure table where the visit_occurrence_id:

• is null

• is 0

• does not exist in the visit_occurrence table

SQL template

SELECT COUNT(DISTINCT vo.visit_occurrence_id) AS total
FROM `visit_occurrence` vo;

SELECT COUNT(DISTINCT po.procedure_occurrence_id) as procedure_rows_w_no_valid_vo
FROM `procedure_occurrence` po
LEFT JOIN `visit_occurrence` vo on vo.visit_occurrence_id = po.visit_occurrence_id
WHERE 
    po.visit_occurrence_id NOT IN (vo.visit_occurrence_id)
    OR po.visit_occurrence_id = 0
    OR po.visit_occurrence_id IS NULL

failure rate = procedure_rows_w_no_valid_vo / total

Route Concept Failure Rate

This metric indicates the percentage of concept_ids in drug exposure table that both:

• Are of the standard_concept ‘S’

• Have the domain 'Route'

SQL template

SELECT COUNT(DISTINCT drug_exposure_id) as number_total_routes
FROM `drug_exposure`;

SELECT COUNT(DISTINCT drug_exposure_id) as number_valid_routes
FROM `drug_exposure` de
JOIN `concept` c ON de.route_concept_id = c.concept_id
WHERE c.standard_concept = 'S'
    AND LOWER(c.domain_id) LIKE '%route%';

route_success_rate = round( number_valid_routes / number_total_routes * 100, 2)

Unit Concept Failure Rate

This metric indicates the percentage of applicable concept_ids in measurement table that both:

1. Are of the standard_concept ‘S’

2. Have the domain 'Unit'

** the applicable concept_ids refer to -- rows with numerical values in value_source_value or value_as_number

SQL template

SELECT COUNT(*) AS number_total_rows
FROM `measurement` m
WHERE (safe_cast(replace(replace(replace(value_source_value, "<", ""), ">", ""), "=", "") as float64) is not null
      OR safe_cast(value_as_number AS float64) is not null);

SELECT COUNT(*) AS number_successful_units
FROM `measurement` m
LEFT JOIN `concept` c ON m.unit_concept_id = c.concept_id
WHERE LOWER(c.standard_concept) LIKE '%s%'
    AND LOWER(c.domain_id) LIKE '%unit%'
    AND (safe_cast(replace(replace(replace(value_source_value, "<", ""), ">", ""), "=", "") as float64) is not null
        OR safe_cast(value_as_number AS float64) is not null);

Unit success rate = number_successful_units/number_total_rows

Features available on dashboard

• Master filter
  • filter by either awardee or organization (chosen in the "NIH Grant Award Metrics" tab)
  • filter by performance group

• Tooltip - detailed information, calculation equation, and links to Zendesk articles by clicking marks
  •