1

I make payments to various kinds of payees and I'm wondering whether I should create a dimension of each kind of payee and have multiple foreign keys in my fact table, or conflate the various kinds of payees via a type attribute and have a single FK in the PaymentFact table at the expense of having attributes that make no sense in the PayeeDim table for any particular value of PayeeDim.Type...

how are these situations normally handled?

TIA - e

ekkis
  • 9,804
  • 13
  • 55
  • 105

2 Answers2

2

As always with dimensional modeling, the answer is "It Depends." It's usually better to have a dimension with a bunch of empty attributes if the alternative is a fact table with 15-20 dimensions.

To the business, if a Payee is a payee, and there are several payee types, then it makes sense to have a payee dimension. But if a one payment record can be assocaited to multiple different "types" of payees, then each dim should get its own key on the fact.

N West
  • 6,768
  • 25
  • 40
  • payees are very different from each other. consider an individual payee who's got an age, gender, etc. versus a corporation which doesn't but has a jurisdiction of incorporation, officers, etc. it seems awkward to create a single dimension to represent two very different animals. But on the other hand I'm not happy to have 2 FKs in the fact table for what is in essence a reference to a single entity... – ekkis Oct 26 '12 at 23:16
  • 1
    Kimball's discussion of the "Customer" dimension in his book "The Data Warehouse Toolkit" goes into some detail about how to handle this. Essentially, it's usually better to keep your customer info all in one dimension unless there's a pressing business reason to separate corps and individuals... – N West Oct 29 '12 at 13:22
  • @N West, +1 on that. Thanks for sending me back to basics... yes, I'll agree with you on your original response. – ekkis Oct 30 '12 at 03:19
0

One option is to have two dimensions - one with the Payee information, and the other with the Payee_Type information.

Mike McAllister
  • 1,479
  • 2
  • 12
  • 15