I am making a sample application in ODI using the samplesales schema that comes along with OBIEE. I truncated the SAMP_OFFICES_D dimension table and trying to load the data from a text file. I added a surrogate key to the table, and loading the value using an ODI sequence.
ODI sequences come in three flavours – standard, specific and native. Standard sequences are sequences whose current values are maintained in the repository. Specific sequence values are stored in a database table cell. ODI will read the value first, lock that row and then update that same row once the job is completed. Both standard and specific should be used when the underlying database does not have proper support for sequences (weird, can’t think of any DBMS w/o sequences). Native, as the name suggests, are the native sequences managed by the underlying database system.
I created a standard sequence and used :SEQ_OFFICE_ID_NEXTVAL for the surrogate key mapping. When I ran the interface, I got the unique constraint violation error for the primary key (surrogate key OFFICE_ID). When I read the documentation, I found that Standard and Specific constraints cannot be used in KMs or procedures that use a single INSERT/UPDATE… SELECT in the target command to load the data.
I was using the IKM SQL Incremental Update KM and as the screenshot above clearly says, it uses INSERT INTO .. SELECT FROM statement. In such cases, standard or specific sequences cannot be used and the only work-around is to use native sequences.
My Tech Accounts 