Which SK to Use in Fact Table

View previous topic View next topic Go down

Which SK to Use in Fact Table

Post  akjason on Thu Oct 25, 2012 12:55 pm

Hello, I am new to dimensional modeling and am working on a design and have run accross a situation I could use some help on. I have a dimension that has been denormalized to include banks and any addresses associated with that bank (like a branch). Consequently, my bank dimension will have many rows for each bank as follows:

SK 1 NK 1 Bank A Address 1
SK 2 NK 1 Bank A Address 2
SK 3 NK 2 Bank B Address 1
SK 4 NK 3 Bank C Address 1

As you can see, I will have the same natural key for bank A so how do I know which SK row to use for the FK in the fact table.

Thanks in advance,
Jason

akjason

Posts : 11
Join date : 2012-10-08

View user profile

Back to top Go down

Re: Which SK to Use in Fact Table

Post  TheNJDevil on Thu Oct 25, 2012 2:58 pm

The Natural key for a record cannot be the same across multiple records. your Actual natural key is whatever you have in addition to the address.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Which SK to Use in Fact Table

Post  akjason on Thu Oct 25, 2012 3:37 pm

Thanks NJDevil, I need to expand a little bit

The NK I was refering to was the NK from the source Bank table only, there is a key for the bank address table as well so i guess the NK in the denormalized dimension would be the combination of those two keys from the source tables or just the NK from the address table. My question is, how do I determine which SK to use in the fact table for Bank A if the user of this fact table only wants to get the bank name?

SK 1 NK 1 Bank A Address 1
SK 2 NK 2 Bank A Address 2

akjason

Posts : 11
Join date : 2012-10-08

View user profile

Back to top Go down

Re: Which SK to Use in Fact Table

Post  ngalemmo on Thu Oct 25, 2012 3:37 pm

And, to avoid a very large natural key, you may only store a partial address (sufficient to distinguish rows) or use a hash.

But, it introduces other problems. You need to locate the dimension when loading facts. If the fact does not contain the address you would not have sufficient information to locate the correct row. You may want to consider rethinking your table design. Besides, why would the same branch have two different addresses?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Which SK to Use in Fact Table

Post  akjason on Thu Oct 25, 2012 3:51 pm

Thanks ngalemmo, it looks like I was answering NJDevil as you were posting. A bank (e.g. Bank of America) can have multiple locations (branches). It would be simple if I did not have a denormalised dimension where a bank has multiple addresses. But my understanding of dimensional modeling is that I do need this. That creates a problem for this particular fact table that needs to link to only one dimension record so the user can determine the bank name (they don't care about the address)

akjason

Posts : 11
Join date : 2012-10-08

View user profile

Back to top Go down

Re: Which SK to Use in Fact Table

Post  ngalemmo on Thu Oct 25, 2012 5:19 pm

You are describing a branch dimension, not a bank (company) dimension. Presumably the natural key would be bank and branch number.

You can still have a bank dimension, with one row for BofA... but if you are interested in branches, you need a dimension for that.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Which SK to Use in Fact Table

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