two facts that relate via degenerate dimension

[kevintxu]

Hi,

I have a order line fact which look like this:

Order Line Fact
-------------
Order Line Key (SK)
Order Line number (DD)
Order Number (DD)
Some measure (fact)

Now some time later I have another fact which would reference the Order Number (DD) of the above table. How would I establish the reference? Would I have a FK in the new fact pointing to Order Dnumber of the old fact?

Thanks.

[BoxesAndLines]

You probably already have an index on your DD, so really, you don't have to do anything else.

[ngalemmo]

As B&L stated, your fine.

You need to put aside ER modeling concepts when dealing with a dimensional model. In a dimensional model a fact table has relationships to dimension tables (and the occasional bridge table) only. Since a degenerate dimension is a dimension, its all good.

Now, different fact tables will have dimensions in common (conforming dimensions). And information in one fact table can be combined with information in another fact table along common dimensions, but there is no concept of a FK relationship between facts. And, to carry this a bit further, the relationship between facts with conforming dimensions is NOT based on the dimensional keys, but rather the ATTRIBUTES in the dimension.

[Sponsored content]