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 Oracle 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. |
Parent topic: View source data