CDISC递交数据--SDTM Dataset Metadata之SUPPQUAL —Primary key

4.1.1.9 Assigning Natural Keys in the Metadata

SDTMIG V3.2版本以PE举例，3.4以MK举例。

Physical Examination (PE) domain example

Musculoskeletal System Findings (MK) Domain Example

以SDTMIG V3.2为主：

Section 3: 3.2, Using the CDISC Domain Models in Regulatory Submissions - Dataset Metadata说明natural key属于sponsor递交数据集metadata的一部分。datasets中每条records可能由不同的natural key来描述独立性，那么则需要提供最完整描述dataset structure的natural key。（因为records级别的natural keys可能不同，所以在datasets级别需要做一个全面的涵盖所有records的natural keys）下面示例介绍了如何定义natural keys，并且是包含Supplemental Qualifier（补充修饰）变量（组成natural key的一部分）。

体格检查（PE）：

Sponsor A 选择以下变量作为PE的natural key：

STUDYID, USUBJID, VISTNUM, PETESTCD

Sponsor B 则是另一种方式收集数据，location(PELOC)和method(PEMETHOD)变量需要包含在natural key里，以标识唯一观测行，但是没有收集访视(VISIT)变量；而是使用访视日期(PEDTC)对数据进行排序。所以Sponsor B 定义以下变量为PE的natural key。

STUDYID, USUBJID, PEDTC, PETESTCD, PELOC, PEMETHOD

在某些情况下，补充修饰变量（例如：QNAM 中的某个值，Section 8: 8.4, Relating Non-Standard Variables Values To A Parent Domain）也可以是观测的natural key，因此可以作为domain的natural key的一部分。这里需要特别注意的是，domain并不受physical structure的限制（natural keys是用来描述数据集的physical structure，而一般的natural keys由standard variables构成，故此处可理解为可添加SUPPQUAL变量）。一个domain可能由多个数据集构成，例如：main domain(父域) 数据集和与相关的Supplemental Qualifiers(子域)数据集。应该使用两部分名称将补充修饰变量也列在natural key里面。单词“QNAM” 应该作为第一部分名称使用，以说明在数据集中存在此变量（比如某对应domian的SUPP--数据集的QNAM，或者general SUPPQUAL 数据集里的QNAM）【此处标黄部分为3.2版本解释，3.4版本：QNAM来自对应的SUPP--数据集中的QNAM】，当SUPPQUAL记录（即与domain对应的SUPP--数据集）与对应的main domain数据集联合起来的时候，QNAM 的值最终会作为变量名（例如：QNAM.XVAR，当SUPP--数据集的一条记录含有QNAM 的值为“XVAR”）。

        接着上面的PE示例，Sponsor B 可能会使用超声检查作为测量方法，并且会收集一些关于使用的设备的额外信息（如：制造商、模式）。Sponsor认为“制造商和模式”信息是必要的数据有助于检查数据的唯一性，所以创建补充修饰变量“制造商（QNAM=PEMAKE）”和“模式（QNAM=PEMODEL）”。natural key定义为以下几个变量（本例只为展示natural key，真实数据模型需遵循SDTMIG-MD）:

STUDYID, USUBJID, PEDTC, PETESTCD, PELOC, PEMETHOD, QNAM.PEMAKE,

QNAM.PEMODEL

对于Finading类domain，当Sponsor选择使用generic（通用型）的--TESTCD 值而非复合--TESTCD 值时，这种方法是非常有用的。【3.4：当--TESTCD值是“generic”并且依赖于其他变量来完整地描述test时，这种方法在finding 类domain中非常有用。】使用generic test code，有助于为--TESTCD 变量创建独立的可管理控制术语(CT)列表。在需要做多种重复检查或测量的研究中（例如类风湿性关节炎研究中，需要使用X-线和MRI 设备对手和手腕的骨侵蚀进行多次重复检查），

记录这种数据的方法是对每种测量创建单独--TESTCD 值。【3.2】

用generic --TESTCD 和其他变量一起识别结果。【3.4】

仅对于趾骨，为确保其唯一性，Sponsor可能用以下test code来说明：

左手或右手

趾骨位置（近端/远端/中间）

手的旋转（方向）

测量方法（X-线/MRI）

机器制造商

机器模式

不建议把上述所有的信息都填充--TESTCD来创造一个唯一值，原因如下：(3.2)

当一个test的CDISC 控制术语不可用，并且Sponsor创造了--TEST和--TESTCD值时，将test的所有信息记录在一个唯一的--TESTCD值中是不推荐的方法，原因如下：(3.4)

可能会产生大量的test codes（--TESTCD）。

8 位字符的--TESTCD 值变的无意义

多种test code都是代表同一种检查或测量，--TESTCD 值变成了只是单纯存放检查的属性数据（例如：--TESTCD 值只是为了说明采取的测量的身体位置）。

综上所述，推荐的方法是使用generic的（或简单的）test code以及一些相关的修饰变量来说明test的详细信息。使用这种方法来说明上面的示例为：--TESTCD 值为“EROSION”，其他的test codes值使用一些不同的修饰变量。可能会包含一些在SDTM IG domain中存在的变量（--LOC、--METHOD 等）和补充修饰变量（QNAM.MAKE、QNAM.MODEL 等）。在这种情况下，这些变量需要保持test的唯一性，所以说明natural key很重要。

如果使用generic的--TESTCD，下面的变量可以完全描述检查。检查是“EROSION”，位置是“Left MCP I”，测量方法是“Ultrasound”，超声设备制造商是“ACME”，超声设备的模式是“u2.1”。这个domain中包含SDTM IG domain已存在的变量和补充修饰变量，这些变量组成了每一行的natural key并描述其唯一性。

图片

补充：

--SEQ variable，surrogate key对于跨数据集(比如从SUPPQUAL链接回它们的父域)或使用RELREC数据集时非常有用。但是，surrogate key变量不应用作domain的key。TS domain是一个显著的例外。

SUPPQUAL数据集

SDTM model有一种利用补充限定符(SUPPQUAL)数据集来利用非标准(non-standard)变量的特殊方法。SUPPQUAL数据集中的QNAM和QLABEL变量表示新的非标准变量的名称和标签，QVAL存储其值。通过RDOMAIN, USUBJID, IDVAR和IDVARVAL变量来连接父域。在大多数情况下，surrogate key --SEQ用于标识父记录(例如，IDVAR= ‘AESEQ ')。

总结：

1.为何使用SUPPQUAL变量来作为Natural key？

因为standard variables不足以完整的描述收集来的数据的结构，所以需要增加SUPPQUAL变量体现数据的唯一性。

2.什么时候使用SUPPQUAL变量作为Key？

a.SDTM版本outdated，新版本的Standard variables旧版本没有，只能放到SUPPQUAL。

b.例如，SDTM有一个变量--LAT，这意味着在一个受试者体外如“左腿”和“右腿”偏侧(Laterality) (如“左”或“右”)。在临床前研究中，有一个额外的概念，即在一个器官内而不是在受试者体外的偏侧，如“肝脏左侧”与“肝脏右侧”。Standard --LAT变量不适用于这种情况。预期会使用新的非标准变量。也许SDTM模型可以引入相似的变量作为标准变量。在此之前，唯一的选择是将它们存储在SUPPQUAL数据集中。

c.如果超过200字符长度，也会把变量拆分，比如DVTERM, QNAM.DVTERM1, QNAM.DVTERM2….

3.什么时候不能使用SUPPQUAL变量作为Key？

有一些标准的SDTM变量使用起来极其灵活，有时可以用来代替SUPPQUAL数据集。

--SPID变量是Sponsor定义的标识符。SDTM IG认为其不仅是标识符，也有跨domains的意义。

例如，以前许多用户使用--SPID变量来追溯Vital Signs的重复测量。正式来说，它被认为是糟糕的SDTM映射实践，因为重复编号是Record Qualifier而不是Identifier。因此，CDISC在SDTM-IG 3.3中增加了新的--REPNUM变量，防止--SPID变量的误用。

然而，在实践中，大多数用户更倾向于在父域而不是SUPPQUAL数据集中保存可能包含domain keys的重要信息。虽然这违反了SDTM model，但将所有核心信息集中在一起的好处促使user选择这种 *** 作。

虽然我的主业是实时计算和批量计算，并不是数仓，但是在日常工作中绝对少不了与数仓打交道。并且我也算是参与过离线数仓建设的，维度建模的基础还是不能忘。本文就作为一篇抄书笔记吧。

顾名思义，缓慢变化维度（slowly changing dimension, SCD）就是数据仓库维度表中，那些随时间变化比较不明显，但仍然会发生变化的维度。考虑以下两个情境：

处理缓慢变化维度是Kimball数仓体系中永恒的话题，因为数据仓库的本质，以及维度表在维度建模中的基础作用，我们几乎总是要跟踪维度的变更（change tracking），以保留历史，并提供准确的查询和分析结果。在《The Data Warehouse Toolkit, 3rd Edition》一书的第5章，Kimball提出了多种缓慢变化维度的类型和处理方法，其中前五种是原生的，后面的方法都是混合方法（hybrid techniques），因此下面来看看前五种，即Type 0~Type 4。

一种特殊的SCD类型，即不管维度属性的实际值如何变化，数仓中维度的值都会维持第一次的值。它主要适用于那些本身含义就是“原始值”（original）的维度，比如在用户维度表中，用户注册时使用的原始用户名（original_user_name）。如果它发生变化，那么变化后的值是无效的，会被抛弃。

最简单的SCD类型，即一旦维度属性的实际值发生变化，就会直接覆写到数仓中。数仓中的维度属性总是且仅仅保存着最近一次变更的值（most recent assignment）。书中的例子如下：

在上图中，Department Name维度发生了变化，并且新值直接覆盖了上一次的值。虽然它很容易实现，但是这样做会丢掉所有变更历史，并且在跨时域查询时，有可能会得到错误的结果。在实际 *** 作中，这种方式几乎总是一种不良设计。

最主要、最常用的SCD类型，在我们日常以Hive为基础的数仓建设过程中，体现为拉链表技术。

这种类型在维度表中添加两个辅助列：该行的有效日期（effective date）和过期日期（expiration date），分别指示该行从哪个时间点开始生效，以及在哪个时间点过后会变为无效。每当一个或多个维度发生更改时，就创建一个新的行，新行包含有修改后的维度值，而旧行包含有修改前的维度值，且旧行的过期日期也会同步修改。书中的例子如下：

在上图中，当前有效列（current列）的过期日期会被记录为9999-12-31。当Department Name维度变化时，旧有的Product Key为12345的行的过期日期被更新为修改日期，并且新建了一个Key为25984的行，包含新的数据。

需要注意的是，这里的Product Key是所谓代理键（surrogate key），即不表示具体业务含义，而只是代表表内数据行的唯一ID。在处理SCD时，代理键可以直接用来区分同一自然键（natural key）的数据的新旧版本。上图中的SKU就是自然键。

这种类型的SCD处理方式能够非常有效且精确地保留历史与反映变更，但缺点是会造成数据的膨胀，因为即使只有一个维度变化，也要创建新行。

Type 2虽然非常好，但是当要在同一个时间维度内把新值和旧值关联起来时，就没有那么方便了。比如在上一节的表中，如果查询2013年2月1日以后的记录，就只能查到Department Name为“Strategy”的记录，而“Education”就被屏蔽了。Type 3就是一种与Type 2互补的类型。在Type 3的处理方法中，不会添加新行，而会添加一个新的属性列，该属性列中保存有对应维度的上一次变化的值。书中的例子如下：

在上图中新增了一个名称为“Prior Department Name”的列，保存着上一次变更的值。这样也解决了Type 2的数据膨胀问题，但是就只能保存一次变更历史，称为“变更现实”（alternate realities）。

另外仍然要注意，如果维度表中的许多维度都会发生类似的变更，那么就要新增很多列，这显然不太靠谱。所以这种类型经常用来处理那种变化可预测的（predictable）、“牵一发而动全身”的少数SCD。

当然，也可以根据实际需求新增多个列来保存多次变更历史：

当维度的变化没有那么“缓慢”时，前面三种类型的处理就都显得力不从心了（特别是对于规模非常大的维度表，比如有百万甚至千万行）。这种维度一般就不再称为SCD，而称为“快速变化维度”（rapidly changing dimensions, RCD）。当RCD的规模比较小时，还能够采用Type 2或者Type 3来撑着，但规模很大时，就只能采用Type 4了。Type 4的方式是将那些快速变化的维度从原来的大维度表中拆分出来单独处理，是为微维度（mini-dimension）。

以书中的内容为例，如果顾客维度中有一部分人口统计学（demographic）维度是RCD，就将它们拆成单独的维度表：

其中，微维度表的维度最好是少量、分段的（banded）离散值，例如：

下表仍然来自《数据仓库工具箱》的原文。注意其中除了Type 0~4之外，还有三种混合方式，即Type 5~7。

最后善意提醒，《数据仓库工具箱（第三版）》这本书一定要读英文原版，千万不要读中译本。中译本错误百出，很多地方读起来都不通顺，令人窒息。

民那晚安~

---