MS SQL Data Warehousing - DataWarehouse

Author

DataWarehouse

TBoon

2006-10-31, 12:15 am

Hi there,

Let' look at the following scenario:

Database A with a Customer table, PK ID#.
Database B with a Customer table, PK ID#.

Both Database A & B are 2 different system and not related.
However, same Customer may exists in both system.

Now, I would like to create a common Customer info in another database.
With a DateTime field, I can get the latest record from either system with
the PK ID#.

But, if System A updates Tel No for Customer John on 30/10/2006.
And System B updates Address only for the same Customer John on 31/10/2006.

By taking the lastest data i.e. from System B is not entirely correct.
Because System A has the new Tel No.

Is there a way to check which Field is updated??? Or any suggestion?

regards,
Boon

Peter Nolan

2006-10-31, 12:15 am

Hi Boon,
it's actually a lot worse than that.....let's say you have 10 different
systems which all contain customer information of varying customer
attributes and of varying qualities all being updated via the
transaction systems they support and your CMO has decided he wants 'one
360 view of the customer' so he/she can more effectively promote the
products and services of the company.

This problem is the heart of building marketing analysis systems....

You need:
1. A target model to write to.
2. The ability to cross reference the incoming customer data between
systems to indentify unique individuals or companies.
3. To Define your 'survival' of data from various systems so that there
is a hierarchy of which systems data is cosidered more reliable than
other systems data.
4. To then encode all this into your ETL subsystem.

It's not easy....no-one said it was......with all these different
operational systems out there....data integration is going to be around
for a while..

Peter
www.peternolan.com

TBoon wrote:
> Hi there,
>
> Let' look at the following scenario:
>
> Database A with a Customer table, PK ID#.
> Database B with a Customer table, PK ID#.
>
> Both Database A & B are 2 different system and not related.
> However, same Customer may exists in both system.
>
> Now, I would like to create a common Customer info in another database.
> With a DateTime field, I can get the latest record from either system with
> the PK ID#.
>
> But, if System A updates Tel No for Customer John on 30/10/2006.
> And System B updates Address only for the same Customer John on 31/10/2006.
>
> By taking the lastest data i.e. from System B is not entirely correct.
> Because System A has the new Tel No.
>
> Is there a way to check which Field is updated??? Or any suggestion?
>
> regards,
> Boon

Sponsored Links