About the Complete PDSR Curated Data Set — Structure, Data Details, & SQL Query Examples

This document describes the Complete PDSR Curated Data Set structure and provides example SQL statements you can use to query the data. 

Introduction

The Complete Patient Data Science Repository Curated Data Set is a repository of curated clinical data at Mass General Brigham (MGB). You can use the data for exploratory research or hypothesis-generating studies, cohort studies, pattern analysis, and more.

Prerequisites

• You have reviewed the Complete PDSR Curated Data Set Dashboard in Collibra and the Complete PDSR Curated Data Set Data Dictionary.
• Access to the data repository has been approved and provisioned.
• Access to the Enclave Platform has been provisioned and you are logged into the virtual desktop environment.

Please allow 2-3 hours after provisioning before accessing the Complete PDSR Curated Data Set.

Complete PDSR Curated Data Set Structure

The data is available for direct access in two SQL server databases:

The data within both databases is organized into three schemas - Analytics, Dimension, and i2b2.

Please refer to the Complete PDSR Curated Data Set Data Dictionary in Collibra for the complete data dictionary.

There are twenty (20) ontologies that organize concepts. These hierarchies correspond to the folder structure in the PDSR i2b2 Query Tool (and the RPDR Query Tool). Concept types are found in the Dimension.Concepts view.

List of concept types:

• Demographics
• Diagnosis
• DRG
• Encounters
• HealthHistory
• LabTests
• Medications
• Microbiology
• Molecular Medicine
• PatientCMSEnrollment
• PatientConsents
• PhenotypeOntology
• Procedures
• Providers
• RadiologyTests
• ReasonForVisit
• Specimens
• Transfusion

The Dimension and Analytics data are curated in a non-i2b2 format to make the data more easily understood and querying faster.

Examples of SQL Statements

List available Providers (Note: Some providers have multiple specialties)

select TOP 1000 * from i2b2.provider_dimension
where PROVIDER_ID in (select PROVIDER_ID from i2b2.provider_dimension group by PROVIDER_ID
having count(1)>1)
order by PROVIDER_ID

List non-demographic concepts that appear multiple times in the i2b2.concept_dimension table (they belong to different folders and/or have symptoms)

select TOP 1000 * from i2b2.concept_dimension
where CONCEPT_CD in
(select CONCEPT_CD from i2b2.concept_dimension
where CONCEPT_PATH not like '%I2B2MetaData\Demographics%'
group by CONCEPT_CD having
count(1)>1)
order by CONCEPT_CD

Sample SQL for one patient

Execute/run the below SQL to list all information for a given PATIENT_NUM, including encounter, provider, concept, and visit information.

Specifying certain columns will help to filter output information leaving just the columns that are useful for the task.

A patient may have multiple encounters with different providers, each of those with multiple specialties, output includes multiple concepts, depending on the nature of the visit

select TOP 1000 * from i2b2.observation_fact a
left join i2b2.provider_dimension b on a.PROVIDER_ID=b.PROVIDER_ID -- one provider can have multiple specialties
left join i2b2.patient_dimension c on a.PATIENT_NUM=c.PATIENT_NUM -- one row per patient
left join i2b2.visit_dimension d on a.ENCOUNTER_NUM=d.ENCOUNTER_NUM -- one row per encounter
left join i2b2.concept_dimension e on a.CONCEPT_CD=e.CONCEPT_CD -- one concept can have multiple hierarchies
where a.PATIENT_NUM=9869928 and
a.CONCEPT_CD not in ('','@') and
e.CONCEPT_PATH not like '%I2B2MetaData\Demographics%'
order by a.START_DATE desc

Sample SQL for patient population

Identify a list of patients with the same concept type - using CONCEPT_PATH value in concept_dimension view helps to find patients with specific concepts (e.g. the following query outputs all patients who have a transfusion concept):

select distinct TOP 1000 PATIENT_NUM from i2b2.observation_fact a
where exists (select * from i2b2.concept_dimension b
where CONCEPT_PATH like '%I2B2MetaData\Transfusion%' and
a.CONCEPT_CD=b.CONCEPT_CD)

Sample SQL for mean, min, and max age and count of patients with HBA1C measured lab test by patient sex:

Query computes the mean, min and max age and count of patients with an HBA1C measured lab test by patient sex. The query includes all patients in the patient_dimension table (full PDSR LDS population), joins to the observation_fact table and includes all the CONCEPT_CDs corresponding to an HBA1C lab test by looking in the concept_dimension table. You can change the CONCEPT_PATH (or add additional CONCEPT_PATHS using an OR statement) to look for a different set of concepts: 
(Execution completes after ~5 min.)

select SEX_CD,
avg(AGE_IN_YEARS_C) mean_age,
max(AGE_IN_YEARS_C) max_age,
min(AGE_IN_YEARS_C) min_age,
count(*) n
from [PDSR_Monthly].[i2b2].[patient_dimension] p
inner join PDSR_Monthly.i2b2.observation_fact f on p.PATIENT_NUM = f.PATIENT_NUM
and f.CONCEPT_CD IN (select CONCEPT_CD
from PDSR_Monthly.i2b2.concept_dimension
where CONCEPT_PATH LIKE '\I2B2MetaData\LabTests\LAB\Chemistry\GHBA1C\%')
group by SEX_CD

Other SQL Queries

Analytics Labs and Vitals views take longer to return results. Since there are indexes on both Encounter_Num and Patient_Num, in order to return rows quickly, please add a ‘where clause’ on either Encounter_Num or Patient_Num. Examples are below:

select top 10 * from Analytics.Labs where Encounter_Num <10000000
select top 10 * from Analytics.Vitals where Patient_Num <10000000

List showing the highest level of folder grouping of the concepts:

select TOP 1000 HierarchyLevel1NM from Dimension.Concepts
group by HierarchyLevel1NM
order by HierarchyLevel1NM

Use the Dimension.Concepts table to identify which concepts you are interested in and use that list of concepts to filter data from the Analytics tables

For example, identify all medications under the Valacyclovir folder:

select TOP 1000 * from Dimension.Concepts
where HierarchyLevel4NM='Valacyclovir'

There are thirty five (35) concepts grouped under the Valacyclovir category/folder/hierarchy level. To identify these concepts, run the below query:

select TOP 1000 * from Analytics.Medications m
where exists (select 1 from Dimension.Concepts c
where m.ConceptCD =c.ConceptCD
and c.HierarchyLevel4NM='Valacyclovir')

The Medications ontology organizes medication concepts into hierarchies/folders similarly for Diagnoses, LabTests, HealthHistory, and Procedures.

List patient set for a specific clinic in vitals by the patient’s encounter num:
(Execution completes after ~2 min.)

select TOP 1000 * from Dimension.EncountersCurrentPatients e
left join Analytics.VitalsCurrentPatients v on e.Encounter_Num = v.Encounter_Num
where v.ClinicNM = 'MGH Cancer Center (232)'
order by e.Patient_Num, e.Encounter_Num

Additional Resources

How Can Covered Entities Use and Disclose Protected Health Information for Research and Comply with the Privacy Rule?

SQL example statements for retrieving data from a table

SQL Tutorial