Dimension table with no business key?

View previous topic View next topic Go down

Dimension table with no business key?

Post  hasooyeh on Wed Feb 25, 2009 3:52 pm

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?

hasooyeh

Posts : 5
Join date : 2009-02-25

View user profile

Back to top Go down

Re: Dimension table with no business key?

Post  BoxesAndLines on Wed Feb 25, 2009 5:30 pm

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

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

View user profile

Back to top Go down

Re: Dimension table with no business key?

Post  hasooyeh on Wed Feb 25, 2009 6:39 pm

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?

hasooyeh

Posts : 5
Join date : 2009-02-25

View user profile

Back to top Go down

Re: Dimension table with no business key?

Post  Todd McDermid on Wed Feb 25, 2009 6:41 pm

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.
avatar
Todd McDermid

Posts : 11
Join date : 2009-02-04
Location : Nanaimo, BC

View user profile http://toddmcdermid.blogspot.com

Back to top Go down

Re: Dimension table with no business key?

Post  hasooyeh on Wed Feb 25, 2009 8:04 pm

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

hasooyeh

Posts : 5
Join date : 2009-02-25

View user profile

Back to top Go down

Re: Dimension table with no business key?

Post  BoxesAndLines on Wed Feb 25, 2009 8:19 pm

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Dimension table with no business key?

Post  rakeshjn on Wed Feb 25, 2009 11:12 pm

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!

rakeshjn

Posts : 1
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Dimension table with no business key?

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