Add customer attributes to fact or new dimension

View previous topic View next topic Go down

Add customer attributes to fact or new dimension

Post  kjfischer on Wed Feb 18, 2015 10:14 am

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?


kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Add customer attributes to fact or new dimension

Post  nick_white on Wed Feb 18, 2015 12:07 pm

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Add customer attributes to fact or new dimension

Post  BoxesAndLines on Wed Feb 18, 2015 6:54 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re Add customer attributes to fact or new dimension

Post  hkandpal on Thu Feb 19, 2015 4:54 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Add customer attributes to fact or new dimension

Post  nick_white on Fri Feb 20, 2015 8:02 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Add customer attributes to fact or new dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum