多对多尺寸模型 [英] Many-To-Many dimensional model

问题描述

Folks，

我有一个称为DIM_FILE的维度表，其中包含我们从客户那里收到的文件的信息。每个文件都有详细记录，构成我的FACT表CUST_DETAIL。在主要过程中，文件经过几个阶段，每个阶段都会标记一个状态。在短暂的时间里，我有很多关系。关于星形图维度建模的任何想法。客户记录仅属于单个文件，文件可以具有多个状态。

I have a dimension table called DIM_FILE which holds information of the files we received from customers. Each file has detail records which constitutes my FACT table, CUST_DETAIL. In the main process, file is gone through several stages and each stage tags a status to it. Long in a short, I have many-to-many relationship. Any ideas around star schema dimensional modeling. A customer record only belong to a single file and a file can have multiple statuses.

FACT
----
CustID
FileID
AmountDue


DIM_FILE
--------
FileID
FileName
DateReceived

FILE_STATUS
-----------
FileID
StatusDateTime
StatusCode

推荐答案

有一些事情你可以做一个维度模型/星模式：

There are a few things you can do to marry this with a dimensional model / star schema:

1. 构建两颗星（可能最终会在不同的数据表中）。一个具有FACT作为事实表，另一个明星具有FILE_STATUS作为事实（您可以将其视为事务粒度事实表）。为了使这项工作，我可能会将规范化并将CustId添加到FILE_STATUS中


2. 由于您正在处理FILE_STATUS，您可以将FACT转换为累积快照表。在此模型中，您将在FACT中单独设置一列额外列，以记录属于每个状态转换的所有信息。至少，您将有一个列到日期/时间维度，以记录何时达到特定状态。在您的ETL中，您必须更新事实表以记录文件如何通过状态。该设计仅在状态数量有限且相对较小时才起作用。另外，应该有一个或多或少的明确的状态进展路线（如客户订单：收到 - >选择 - >打包 - >发货 - >支付）


3. 做一个称为多值维度的状态：FACT将获得此新维度的关键，此新维度实际上将表示适用于FACT表中的一行的状态集合。


4. 您可以有一个桥表（虽然我不认为这适用于这个主题，不确定）

1. Build two stars (possibly, they'd end up in different datamarts). One has FACT as the fact table, the other star has FILE_STATUS as fact (you can consider it as a transaction grained fact table). To make this work, I'd probably denormalize and add CustId to FILE_STATUS too
2. Since you are dealing with FILE_STATUS, you could turn FACT into a accumulating snapshot fact table. In this model, you'd have a separate set of extra columns in FACT to record all information belonging to each status transition. At least, you'd have a column to the date/time dimension to record when a particular status was reached. In your ETL, you'd have to UPDATE the fact table to record how a file progresses through states. This design only works if the number of statuses is finite and relatively small. In addition, there should be a more or less clear path of status progressions (like with a customer order: received -> picked -> packaged -> shipped -> payed)
3. Make a so called multivalued dimension for the statuses: FACT would get a key to this new dimension, and this new dimension would actually represent a collection of statuses that apply to a row in the FACT table.
4. You could have a bridge table (although I don't think that applies to this subject, not sure)

参考文献：

累积快照： http：// www.kimballgroup.com/2002/06/design-tip-37-modeling-a-pipeline-with-an-accumulating-snapshot/
多值维度与桥表： http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/维度建模技术/多值维度桥表/

这篇关于多对多尺寸模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！