Chapter 6 Exploring the catalogue
Before extracting anything we explore the federation with dsOMOP’s aggregate functions. These introspect each site’s OMOP schema and return only disclosure-checked metadata and counts — no patient-level data is transferred. This is how an analyst decides what to study.
6.1 What tables does each site expose?
dsOMOP discovers tables at runtime by reading the CDM schema; it does not hard-code an OMOP version. Here are the tables visible on the first site:
tabs <- ds.omop.tables(symbol = "omop", conns = conns)
head(tabs[[1]], 15)
#> table_name schema_category has_person_id concept_prefix
#> 1 person CDM TRUE gender
#> 2 observation_period CDM TRUE period_type
#> 3 visit_occurrence CDM TRUE visit
#> 4 visit_detail CDM TRUE visit_detail
#> 5 condition_occurrence CDM TRUE condition
#> 6 drug_exposure CDM TRUE drug
#> 7 procedure_occurrence CDM TRUE procedure
#> 8 device_exposure CDM TRUE device
#> 9 measurement CDM TRUE measurement
#> 10 observation CDM TRUE observation
#> 11 death CDM TRUE death
#> 12 note CDM TRUE note
#> 13 note_nlp CDM FALSE note_nlp
#> 14 specimen CDM TRUE specimen
#> 15 fact_relationship CDM FALSE6.2 What variables live in a table?
ds.omop.columns() lists a table’s fields. The person table carries the
demographics we will use, and the event tables carry the clinical concepts:
ds.omop.columns("person", symbol = "omop", conns = conns)[[1]]
#> column_name cdm_datatype db_datatype concept_role
#> 1 person_id integer integer non_concept
#> 2 gender_concept_id integer integer domain_concept
#> 3 year_of_birth integer integer non_concept
#> 4 month_of_birth integer integer non_concept
#> 5 day_of_birth integer integer non_concept
#> 6 birth_datetime datetime timestamp without time zone non_concept
#> 7 race_concept_id integer integer attribute_concept
#> 8 ethnicity_concept_id integer integer attribute_concept
#> 9 location_id integer integer non_concept
#> 10 provider_id integer integer non_concept
#> 11 care_site_id integer integer non_concept
#> 12 person_source_value varchar(50) character varying non_concept
#> 13 gender_source_value varchar(50) character varying non_concept
#> 14 gender_source_concept_id integer integer source_concept
#> 15 race_source_value varchar(50) character varying non_concept
#> 16 race_source_concept_id integer integer source_concept
#> 17 ethnicity_source_value varchar(50) character varying non_concept
#> 18 ethnicity_source_concept_id integer integer source_concept
#> fk_domain is_date is_sensitive is_blocked
#> 1 FALSE FALSE FALSE
#> 2 Gender FALSE FALSE FALSE
#> 3 FALSE FALSE FALSE
#> 4 FALSE FALSE FALSE
#> 5 FALSE FALSE FALSE
#> 6 TRUE FALSE FALSE
#> 7 Race FALSE FALSE FALSE
#> 8 Ethnicity FALSE FALSE FALSE
#> 9 FALSE FALSE FALSE
#> 10 FALSE FALSE FALSE
#> 11 FALSE FALSE FALSE
#> 12 FALSE TRUE TRUE
#> 13 FALSE TRUE TRUE
#> 14 Gender FALSE TRUE TRUE
#> 15 FALSE TRUE TRUE
#> 16 Race FALSE TRUE TRUE
#> 17 FALSE TRUE TRUE
#> 18 Ethnicity FALSE TRUE TRUE
ds.omop.columns("condition_occurrence", symbol = "omop", conns = conns)[[1]]
#> column_name cdm_datatype db_datatype
#> 1 condition_occurrence_id integer integer
#> 2 person_id integer integer
#> 3 condition_concept_id integer integer
#> 4 condition_start_date date date
#> 5 condition_start_datetime datetime timestamp without time zone
#> 6 condition_end_date date date
#> 7 condition_end_datetime datetime timestamp without time zone
#> 8 condition_type_concept_id integer integer
#> 9 condition_status_concept_id integer integer
#> 10 stop_reason varchar(20) character varying
#> 11 provider_id integer integer
#> 12 visit_occurrence_id integer integer
#> 13 visit_detail_id integer integer
#> 14 condition_source_value varchar(50) character varying
#> 15 condition_source_concept_id integer integer
#> 16 condition_status_source_value varchar(50) character varying
#> concept_role fk_domain is_date is_sensitive is_blocked
#> 1 non_concept FALSE FALSE FALSE
#> 2 non_concept FALSE FALSE FALSE
#> 3 domain_concept Condition FALSE FALSE FALSE
#> 4 non_concept TRUE FALSE FALSE
#> 5 non_concept TRUE FALSE FALSE
#> 6 non_concept TRUE FALSE FALSE
#> 7 non_concept TRUE FALSE FALSE
#> 8 type_concept Type Concept FALSE FALSE FALSE
#> 9 attribute_concept Condition Status FALSE FALSE FALSE
#> 10 non_concept FALSE TRUE TRUE
#> 11 non_concept FALSE FALSE FALSE
#> 12 non_concept FALSE FALSE FALSE
#> 13 non_concept FALSE FALSE FALSE
#> 14 non_concept FALSE TRUE TRUE
#> 15 source_concept Condition FALSE TRUE TRUE
#> 16 non_concept FALSE TRUE TRUE6.3 How big is each table, per site and pooled?
ds.omop.table.stats() returns disclosure-checked row counts and
distinct-person counts for each site. Because the cohort is sharded by
person into disjoint slices, the pooled distinct-person count is the sum across
sites — and for person it reconstructs the whole cohort (2 694), without
any site exposing more than its own shard.
sites <- names(conns)
tbls <- c("person", "condition_occurrence", "drug_exposure", "measurement")
per_site <- lapply(tbls, function(tb)
ds.omop.table.stats(tb, symbol = "omop", conns = conns)$per_site)
names(per_site) <- tbls
tabulate_field <- function(field) {
m <- t(vapply(per_site, function(ps)
vapply(sites, function(s) as.numeric(ps[[s]][[field]]), numeric(1)),
numeric(length(sites))))
colnames(m) <- sites
cbind(m, pooled = rowSums(m))
}Distinct persons per table — the person row is the full cohort, and the event
tables show how many of those persons have records of each kind:
tabulate_field("persons")
#> aphrc dgh iressef pooled
#> person 889 878 927 2694
#> condition_occurrence 889 878 927 2694
#> drug_exposure 889 878 927 2694
#> measurement 885 875 926 2686Total rows per table:
6.4 Which clinical concepts are most prevalent?
ds.omop.concept.prevalence() ranks concepts by the number of distinct persons
who have them, fusing the three shards into one pooled ranking. A concept is
reported only when every shard contributes (otherwise it is suppressed), so the
ranking is both pooled and disclosure-safe.
The most prevalent conditions point straight at a question — gastrointestinal haemorrhage and peptic ulcer are both here:
prev_cond <- ds.omop.concept.prevalence(
"condition_occurrence", metric = "persons", top_n = 12,
scope = "pooled", symbol = "omop", conns = conns)
subset(prev_cond$pooled, !suppressed, select = c("concept_name", "n_persons"))
#> concept_name n_persons
#> 1 Osteoarthritis 2694
#> 2 Viral sinusitis 2686
#> 3 Acute viral pharyngitis 2606
#> 4 Acute bronchitis 2543
#> 5 Otitis media 2025
#> 6 Streptococcal sore throat 1677
#> 7 Sprain of ankle 1357
#> 8 Concussion with no loss of consciousness 852
#> 9 Sinusitis 833
#> 10 Chronic sinusitis 812
#> 11 Peptic ulcer 802
#> 12 Acute bacterial sinusitis 786And the most prevalent drugs are dominated by NSAIDs and analgesics — the exposure of interest for a GI-bleed study:
prev_drug <- ds.omop.concept.prevalence(
"drug_exposure", metric = "persons", top_n = 12,
scope = "pooled", symbol = "omop", conns = conns)
subset(prev_drug$pooled, !suppressed, select = c("concept_name", "n_persons"))
#> concept_name
#> 1 tetanus and diphtheria toxoids, adsorbed, preservative free, for adult use
#> 2 Acetaminophen 325 MG Oral Tablet
#> 3 poliovirus vaccine, inactivated
#> 4 Amoxicillin 250 MG / Clavulanate 125 MG Oral Tablet
#> 5 Aspirin 81 MG Oral Tablet
#> 6 celecoxib
#> 7 hepatitis A vaccine, adult dosage
#> 8 hepatitis B vaccine, adult dosage
#> 9 Acetaminophen 160 MG Oral Tablet
#> 10 Acetaminophen 21.7 MG/ML / Dextromethorphan Hydrobromide 1 MG/ML / doxylamine succinate 0.417 MG/ML Oral Solution
#> 11 Ibuprofen 200 MG Oral Tablet
#> 12 Penicillin V Potassium 250 MG Oral Tablet
#> n_persons
#> 1 2660
#> 2 2580
#> 3 2140
#> 4 2021
#> 5 1927
#> 6 1844
#> 7 1737
#> 8 1560
#> 9 1428
#> 10 1393
#> 11 1280
#> 12 12726.5 The study this suggests
The catalogue hands us a clinically coherent question. Gastrointestinal haemorrhage is well represented; peptic ulcer — its best-known antecedent — is even more common; and the cohort is saturated with NSAIDs, the classic pharmacological trigger. So we will build a person-level frame fusing:
| Source table | Feature(s) | Role |
|---|---|---|
person |
sex, birth_year (→ age) |
adjustment |
condition_occurrence |
gi_bleed, peptic_ulcer |
outcome, key predictor |
drug_exposure |
nsaid (any of 5), nsaid_n (count), diclofenac |
exposure |
The next chapter extracts exactly this, on the server, in one shot.