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

No PATIENT_NUM in OBSERVATION_FACT_PK (conflict with documentation)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.6.08
    • Fix Version/s: 1.7.00
    • Component/s: CRC Cell, Data
    • Labels:
      None
    • Environment:
      Both SQL Server and Oracle, OS independent.
    • Rank:
      0|i000pj:

      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

            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Git Source Code