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 列限制问题。