vendredi 20 avril 2012 14:32
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 ?
Toutes les réponses
samedi 21 avril 2012 03:03
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
mardi 1 mai 2012 05:54
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
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