Temporal table and PRIMARY INDEX

Database
Highlighted
Enthusiast

Temporal table and PRIMARY INDEX

I have a dimessnoin which have both Identity and Suggorate Key columns ,

 

In which Each Nuture Key have One and Only One Identity (int), While a Surgorate Key will be generated for Every Update occured on the the dimessnoin columns.

 

I am planning to use the Identiy column to do Master data management , 

I am planning to use Surrogrete key column to represent the data for the Identiy in Specific point of time 

 

Example 

Surrugate Key IdentityNatural KeyCustomer NameStart DTEnd Date
11123456Ali01/01/201807/09/2018
21123456Mohamed08/09/201831/12/9999

 

I am using temporal table to build the history (Start&End DT ) of the dimenssion table.

The temporal for me the Identity column to be the primary Index, 

My issue is 

Temporal required Identintiy to be the primary index of the table , While I have to make the Surroraget Key to be the primary Index .

 

 

 

 

 

 


Accepted Solutions
Teradata Employee

Re: Temporal table and PRIMARY INDEX

No, what Dieter is explaining to you is that you don't need the surrogate key as the database will already handle the update on it's own, and giving you only one row for an id at one point of time.

The work you want to do with the surrogate key is redundant with temporal. Except it will be harder to code, maintain, and will probably perform lesser.

1 ACCEPTED SOLUTION
13 REPLIES
Junior Contributor

Re: Temporal table and PRIMARY INDEX

Can you show your actual CREATE TABLE?

Your desription is quite confusing, Natural Key, Surrogate Key, Identity.

Based on your example date the identity column is not an IDENTITY GENERATED ...

Enthusiast

Re: Temporal table and PRIMARY INDEX

Sorry for such detail , which cause confusing , 

My question in simpler way , 

I need the temporal table to control the Start and End date of each row based on a column Which is not part of the primary key of the table . Is it possible ?

 

Junior Contributor

Re: Temporal table and PRIMARY INDEX

Yes, it is, why don't you simply try it?

Btw, there's no (normal) Primary Key in a Temporal Table or did you mean Primary Index?

Enthusiast

Re: Temporal table and PRIMARY INDEX

I mean Primary Index ,

 

Need temporal to control validity column based on Account_ID , While the primary index of the table should be Account_SK

 

CREATE MULTISET TABLE imart.DIM_ACCOUNT ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
     Account_SK INTEGER ,
      ACCOUNT_ID INTEGER ,
      Validity PERIOD(DATE) AS VALIDTIME,
      CURRENCY_ID INTEGER ,
      CUSTOMER_ID INTEGER TITLE 'CUSTOMER_ID',
      APPLICAITON_ID INTEGER TITLE 'APPLICAITON_ID'      )PRIMARY INDEX ( Account_SK );

 

Teradata Employee

Re: Temporal table and PRIMARY INDEX

Hi khaled_tec,

 

I'm pretty sure your surrogate key here is not needed.

Try this code :

 

CREATE MULTISET TABLE imart.DIM_ACCOUNT
, FALLBACK
, NO BEFORE JOURNAL
, NO AFTER JOURNAL
, CHECKSUM = DEFAULT
, DEFAULT MERGEBLOCKRATIO
( ACCOUNT_ID       INTEGER    not null
, Valid_Start      DATE       not null
, Valid_End        DATE       not null compress (date '9999-12-31')
, PERIOD FOR Validity (Valid_Start, Valid_End) AS VALIDTIME
, CURRENCY_ID      INTEGER
, CUSTOMER_ID      INTEGER
, APPLICATION_ID   INTEGER
, PRIMARY KEY (ACCOUNT_ID, Validity WITHOUT OVERLAPS)
)
PRIMARY INDEX ( ACCOUNT_ID );

 

 

Junior Contributor

Re: Temporal table and PRIMARY INDEX

Again, it is allowed, simply try it.

Of course performance might be bad when you don't specify the PI for Updates/Deletes

 

Btw, I don't know why you think you need that.

Enthusiast

Re: Temporal table and PRIMARY INDEX

The temporal manage the vallidity column accourding to the Primary Index define on the table , 

Can you please share a sample of the creation table , and Sample of the Insert ,Update.

 

I have to generate Identity column which is mapped to the natural key , to do master data managment excersie. 

 ( the nature key in some source system is Passport Number , While other source use Debit card No , )  So I have to generate a unique Identity for every customer and  map Passport Number and Debit Card with this Identity number .

If source A has one customer , and Source B has one customer , The DWH may conpine the 2 sources as One customer , The He is the same customer repeated in different sources.

 

The surogate key will be used to match kimball standard in dimenssion modeling , Where the dimenssion table has ONE and only One column to Identify the row at a certain point in type 

Junior Contributor

Re: Temporal table and PRIMARY INDEX

How do you want to maintain a SCD2 when you got source rows from two different tables with overlapping data?

 

What sense makes a Temporal Table when you maintain it on your own?

The validity of a row is based on VT & TT, not on an additional unique identifier.

Kimbal didn't know about Temporal Tables :-)

Enthusiast

Re: Temporal table and PRIMARY INDEX

My case is I will source the customer from 2 sources into one dimenssion table (SCD2).

and I will set periorty between sources system and columns in dim_Customer. , I Just want the identity column to avoid having duplicate customers who in real is same customer in different source system.

 

What I have understand from your points , If I stick on Kimbal standard , and generate a Surrogate Key for every update occured on the identity Key , I can not use temporal feature , :-(