数据仓库中的一对一关系 [英] One to one relationship in data warehouse

问题描述

一个简单的场景：我想创建一个数据仓库，其中包含有关问题的信息（费用，耗时等）。该问题的状态可能会随着时间而改变。因此，然后我创建了一个称为issueRealization的事实表来描述每个问题。

Simple scenario: I'd like to create data warehouse which information about "issues" (cost, wroking time etc.). issue also has status which might change over time. So then i'm creating fact table called issueRealization decribing each issue.

我的问题是：我是否应该创建问题维度，以便给我一个一对一的关系beetwen维度和事实表？还是我应该将问题维度划分为最小的维度，例如状态等？

My question is: should i create "issue" dimension which will give me one to one relationship beetwen dimension and fact table? Or i should divide Issue dimension to smallest dimension like status etc?

推荐答案

问题状态跟踪是使用累积快照事实表，以跟踪问题状态随时间的变化。

Issue status tracking is a good case to use an Accumulating Snapshot fact table, to track the changes in the status of an issue over time.

例如，这是一个IT问题/错误/增强管理系统，问题只有3种状态：已创建和进行中， '已解决'。

As an example, let's say this is an IT issue/bug/enhancement management system, with issues that only have 3 statuses, 'Created' and 'In Progress' and 'Resolved'.

问题事实表如下所示：

ID Number (Degenerate Dimension)
Issue description (Degenerate dimension. You can also create a 1-1 table for these if it's not often used in reporting)
Type ID (bug/enhancement/etc, this is a dimension key)
Assigned Developer ID (Dimension key)
Current Status ID (Status dimension key)
Date Created (DATE dimension)
Created Flag (1 = created, 0 = otherwise)
Date In Progress (DATE dimension)
In Progress Flag (1 = created, 0 = otherwise)
Date Resolved (DATE dimension)
Resolved Flag (1 = created, 0 = otherwise)
Created Datetime (measure)
InProgress Datetime (measure)
Resolved Datetime (measure)
Worktime Interval (measure)
Cost (measure)

此表的粒度是每个发行ID号的1行。

The grain of this table is 1 row per issue ID number.

使用这种类型的事实表，则每次源系统修改问题时，您都会更新同一行。请注意，我们如何为每种状态类型创建一个字段以及一个日期时间记录，以允许我们计算指标，例如已创建状态与已解决状态之间的时间。此外，我添加了一个时间间隔字段，以允许您存储实际工作时间，例如开发人员用于修复的小时。

With this type of fact table, you update the same row each time the source system modifies an issue. Note how we create a field for each status type, as well as a datetime record to allow us to compute metrics such as "time between created and resolved status". In addition, I added an interval field to allow you to store "actual" work time, such as "hours" the developer put towards the fix. This could easily be an integer.

该表将能够回答有关问题的任何问题，并汇总显示有多少个问题花费了超过1周的时间解决，等等。

This table would then be able to answer any questions about an issue, and provide rollups to show "how many issues took longer than 1 week to resolve", etc.

这篇关于数据仓库中的一对一关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！