Add customer attributes to fact or new dimension

We have a conformed customer dimension which includes: customer_id (natural key), name, company, email, phone. It has a surrogate key customer_dim_id and the value of -1 is designated for the "Unknown" where the customer cannot be identified by the natural key (customer_id) in the fact row.

We are bringing a new source of data into our data warehouse. It is our company's technical support system. Each call has a reference_id to make it unique. Associated with the call, a customer_id, name, email, company may be included. But, a customer_id is not required.

In our call_support_fact table, we have assigned the -1 unknown customer_dim_id to those fact rows where a customer_id was not included. However, the business users would still like to see any of the other entered customer related information that was recorded during the call, i.e. name, email, company.

If I add those fields to the fact table, the values may not match the values in the customer_dim where we have the customer_id.

What is the best approach to be able to provide those values for reporting?

If you can identify an existing company record based on the information supplied then use that, otherwise create a new company record in your Customer Dim.
Basically you have customer data coming from multiple sources and you need to de-duplicate it as far as possible before loading into your DW

Create a junk dimension for the customer values or create a new customer dimension row with only the values provided (assuming nullability constraints are not violated). Your ETL lookup will be a little more complex (build a concatenated key from all columns), but once you've done that, you can relate all facts with the same values to the same unknown customer dimension row. I did something similar to this for partial addresses. Once I received complete information, I would just point to the correct address row, or in your case, the correct customer dimension row.

Hi,

one other approach could be whenever you receive a customer with out a customer_id then you can create a new customer where in the customer_dim_id will be -1,-2,-3 .... This way you can capture the other details of the customer and show it to the users.




Himanshu

In my opinion, the SK for the Dim should always just be a sequentially generated number; the only exception is the 0 (or -1) record that indicates the generic "does not exist" record. Where you don't have a customer ID, and can't identify one based on the information that you do have, it's the BK that you will need to make up - and it makes sense for you to construct it in such a way that it's obvious where the data has come from e.g. prefix it with a system identifying code