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

```sql
SELECT result_date, COUNT(DISTINCT casefile_id) AS deliveries
FROM CLINICOGENOMICS.DASHBOARD.OH_VERTEX_DRV_BIWEEKLY
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
```

Top providers by patient volume:

```sql
SELECT provider_name, ordering_provider_npi, patient_count, drv_percentage
FROM CLINICOGENOMICS.DASHBOARD.VERTEX_PROVIDER_ANALYSIS
ORDER BY patient_count DESC
LIMIT 20;
```