Race

Vocabulary_ID=13

The following describes the procedure to update the Race vocabulary with the latest release of the source.

The load process is a complete refresh of data. All new vocabulary entieties will be added. All vocabulary entities that do not exist in the latest files set will be marked as deprecated.

1.1 Scripts Transfer

All scripts and files listed at the bottom of this page should be transferred to specially created directory (eg /data/Race).

Script Name	Description
13_create_schema.sql	Create prestage-stage schema with tables and indexes
13_load_to_prestage_concepts.ctl	Concepts control file
13_load_concepts.sql	Load only new concepts into concept table, add invalid code information
13_transform_row_concepts.sql	Convert and store in stage table concept_stage

1.2 Download data from source

1.2.1 Download data from CMS source

The most current international release is located at the following URL: http://www.cdc.gov/phin/library/resources/vocabulary/CDC%20Race%20&%20Ethnicity%20Background%20and%20Purpose.pdf

Note: Make sure you perform updates in chronological order. So, if you want to process a January and April release, execute the below steps for January, and after successful completion, for April.

Download the latest release file (e.g. Race_Ethnicity_CodeSet.pdf)
Transfer downloaded zip file to /data/Race directory.

Latest production zip file is ~1MB in size

1.3 Build Database Schema

All tables will be located in the schema Race_<DateOfSourceFile> as following: Race_20120131
The following steps will create a schema and will grant to a schema owner appropriate permissions.

In addition to the schema, it will create tables to load raw data: RACE_CONCEPTS as well as staging tables:

CONCEPT_STAGE, CONCEPT_ANCESTOR_STAGE,

CONCEPT_RELATIONSHIP_STAGE,
SOURCE_TO_CONCEPT_MAP_STAGE

$ sqlplus System/<SystemPass>@DEV_VOCAB @13_create_schema.sql < RACE_Schema> <Pass_RACE_Schema>

Format of RACE_Schema should be RACE_YYYYMMDD (e.g. RACE_20120918)

1.4 Raw data load into Oracle tables

1.4.1 Extract data from the pdf file into the csv file in the current directory

1.4.2 Verify that the following files have been created:

RACE_CONCEPTS.txt (Latest file size ~1MB)

1.4.3 Load RACE raw data into Oracle.

Each sqlldr process will run for about 1min.

$ sqlldr RACE_20120918/myPass@DEV_VOCAB control=13_load_to_prestage_concepts.ctl

1.4.5 Verify that files with extension .bad are empty, no records have been rejected.

1.4.6 Copy data and intermediate files to backup area. In this step, downloaded files will be archived to preserve source of the data. Discuss a location of backup area with your system administrator.

$ cp -Ru /data/Race/*.zip /data/backup_area ; cp -Ru /data/Race/*.log /data/backup_area

1.4.6 Verify that number of records loaded is equivalent to prior production load

$ sqlplus RACE_20120918/myPass@DEV_VOCAB

Execute the following SQL commands:

SELECT count(*) FROM RACE_CONCEPTS;

Current production row count is: 1500 Rows

1.5 Loading Staging Tables from raw

1.5.1 Convert and store in staging table concepts

Loaded from raw staged data RACE_CONCEPTS into the staged data CONCEPT_STAGE

In this step we will perform substitute GUID identifiers into numerical format.

$ sqlplus RACE_20120918/myPass@DEV_VOCAB @13_transform_row_concepts.sql

1.5.2 Verify that number of records loaded is equivalent to prior production load

In this step we will verify that number of active records in staging tables is valid prior to transfer data to historical DEV schema.

a. Number of Records in stage table

b. Number of records in DEV schema not deleted (active, prior load production)

c. How many records would be added to DEV schema table

d. How many active DEV records will be marked for deletion

In most cases number of records in stage (a) should be greater than number of active records in production (b)
In rare occasion that might not be the case. It is responsibility of the developer running the scripts to verify that that is the the valid.

$ sqlplus RACE_20120918/myPass@DEV_VOCAB

Execute the following SQL commands:

SELECT '- Num Rec in stage' AS scr, COUNT (8) cnt
FROM CONCEPT_STAGE c
WHERE C.VOCABULARY_ID IN (13)
UNION ALL
SELECT '- Num Rec in DEV not deleted' AS scr, COUNT (8) cnt
FROM DEV.CONCEPT d
WHERE d.VOCABULARY_ID IN (13) AND NVL (d.INVALID_REASON, 'X') <> 'D'
UNION ALL
SELECT '- How many records would be new in DEV added' AS scr, COUNT (8) cnt
FROM CONCEPT_STAGE c
WHERE C.VOCABULARY_ID IN (13)
       AND NOT EXISTS
              (SELECT 1
                 FROM DEV.CONCEPT d
                WHERE d.VOCABULARY_ID IN (13) AND c.CONCEPT_CODE = D.CONCEPT_CODE)
UNION ALL
SELECT '- How many DEV active will be marked for deletion' AS scr,
       COUNT (8) cnt
FROM DEV.CONCEPT d
WHERE     d.VOCABULARY_ID IN (13)
       AND NVL (d.INVALID_REASON, 'X') <> 'D'
       AND NOT EXISTS
              (SELECT 1
                 FROM CONCEPT_STAGE c
                WHERE c.VOCABULARY_ID IN (13) AND c.CONCEPT_CODE = D.CONCEPT_CODE);

Current Result:

- Num Rec in stage       51

- Num Rec in DEV not deleted           53

- How many records would be new in DEV added        3

- How many DEV active will be marked for deletion      5