主页 / dba / 问题 / 8128
Accepted
Matt Palmerlee
Asked: 2011-11-19 13:20:40 +0800 CST

数据仓库设计：组合日期时间维度与单独的日期和时间维度和时区

  • 772

我们刚刚开始设计一个新的数据仓库，我们正在尝试设计我们的日期和时间维度将如何工作。我们需要能够支持多个时区（可能至少 GMT、IST、PST 和 EST）。我们最初认为我们将有一个广泛的组合日期时间维度，粒度可能低至 15 分钟，这样我们的事实表中有一个键，所有支持的时区的所有不同日期时间数据都在一个维度表中。（即日期键、GMT 日期、GMT 时间、IST 日期、IST 时间等...）

Kimball 建议从一天中的时间维度中分离出一天维度，以防止表变得太大（数据仓库工具包第 240 页），这听起来不错，但这意味着我们在每个时区的事实表中都有两个键我们需要支持（一个用于日期，一个用于一天中的时间）。

由于我在这方面非常缺乏经验，我希望有人知道这两种方法之间的权衡，即性能与所有不同时区键的管理。也许还有其他方法，我见过一些人谈论在每个时区的事实表中有一个单独的行，但是如果您的事实表是数百万行，那么这似乎是一个问题，那么您需要将其增加四倍以添加时区.

如果我们执行 15 分钟的粒度，我们的日期时间维度表中每年将有 131,400 行（24 * 15 * 365）行，这听起来对性能来说并不太可怕，但在我们测试一些之前我们不会确定原型查询。在事实表中具有单独的时区键的另一个问题是查询必须根据所需的时区将维度表连接到不同的列，也许这是 SSAS 为您处理的事情，我不确定.

感谢您的任何想法，-马特

sql-server ssis

3 个回答

  1. Best Answer

    将日期和时间分开将使您可以轻松地按时间进行聚合。例如：如果您想运行查询以查找一天中最忙的时间段。这很容易使用单独的时间维度来执行。

    此外，您应该只有一个时间密钥。确定 GMT/EST 时间 - 然后在事实表中使用它。如果您需要根据其他时区运行报告，只需在您的应用程序或查询中进行转换。

    • 5

  2. 只是跟进我们如何决定实施我们的 DataWarehouse 以支持多个时区并尽可能高效：我们选择创建一个时区表（id、名称等）以及一个“时区”桥”表，如下所示：

    time_zone_bridge
---------------
date_key_utc
time_key_utc
timezone_id
date_key_local
time_key_local

    这样我们可以保持我们正常的日期和时间维度表很小，我们所有的事实都链接到 UTC 日期/时间键，然后如果我们需要按不同的时区报告/分组，我们只需要通过时区桥表加入并将本地日期/时间键链接回日期和时间维度表。我们使用从 SSIS 调用的 C# 代码填充我们的时区桥接表，因为这比直接从 SqlServer 执行 TZ 操作要简单得多。

    • 5

  3. 我已经看到使用组合DateTime维度的仓库的想法被拒绝，但我还没有看到一个非常明确的原因。稍微简化一下，这是我现在正在构建的事实表：

    Transactions
(
...
CreatedDateTimeSK         INT NOT NULL,  -- Four bytes per date...
AuthorizedDateTimeSK      INT NOT NULL,
BatchSubmittedDateTimeSK  INT NOT NULL,
BatchApprovedDateTimeSK   INT NOT NULL,
SettlementDateTimeSK      INT NOT NULL,
LocalTimeZoneSK           TINYINT NOT NULL  -- ...plus one byte for the time zone
)

    这些DateTime字段连接到 DateTime 表：

    DateTimes
(
DateTimeSK   INT NOT NULL PRIMARY KEY,
SQLDate      DATE NOT NULL,
SQLDateTime  DATETIME2(0) NOT NULL,
Year         SMALLINT NOT NULL,
Month        TINYINT NOT NULL,
Day          TINYINT NOT NULL,
Hour         TINYINT NOT NULL,
Minute       TINYINT NOT NULL CHECK (Minute IN (0, 30)),
...
)

    这是半小时的分辨率，因此每天有 48 条记录，20 年内有 350,400 条 - 非常易于管理。

    事件日期/时间在存储时会转换为 UTC，但通过LocalTimeZoneSK字段和桥接表，我们可以轻松地加入以获取本地时间：

    TimeZoneBridge
(
DateTimeSK       INT NOT NULL,
TimeZoneSK       TINYINT NOT NULL,
PRIMARY KEY (DateTimeSK, TimeZoneSK),
LocalDateTimeSK  INT NOT NULL
)

    要获取今天创建的交易，UTC 时间：

    SELECT COUNT(*)
FROM Transactions AS T
  INNER JOIN DateTimes AS CD ON T.CreatedDateTimeSK = CD.DateTimeSK
WHERE CD.SQLDate = '2014-08-22'

    要获取今天创建的交易，在本地时间进行交易：

    SELECT COUNT(*)
FROM Transactions AS T
  INNER JOIN TimeZoneBridge AS TZB ON T.CreatedDateTimeSK = TZB.DateTimeSK AND T.TimeZoneSK = TZB.TimeZoneSK
  INNER JOIN DateTimes AS CD ON TZB.LocalDateTimeSK = CD.DateTimeSK
WHERE CD.SQLDate = '2014-08-22'

    您可能很想通过用偏移量替换 来简化事情TimeZoneSK（REAL例如，-5.0 表示美国中部夏令时），但如果事实记录的某些日期/时间在夏令时，而有些则不是，这将被打破。

    如果事实记录的事件可能发生在不同的时区，例如装运或航班，那么您需要每个日期的时区字段，并且每个日期最多五个字节。

    • 2

相关问题