How to configure ODI to auto-increment rows for IKM Incremental Update
I have question about Autoincrement ID for my 'dimension' and IKM: Incremental Update.
I have source table with only one column: SUPPLIER_NAME. It has 23 rows with suppliers name.
I have target table with two columns: SUPPLIER_ID, SUPPLIER_NAME
Next I want on SUPPLIER_ID create autoincrement ID for every new row and use IKM: increment update - where someone add new supplier I only want update table (add new rowe) and add for this supplier new ID (next value for autoincrement).
How can I do this?
I create sequence on DB like:
Create sequence autoinc start with 1
increment by 1
minvalue 1
maxvalue 1000000;
In ODI I create sequence:
AutoIncrementDIm --> Increment: 1,
Native seuqence - native sequence name: autoinc
Next I create ODI mapping:
Source table (with one colum) map to target table (with ID and NAME).
Map supplier_name to supplier_name
For ID I use: #NFS_HD.AutoIncrementDim_NEXTVAL
In logical part I set integration type: Incremental Update
In physical part I set for IKM: IKM Oracle Merge
For my first running everthing it's ok. I have autoincrement from 1 to 23 for every supplier.
But when I have new rows with new supplier name in source table and run my mapping I get something like:
Results
New row (with new supplier) has 47 ID ... I think that s because sequence was running for every rows.
What I must change to correct or what it is the better solution to do this?
oracle oracle-data-integrator
asked Nov 22 '18 at 15:30
user3764298user3764298
104
add a comment |
I have question about Autoincrement ID for my 'dimension' and IKM: Incremental Update.
I have source table with only one column: SUPPLIER_NAME. It has 23 rows with suppliers name.
I have target table with two columns: SUPPLIER_ID, SUPPLIER_NAME
Next I want on SUPPLIER_ID create autoincrement ID for every new row and use IKM: increment update - where someone add new supplier I only want update table (add new rowe) and add for this supplier new ID (next value for autoincrement).
How can I do this?
I create sequence on DB like:
Create sequence autoinc start with 1
increment by 1
minvalue 1
maxvalue 1000000;
In ODI I create sequence:
AutoIncrementDIm --> Increment: 1,
Native seuqence - native sequence name: autoinc
Next I create ODI mapping:
Source table (with one colum) map to target table (with ID and NAME).
Map supplier_name to supplier_name
For ID I use: #NFS_HD.AutoIncrementDim_NEXTVAL
In logical part I set integration type: Incremental Update
In physical part I set for IKM: IKM Oracle Merge
For my first running everthing it's ok. I have autoincrement from 1 to 23 for every supplier.
But when I have new rows with new supplier name in source table and run my mapping I get something like:
Results
New row (with new supplier) has 47 ID ... I think that s because sequence was running for every rows.
What I must change to correct or what it is the better solution to do this?
oracle oracle-data-integrator
asked Nov 22 '18 at 15:30
user3764298user3764298
104
add a comment |
I have question about Autoincrement ID for my 'dimension' and IKM: Incremental Update.
I have source table with only one column: SUPPLIER_NAME. It has 23 rows with suppliers name.
I have target table with two columns: SUPPLIER_ID, SUPPLIER_NAME
Next I want on SUPPLIER_ID create autoincrement ID for every new row and use IKM: increment update - where someone add new supplier I only want update table (add new rowe) and add for this supplier new ID (next value for autoincrement).
How can I do this?
I create sequence on DB like:
Create sequence autoinc start with 1
increment by 1
minvalue 1
maxvalue 1000000;
In ODI I create sequence:
AutoIncrementDIm --> Increment: 1,
Native seuqence - native sequence name: autoinc
Next I create ODI mapping:
Source table (with one colum) map to target table (with ID and NAME).
Map supplier_name to supplier_name
For ID I use: #NFS_HD.AutoIncrementDim_NEXTVAL
In logical part I set integration type: Incremental Update
In physical part I set for IKM: IKM Oracle Merge
For my first running everthing it's ok. I have autoincrement from 1 to 23 for every supplier.
But when I have new rows with new supplier name in source table and run my mapping I get something like:
Results
New row (with new supplier) has 47 ID ... I think that s because sequence was running for every rows.
What I must change to correct or what it is the better solution to do this?
oracle oracle-data-integrator
asked Nov 22 '18 at 15:30
user3764298user3764298
104
I have question about Autoincrement ID for my 'dimension' and IKM: Incremental Update.
I have source table with only one column: SUPPLIER_NAME. It has 23 rows with suppliers name.
I have target table with two columns: SUPPLIER_ID, SUPPLIER_NAME
Next I want on SUPPLIER_ID create autoincrement ID for every new row and use IKM: increment update - where someone add new supplier I only want update table (add new rowe) and add for this supplier new ID (next value for autoincrement).
How can I do this?
I create sequence on DB like:
Create sequence autoinc start with 1
increment by 1
minvalue 1
maxvalue 1000000;
In ODI I create sequence:
AutoIncrementDIm --> Increment: 1,
Native seuqence - native sequence name: autoinc
Next I create ODI mapping:
Source table (with one colum) map to target table (with ID and NAME).
Map supplier_name to supplier_name
For ID I use: #NFS_HD.AutoIncrementDim_NEXTVAL
In logical part I set integration type: Incremental Update
In physical part I set for IKM: IKM Oracle Merge
For my first running everthing it's ok. I have autoincrement from 1 to 23 for every supplier.
But when I have new rows with new supplier name in source table and run my mapping I get something like:
Results
New row (with new supplier) has 47 ID ... I think that s because sequence was running for every rows.
What I must change to correct or what it is the better solution to do this?
oracle oracle-data-integrator
oracle oracle-data-integrator
asked Nov 22 '18 at 15:30
user3764298user3764298
104
asked Nov 22 '18 at 15:30
user3764298user3764298
104
asked Nov 22 '18 at 15:30
user3764298user3764298
104
asked Nov 22 '18 at 15:30
user3764298user3764298
104
asked Nov 22 '18 at 15:30
user3764298user3764298
104
104
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
On the logical mapping, click on your SUPPLIER_ID target attribute. In the property pane in the Target tab, unselect the Update checkbox. It means that this attribute will not be used in the update query.
Also make sure that the SUPPLIER_NAME attribute is set as a key so the IKM use it to know when it should do an insert or an update.
answered Nov 22 '18 at 16:29
JeromeFrJeromeFr
96311018
Hi JeromeFr, Thanks for reponse. It is working for only update new row - but when I use 'solution' like yours in first running - when I have empty table with supplier_id || supplier_name and I want running ETL I get id = 1 for every row ans supplier_name. Must I have two etl process? One for first loading data in table and second for update only new rows?
– user3764298
Nov 23 '18 at 17:04
Update: Your solution it is working when its only ONE new row - when I have 3 new row I get the same ID for this new rows.
– user3764298
Nov 23 '18 at 17:17
When I use: #NFS_HD.AutoIncrementDim_NEXTVAL -- I get the same ID for new rows (I have 1-24 supplier, add new 3 suppliers and after etl I have three 25 ID for three new suppliers) :NFS_HD.AutoIncrementDim_NEXTVAL -- I get higher ID for example now I have 1 - 24, 25, 25, 25 --- next I add two new suppliers and use etl and result like: 1-24,25,25,25,53,54.
– user3764298
Nov 23 '18 at 17:24
Yes you need to use : and not #. : is binding in PL/SQL while # is for a substitution at ODI level. So for a set-based operation # will return the same value. : will returns different values. The fact it jumped from 1 to 24 is because Oracle sequences are not gapless.
– JeromeFr
Nov 26 '18 at 20:13
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434156%2fhow-to-configure-odi-to-auto-increment-rows-for-ikm-incremental-update%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
On the logical mapping, click on your SUPPLIER_ID target attribute. In the property pane in the Target tab, unselect the Update checkbox. It means that this attribute will not be used in the update query.
Also make sure that the SUPPLIER_NAME attribute is set as a key so the IKM use it to know when it should do an insert or an update.
answered Nov 22 '18 at 16:29
JeromeFrJeromeFr
96311018
Hi JeromeFr, Thanks for reponse. It is working for only update new row - but when I use 'solution' like yours in first running - when I have empty table with supplier_id || supplier_name and I want running ETL I get id = 1 for every row ans supplier_name. Must I have two etl process? One for first loading data in table and second for update only new rows?
– user3764298
Nov 23 '18 at 17:04
Update: Your solution it is working when its only ONE new row - when I have 3 new row I get the same ID for this new rows.
– user3764298
Nov 23 '18 at 17:17
When I use: #NFS_HD.AutoIncrementDim_NEXTVAL -- I get the same ID for new rows (I have 1-24 supplier, add new 3 suppliers and after etl I have three 25 ID for three new suppliers) :NFS_HD.AutoIncrementDim_NEXTVAL -- I get higher ID for example now I have 1 - 24, 25, 25, 25 --- next I add two new suppliers and use etl and result like: 1-24,25,25,25,53,54.
– user3764298
Nov 23 '18 at 17:24
Yes you need to use : and not #. : is binding in PL/SQL while # is for a substitution at ODI level. So for a set-based operation # will return the same value. : will returns different values. The fact it jumped from 1 to 24 is because Oracle sequences are not gapless.
– JeromeFr
Nov 26 '18 at 20:13
add a comment |
On the logical mapping, click on your SUPPLIER_ID target attribute. In the property pane in the Target tab, unselect the Update checkbox. It means that this attribute will not be used in the update query.
Also make sure that the SUPPLIER_NAME attribute is set as a key so the IKM use it to know when it should do an insert or an update.
answered Nov 22 '18 at 16:29
JeromeFrJeromeFr
96311018
Hi JeromeFr, Thanks for reponse. It is working for only update new row - but when I use 'solution' like yours in first running - when I have empty table with supplier_id || supplier_name and I want running ETL I get id = 1 for every row ans supplier_name. Must I have two etl process? One for first loading data in table and second for update only new rows?
– user3764298
Nov 23 '18 at 17:04
Update: Your solution it is working when its only ONE new row - when I have 3 new row I get the same ID for this new rows.
– user3764298
Nov 23 '18 at 17:17
When I use: #NFS_HD.AutoIncrementDim_NEXTVAL -- I get the same ID for new rows (I have 1-24 supplier, add new 3 suppliers and after etl I have three 25 ID for three new suppliers) :NFS_HD.AutoIncrementDim_NEXTVAL -- I get higher ID for example now I have 1 - 24, 25, 25, 25 --- next I add two new suppliers and use etl and result like: 1-24,25,25,25,53,54.
– user3764298
Nov 23 '18 at 17:24
Yes you need to use : and not #. : is binding in PL/SQL while # is for a substitution at ODI level. So for a set-based operation # will return the same value. : will returns different values. The fact it jumped from 1 to 24 is because Oracle sequences are not gapless.
– JeromeFr
Nov 26 '18 at 20:13
add a comment |
On the logical mapping, click on your SUPPLIER_ID target attribute. In the property pane in the Target tab, unselect the Update checkbox. It means that this attribute will not be used in the update query.
Also make sure that the SUPPLIER_NAME attribute is set as a key so the IKM use it to know when it should do an insert or an update.
answered Nov 22 '18 at 16:29
JeromeFrJeromeFr
96311018
On the logical mapping, click on your SUPPLIER_ID target attribute. In the property pane in the Target tab, unselect the Update checkbox. It means that this attribute will not be used in the update query.
Also make sure that the SUPPLIER_NAME attribute is set as a key so the IKM use it to know when it should do an insert or an update.
answered Nov 22 '18 at 16:29
JeromeFrJeromeFr
96311018
answered Nov 22 '18 at 16:29
JeromeFrJeromeFr
96311018
answered Nov 22 '18 at 16:29
JeromeFrJeromeFr
96311018
answered Nov 22 '18 at 16:29
JeromeFrJeromeFr
96311018
96311018
Hi JeromeFr, Thanks for reponse. It is working for only update new row - but when I use 'solution' like yours in first running - when I have empty table with supplier_id || supplier_name and I want running ETL I get id = 1 for every row ans supplier_name. Must I have two etl process? One for first loading data in table and second for update only new rows?
– user3764298
Nov 23 '18 at 17:04
Update: Your solution it is working when its only ONE new row - when I have 3 new row I get the same ID for this new rows.
– user3764298
Nov 23 '18 at 17:17
When I use: #NFS_HD.AutoIncrementDim_NEXTVAL -- I get the same ID for new rows (I have 1-24 supplier, add new 3 suppliers and after etl I have three 25 ID for three new suppliers) :NFS_HD.AutoIncrementDim_NEXTVAL -- I get higher ID for example now I have 1 - 24, 25, 25, 25 --- next I add two new suppliers and use etl and result like: 1-24,25,25,25,53,54.
– user3764298
Nov 23 '18 at 17:24
Yes you need to use : and not #. : is binding in PL/SQL while # is for a substitution at ODI level. So for a set-based operation # will return the same value. : will returns different values. The fact it jumped from 1 to 24 is because Oracle sequences are not gapless.
– JeromeFr
Nov 26 '18 at 20:13
add a comment |
Hi JeromeFr, Thanks for reponse. It is working for only update new row - but when I use 'solution' like yours in first running - when I have empty table with supplier_id || supplier_name and I want running ETL I get id = 1 for every row ans supplier_name. Must I have two etl process? One for first loading data in table and second for update only new rows?
– user3764298
Nov 23 '18 at 17:04
Update: Your solution it is working when its only ONE new row - when I have 3 new row I get the same ID for this new rows.
– user3764298
Nov 23 '18 at 17:17
When I use: #NFS_HD.AutoIncrementDim_NEXTVAL -- I get the same ID for new rows (I have 1-24 supplier, add new 3 suppliers and after etl I have three 25 ID for three new suppliers) :NFS_HD.AutoIncrementDim_NEXTVAL -- I get higher ID for example now I have 1 - 24, 25, 25, 25 --- next I add two new suppliers and use etl and result like: 1-24,25,25,25,53,54.
– user3764298
Nov 23 '18 at 17:24
Yes you need to use : and not #. : is binding in PL/SQL while # is for a substitution at ODI level. So for a set-based operation # will return the same value. : will returns different values. The fact it jumped from 1 to 24 is because Oracle sequences are not gapless.
– JeromeFr
Nov 26 '18 at 20:13
Hi JeromeFr, Thanks for reponse. It is working for only update new row - but when I use 'solution' like yours in first running - when I have empty table with supplier_id || supplier_name and I want running ETL I get id = 1 for every row ans supplier_name. Must I have two etl process? One for first loading data in table and second for update only new rows?
– user3764298
Nov 23 '18 at 17:04
Hi JeromeFr, Thanks for reponse. It is working for only update new row - but when I use 'solution' like yours in first running - when I have empty table with supplier_id || supplier_name and I want running ETL I get id = 1 for every row ans supplier_name. Must I have two etl process? One for first loading data in table and second for update only new rows?
– user3764298
Nov 23 '18 at 17:04
Update: Your solution it is working when its only ONE new row - when I have 3 new row I get the same ID for this new rows.
– user3764298
Nov 23 '18 at 17:17
Update: Your solution it is working when its only ONE new row - when I have 3 new row I get the same ID for this new rows.
– user3764298
Nov 23 '18 at 17:17
When I use: #NFS_HD.AutoIncrementDim_NEXTVAL -- I get the same ID for new rows (I have 1-24 supplier, add new 3 suppliers and after etl I have three 25 ID for three new suppliers) :NFS_HD.AutoIncrementDim_NEXTVAL -- I get higher ID for example now I have 1 - 24, 25, 25, 25 --- next I add two new suppliers and use etl and result like: 1-24,25,25,25,53,54.
– user3764298
Nov 23 '18 at 17:24
When I use: #NFS_HD.AutoIncrementDim_NEXTVAL -- I get the same ID for new rows (I have 1-24 supplier, add new 3 suppliers and after etl I have three 25 ID for three new suppliers) :NFS_HD.AutoIncrementDim_NEXTVAL -- I get higher ID for example now I have 1 - 24, 25, 25, 25 --- next I add two new suppliers and use etl and result like: 1-24,25,25,25,53,54.
– user3764298
Nov 23 '18 at 17:24
Yes you need to use : and not #. : is binding in PL/SQL while # is for a substitution at ODI level. So for a set-based operation # will return the same value. : will returns different values. The fact it jumped from 1 to 24 is because Oracle sequences are not gapless.
– JeromeFr
Nov 26 '18 at 20:13
Yes you need to use : and not #. : is binding in PL/SQL while # is for a substitution at ODI level. So for a set-based operation # will return the same value. : will returns different values. The fact it jumped from 1 to 24 is because Oracle sequences are not gapless.
– JeromeFr
Nov 26 '18 at 20:13
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434156%2fhow-to-configure-odi-to-auto-increment-rows-for-ikm-incremental-update%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
