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.
    • 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

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

            Dates

              Created:
              Updated:
              Resolved: