I have a Customer Table and Multiple Customer Addresses. A customer can have multiple addresses: home, work, vacation,etc.

In Kimball data warehouse, would I utilize a bridge table to go between Customer and Customer Address information?

(a) So [DimCustomer] to [DimCustomerAddressBridge] to [DimAddress] Table? Would this be the proper technique?

(b) Or would I just have a Fact Table, like CustomerTransactionPayment Table, that connects the two Dimensions [DimCustomer] and [DimAddress]?

So one strategy is the connected Dims using bridge, and another is disconnected Dims connected by a Fact transaction table?


The right schema depends on how you intend to use this data. Let's talk about your options:

a) Bridge table.

I would not use this approach, because bridge tables are used to express N:N relationships. If you go for it, that would not be possible to precisely say what is the right address for your transaction; so it would be useless.

Following Kimball group -> "Bridge tables are used...where a many-to-many relationship can’t be resolved in the fact table itself..." https://www.kimballgroup.com/2008/09/design-tip-105-snowflakes-outriggers-and-bridges/

b) Create another dimension DimAddress (DimGeography?)

This looks a good approach, especially if this Address information could be related to other Fact tables. Maybe you could expand it into a DimGeography, or something like that. In this case, DimGeography and DimCustomer would not be related directly.

Other options

c) You could also change the granularity of your Customer dimension to the Address level; so you would have one row per address and the "master customer data" would be the same for the same customer in this dimension. -> Do that if most of your data is related to the Address level.

d) Another approach would be snowflaking your schema. Creating then a DimCustomerAddress that can be used on this fact; where your fact table points to DimCustomerAddress and DimCustomerAddress points to DimCustomer. In this way, you don't change your Customer table neither the other fact tables and can create the Customer -> Address hierarchy.

e) If you have to track historical address changes (based on comments)

This solution is similar to d), but the DimCustomerAddress would be an SCD type 2. This will make the granularity of your new DimCustomerAddress be the "address history". I.E: I would assume that your business key for this dimension is the Customer ID, AddressID and Address type, so you should add a new row everytime you get an address change; keeping tracking of the start and end dates that the address is valid. Your fact table should be related with DimCustomerAddress by the surrogate key "CustomerAddressKey", and DimCustomerAddress should relate with DimCustomer using "CustomerKey". The Start and End dates can be used during the ETL time to populate the Fact table to determinate the right "CustomerAddressKey". This example illustrates the case e)

| improve this answer | |
  • OK, I am thinking c) and d), simple because we want to track customer address changes with begin and end dates, doing a fact table, would require a daily snapshot fact table if not mistaken, this may be too redundant, so we will last two, thanks – user162943 Oct 17 '18 at 5:11
  • also, can we modify (d) slightly, so the fact table points to the DimCustomer, and DimCustomer points to DimCustomerAddress? – user162943 Oct 17 '18 at 5:12
  • If you change d) as you said, you will have the same problem than a). You will not be able to define which is the right address related to the fact table. I assume that your DimCustomer is related to other fact tables, and you can't change that, right? so d) would be your best approach. I will add an e) option on te answer to deal with history tracking – Evandro Oct 17 '18 at 8:28
  • 1
    thanks this is very helpful! by the way, we want to track slowly changing dimensions on DimCustomer also, so we'll just add Start and End date on Dim Customer – user162943 Oct 17 '18 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy