Modelling for Extended Dimensions

[cognostechie]

Wanted to get opinions on this to know the different approaches and their results.

This is the scenario:

Fact Table:
------------
Fact_Key           (Primary Key - one record for each key)
Customer_Key
Amount

Customer Dim:
----------------

Customer_Key   (Primary Key - one record for each key)
Address1
<Other fields>

Customer_Contact_Dim:
---------------------------

Customer_Contact_Key  (Primary Key - one record for each key)
Customer_Key
Contact_Name
Contact_Address
<Other fields>


The Customer Dimension is a standard dimension with one record for each customer. However, there are many contacts for each customer. A Contact is an employee of the Customer and there are many employees of that Customer who become the Contacts for us.

How would you model it? Join Customer_Dim to the Fact and join Customer_Dim to Customer_Contact_Dim?
Any other way? What happens to the rollup of the Amount column from the Fact?

Thanks !

[cognostechie]

Nobody ??

[bdbits]

Dimensions should not have foreign keys, and should not be joined to each other.

This strikes me as a grain issue. Why would you need contacts for the facts, if the grain is at the customer level? Or am I missing something?

[cognostechie]

because they would want to see the Customer, their amounts and who to contact (at least primary contact and one more - management contact). What you are saying makes sense but all users are not the same..

[MFGF]

because they would want to see the Customer, their amounts and who to contact (at least primary contact and one more - management contact). What you are saying makes sense but all users are not the same..

Hi,

The issue you have is that the facts are held at the Customer grain, not at the Contact grain. You need to ensure a couple of things in your model:

1. The contacts query subject should not be treated as a fact (or else you will end up with an erroneous stitch query)
2. The Customer fact values should be aggregated at the Customer level, not the Contact level.

It is a similar paradigm to where you have an aggregated fact table at the Month level, but your time dimension includes attributes at the Day level. The way I would model this would be to merge Customer and Customer Contact into a single customer/contact dimension query subject, joined to the Fact based on Customer Key, and then using determinants to split out the Customer attributes vs the Contact attributes in the dimension (ie first determinant with key of Customer_Key and with all the other Customer items as attributes - with Group By checked, and second determinant with key of Customer_Contact_Key and with Uniquely Identified checked.)

Cheers!

MF.

[cognostechie]

Works pretty well !

Thanks a lot MFGF!

Guess what? After you gave me the solution, I remembered having done this using the Date dimension years ago and I have also posted this myself in a discussion 8 yrs ago !

https://www.cognoise.com/index.php/topic,11557.15.html

:o

[MFGF]

Works pretty well !

Thanks a lot MFGF!

Guess what? After you gave me the solution, I remembered having done this using the Date dimension years ago and I have also posted this myself in a discussion 8 yrs ago !

https://www.cognoise.com/index.php/topic,11557.15.html

:o

Outstanding! Glad it works for you :)

Don't be hard on yourself. There are times recently where I have read an old post on Cognoise and thought "Hmmm. That's an interesting thought on how to solve this issue" - before realising it was posted by me :) I just put it down to my getting old and senile. There's only a finite amount of storage in my brain, and as new stuff gets added, old stuff just seems to dissolve...

MF.

[cognostechie]

Outstanding! Glad it works for you :)

Don't be hard on yourself. There are times recently where I have read an old post on Cognoise and thought "Hmmm. That's an interesting thought on how to solve this issue" - before realising it was posted by me :) I just put it down to my getting old and senile. There's only a finite amount of storage in my brain, and as new stuff gets added, old stuff just seems to dissolve...

MF.

Ahh ! That proves I am not insane !! 

Anytime I have a doubt, I will contact you  ;)