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.
