Sourcing data with GUID source key datatype into Oracle Data Warehouse
762114Mar 26 2010 — edited Apr 13 2010All,
We are working through the ETL design for our Oracle Data Warehouse. One issue I have run into is that my target data model for a dimension table has a source natural key defined as an integer.
for example:
CUSTOMER_DIM
surrogate_key INTEGER
natural_key INTEGER
description VARCHAR2(100)
This works fine for the majority of source data being brought into it, but one of my sources is a sql server instance that is using GUID as the data type for the key that would naturally fit into my CUSTOMER_DIM.natural_key field.
I have looked into converting GUIDs to ints, but an INTEGER is 32 bits while a GUID converts to a 128 bit value so it wont exactly fit. I need to use this value to know when a Customer Dimension record comes through whether it is a new record or modified record by comparing on this value.
Has anyone ever run into this situation or had to use GUIDs in some way?
The alternatives as I see them now are:
1. Convert my natural key to a varchar to store the integers and GUID values (not a fan of this
2. Create a mapping table within the warehouse that maps the Guids to keys I make up
Thanks in advance.
John