Dimesional Model Design Question

Question

Hi,

I have a Dimensiona Model Design question on Contact with Multiple Addresses. As in below image Contact Dimension and Address Dimension have Unique records. But Fact table can have multiple records as a Contact has Multiple Addresses.

The issue is:

Even when the Paid Amount is 100 for ContactId 1 the Fact table will show it as 200.  What is a usual solution for this ? Do I Create Avg(PaidAmount) in my Cube or Do I do the Calculation and Update my fact table with 50,50 for ContactId 1 instead of 100,100.

Both of these solution do not look good to me. Can anybody suggest me a best way to handle this ?

Thanks!!

Answer 1

I'd say normally the [Sales Table].[PaidAmount] would be the source for the Measure group and the content of [Fact Table] actually would be a Many-to-many bridge table.

This assumes you are trying to associate the $100 with both addresses so that you will get the $100 total no matter which customer address you choose, but the aggregate of a customer will still only state $100, is that correct?  If so, then I think you drop the [Paid Amount] from [Fact Table], and this table becomes a Many-to-Many between the [Sales Table] and [Address] tables, which we might call [bridgeCustomerAddress] . The relationships are like:

[Sales] <- [bridgeCustomerAddresses] -> [Address] (tie bridge to sales by ContactId; bridge to Address by AddressId) 
[Sales] -> [Client] (tie sales to contact by contactID)

---

Rob Kerr SQL Server MVP CTO, BlueGranite

Answer 2

Hi

It might be worth clarifying "But Fact table can have multiple records as a Contact has Multiple Addresses."

Typically (unless you are exception) you have Contact Dimension with Address attributes. Address attributes are slowly changing dimension type 2 (track history). That means that sales is associated with a contact and address at that point in time not with multiple address which doesn't seems to make logical sense.

In contact dimension you would have multiple contacts (with different ID = surrogate key) below is example:

ID, Name, Address

1, Emil, Ipswich

2, Emil, London

Fact:

ContactID, PaidAmount

1, 100

2, 200

When you aggregate Contact Name you will get 300 for Emil. When you aggregate Contact Name + Address you will Emil, Ipswich 100 and Emil, London 200

Let me know if that helps

Emil