Source database tables

In this section ShowHide

Examples of base tables
Source database tables preparation
Resulting source database tables

Source database tables are the Oracle tables or views that store prepared safety data, which has been cleaned and transformed for data mining. To prepare source database tables, you can use standard Oracle tools or any application that enables you to work with Oracle tables. In preparing the analysis tables, you must:

Decide how many source database tables or views are needed.
Ensure that all of the source database tables include an item that links them and can serve as a case identifier in the Empirica Signal application.
Ensure that, if the source data is timestamped, the source database tables include dates that can be used as start and end dates for data.
Ensure that the source database tables include the following:
An item or items representing the therapeutic product being studied.
An item or items representing the event or symptom being studied.
Items that allow stratification during a data mining run.
Items that allow subsetting during a data mining run.

Examples of base tables

The base tables are as follows:

DEMO table:

CASE_ID	GENDER	COUNTRY	REPORT_DATE	BIRTH_DATE
123	M	US	12/01/2001	01/16/1954
138	F	France	01/05/2000	07/01/1965
147	M	Japan	09/17/2001	03/11/1942
154	F	US	10/04/1999	09/07/1966

ANTIGENS table:

CASE_ID	VAX_NAME	ANTIGEN	VAX_LOT
123	VAX A	ANTIGEN 1	15HR34
123	VAX A	ANTIGEN 2	15HR34
138	VAX B	ANTIGEN 3	49J3
147	VAX C	ANTIGEN 4	97WEO
147	VAX C	ANTIGEN 2	97WEO
154	VAX D	ANTIGEN 3	D90477F6

SYMPTOMS table:

CASE_ID	SYMPTOM	START	STOP	SEVERITY	NARRATIVE
123	Rash	10/30/01	11/25/01	Moderate	Red weeping rash on left side of neck extending from left back of neck over shoulder to left clavicle.
138	Hearing loss	11/14/00	12/05/00	Severe	Total hearing loss, both ears.
147	Rash	09/01/01	09/03/01	Mild	Red weeping rash on right forearm.
154	Vertigo	09/30/99	10/03/99	Severe	After walking block, patient experienced abrupt feeling of spinning and almost fell down.

Source database tables preparation

Suppose that the following data is needed for data mining:

All cases originating in the U.S.
The year that the case report was received.
Age groups of patients.
The symptom duration.

To prepare source database tables, you can do the following:

Create a DEMO_ANALYSIS view on the DEMO table. Rename the CASE_ID to RPT_ID. Exclude case reports that did not originate in the U.S. Derive AGE_GROUP from AGE, which is derived from REPORT_DATE and BIRTH_DATE.
Create a VACCINES_ANALYSIS view on the ANTIGENS table. Exclude case reports that did not originate in the U.S. Rename the CASE_ID to RPT_ID.
Create a SYMPTOMS_ANALYSIS view on the SYMPTOMS table. Rename the CASE_ID to RPT_ID. Exclude case reports that did not originate in the U.S. Derive RCVD_YR from CASE_DATE. Derive DUR_DAYS (symptom duration in days) from START and STOP in the SYMPTOMS table.
Create a DRILLDOWN view including a RPT_ID and any fields from the base tables that the user needs to see upon drilling down to case-level information. Exclude case reports that did not originate in the U.S. (This view is not the same as the drilldown map table.)

Resulting source database tables

The resulting source database tables (or views) might look like this:

DEMO_ANALYSIS view:

RPT_ID	GENDER	RCVD_YEAR	AGE_GROUP
123	M	2001	40-49
154	F	1999	30-39

VACCINES_ANALYSIS view:

RPT_ID	VACCINE	ANTIGEN	LOT_NO
123	VAX A	ANTIGEN 1	15HR34
123	VAX A	ANTIGEN 2	15HR34
154	VAX D	ANTIGEN 3	D90477F6

SYMPTOMS_ANALYSIS view:

RPT_ID	SYMPTOM	DUR_DAYS	SEVERITY
123	Rash	27	Moderate
154	Vertigo	4	Severe

DRILLDOWN view:

RPT_ ID	GENDER	VACCINE	SYMPTOM	DUR_ DAYS	SEVERITY	AGE_ GROUP	NARRATIVE
123	M	VAX A	Rash	27	Moderate	40-49	Red weeping rash on left side of neck extending from left back of neck over shoulder to left clavicle.
154	F	VAX D	Vertigo	4	Severe	30-39	After walking a block, patient experienced abrupt feeling of spinning and almost fell down.