Building Customer Conformed Dimension

View previous topic View next topic Go down

Building Customer Conformed Dimension

Post  ozisamur on Fri Nov 28, 2014 4:39 am

Hi guys,

I have two source system which has customer information. But in dwh I should create a just one dimension ( conformed dimension ) which holds the all customer for that company.
Some of the customers can be existed in both source system. Some of them are existed in just one system.
What are the best practices of combining two customer source tables into the one dimension table ?

Thanks.

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Building Customer Conformed Dimension

Post  nick_white on Fri Nov 28, 2014 8:18 am

1. Decide on a mechanism that can reliably deduplicate your customer records - assuming that your customers are individuals then duplicating on first and last name would obviously not be a reliable mechanism.

2. There are other approaches to this, but I would make up a business key for my customer dim and then create a cross reference table that links my source business keys to my generated business key. Having decided that a source customer record matches a record in the customer dim (or it doesn't and you create a new customer dim record), you enter this data in the cross-reference table and then in subsequent data loads you can check this table first and if a record exists for your source record then you can just pick up the Dim business key and you don't have to go through the de-duplication process each time.

3. For every customer attribute, decide which of your source systems is the master (or possibly that all of them are) and then design your ETL so that an attribute in your customer dim only gets updated if it is changed in the master source.

nick_white

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

View user profile

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