data-warehouse - 数据仓库 : Working with accumulated data

Question

我们的数据仓库使用来自数据源的累积数据(并且无法反转累积)来创建雪花模式。我们必须满足的一个要求是我们的架构必须可用于创建基于日期范围的报告。

我们的模式看起来像这样(简化):

+------------------------------------------+
| fact                                     |
+-------+-----------------+----------------+
|    id | statisticsDimId | dateRangeDimId |
+-------+-----------------+----------------+
|     1 |               1 |             10 |
|     2 |               2 |             11 |
|     3 |               3 |             12 |
|     4 |               4 |             13 |
|     5 |               5 |             14 |
|     6 |               5 |             15 |
|     7 |               5 |             16 |
|   ... |             ... |            ... |
| 10001 |            9908 |             11 |
| 10002 |            9909 |             11 |
+-------+-----------------+----------------+

+-------------------------------------------------+
| date_range_dimension                            |
+-------+-----------------------------------------+
|    id | startDateTime      | endDateTime        |
+-------+--------------------+--------------------+
|    10 | '2012-01-01 00:00' | '2012-01-01 23:59' |
|    11 | '2012-01-01 00:00' | '2012-01-02 23:59' |
|    12 | '2012-01-01 00:00' | '2012-01-03 23:59' |
|    13 | '2012-01-01 00:00' | '2012-01-04 23:59' |
|    14 | '2012-01-01 00:00' | '2012-01-05 23:59' |
|    15 | '2012-01-01 00:00' | '2012-01-06 23:59' |
|    16 | '2012-01-01 00:00' | '2012-01-07 23:59' |
|    17 | '2012-01-01 00:00' | '2012-01-08 23:59' |
|    18 | '2012-01-01 00:00' | '2012-01-09 23:59' |
|   ... |                ... |                ... |
+-------+--------------------+--------------------+

+-----------------------------------------------------+
| statistics_dimension                                |
+-------+-------------------+-------------------+-----+
|    id | accumulatedValue1 | accumulatedValue2 | ... |
+-------+-------------------+-------------------+-----+
|     1 |    [not relevant] |    [not relevant] | ... |
|     2 |    [not relevant] |    [not relevant] | ... |
|     3 |    [not relevant] |    [not relevant] | ... |
|     4 |    [not relevant] |    [not relevant] | ... |
|     5 |    [not relevant] |    [not relevant] | ... |
|     6 |    [not relevant] |    [not relevant] | ... |
|     7 |    [not relevant] |    [not relevant] | ... |
|   ... |    [not relevant] |    [not relevant] | ... |
|   ... |    [not relevant] |    [not relevant] | ... |
| 10001 |    [not relevant] |    [not relevant] | ... |
| 10002 |    [not relevant] |    [not relevant] | ... |
+-------+-------------------+-------------------+-----+

我们想用这样的东西创建我们的报告数据集:

SELECT *
    FROM fact
INNER JOIN statistics_dimension
    ON (fact.statisticsDimId = statistics_dimension.id)
INNER JOIN date_range_dimension
    ON (fact.dateDimId = date_range_dimension.id)
WHERE
    date_range_dimension.startDateTime = [start]
AND
    date_range_dimension.endDateTime = [end]

问题是我们统计维度的数据已经积累了，我们不能反转积累。我们计算了事实表中的近似行数，得到 5,250,137,022,180。我们的数据大约有 250 万个日期范围排列，由于积累，我们需要将它们计算到我们的日期维度和事实表中。由于累加，SQL 的 SUM 函数对我们不起作用(您不能添加属于非不同集合的两个值)。

是否有我们可以遵循的最佳实践来使其在计算上可行？我们的架构设计有问题吗？

我们需要报告有关在线培训的数据。数据源是遗留数据提供者，其中的部分已超过 10 年 - 因此没有人可以重建内部逻辑。统计维度包含 - 例如 - 用户在基于网络的培训 (WBT) 中完成的进度(%)、每个 WBT 页面的调用次数、WBT 的状态(对于用户，例如“已完成”) , a.s.o.. 数据提供者的重要之处在于:它只是给我们一个当前状态的快照。我们无权访问历史数据。

Answer 1

我假设您为此使用了一些非常强大的硬件。您的设计有一个主要缺点 - 事实表和“统计”维度之间的连接。

一般情况下，事实表包含维度和度量。在我看来，您的“统计”维度和事实表之间可能存在 1-1 关系。由于事实表本质上是一个“多对多”关系表，因此将您的统计数据放在单独的表上没有意义。此外，您说统计表包含“按用户”的信息。

每当您在仓储中说“按 X”时，您几乎总是可以确定 X 应该是一个维度。

我会考虑直接使用度量来构建您的事实表。我不确定您要通过“反转”统计表上的累积来做什么？您是说它是跨日期范围累积的吗？用户？如果数据不是原子的，你能做的最好的就是给出你拥有的...