Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 05 Jul 2011 @ 15:06:03 GMT

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

Subj:   Re: Multiset with a UPI
From:   John_Wight

Kartik Replied to previous post on UPI usage with Multiset tables:

  When I worked over at one of the GE accounts, it was a mandate to only use Multiset Tables. It was strongly recommended by consultants from Teradata. So if we had to build a UPI we would build it over Multiset tables so yes you can find Multiset tables with UPI there :)  

Yes, and I would agree with your premise.

I thought Multiset tables were really nice when they first came out (V2R2??) but had to do a lot of personal testing to find out (what I thought was) the best way to use them. And, yes, there are a lot of folks that are not fully aware of their properties. When I was a TD PS 'guy', I witnessed (what I thought) the brutal misuse of MULTISET tables on both sides.

If we really think about it, Multiset tables allow us to have DUPLICATE rows ('undefined' in set theory and hence in the relational model!!). As such, TD will not do a Duplicate Row check! This is great if we are loading a lot of rows (Utility or I/S) into a table with a NUPI PI and that those values have a high number of synonyms! HOWEVER, the down side of it is - we are now 'IN CHARGE' of 'row integrity' (i.e. - not storing duplicate rows, unless that is we want them but usually we don't. Now, if we define a uniqueness constraint on a table through a UPI or a USI, they automatically check for any duplicate 'key' values over the columns that define the PI in either SET or MULTISET tables. Hence, we can not get a Duplicate Row!!! If that is the case, then why use a Multiset? Again, if I m wrong, PLEASE anyone, let me know!

When we look at all the 'options' there are available to load and manipulate data in an EDW, we need to take them all into account. i.e. If I have a Multiset STAGE table to load, I can't have a USI if wanting to use an Fload or Mload, etc. etc. Again, it's horses for courses and a good database practitioner should know how their particular DBMS 'works', all the options/tools they have available and the best way to apply them in any given situation. Not saying I fit all those categories, but is this not what we should strive for?


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023