OpenClinica 使用者手冊/社群資料倉庫

OpenClinica 社群資料倉庫

要下載，請參見 github：https://github.com/lindsay-stevens/openclinica_sqldatamart

依賴項

1. Windows 作業系統（在 Server 2008 R 2、64 位和 Windows 7 上測試）
2. Progress 在 9.2.2（64 位）上測試）
3. Progress ODBC 驅動程式（在 9.02.0100 上測試，安裝了 32 位和 64 位版本）
4. 7-zip
5. Access 執行時（在 2010，32 位上測試）或 MS Office

包

1. oc_warehousing sql 指令碼（為所有研究生成資料集）
2. oc_access_processor 資料庫（為了自定義它，您需要一個 MS Office 的副本）
   1. 假設您想按原樣使用它，壓縮檔案包含預打包的安裝程式。
3. 批處理指令碼，將每日生產備份還原到目標伺服器，然後執行 sql 指令碼和 Access 資料庫。

特性

描述

該專案的目標包括

• 在 OpenClinica 資料庫中以 SQL 形式提供所有資料，而無需透過 ODM XML 提取作為中間資料來源，或受其包含內容的限制。
• 提供所有相關程式碼以進行自定義和錯誤修復。生成 ODM XML 的程式碼是開源的，但 OpenClinica 企業資料倉庫的程式碼不是。

提供的 SQL 指令碼和 Access 資料庫將 OpenClinica 資料庫中的資料處理成一組研究元資料表，以及每個條目組一張表，資料以樞軸方式進行處理，變數位於自己的列中。

輸出

1. 為每項研究建立了一個架構。
2. 為每個架構建立了一個角色；該角色只能訪問該架構。這些角色旨在限制資料庫連線的範圍以避免錯誤，而不是作為安全措施。
3. 為每個條目組建立一個表。
4. 在每個條目組表中，為條目組中的每個條目建立一個列。如果條目使用響應選項，則會為該條目建立一個伴隨列。如果條目使用多選，則會為每個具有值的響應建立這對列。
5. 建立了包含以下資料的其他表
   1. clinicaldata：已反規範化的資料集，已轉換為條目組資料
   2. metadata：已反規範化的資料集，用於條目組表定義
   3. subjects：不同的受試者（來源：a）
   4. subject_event_crf_status：不同的受試者/事件/CRF 資訊 (a)
   5. subject_event_crf_expected：受試者 (a) 和事件 CRF (b) 交叉連線
   6. subject_event_crf_join：預期 (e) 和狀態 (d) 左連線
   7. discrepancy_notes_parent：所有實體型別上的父 DN，如果未關閉或為 n/a 則為年齡
   8. discrepancy_notes_all：所有實體型別上的所有 DN
   9. subject_groups：受試者組分配
   10. response_set_labels：程式碼值/標籤對

自 2013-06-18 以來變更

資料倉庫指令碼

已完成

• 修復了由 >1 SDV 狀態更改導致的重複問題（現在採用最新的更改）
• 添加了 crf_section_label 並將 crf_section 重新命名為 crf_section_title
• 將 event_status 邏輯替換為僅顯示 subject_event_status.name
• 將基於日期的 crf_status 邏輯替換為來自 openclinica 介面的邏輯
• 在每個表之後新增分析，這加快了 dm.clinicaldata 的建立速度
• 在 user_account.user_id 上新增索引
• 在 clinicaldata 中新增過濾器，以便不包含任何已刪除的實體（受試者、CRF 等）
• 將 cd_no_labels 拆分為兩個步驟，以便正確檢索站點事件定義
• 添加了對多值欄位的處理 - 這些欄位將使用響應值（如 item_oid_44）扁平化
• 為受試者、研究事件、事件 CRF 和條目添加了 owner_id 和 update_id，並使用使用者帳戶名稱
• 添加了包含元資料和臨床資料之外資訊的額外表

待辦事項

• 空值處理：目前，當資料轉換時，非文字中的空值程式碼會被刪除，因為型別不正確（OC 資料倉庫也不處理空值程式碼）。

Access 資料庫

• 添加了獲取 dm.clinicaldata 中研究列表併為所有研究執行該過程的功能
• 添加了在 postgres 中建立物件並將所有資料插入的功能（如果您想將插入語句池化，請注意 Access 限制每個語句傳送 64000 個字元，即使在 passthru 查詢上也是如此）
• 添加了用於設定 odbc 引數的控制面板
• 添加了匯入和連結表功能
• 將行標題定義移動到一個地方，以便更容易更改

待辦事項

• 歡迎提出建議

安裝部分 1

1. 在伺服器上安裝 Access 執行時（如果尚未安裝 MS Office）
2. 編輯 postgres 配置（postgresql.conf 和 pg_hba.conf）以允許遠端連線，例如允許本地、所有或允許來自您網路中的 IP 的連線等。
3. 配置 oc_access_processor
   1. 輸入您的 postgres 資料庫的詳細資訊。
   2. 如果您想在每個條目組資料集中的標準列中新增內容，請修改 mod_populate_rowheaders 中的陣列。確保 public rowheaders 變數中的維度與元素數量相同（預設值為 13x2）。
4. 使用 Access 將 oc_access_processor 打包到一個可安裝的執行時檔案中，並將其安裝到伺服器上。如果伺服器上有 MS Office 的副本，只需將其複製到伺服器即可。
   1. 如果在 Access 2010 中打包，您必須新增一個登錄檔項，以避免打包程式中的一個錯誤，該錯誤會導致執行時使用者無法永久信任資料庫。這意味著每次開啟資料庫時都會顯示一個隱私警告，從而停止程序，直到手動確認該警告。所需的登錄檔項設定如下
      1. 根 - “當前使用者”
      2. 鍵 - “Software\Microsoft\Office\14.0\Access\Security\Trusted Locations\2013-09-20 oc access processor”
         1. 根據應用程式名稱更改最後部分
      3. 名稱 - “路徑”
      4. 值 - “[DATABASEDIR]”
5. 修改批處理檔案以適合您的檔案位置。它需要還原每日備份（最好在不同的伺服器上），執行資料倉庫指令碼並開啟 Access 資料庫並執行“run_get_study_list”宏。
6. 在每日備份執行後，新增計劃任務執行批處理指令碼，例如在 30 分鐘後。

oc_access_processor 中保留了一個日誌，其中記錄了每個處理的研究的開始時間和結束時間。它在第一次使用時自動建立。當日志中有 150 個或更多條目時，將刪除最舊的 25% 條目。

安裝部分 2

設定完成後，研究資料將每天重新整理，並在其他程式中可以使用。

為了讓其他 Access 資料庫連線到 postgres 並獲取研究資料的副本，請執行以下操作

1. 確保在每臺客戶端機器上都安裝了 postgres ODBC 驅動程式。
2. 將以下物件匯入 Access 資料庫（s）
   1. mod_make_locallinked_table_list 模組
   2. mod_make_locallinked_table 模組
   3. mod_process_text 模組
   4. mod_object_exists 模組
   5. mod_open_pg_odbc_conn 模組
   6. control_panel 表單（匯入後移除所有操作按鈕，或者只匯入 import_link_tables 宏以呼叫 make_locallinked_table_list 並用雙引號給出研究名稱）。
   7. oc_table_selection 表單（此表單應隱藏）
   8. odbc_connection 表格（此表格應隱藏）

“匯入/連結表格”操作會開啟一個表單，允許使用者從其研究模式中選擇一個表格列表，並建立相應的連結表格，或建立遠端表格的本地表格副本。表格列表儲存為查詢定義（稱為 oc_tables_to_copy）。兩種操作都會刪除具有相同名稱的現有定義，因此請記住這一點，因為它會影響任何已定義的關係。

已知問題

命名約定

除了 OpenClinica 強加的限制之外，命名限制如下。函式“process_text”將字串截斷為 45 個字元，並在刪除任何非字母數字字元後將空格替換為下劃線。

1. 研究名稱將透過 process_text 執行，因此每個研究名稱的前 45 個字元都需要是唯一的。OpenClinica 允許 255 個字元。處理後的名稱用於為每個研究建立的角色名稱和密碼。
2. 專案名稱轉換為小寫，並截斷為 12 個字元。OpenClinica 允許 255 個字元。
   1. 假設專案名稱使用最多 8 個字母數字字元（允許下劃線）以實現 CDISC / SAS 相容性。沒有強制執行，尚未測試使用更長的專案名稱會發生什麼。
   2. 另外 4 個字元是為多值欄位預留的。它們的專案名稱被修改為在 下劃線後面新增編碼值。例如，專案名稱“MYMULTI”具有編碼響應 99，表示“something”；專案名稱變為“mymulti_99”。更長的多值程式碼需要在頭 3 個字元內是唯一的（尚未測試）。
3. 專案描述將透過 process_text 執行。OpenClinica 允許 4000 個字元。
4. 專案組表格中的專案變數列名由處理後的專案名稱和專案描述組合而成。例如，專案名稱“MYITEM”描述“My first OpenClinica item which collects my favourite variable”將獲得列名“myitem_my_first_openclinica_item_which_collects_my_f”，這突出了簡短、有意義的專案描述的優點。

空值程式碼

此階段尚未新增對空值程式碼的處理。如果使用它們，並且專案不是字串，Access 將丟擲型別不匹配錯誤，並且不會插入值（例如，無法將“NI”插入整數字段）。目前，如果在預期位置發生型別不匹配錯誤，transform_insert_ig_data 函式將恢復。

查詢空值程式碼的一種方法是篩選允許空值程式碼的臨床資料表，並篩選專案值列以查詢包含任何這些空值程式碼的值。

為了避免此問題，請新增一個專案或響應列表選項來捕獲缺少值的原因，而不是使用空值程式碼功能，或者在註釋差異說明中新增解釋。

專案組大小

Access 無法處理具有超過 255 列的表格或查詢。這是否是一個問題取決於專案數量，其中有多少是單選，以及有多少是多選。例如

• 這沒問題：13 個行標題 + 100 個單選專案 + 100 個程式碼標籤列 = 213 列。
• 這沒問題：13 個行標題 + 15 個多值專案，每個專案有 6 個響應選項（結果最多 90 列，取決於響應）+（最多）90 個程式碼標籤列 = 193 列。
• 這沒問題：13 個行標題 + 10 個日期欄位 + 10 個單選專案 + 10 個整數字段 + 10 個浮點欄位 + 5 個多值專案，每個專案有 10 個響應選項（結果最多 50 列，取決於響應）+（最多）60 個程式碼標籤列 = 163 列。

在開發過程中遇到了此問題；最簡單的解決方法是新增更新查詢，這些查詢根據專案的表單順序對專案組專案進行序列化。這些更新查詢已被註釋掉，但仍然存在，並且在 make_local_clinicaldata、make_local_metadata、make_local_item_metadata 函式中將需要這些查詢。

未來改進

一些進一步改進的想法包括

文字匯出

將資料寫入 SQL 檔案或一組 CSV 檔案，以便匯入到其他 RDBMS 或程式中。可以使用 Microsoft Scripting Runtime 庫從 Access 中寫入文字檔案，主要優點是 DDL 和 DML 語句都可以包含在內，就像 Enterprise Data Mart 可下載檔案一樣。使用 Postgres 的 COPY 函式建立 CSV 可能更簡單。或者可以設定 ODBC 連線。

為 Linux 相容性重寫

使用更易於理解的語言（如 java 或 python）重新編寫 VBA 程式碼，這些語言可以在 linux 上執行。除了個人滿足感之外，我目前沒有其他目的，所以可能需要一段時間。

主要障礙是複製 Access 交叉表/透視功能，這可以透過使用一個巨大的 CASE…WHEN 樣式的透視來一次性完成。這無法從 Access 完成，因為它無法傳送超過 64000 個字元長的 SQL 語句。Postgres 具有更大的語句大小限制。

另一種方法是插入不同的行標題資料，然後使用一系列 UPDATE 語句新增每一列，這也將避免/放鬆列限制問題，因為 Postgres 的限制在 250 到 1600 之間，具體取決於資料。

新增空值程式碼處理

雖然我避免使用空值程式碼，但一些使用者可能重視此功能，並希望空值型別資訊與其他資料一樣可用。一種可能的實現方法是為任何存在空值程式碼的列新增一個用於空值程式碼字串的列；但是，這可能會加劇 Access 的每表格 255 列限制問題。