Which SK to Use in Fact Table
3 posters
Page 1 of 1
Which SK to Use in Fact Table
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:
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
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
Re: Which SK to Use in Fact Table
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
Re: Which SK to Use in Fact Table
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?
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
Re: Which SK to Use in Fact Table
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?
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?
Re: Which SK to Use in Fact Table
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
Re: Which SK to Use in Fact Table
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.
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.
Similar topics
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum