ODI Interview Questions and Answers

ODI Interview Questions and Answers

1)We have an options recyle_errors on target table Flow tab. how exactly it works?

A)ODI KM’s option called Recycle Errors does the following

ODI reads from the E$ tables and loads into the I$ capturing all the records

 which are not present in the I$ by matching on the Primary Key or Unique Keys.

2)-I'd like to ask if it possible to use a stored procedure (maybe in cursor) as a data source?

A) As far as i know i dont think you can. 

Either you can use a table, or view or synonym. 

If you have a sql query convert it to a view or use a temporary interface.

 Iam not very sure the codes used in your procedure. If you can put the code here then it would be better to understand and develop the logic.

If its a direct insert to your table you can use odi procedure with java code and jython codes. 

We can write the return resultset of your procedure to a table with help help java/jython without taking the help of interface.

3)

Is it possible to place a variable as a parameter in an API Reference? 

I have a project variable (var1) which uses an API Reference <%=odiRef.getContext("CTX_CODE")%> to get the context code. 

I also have another project variable (var2) that uses the API reference <%=odiRef.getSchemaName ()%>.

Then I want to use var1 as a parameter in API <%=odiRef.getSchemaName("SCHEMA_NAME", "VAR1", "W")%>... is this possible?

A)Though i doubt on this still Check it once.

<%=odiRef.getSchemaName("SCHEMA_NAME", "#VAR1", "W")%>

4)

Whenever i start the CDC it is creating, J$ tables, JV$ Views, UT$ and DT$ triggers.

 but when i stop the CDC it is dropping the tables and views but not the triggers. 

Can anyone please tell me why it is not dropping the tirggers . 

one more issues i have is when i check the trigger script it is pointing to SNP_SUBSCRIBERS in different schema. 

i am not understanding why it is pointing to SNP_SUBSCRIBERS in another schema.please help me with this

A)SNP_SUBSCRIBER will be created in the work schema defined under your Source connection (in Topology) -

 I would advise you keep it here to be honest, saves cluttering up the data schema in the source system.

If ODI is not attempting to drop triggers, why not create an additional KM step to do this for you?

If you study the JKM, there are options to determine when a step runs (Create Journal, Drop Journal,

 Extend Window etc etc.) - Just grab the API ref to create trigger name and re-use in a simlar fashion to drop it.

5)

I am using OdiScpPut for scp the file from ODI server to another Hyperion Server, getting following error.

ODI Command used:

OdiScpPut "-HOST=<Target Server Name>" "-USER=<User Name>" "-PASSWORD=<encrypted password>" 

"-LOCAL_DIR=C:\ODI\FILES" "-LOCAL_FILE=EXTRACT.csv" "-REMOTE_DIR=/eas/app/BI/test" "-COMPRESSION=NO" 

"-STRICT_HOSTKEY_CHECKING=NO" "-TIMEOUT=10"

error-caused by :timeout (socket is not established)

6) how to prevent overwrite existing data in table

A)In target propertices of flow tab if you are using Incremental update tab

Set Option UPDATE = false.   
OR 
A) Use Control append KM

7)

I have Table1(col1,col2),Table2(col1,col2,col3) and excel spredsheet.

I need to compare value of col1 from table1 with col1 of table2.

If it is present in table2 then no action and if not insert that record in excel file.

A)

Reverse engineer Table1, Table2 and your excel spreadsheet (with relevant topology, ODBC / JDBC connections to write out to excel)

Two ways of doing it in an interface :

Option 1 (if using 11G) 

Use the Minus operator , First data set : Table1 , Second data set : Table 2 , map only colum1 ,

 the resulting SQL will load all the values from Table1 that do not exist in table2 to your I$ table, then write out to the Excel File.

Option 2 (if using 10G or alternative to the above)

Source datastore : Table 1

Target datastore : Excel File.

Filter on Table 1 ,NOT EXISTS (select 'x' from <%=odiRef.getObjectName("Table2")%> 

where <%=odiRef.getObjectName("Table1")%>.col1 = <%odiref.getobjectname("Table2")%>.col1 )

so the SQL when loading C$ table will look like :

select

col1,col2 from Table 1 where 1=1 and not exists (select 'x' from table2 where table1.col1 = table2.col1)

This will only give you the values from Table1 that dont exist in Table2, into the I$ table , ready to go out to your excel file.

8) How to tune a mapping in ODI ?

A)Choose between various knowledge modules (KMs) for loading and integration.

 Each one performs differently based on the source/target technology and the type of data that you're dealing with. 

Some perform better than others depending on the situation. Some KMs use set-based transactions, others use PL/SQL blocks to commit data in small batches.

Choose where ODI should execute each join/filter/transform. You can usually choose between the source, target or a separate staging area.

What you choose here can influence how much source data you will extract and process.

The ODI KMs utilize temporary tables to perform the loading, checking and transform operations and will create the necessary indexes for performance.

 It will also analyse these tables as part of the flow to enable the DB engine to generate accurate execution plans.

9)  what is the purpose of defining the OLAP type (dimension, fact table, SCD) in the data store properties in ODI?

 I'm already familiar with dimensions & fact tables as used in data warehousing modeling. 

I am just wondering how specifying the OLAP type of a data store affects its behavior.

A)Pretty sure it opens up the UI to set more options depending on that option, not at PC so cant check.

e.g SCD - Surrogate Key, Current Record, Start Date, End Date etc.

10)what is the difference between JKM Oracle Simple and JKM Oracle Consistent?

A)  With simple journalisation, you focus on 1 table / 1 interface.

When you launch the interface, the journalized data are extracted, then loaded into the target, and the J$ table is deleted.

There is no relation other tables in the CDC process.

It means you can capture changed data in a table A, but not in table B if you want.

With Consistent journalisation, you focus on a set of tables (ex : an entire model). You will focus on a package, and not an unique interface.

When you launch an interface, the journalized data will be extracted and loaded into the target, 

but the J$ table won't be deleted until you decide to purge the journal (at the end of the package.).

This type of journalisation is more complicated, but very important if you have relations between journalised table.

for instance, suppose you have an "order table" and "order line table" in your source. With consistent CDC, 

the data you will capture will be consistent between the 2 tables. You won't capture only order ligne table data, but no order table data.

Actually, the J$ table and the JV$ view structures are different in Simple CDC, or Consistent CDC.

To make it simple, the simple CDC has to be used ONLY if you focus on 1 source table, or various independant source table. 

But if you have relationships between many source table and you want to journalize all these table, you should use consistent CDC. Then consistent JKM.

JKM Oracle consistenet takes care of referential integrity 

i.e. befor populating journalised data in child table, it will make sure that parent table has related data.

While Simple JKM doesnt check referential integrity

11)

I just want to know the procedure to do testing in ODI. 

And along with how to move data from one environment to another environment, say for example now I am working with DEV environment. I want to 

move this data to Testing environment and after that to the Production.

A)What do you mean by "moving data" ?

If you're talking about moving the ODI object, like scenario, projects, interfaces... then use "export / import" tool to move your source into other environment.

If you're talking about data that are in your database table, then use "context". Create 1 context for development, 1 context for Testing and so on.

Just change the context when you want to change data / environment in order to do your QA tests.

12) 

 I am attempting to re-organize the Projects in my ODI instance and can't seem to copy/paste an interface from one Project to another.

 Is this not allowed in ODI or am I doing something wrong? 

A)That's not a stupid question at all.

Actually you can't move it or copy/cut-paste it, because they have some reference in their project.

But you can export it and then import it in your other project

13) I need to process the data in each line of a file, controlling for each line, lengths, comparisons with other tables, etc.

 where these lines are correct in other table and insert them in cases of error, in another file, but not as can be.

I might give some idea.

A)

There are 2 ways to control your file.

1) Define the file as an ODI Datastore. 

When ODI will read it, it will reject the rows that doesn't fit to your specification (length, type...) in a .BAD file and an ERR.file. 

These 2 files are located in the same folder than your original file.

2) Define some constraints in your datastore : alternative key, references and conditions. Thanks to alternative key,

 your can reject the rows that are not unique. Thanks to references, you can compare data of column with values of other tables (like foreign key) and reject the bad ones.

Thanks to conditions, you can set custom rules like "this field must be equal to A, B, C or D only".

These constraints will be set in an ODI interface, whose "working schema" will be set on a specific database.

Indeed, you cannot use the constraints on the file technology, because it requires to create an error table on a database.

14)

For instance, create a simple interface that use your file on source and load any dummy table, but with insert=NO and update=NO. 

The important thing is to activate the flow control in your IKM.

OR... you can also create a full customised KM that will control your file, but it's more difficult and not native. 

15)In one of my interface when ever I executes there are some duplicates are coming to the target table. 

Say if the count of the rows around 5000 in the source table and in the target it would be around 120000. Even after using the distinct rows in the flow control some bugs are coming.

Can you please help how solve this...

Note:In source table one column contains surrogate key.

IKM oracle control append is the KM I am using

A)Using the Control Append IKM will always add the data that is in the Source to the Target, unless you truncate or delete from the Target first.

 If you have data in the Source that has already been loaded to the Target, and you do not truncate the Target prior to the next load, you will have duplicates.

Are you truncating the Target or is the Source data always "new" each time the Interface is run?

Yes I am Truncating the target table, but also there will be a duplicates in the target.

 We are using three source tables out of which two tables having join condition, and the third table have filter.

Just check the number of rows loaded to C$ table. It the number of rows is as per your expction then you need to look into I$ table and the number of rows.

If the C$ table count is more than the actual count then the problem is with cross join.

For my interface rows are inserted with duplicate values and when ever I checked for the data I$ table and C$ table that table is not existing for me.

You can this in your operator tab. Open the C$ step (Load Data in LKM )and I$ steps ( Insert flow into I$ in IKM) to see the records inserted,update,deleted etc.

16)

How to pass a variable more than one values?

A)You can't.

During 1 session, 1 ODI variable can have only 1 value at a time.

If you need more than 1 value, you will have to do somethink like a loop inside your package, and refresh the variable value each time.