Mapping data from separate systems

Question

Mapping data from separate systems

ian.scrivens

Valued Member

Points: 64
More actions
July 28, 2011 at 10:09 am

#390080

I am in the process of designing a DW (something I have no prior experience of), part of which will contain client data.
This client data will be fed from a number of data sources, one of which contains a client id and name as natural keys, the other data sources will just contain client names.
What are the best techniques/approaches to ensuring I have usable client data in my DW, and how might I handle changes to client data in the future, i.e. client name changing, from any of the data sources (especially the ones with no ID).
Thanks in advance.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

ian.scrivens (7/28/2011)
I am in the process of designing a DW (something I have no prior experience of), part of which will contain client data.
This client data will be fed from a number of data sources, one of which contains a client id and name as natural keys, the other data sources will just contain client names.
What are the best techniques/approaches to ensuring I have usable client data in my DW, and how might I handle changes to client data in the future, i.e. client name changing, from any of the data sources (especially the ones with no ID).

Not bullet proof solution when dealing with a external datasource that does not includes a reliable unique ID.

Either a change on the external system is needed or a mapping table has to be created, mapping whatever is used as ID on external system and DWH side ID... that's the "T" of the ETL process... Extract, Transform and Load.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.