Design Question

Add them to your existing vendor dimension. You may also want to de-dupe that list... it may knock out a few million rows.

Yes. Definitely there is some data cleansing that can be done. Even after that, vendor dimension will be large. I gave it some additional thought and one option is to have this table created neither as a dimension table nor as a fact. Since it is having same subset of keys as my original fact, transform the common keys into SID values and keep vendor related attributes as is. Then onus is on reporting tool to have some drill through functionality to these single payment vendors. Is it going to work? I am sure it is not a perfect dimensional model. But I want to pick the one which is best of the worst. Thanks!!

What are your concerns about adding the single payment vendors to your existing vendor dimension? Is it purely an issue of size or do the single payment vendors have different attributes and would require fundamentally changing your existig dimension?

I think different solutions would come into play for different concerns.

If it's just a size concern, then it's not really an issue because no matter how you model it, you're going to have a big dimension table some where. If the concern is that a large dimension table will reduce performance for queries on the "normal" vendors, then you could try fragmenting the table or even creating 2 identical looking dimensions, one for normal vendors and another for the single payment vendors, forcing one of the surrogate keys to be negative, and then creating union join (a poor man's fragmented table).

I think the correct design depends on concerns and how the data will be used.

Hi Jeff,

My concern is regarding performance only. I am a little bit worried that it would effect performance if I add those single payment vendors into my regular vendor dimension.