Dimension table with no business key?

We have a CustomerAddress dimension table that will have a surrogate key assigned only. The customer address information will come down on both the CustomerOrder and CustomerShipmentInfo files (Bill To Customer and Ship To Customer). We have never had a dimension table that could be updated with no business key before, so what is the process to get the customer address information into the dimension table before you process the CustomerOrder or CustomerShipmentInfo on the fact table?

Addresses are distinct. The combination of all the address columns are your business key.

Not necessarily, as you can have two customers from the same address. Anyhow, I would need to update the dimension with the customer address information, and then insert or update the fact record, correct? Have many people run into this situation before?

Sounds a bit odd. I'd wonder what attributes you're storing in this "Address" dimension besides the actual address... but that's not germane to the issue at hand.

First, it's not a "CustomerAddress" table, is it? It's an "Address" table. There's no customer reference in there, so why refer to it as having anything to do with a customer? If you're making a dimension table in a conformed Bus architecture, leave your terminology as "open" as possible to allow for future expansion and reuse of the table.

Second, Boxes And Lines is right - whatever columns make up the "Address" (number, street, city, ...) are your "business key". To shrink that down, you may want to use a Hash algorithm for comparisons during load.

Apart from that, your ETL process is exactly the same as any other dimension. You need to extract the "new" state of the dimension from your OLTP system, compare and update/insert to your DW dimension table. Once your dimension is ETL'd, then use it in lookups for your fact table load.

So essentially it is wrong to have a dimension with no business key, correct?

hasooyeh wrote:So essentially it is wrong to have a dimension with no business key, correct?

Rule #1 in data modeling. First normal form applies to all types of data modeling.

Based on your naming conventions, I'm not sure you are performing dimensional modeling. A CustomerAddress table sounds like an associative entity. An associative entity resolves a many to many relationship between two entities. In this case A customer can have many addresses. And as you noted previously, An address can have many customers.

Hasooyeh, I think you need to re-think your question, the replies above make complete sense! Maybe you are just confused with the term "Customer" - Address, maybe its an associative table which in the case will have primary keys from Address and Customer Table, so I believe its not the case. If its an address table then all the address attributes are part of the business key!