Conformed Bridge Dimension?

View previous topic View next topic Go down

Conformed Bridge Dimension?

Post  johankarlss on Mon Oct 31, 2011 6:43 pm

Hi all,

I've got a conformed dim "Ethnicity" which is used by several facts in my data-mart. The business process recently changed so that a record in the fact table can be linked to one or more Ethnicities, so I built a bridge dimension "EthnicityBridge".
What's the best approach here out of the 2 options:

1 - create only 1 bridge and "conform" it so that all facts are referencing the same physical bridge. The surrogate key in the fact would have to be unique across all fact tables.

2 - create a bridge table per fact which links to the same Ethnicity dimension, I will effectively end up with 7 bridges as I have 7 fact tables.

Any feedback would be appreciated.

Thanks,
Johan

johankarlss

Posts : 10
Join date : 2011-05-31
Location : New Zealand

View user profile

Back to top Go down

Re: Conformed Bridge Dimension?

Post  BoxesAndLines on Mon Oct 31, 2011 9:06 pm

Ethnicity is such a low cardinality, I would look to create a row for all combinations to avoid a bridge table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Conformed Bridge Dimension?

Post  johankarlss on Mon Oct 31, 2011 9:11 pm

thanks.

yes, that was actually the initial design. However as there are around 32 recognized ethnicities here in New Zealand that we need data analysed by and we're capturing up to 6 per person depending on the source system; we had to change to a bridge.

Any other thoughts on the options above?

johankarlss

Posts : 10
Join date : 2011-05-31
Location : New Zealand

View user profile

Back to top Go down

Re: Conformed Bridge Dimension?

Post  hang on Tue Nov 01, 2011 12:13 am

I would have a single bridge table for m-m relationship between person and ethinicity dimension. I would also have a primary ethinicity flag in the bridge if it is type 1, or a primary attribute in person dimension if you want to keep it as type 2.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Conformed Bridge Dimension?

Post  ngalemmo on Tue Nov 01, 2011 5:28 pm

Assuming you don't need to restate historical facts, and assuming your ethnicity dimension is common across all facts, then I would go with the single bridge. Historically, you can create bridge rows for the existing dimension rows, so that you do not need to rekey the ethnicity FK's in the facts. Then generate new combinations in the bridge as you encounter them in new facts.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Conformed Bridge Dimension?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum