How To identify the interfaces that are using a particular mapping value?

1009817

I need to replace a hard coded mapping expression value with a ODI variable. Now the question is . how to identify as to how many interfaces are actually using that hard coded value in its mapping expression ?
The only way I right now know is manually checking all the interfaces in their "Quick Edit" Mode and if the value is mapped I would be replacing it with that variable.

Is there any way like performing a search for all interfaces that are using the hard coded value and then I can replace it with the variable in those interfaces.

Bos

Hi,

Have you got access to the ODI repository schema? there is a table SNP_TXT_CROSSR where it holds the mapping information. The column STRING_ETL contains this - you can query this table to see how often it is used - it has got the I_COL column which relates to the specific column in the mapping so you can join that from SNP_COL to SNP_TABLE to find the table. from there, you should be able to find the interfaces by checking where it is used on Target from the ODI Model definition.

Cheers

Bos

1009817

Hi,

Thank you for your response. But the value for I_COL is being populated as null in my query. Although I get some 30 records as output but all of them are showing NULL value for the mentioned column.
Is there any other way to get that ? The query I used is -

select * from SNP_TXT_CROSSR where STRING_ELT LIKE '%12-31%' ;

DecaXD

Hi,

try this query (really ugly but it was fast to write)

Select * from SNP_POP
where I_POP in
(
Select I_POP from SNP_POP_COL
where i_pop_col in
( select I_POP_COL from SNP_TXT_CROSSR where STRING_ELT LIKE '%12-31%')
)

i think there was a typo in Bos reply. Also give a look (but i haven't checked) at SNP_EXT_TXT. if your hardcoded is under 250 char you could try

Select * from SNP_POP
where I_POP in
(
Select I_POP from SNP_POP_COL
where i_txt_map in
( select I_TXT from SNP_EXT_TXT where TXT LIKE '%12-31%')
)

but you have to test (i'm not sure if it was I_TXT_MAP or I_TXT_MAP_TXT)

Let us know

1009817

Hi,

i modified a lil and the analysis worked out..

the Final query i framed is ---


SELECT SP.POP_NAME, SPC.COL_NAME, spc.I_TXT_MAP, stc.i_txt FROM SNP_POP SP, SNP_POP_COL SPC, SNP_TXT_CROSSR stc
WHERE SP.I_POP = SPC.I_POP AND
SPC.I_TXT_MAP = STC. I_TXT
AND STC.STRING_ELT LIKE '%12-31%';

And it would be SNP_POP_COL.I_TXT_MAP


However, only with query STRING_ELT LIKE '%12-31%' , I get some 30 records as I stated before .. but after framing the final query ,
I jus observed 11 records.

there are afew I_TXT values of SNP_TXT_CROSSR that do not map with I_TXT_MAP column of SNP_POP_COL . so those are getting missed. I tried other combinations but there is no way I found out to get the missing I_TXT_MAP values too.
SO, as against 35 records I have 11 as output but apart from these 11 interfaces there are other interfaces that I know of that contain matching string..

So a lil disarray but still
Thank You both of you for your responses. !!

Ill try to seek if anything works out.

Edited by: 1006814 on May 24, 2013 12:12 AM