Archives of the TeradataForum

Message Posted: Fri, 30 Nov 2007 @ 14:21:13 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

To go deeper into this discussion:

Yes, you are right, there is not really a need for an artificial key for a table as long as the amount of "real world data" columns uniquely identifying a record ( the key ) is reasonable.

The challenge in many DWH implementations is to create a UNIQUE KEY for an ENTITY, which is sourced from different systems.

E.g. in the TELCO area:

A Prepaid customer can change to a Postpaid customer ( or vice versa ) and this will be handled on 2 different operational systems: The Prepaid IN-System OR the Postpaid Billing system. The operational systems do not care about any history and will provide new KEYS after any switch.

In the DWH there is a need to know the full history of the customer, so you need to define a SURROGATE KEY to identify each customer independently in which system he is currently active.

For the creation of a surrogate key:

IDENTITY columns may be only helpful ( if ever ) in a "Delta"-Load environment for TEMPORARY INTERMEDIATE tables. At the end of the transformations the IDENTITY column should be sorted and renumbered according to the base table ( starting from max(id) + 1 ) ...and the records inserted.

The target table should not have an IDENTITY column .. it should be INTEGER or so ...

>From the real world experience:

Copy a table to another table with an IDENTITY column. If you add records to the new ( copied ) table, the IDENTITY column may be not unique anymore, because the pool of IDENTITY numbers depends on the object-id ( table ). New table -> new object -> start the IDENTIY from scratch. If there are already records in the table you will very likely see dupes in the IDENTITY. NOT NICE !!!!

BR

Christian from Vienna

	<Prev	Next>		<<First	<Prev	Next>	Last>>