Vocabulary_ID=22

The following describes the procedure to update the Multilex 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/MLEX).

Script Name	Description
22_create_schema.sql	Create prestage-stage schema with tables and indexes
22_transform_row_maps.sql	Convert and store in stage table SOURCE_TO_CONCEPT_MAP_STAGE
22_transform_row_concepts.sql	Convert and store in stage table concept_stage
22_transform_row_relations.sql	Convert and store in stage table concept_relationship_stage
22_load_maps.sql	Load only new maps into SOURCE_TO_CONCEPT_MAP table, add invalid code information
22_load_concepts.sql	Load only new concepts into concept table, add invalid code information
22_load_relations.sql	Load only new relationships into relationship table, add invalid code information.
22_load_to_prestage_concepts.ctl	Concepts control file
22_load_to_prestage_ingrs.ctl	Ingredients control file
22_load_to_prestage_ingrs_dict.ctl	Terms control file
22_load_to_prestage_units.ctl	Units control file
22_load_to_prestage_units_dict.ctl	Terms control file
22_load_to_prestage_strength.ctl	Strength control file
22_load_to_prestage_formula.ctl	Formula control file
22_load_to_prestage_miss.ctl	Miss control file
22_load_to_prestage.sh	Load RxNomm raw data

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.fdbhealth.co.uk/multilex/

                    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. ASCASC801STD_201112_FULL.ZIP )
                     Transfer downloaded zip file to /data/MLEX directory.

                     Latest production zip file is ~75MB in size

         1.2.2 Download data from RxNorm source              

                The most current international release is located at the following URL: http://www.nlm.nih.gov/research/umls/rxnorm/docs/rxnormfiles.html

                Historical archives located at: http://www.nlm.nih.gov/research/umls/rxnorm/docs/rxnormarchive.html

                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. RxNorm_full_07022012.zip)
                 Transfer downloaded zip file to /data/MLEX directory.

                Zip file suffix contains release date (e.g. 07022012=02-Jul-2012)
                 Latest production zip file is ~136MB in size

                Note: For the rest of the document we will be referring to current schema as RXNORM_20120702 you would have to adjust this entry to correspond to current release date file

1.3 Build Database Schema

All tables will be located in the schema MLEX_<DateOfSourceFile> as following: MLEX_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:  MDDF_Product as well as staging tables: 

CONCEPT_STAGE, CONCEPT_ANCESTOR_STAGE, 

 

CONCEPT_RELATIONSHIP_STAGE, 

 

SOURCE_TO_CONCEPT_MAP_STAGE

$ sqlplus System/<SystemPass>@DEV_VOCAB @22_create_schema.sql <MLEX_Schema> <Pass_MLEX_Schema>

Format of  MLEX_Schema should be  MLEX_YYYYMMDD (e.g.  MLEX_20121220)

1.4 Raw data load into Oracle tables

 1.4.1 Extract files from the zip archive into the current directory

        $ unzip -u ASCASC801STD_201112_FULL.ZIP

1.4.2 Verify that the following  file(s) have been created:

     MDDF_Product.asc   (Latest file size ~20MB)

     MDDF_linkIngredient.asc   (Latest file size ~3MB)

     MDDF_dictIngredient.asc   (Latest file size ~2MB)

     MDDF_linkProductUnitOfMeasure.asc   (Latest file size ~2MB)

     MDDF_dictUnitOfMeasure.asc   (Latest file size ~1MB)

     MDDF_dictStrength.asc   (Latest file size ~300KB)

     MDDF_dictFormulation.asc   (Latest file size ~110KB)

         1.4.3    Load Multilex raw data into Oracle. 

            Each sqlldr process will run for about 1min.

             $ sqlldr MLEX_20121220/myPass@DEV_VOCAB control=22_load_to_prestage_concepts.ctl

             $ sqlldr MLEX_20121220/myPass@DEV_VOCAB control=22_load_to_prestage_ingrs.ctl

             $ sqlldr MLEX_20121220/myPass@DEV_VOCAB control=22_load_to_prestage_ingrs_dict.ctl

             $ sqlldr MLEX_20121220/myPass@DEV_VOCAB control=22_load_to_prestage_units.ctl

             $ sqlldr MLEX_20121220/myPass@DEV_VOCAB control=22_load_to_prestage_units_dict.ctl

             $ sqlldr MLEX_20121220/myPass@DEV_VOCAB control=22_load_to_prestage_strength.ctl

             $ sqlldr MLEX_20121220/myPass@DEV_VOCAB control=22_load_to_prestage_formula.ctl

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

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

                     $ sqlplus  MLEX_20121220/myPass@DEV_VOCAB

            Execute the following SQL commands:

SELECT count(*) FROM MDDF_PRODUCT;

Current production row count is:     65,817 Rows

SELECT count(*) FROM MDDF_LINKINGREDIENT;

Current production row count is:     113,273 Rows

SELECT count(*) FROM MDDF_dictIngredient;

Current production row count is:     4,006 Rows

SELECT count(*) FROM MDDF_linkProductUnitOfMeasure;

Current production row count is:     45,540 Rows

SELECT count(*) FROM MDDF_DICTUNITOFMEASURE;

Current production row count is:     123 Rows

SELECT count(*) FROM mddf_dictstrength;

Current production row count is:     5,448 Rows

1.4.6 Extract files from the zip archive into the current directory

        $ unzip -u RxNorm_full_07022012.zip -d rxnorm_20120702

1.4.7 Verify that the following 2 files have been created:

      RXNCONSO.RRF    (Latest file size ~96MB)

    RXNATOMARCHIVE.RRF    (Latest file size ~42MB)

    RXNCUI.RRF    (Latest file size ~1MB)

    RXNCUICHANGES.RRF    (Latest file size ~1MB)

    RXNDOC.RRF    (Latest file size ~1MB)

    RXNREL.RRF    (Latest file size ~230MB)

    RXNSAB.RRF    (Latest file size ~8KB)

    RXNSAT.RRF    (Latest file size ~385MB)

   RXNSTY.RRF    (Latest file size ~15MB)

        1.4.8    Load raw data into Oracle. 

        This sqlldr process will run for about 2min.

        $ 22_load_to_prestage.sh /data/MLEX/rxnorm_20130304 <MLEX_Schema>/<Pass_MLEX_Schema>

        1.4.9 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/MLEX/*.zip /data/backup_area ; cp -Ru /data/MLEX/*.log /data/backup_area

1.5 Loading Staging Tables from raw

1.5.1 Convert and store in staging table concepts

Loaded from raw staged data RXNCONSO into the staged data CONCEPT_STAGE

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

$  sqlplus MLEX_20121220/myPass@DEV_VOCAB @22_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  MLEX_20121220/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 = 22 UNION ALL SELECT '- Num Rec in DEV not deleted' AS scr, COUNT (8) cnt   FROM DEV.CONCEPT d  WHERE d.VOCABULARY_ID = 22 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 = 22        AND NOT EXISTS               (SELECT 1                  FROM DEV.CONCEPT d                 WHERE d.VOCABULARY_ID = 22 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 = 22        AND NVL (d.INVALID_REASON, 'X') <> 'D'        AND NOT EXISTS               (SELECT 1                  FROM CONCEPT_STAGE c                 WHERE c.VOCABULARY_ID = 22 AND c.CONCEPT_CODE = D.CONCEPT_CODE);

                Current Result:

- Num Rec in stage                                                   69823

- Num Rec in DEV not deleted                                     69823

- How many records would be new in DEV added            0

- How many DEV active will be marked for deletion         0

        1.5.3    Load new concepts into DEV schema concept table,mark deprecated concepts as deleted. Valid start date, valid end date and invalid reason are left empty for now.
                 Transfer records from CONCEPT_STAGE to DEV.CONCEPT table

        $  sqlplus MLEX_20121220/myPass@DEV_VOCAB @22_load_concepts.sql

        1.5.4 Convert and store in staging table maps

   Loaded from raw staged data MDDF_PRODUCT into the staged data SOURCE_TO_CONCEPT_MAP_STAGE

$  sqlplus MLEX_20121220/myPass@DEV_VOCAB @22_transform_row_maps.sql

1.5.5  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 MLEX_20121220/myPass@DEV_VOCAB

        Execute the following SQL commands:

        Execute the following SQL commands:

SELECT '- Num Rec in stage' AS scr, COUNT (8) cnt   FROM SOURCE_TO_CONCEPT_MAP_STAGE c  WHERE c.SOURCE_VOCABULARY_ID IN (22) UNION ALL SELECT '- Num Rec in DEV not deleted' AS scr, COUNT (8) cnt   FROM DEV.SOURCE_TO_CONCEPT_MAP d  WHERE     d.SOURCE_VOCABULARY_ID IN (22)        AND D.TARGET_VOCABULARY_ID IN (08)        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 SOURCE_TO_CONCEPT_MAP_STAGE c  WHERE c.SOURCE_VOCABULARY_ID IN (22) AND c.TARGET_VOCABULARY_ID IN (08)        AND NOT EXISTS                   (SELECT 1                      FROM DEV.SOURCE_TO_CONCEPT_MAP d                     WHERE     d.SOURCE_VOCABULARY_ID IN (22)                           AND c.SOURCE_CODE = D.SOURCE_CODE                           AND d.SOURCE_VOCABULARY_ID = c.SOURCE_VOCABULARY_ID                           AND d.MAPPING_TYPE = c.MAPPING_TYPE                           AND d.TARGET_CONCEPT_ID = c.TARGET_CONCEPT_ID                           AND d.TARGET_VOCABULARY_ID = c.TARGET_VOCABULARY_ID) UNION ALL SELECT '- How many DEV active will be marked for deletion' AS scr,        COUNT (8) cnt   FROM DEV.SOURCE_TO_CONCEPT_MAP d  WHERE     d.SOURCE_VOCABULARY_ID IN (22)        AND D.TARGET_VOCABULARY_ID IN (08)        AND NVL (d.INVALID_REASON, 'X') <> 'D'        AND d.VALID_START_DATE <               TO_DATE (                  SUBSTR (USER, REGEXP_INSTR (USER, '_[[:digit:]]') + 1, 256),                  'YYYYMMDD')        AND NOT EXISTS                   (SELECT 1                      FROM SOURCE_TO_CONCEPT_MAP_STAGE c                     WHERE     c.SOURCE_VOCABULARY_ID IN (22)                           AND c.SOURCE_CODE = D.SOURCE_CODE                           AND d.SOURCE_VOCABULARY_ID = c.SOURCE_VOCABULARY_ID                           AND d.MAPPING_TYPE = c.MAPPING_TYPE                           AND d.TARGET_CONCEPT_ID = c.TARGET_CONCEPT_ID                           AND d.TARGET_VOCABULARY_ID = c.TARGET_VOCABULARY_ID)        AND EXISTS               (SELECT 1                  FROM SOURCE_TO_CONCEPT_MAP_STAGE c                 WHERE     d.SOURCE_CODE = c.SOURCE_CODE                       AND d.SOURCE_VOCABULARY_ID = c.SOURCE_VOCABULARY_ID                       AND d.MAPPING_TYPE = c.MAPPING_TYPE                       AND d.TARGET_VOCABULARY_ID = c.TARGET_VOCABULARY_ID);

                Current Result:

- Num Rec in stage                                                   6400

- Num Rec in DEV not deleted                                     7333

- How many records would be new in DEV added            52

- How many DEV active will be marked for deletion          52

    1.5.6   Load new maps into DEV schema concept table,mark deprecated concepts as deleted. Valid start date, valid end date and invalid reason are left empty for now.
          Transfer records from SOURCE_TO_CONCEPT_MAP_STAGE to DEV.SOURCE_TO_CONCEPT_MAP table

    $  sqlplus MLEX_20121220/myPass@DEV_VOCAB @22_load_maps.sql

           1.5.7    Load new concept relationships stage, identify invalid codes  information 

               Loaded from the raw staged data RXNREL into the staged data CONCEPT_RELATIONSHIP_STAGE,
               identify invalid code information     

      $  sqlplus MLEX_20121220/myPass@DEV_VOCAB @22_transform_row_relations.sql


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

         $ sqlplus MLEX_20121220/myPass@DEV_VOCAB

SELECT '- Num Rec in stage' AS scr, COUNT (8) cnt   FROM CONCEPT_RELATIONSHIP_STAGE c UNION ALL SELECT '- Num Rec in DEV not deleted' AS scr, COUNT (8) cnt   FROM DEV.CONCEPT_RELATIONSHIP d  WHERE EXISTS           (SELECT 1              FROM DEV.CONCEPT c             WHERE c.VOCABULARY_ID IN (08, 22) AND c.CONCEPT_ID = CONCEPT_ID_1)        AND EXISTS               (SELECT 1                  FROM DEV.CONCEPT c                 WHERE c.VOCABULARY_ID IN (08, 20)                       AND c.CONCEPT_ID = CONCEPT_ID_2)        AND NVL (d.INVALID_REASON, 'X') <> 'D'        AND D.RELATIONSHIP_ID IN (315, 316, 317, 318, 319, 320) UNION ALL SELECT '- How many records would be new in DEV added' AS scr, COUNT (8) cnt   FROM CONCEPT_RELATIONSHIP_STAGE c  WHERE NOT EXISTS               (SELECT 1                  FROM DEV.CONCEPT_RELATIONSHIP d                 WHERE     c.CONCEPT_ID_1 = d.CONCEPT_ID_1                       AND c.CONCEPT_ID_2 = d.CONCEPT_ID_2                       AND c.RELATIONSHIP_ID = d.RELATIONSHIP_ID) UNION ALL SELECT '- How many DEV active will be marked for deletion' AS scr,        COUNT (8) cnt   FROM DEV.CONCEPT_RELATIONSHIP d  WHERE EXISTS           (SELECT 1              FROM DEV.CONCEPT c             WHERE c.VOCABULARY_ID IN (08, 22) AND c.CONCEPT_ID = CONCEPT_ID_1)        AND EXISTS               (SELECT 1                  FROM DEV.CONCEPT c                 WHERE c.VOCABULARY_ID IN (08, 22)                       AND c.CONCEPT_ID = CONCEPT_ID_2)        AND NVL (d.INVALID_REASON, 'X') <> 'D'        AND NOT EXISTS                   (SELECT 1                      FROM CONCEPT_RELATIONSHIP_STAGE c                     WHERE     c.CONCEPT_ID_1 = d.CONCEPT_ID_1                           AND c.CONCEPT_ID_2 = d.CONCEPT_ID_2                           AND c.RELATIONSHIP_ID = d.RELATIONSHIP_ID)        AND D.RELATIONSHIP_ID IN (315, 316, 317, 318, 319, 320);

Current Result:

- Num Rec in stage	188162
- Num Rec in DEV not deleted	20642
- How many records would be new in DEV added	180
- How many DEV active will be marked for deletion	11408

 

1.5.9    Load new concept relationships into DEV schema,mark deprecated concept relatuionships as deleted

         Transfer records from CONCEPT_RELATIONSHIP_STAGE to DEV.CONCEPT_RELATIONSHIP table       

     $  sqlplusMLEX_20121220/myPass@DEV_VOCAB @22_load_relations.sql