0

I am new to Dimensional Data Modeling. I have one dimension and 2 fact tables:

The one fact table (fact1) is joined to the dimension using the surrogate key. No problems here. I cannot seem to figure out what is the best way to join the dimension to the second fact table (fact2). If it was a relational database design I would have used the NaturalKey2 on fact2 table: (NaturalKey1 + NaturalKey2 make a record unique in the dimension table).

i.e.

DIMENSION
SurogateKey | NaturalKey1 | NaturalKey2 | Current
1 | a1 | b1 | 0 
2 | a1 | b2 | 1 
3 | a2 | b3 | 1

FACT1

(fk to Dimension)
1
2
3

FACT2
a1 ?
a2 ?

Do I use a bridge dimension that has only NaturalKey2 and use the SK from that dimension to fact2? Something like that

DIMENSION 2

SurogateKey | NaturalKey2 
1 | a1 
2 | a2
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Nathan
  • 1
  • 2
  • This is best approached when asking questions like: In the business, how does the "fact2" data know to which dimension data it belongs? What is the common business key? Is there any way to have only one fact table to reduce complexity, since there seems to be a common key to both? Why is there a surrogate key to a dimension? What will this represent in the business (is there no other rule to match into the dimension)? – tobi6 Sep 30 '16 at 08:13
  • you don't need foreign keys in a DW. See "hot-swappable dimensions" – Neil McGuigan Oct 04 '16 at 20:05

1 Answers1

0

Your dimension is represented by the surrogate key in whatever facts it applies to, so it should be the surrogate key that appears in fact 2.

I didn't spot the point of your question, originally, where you mentioned the 2nd fact was of a different grain. The dimensional way of doing this is to have a second dimension at that higher level, which only has columns appropriate to that grain. Both dimensions are 'conformed' in the sense that the titles and contents of the fields align.

Rich
  • 2,207
  • 1
  • 23
  • 27