Dashboard & Reporting Queries

The DASHBOARD schema holds BI marts — biweekly delivery reports and provider analysis metrics. The ADS (Abstracted Data Store) holds clinical abstraction data.

Available dashboard tables

Table	Description
`OH_VERTEX_DRV_BIWEEKLY`	Vertex Organ Health biweekly delivery report
`OH_NON_VERTEX_DRV_BIWEEKLY`	Non-Vertex OH biweekly delivery report
`OH_FORIAN_RENASIGHT`	Forian Renasight cohort dashboard
`VERTEX_PROVIDER_ANALYSIS`	Vertex provider performance metrics
`ONC_COMPASS_SIGNATERA_TESTS`	ONC COMPASS Signatera tests mart
`ONC_COMPASS_BIOMARKERS`	ONC COMPASS biomarkers mart

ADS (Abstracted Data Store)

The ADS schema contains clinical abstraction data from the CDA team (manual) and Mendel AI (automated). Access it via staging views in ADS_STAGING or the ADS_OH_AGGREGATIONS aggregation in LIMS_PUB.

Example queries

Biweekly delivery counts by result date:

biweekly.sql

1SELECT result_date, COUNT(DISTINCT casefile_id) AS deliveries2FROM CLINICOGENOMICS.DASHBOARD.OH_VERTEX_DRV_BIWEEKLY3GROUP BY 14ORDER BY 1 DESC5LIMIT 10;

Top providers by patient volume:

provider_analysis.sql

1SELECT provider_name, ordering_provider_npi, patient_count, drv_percentage2FROM CLINICOGENOMICS.DASHBOARD.VERTEX_PROVIDER_ANALYSIS3ORDER BY patient_count DESC4LIMIT 20;