facts 100年代事实表设计指南

Question

我正试图为医疗保健应用程序创建一个datamart。datamart中的事实基本上是与心脏相关的测量和发现，我们有1000多个。从1000开始，每个考试类型最多可以达到20000。

我想知道我对事实表的设计选择是什么：

谷物:每个病人一排，每种检查类型。

我能想到的一些选择-

( 1)有1000或更多列的大而宽的事实表。

2)基于EAV的设计--一个独立的量纲表。这个外键将进入事实表，度量值将在事实表中。因此，事实表格的粒度将被更改为每名患者每一次测量的检查类型为1行。

3)为每个考试类型创建较小的多个事实表，以及其他一些标准，如子组。但是最终用户将跨子组查询该考试类型，并且不建议使用事实联接。

4)还有其他想法吗？

如有任何投入，将不胜感激。

Answer 1

1.一个包含1000或更多列的大的宽事实表.

如果查询直接在数据仓库中执行，那么一个非常宽的事实表可以使最终用户获得最大的灵活性。但是，应该考虑一些因素，因为根据平台的不同，您可能会遇到一些限制。

Server 2014限制如下：

• 每行8,060字节。行溢出存储可能是一种解决方案，但它只支持少数与事实性质无关的列类型，即varchar、nvarchar、varbinary、sql_variant。也不支持内存中的OLTP。https://technet.microsoft.com/en-us/library/ms186981(v=sql.105).aspx
• 每个非宽表1024列。宽表和稀疏列是解决方案，因为每个宽表限制为30,000列。但是，每行限制的字节数是相同的。https://technet.microsoft.com/en-us/library/cc280604(v=sql.120).aspx
• 每个SELECT/INSERT/UPDATE语句4,096列
• 表999中的非聚集索引

https://technet.microsoft.com/en-us/library/ms143432(v=sql.120).aspx

基于2. EAV的设计--一个独立的量纲表.这个外键将进入事实表，度量值将在事实表中。因此，事实表格的粒度将更改为每名患者每一次测量1行。。

根据Kimball的说法，EAV设计被称为事实规范化。当大量的测量非常长，但对于给定的事实却很少有人参与，并且在事实之间不进行计算时，这可能是有意义的。

因此，由于事实是标准化的：

• 可扩展性非常容易，即无需修改数据结构就很容易添加新的度量。
• 为一次考试提取所有测量值，并将测量结果显示为屏幕上的行是很好的。
• 很难在多个度量之间提取/聚合/计算(例如，平均HDL到CHOL比率)，并且将度量/聚合/计算作为列来进行，即需要复杂的位置/旋转或多连接。SQL使在不同行中的事实之间难以进行计算。
• 如果主终端用户平台是OLAP多维数据集，那么事实规范化是有意义的。立方体允许跨任何维度进行计算。
• 如果数据格式是平面样式的CSV，则数据导入可能是一个问题。

Should I use EAV model?在这里也讨论了这个问题。

3)为每个考试类型创建较小的多个事实表，以及其他一些标准，如子组。但是最终用户将跨子组查询该考试类型，并且不建议使用事实联接。

在某些情况下，多个较小的事实表非常有意义。原因之一是如果您遇到了平台设置的物理限制，例如每一行的Bytes。

这些事实可以按主题领域，例如测量组/分组，或按使用频率进行分组。每个表可以放在一个单独的文件组上，并驱动以使I/O最大化。

此外，您可以在不同的事实表之间重复度量，以减少事实表连接的需求，即将一个度量放在特定的度量子组事实表和常用的度量事实表中。

但是，如果对数据加载有一些特定的要求，则应该考虑一些考虑因素。例如，如果将ETL中的记录错误输出到一个事实表中，则可能希望确保删除其他事实表中的相应记录，并将其放置到错误表中，这样就不会得到任何虚假信息。如果终端用户在前端工具中有自己的计算，则尤其如此。

如果使用OLAP多维数据集，那么多个事实表实际上将成为特定事实表的度量值组的源。

就事实到事实连接而言，您(BI应用程序)不应该发出SQL，将两个事实数据表跨事实表的外键连接在一起。相反，应该使用跨两个事实数据表的钻取技术，其中分别创建来自两个或多个事实表的答案集，并在公共行标题属性值上合并结果排序，以生成正确的结果。

有关此主题的更多信息：http://www.kimballgroup.com/2003/04/the-soul-of-the-data-warehouse-part-two-drilling-across/

4)还有其他想法吗？

SQL或某种NoSQL可以是一个选项，但同样存在查询/聚合/计算/表示问题。