处理数据仓库中的空值

Question

我想询问您有关数据仓库和 SSIS/SSAS 时处理 null 或空数据值的最佳实践的意见。

我有几个事实表和维度表，它们在不同的行中包含空值。

细节：

1) 处理空日期/时间值的最佳方法是什么？ 我是否应该在时间或日期维度中创建一个“默认”行，并在发现空值时将 SSIS 指向默认行？

2) 处理维度数据内的空值/空值的最佳方法是什么。 例如：“帐户”维度中的一些行在“帐户名称”列中具有空（非 NULL）值。 我应该将列内的这些空值或空值转换为特定的默认值吗？

3) 与上面的第 1 点类似 - 如果我最终得到的 Facttable 行在维度列之一中没有记录，我该怎么办？ 如果发生这种情况，我是否需要每个维度的默认维度记录？

4) 关于如何在 Sql 服务器集成服务 (SSIS) 中处理这些操作有什么建议或提示吗？ 使用最佳数据流配置或最佳转换对象会很有帮助。

谢谢 ：-）

Answer 1

正如前面的答案所述，维度的空值可能有许多不同的含义，未知、不适用、未知等。如果能够在应用程序中区分它们，添加“伪”维度条目会有所帮助。

在任何情况下，我都会避免使用空事实外键或维度字段，即使有一个“未知”维度值也将帮助您的用户定义包含包罗万象的分组的查询，其中数据质量不是 100%（并且它从来没有）。

我一直在使用的一个非常简单的技巧是使用 T-sql 中的 int IDENTITY(1,1) 定义维度代理键（从 1 开始，每行增加 1）。 伪键（“不可用”、“未分配”、“不适用”）被定义为负整数，并由在 ETL 过程开始时运行的存储过程填充。

例如，创建的表为

    CREATE TABLE [dbo].[Location]
    (
        [LocationSK] [int] IDENTITY(1,1) NOT NULL,
        [Name] [varchar](50) NOT NULL,
        [Abbreviation] [varchar](4) NOT NULL,
        [LocationBK] [int] NOT NULL,
        [EffectiveFromDate] [datetime] NOT NULL,
        [EffectiveToDate] [datetime] NULL,
        [Type1Checksum] [int] NOT NULL,
        [Type2Checksum] [int] NOT NULL,
    ) ON [PRIMARY]

以及填充该表的存储过程，

Insert Into dbo.Location (LocationSK, Name, Abbreviation, LocationBK, 
                      EffectiveFromDate,  Type1Checksum, Type2Checksum)
            Values (-1, 'Unknown location', 'Unk', -1, '1900-01-01', 0,0)

我规定每个维度至少有一个这样的伪行，在维度查找失败的情况下使用，并构建异常报告来跟踪维度的数量分配给这些行的事实。

Answer 2

1. NULL 或日期维度中具有适当含义的保留 ID。 请记住，NULL 实际上可以有许多不同的含义，它可能是未知的、不适用的、无效的等。

2. 我更喜欢空字符串（并且不可为 NULL），但在我现在正在处理的项目中，将空字符串转换为 NULL 并允许它们进入数据库。 要讨论的一个潜在问题是空白中间声母（没有中间名，因此已知中间声母为空）与未知中间声母或类似语义不同。 为了钱，我们的模型允许 NULL - 事实上我对此有一个很大的问题，因为通常它们确实应该是 0，它们总是被用作 0 并且它们总是必须用 ISNULL() 包装。 但由于将空字符串转换为 NULL 的 ETL 策略，它们被设置为 NULL - 但这只是固定宽度传输文件格式的一个产物，它在某些源系统中使用空格而不是 0。

3. 我们的事实表通常具有基于所有维度的 PK，因此这是不允许的 - 它将链接到虚拟或未知维度

4. 在 SSIS 中，我制作了一个修剪组件，用于修剪所有字符串末尾的空格。 我们通常必须在 SSIS 中进行大量日期验证和转换，这在组件中是最好的。

Answer 3

感谢您的投入，

我在最新项目中所做的两件事是：

1）使用 Steve 关于未知/特殊维度值的负 ID 键的建议。 这非常有效，并且在 SSAS 多维数据集构建过程中没有出现任何问题。

2) 创建转换来检查值是否为空，如果是，则转换为 -1（维度中的未知记录），或者如果它是度量值，则转换为 0。表达式如下所示作为示例（我在派生列转换）：

ISNULL(netWeight) ? 0 : netWeight // This is an example of a Measure column
ISNULL(completeddateid) ? -1 : completeddateid // This is an example of a dimension key column

希望这对将来的其他人有帮助;-)

Answer 4

我可以建议的另一个解决方案是，在 ETL 步骤期间定义一个传输表，在所有必要的转换之后导入的记录将临时存储在该传输表中。
我会在该传输表中添加一些额外的属性，以允许某人； 紧邻原始值属性，可以为 NULL 或其他不需要的值； 插入一个“编码”值，一方面标识问题，另一方面插入错误值所在的属性名称。

完成此操作后，我仍然可以决定如何在后续步骤中使用非规范化和传输的数据...可能会过滤掉错误值或在单独的错误维度中提及它们，以便将其包含在报告中，说明哪些值不正常以及它们如何变化/可能会影响聚合值。

例如，

error-code attribute= -1 = NULL date -2 = NULL numerical value -3 = NULL PK -4 = NULL text value

另一个属性 = IdOrder、BirthDate、OrderAmount 等。

当然，如果记录可以有多个，那么您会遇到更多麻烦1 个错误（NULL）值，但在这种情况下，可以扩展“跟踪”属性的数量或“返回源”并找出问题发生的位置和原因（与开发部门一起）。

这在某种程度上是一个复杂的步骤，但是为了完整性和正确性，我认为这是不可避免的和必要的，因为否则人们可能会遇到聚合不良的信息。

也许这也会对某人有所帮助;)