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
|Surrugate Key||Identity||Natural Key||Customer Name||Start DT||End Date|
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 .
Solved! Go to Solution.
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.
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 ...
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 ?
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 );
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 );
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.
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
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 :-)
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 , :-(