Archives of the TeradataForum

Message Posted: Mon, 28 Mar 2016 @ 12:42:31 GMT

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

Hi,

As Irwing has said there are multiple factors which determine the choice of a PI, access being one of them.

This is an example which I think more directly answers your question " but why choose a different Primary Index other than the PK in LDM?".

In a retail customer, probably the two biggest tables will be 'market_basket' and 'market_basket_detail' (or whatever they're actually called). These tables are:

'market_basket': 1 row per visit by a customer (think of everything in the customer's shopping trolley or basket)

'market_basket_detail': 1 row per item (e.g. a tin of baked beans) in the shopping trolley or basket.

If you want fast joins between these tables then you want to try and give them the same PI columns, which are also the join columns.

The LDM for these two tables might be something like:

'market_basket': date, store, checkout_nbr, checkout_nbr_txn_nbr

'market_basket_detail': date, store, checkout_nbr, checkout_nbr_txn_nbr, item_sequence_nbr

The join columns between these two tables will often be:

date, store, checkout_nbr, checkout_nbr_txn_nbr

So the 'market_basket' table might have a UPI = PK and the 'market_basket_detail' table will have a NUPI on the join columns.

The above arrangement gives an amp local, PI to PI join between these two (large) tables.

As noted above, this is an example. It may not be good/right for your specific circumstance, but I think it illustrates the point.

Does that help?

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

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