# Presentation: 100443

## NBN Data Element MappingRetrospective Piece

- Rhett Sutphin, Andrew Winter
- Northwestern University

## IPBS

- Retrospective
- Data Elements

|  | In CAE? |
| --- | --- |
| clinical/pathology |  |
| PSA | No |
| clinical stage | Yes |
| biopsy Gleason | Yes |
| pathology Gleason | Yes |
| surgical margins | Yes |
| seminal vesicle involvement | Yes |
| organ confined | No |
| lymph node metastases | Yes |
| neoadjuvant treatment | No |
| [neoadjuvant dose - XRT] | No |
| [neoadjuvant hormone type] | No |
| [neoadjuvant dose - hormone] | No |
| [neoadjuvant duration] | No |
| adjuvant treatment | No |
| [adjuvant dose - XRT] | No |
| [adjuvant hormone type] | No |
| [adjuvant dose - hormone] | No |
| [adjuvant duration] | No |
|  |  |
| outcomes |  |
| recurrence | No |
| recurrence date | No |
| last follow-up status | No |
| last follow-up date | No |

## What we have

- Home-grown system: CC_P_SPORE
- Designed for the clinical (non-pathology) data in the SPORE
- Home-grown system: Tissuebank
- Designed and used specifically for the Prostate SPORE tissue needs
- Home-grown system: NOTIS
- Designed for managing protocol life cycle and patient consenting, accrual and calendar
- Freezerworks
- Used for all activity in Pathology Core

## Tissuebank

- CC_P_SPORE
- Many tables, reasonably well normalized
- Well maintained
- Entity-Attribute-Value (EAV) system
- Not so well maintained

## Mapping of NBN elements

- Majority come from CC_P_SPORE
- Handful come from Tissuebank
- Some may be better retrieved from Freezerworks
- Some, that don’t map into CAE, are in our clinical trial management system (NOTIS)

## PSPORE:

## Tissuebank:

## How do we get to the CAE Object Model?

- Massage our data into a format compatible with the CAE object model with database views
- Reasonably easy and straightforward, but may not be the most OLTP-friendly due to the increased overhead

## CAE Prostate:

## SQL for the Participant View

- CREATE OR REPLACE VIEW Participant AS
- SELECT pop.pop_id pop_id,
-              pat.last_name lastName,
- 	     pat.first_name firstName,
- 	     pat.middle_initial middleName,
- 	     pat.birth_date birthDate,
- 	     pat.ssn ssn,
- 	     pat.sex gender,
- 	     r.race race,
- 	     e.ethnicity ethnicity
-   FROM patients pat,
-              patients_on_protocols pop,
-              t_patient_race pr,
- 	     tlu_race r,
- 	     tlu_ethnicity e
-  WHERE pat.patient_id = pop.patient_id
-    AND pat.patient_id = pr.patient_id
-    AND pr.race_id = r.race_id
-    AND pr.ethnicity_id = e.ethnicity_id
-    AND r.version = 2
-    AND pop.protocol_id = 2055;

## SQL for the Accession View

- CREATE OR REPLACE VIEW Accession AS
- SELECT pd.patient_id systemIdentifier,
- 		s.snumber surgicalPathologyNumber,
- 		pd.psa psa,
- 		pp.t_stage||pp.n_stage||pp.m_stage cancerStageClassification,
- 		s.gleasonCombo biopsyGleason,
- 		pp.gleason_sum pathologyGleason,
- 		pp.margin_involved surgicalMargins,
- 		pp.seminal_vesicles SMVStatus,
- 		pp.lymph_nodes lymphNodeMetasteses
-   FROM t_patient_diagnosis pd,
- 	    t_patient_pathology pp,
- 	    cc_tissuebank.v_samples_1002 s
-  WHERE pd.PATIENT_ID = pp.PATIENT_ID
- 	 AND pd.patient_id = s.patient_id;

## What’s next?

- Have not yet instantiated our data in the existing java code
- The CAE code relies on a different data model
- The CAE object model does not have all the elements, but adding them shouldn’t be too difficult