II 型尺寸连接

Question

我在 OLTP 中有以下表查找表

CREATE TABLE TransactionState
(
    TransactionStateId INT IDENTITY (1, 1) NOT NULL,
    TransactionStateName VarChar (100)
)

当它进入我的 OLAP 时，我按如下方式更改结构：

CREATE TABLE TransactionState
(
    TransactionStateId INT NOT NULL, /* not an IDENTITY column in OLAP */
    TransactionStateName VarChar (100) NOT NULL,
    StartDateTime DateTime NOT NULL,
    EndDateTime NULL
)

我的问题是关于 TransactionStateId 列。 随着时间的推移，我的 OLAP 中可能会有重复的 TransactionStateId 值，但通过 StartDateTime 和 EndDateTime 的组合，它们将是唯一的。

我见过 Type-2 维度的示例，其中添加了 OriginalTransactionStateId，并将传入的 TransactionStateId 映射到它，加上新的 TransactionStateId IDENTITY 字段成为 PK 并用于连接。

CREATE TABLE TransactionState
(
    TransactionStateId INT IDENTITY (1, 1) NOT NULL,
    OriginalTransactionStateId INT NOT NULL, /* not an IDENTITY column in OLAP */
    TransactionStateName VarChar (100) NOT NULL,
    StartDateTime DateTime NOT NULL,
    EndDateTime NULL
)

我应该选择单身汉#2 还是单身汉#3？

Answer 1

通过这句话：

通过组合 StartDateTime 和 EndDateTime，它们将是唯一的。

你的意思是它们永远不会重叠或者它们满足数据库UNIQUE约束？

如果是前者，那么您可以在连接中使用 StartDateTime，但请注意，它可能效率较低，因为它将使用 "<=" 条件而不是 >“=”。

如果是后者，那就用假身份吧。

数据库通常不允许对此查询使用有效的算法：

SELECT  *
FROM    TransactionState
WHERE   @value BETWEEN StartDateTime AND EndDateTime

，除非您对 SPATIAL 数据执行神秘的技巧。

这就是为什么您必须在 JOIN: 中使用此条件

SELECT  *
FROM    factTable
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    TransactionState
        WHERE   StartDateTime <= factDateTime
        ORDER BY
                StartDateTime DESC
        )

，这将剥夺优化器使用 HASH JOIN 的可能性，而 HASH JOIN 在许多情况下对于此类查询来说是最有效的案例。

有关此方法的更多详细信息，请参阅本文：

• 转换货币

重写查询以使其可以使用 HASH JOIN 会带来 600% 倍的性能提升，但这只有在您的日期时间精度达到一天或更低的情况下才有可能实现（或者哈希表会变得非常大）。

由于您的时间组件已删除 StartDateTime 和 EndDateTime，因此您可以创建如下 CTE：

WITH    cal AS
        (
        SELECT CAST('2009-01-01' AS DATE) AS cdate
        UNION ALL
        SELECT DATEADD(day, 1, cdate)
        FROM   cal
        WHERE  cdate <= '2009-03-01'
        ),
        state AS
        (
        SELECT  cdate, ts.*
        FROM    cal
        CROSS APPLY
                (
                SELECT  TOP 1 *
                FROM    TransactionState
                WHERE   StartDateTime <= cdate
                ORDER BY
                        StartDateTime DESC
                ) ts
        WHERE   ts.EndDateTime >= cdate
        )
SELECT  *
FROM    factTable
JOIN    state
ON      cdate = DATE(factDate)

如果您的日期范围跨度超过 100 日期，调整 CTE 上的 MAXRECURSION 选项。

Answer 2

请注意，IDENTITY(1,1) 是该列中自动生成值的声明。 这与 PRIMARY KEY 不同，后者是使列成为主键聚集索引的声明。 这两个声明意味着不同的事情，如果您不说PRIMARY KEY，则会产生性能影响。

Answer 3

您还可以使用 SSIS 加载 DW。 在缓慢变化的维度 (SCD ) 变换，可以设置如何对待每个属性。 如果选择历史属性，则类型 2 SCD 将应用于整行，并且转换会处理细节。 如果您喜欢 start_date、end_date 或 current/expired 列，您还可以进行配置。

这里要区分的是主键和业务（自然）键之间的区别。 主键唯一标识表中的一行。 业务键唯一标识业务对象/实体，并且可以在维度表中重复。 每次应用SCD 2时，都会插入一个新行，具有新的主键，但业务键相同； 然后，旧行被标记为已过期，而新行被标记为当前行，或者适当填充开始日期和结束日期字段。

DW不应该暴露主键，因此从OLTP传入的数据包含业务键，而主键的分配由DW控制； IDENTITY int 有利于维度表中的 PK。

最酷的是 SSIS 中的 SCD 转换可以解决这个问题。