Uploaded image for project: 'i2b2 Core Software'
  1. i2b2 Core Software
  2. CORE-81

No PATIENT_NUM in OBSERVATION_FACT_PK (conflict with documentation)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.6.08
    • 1.7.00
    • CRC Cell, Data
    • None
    • Both SQL Server and Oracle, OS independent.

    Description

      There is difference between what's documented in the CRC Design Document (p 42) and the actual DB constraints.

      According to this document, the PK includes:
       Encounter number
       Patient number
       Concept code
       Start date
       Modifier code
       Observer code

      However, in reality[1], it consists of:
       ENCOUNTER_NUM,
       CONCEPT_CD,
       PROVIDER_ID,
       START_DATE,
       MODIFIER_CD,
       INSTANCE_NUM

      I think it would make sense to at least add the patient_num to the pk. This could be necessary when the encounter and provider are unknown and point to a default row in the dimension tables. I don't have a strong preference on how to deal with the remaining differences.

      [1]:
      NewInstall/Crcdata/scripts/crc_create_datamart_oracle.sql:140: CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY(ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)
      NewInstall/Crcdata/scripts/crc_create_datamart_sqlserver.sql:138: CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY nonclustered (ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM)
      Upgrade/Crcdata/scripts/crc_create_datamart_oracle.sql:21:drop CONSTRAINT OBSERVATION_FACT_PK
      Upgrade/Crcdata/scripts/crc_create_datamart_oracle.sql:29:add CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY (ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)
      Upgrade/Crcdata/scripts/crc_create_datamart_sqlserver.sql:41:drop CONSTRAINT OBSERVATION_FACT_PK
      Upgrade/Crcdata/scripts/crc_create_datamart_sqlserver.sql:82:add CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY (ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)

      Attachments

        Activity

          People

            jmd86 Janice Donahoe
            bdevloed Boris De Vloed
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: