IQ

Experience Level ~3

Informatica

Qn1 – load file containing 100 records in a target file, following below approach

1-10 à file 1

11-20à file 2 .. and so on

Qn2 - Active and passive transformations

Qn3 – Is sorter and Union active transformation if yes, why?

Qn4 – I am having a file as below

EID, ENAME,SAL

1,Robert, 30

2,William, 40 .. until 10 records

I want a footer record in a flat file target with the above records which would make the file look as below

EID, ENAME,SAL

1,Robert, 30

2,William, 40 .. until 10 records

The sum of Salary is 70

(considering only 2 rows given in the example above) . This question was most frequently asked.

Qn5 – Which transformation create Caches.

Qn6 – I have a file with some duplicate records. I want all the unique records pushed in a file and repeated records pushed to a different file.

Src

1,1,2,3,4,5,5,6,7,2,1,6,3,5

Tgt 1- unique

1,2,3,4,5,6,7

Static and Dynamic lookup cache

Qn7 – Lookup is an active or passive transformation

Qn8 – SCD 2 implementation

Qn9 – Different caches in Lookup Transformation.

Qn10 – Difference between Static and Dynamic lookup cache

Qn11- Different type of ports and their order of execution of ports in Informatica expression transformation.

UNIX

Qn1 – command used to replace string

Qn2 – Command to fetch third column from a CSV file

Qn3 – Status of last executed command

SQL

Qn1– delete all remaining records except the latest record

DELETE FROM student

WHERE ApplicationDateTime <> (SELECT max(ApplicationDateTime)

                              FROM student s2

                              WHERE s2.StudentID  = student.StudentID)

Qn2 – second highest salary

Qn3 – Analytical functions. This was asked in a twisted manner. The question asked was I have data as below,

EmpID	Dept	Sal
1	10	100
2	20	400
3	10	800
4	30	400
5	20	300
6	20	300

I want the output to look like

EmpID	Dept	Sal	Total Sal
1	10	100	900
2	20	400	1000
3	10	800	900
4	30	400	400
5	20	300	1000
6	20	300	1000

Where total Sal comprises of data summed up dept wise.

Experience level 5+

Informatica

Qn1 - I have a file in the below format

USD

50,55,40,23

60,34,20,36

INR

1,3,4,6

5,3,6,5

EUR

80,84,85,88

85,86,88,89

I want the output in the below tabular format

Currency	Val1	Val2	Val3	Val4
USD	50	55	40	23
USD	60	34	20	36
INR	1	3	4	6
INR	5	3	6	5
EUR	80	84	85	88
EUR	85	86	88	89

Qn2 - 3 Flat files having same structure are to be loaded in the target table.. We need to load the file in the target table ad ensure no duplicates are loaded.

2 scenarios here

1)      There can be duplicates in the file which needs to be avoided.

2)      The files can have duplicates between them i.e  File 1 and file 2 can have same record. We need to avoid these as well.

Qn3 – This was asked as a follow up on other question. If my joiner transformation receives inputs from 2 pipelines coming from the same source, is there a specific property I need to set to allow that?

Qn4 – Cache files created in informatica and what will the files contain.

SQL

Qn1 – I have data in a table as below

Seq	Emp ID	Start Date	End Date
1	1	1/1/2016
2	2	2/20/2016
3	3	3/11/2016
4	1	1/1/2017
5	2	8/31/2016
6	1	3/1/2017

The above table has some data discrepancies, wherein the end dates have been deleted accidently. We need to write a single update statement, which would fill the end dates as below

Seq	Emp ID	Start Date	End Date
1	1	1/1/2016	12/31/2016
2	2	2/20/2016	8/30/2016
3	3	3/11/2016	NULL
4	1	1/1/2017	2/28/2017
5	2	8/31/2016	NULL
6	1	3/1/2017

So end date in seq 1 is start date of seq 4 minus 1 day.

Most of the questions asked to 3+ were repeated to 5+ as well. Also there were a few adhoc questions asked as people were solving scenario based qns trying to understand the reason behind a particular implementation like, why a sorter is used here, order of ports when writing the variable logic, etc.

Variables in OBIEE 11g

Variables in Oracle OBIEE 11g :-

There are basically 4 different types of variables in OBIEE 11g.
1. Session Variables
2. Repository Variables
3. Presentation Variables

4. Request Variables.


Session Variables:
1. As the name suggests, session variables are created during the creation of session
i.e., as soon as a user logs into the BI server. So, Every login has its own session
variable.

2. There are two types of session variables
    1. System (which are defined by OBIEE and are reserved)
    2. Non System
    which are defined by developers.

3. Session Variables can be created only through Oracle BI Administration Tool.

Referencing session variable:

For displaying session variables, we should use
@{biServer.variables['NQ_SESSION.VariableName']} .
For using session variables in expression, we should use
VALUEOF(NQ_SESSION.“VariableName”).

System Session Variable
EXP :-
NQ_SESSION.LOGLEVEL
USER,USERGUID,GROUP,ROLES,PERMISSIONS,PROXY

Repository Variables:

1. A repository variable is a variable that has a single value at any point in time.

2. There are two types of repository variables
    1. Static (which changes only if admin or developer changes it value).
    2. Dynamic ( value is refreshed using a query).

3. Repository variables can be created only through Oracle BI Administration Tool.

Referencing repository variable:

For displaying repository variables, we should use @{biServer.variables.VariableName} or
@{biServer.variables['VariableName']}.
For using repository variables in expression, we should use VALUEOF(“VariableName”) for
static variable and for dynamic variable VALUEOF(“Dynamic Initialization Block

Name”.“VariableName”).

Variables should be used as arguments of the function VALUEOF(). This happens automatically when you double-click the variables to paste them into the expression.

VALUEOF("prime_end")

CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

Presentation Variable:

1. A presentation variable is a variable which can be created as a part of creation of
dashboard prompts. Dashboard prompts must be either Column Prompt or Variable
Prompt.

2. The value of presentation variable is set by the prompt for which it is created (upon
user selection).

Referencing presentation variable:

For displaying presentation variables, we should use either
1. @{variables.VariableName}[Format]{DefaultValue} or

1. Format and DefaultValue are optional


1. Scope should be used if you create variables with same name.

2. Scope can be analyses, dashboard etc.

3. Order of precedence is analyses, dashboard pages, dashboards.

For using presentation variables in expression, we should use @{“VariableName”}
{DefaultValue}. Default value is optional.

Request Variable:

1. Request Variable is used to overwrite the value of session variable and it happens only
during request initiation to the database from column prompt.

2. Can be created only during the creation of column prompt.
Referencing request variable:

Same as presentation variable.

In an answer, in the advanced tab, you have the section “Advanced Sql Clauses”.

In the prefix field, you can add a “SET VARIABLE MYVARIABLE=MyValue;”.

Example with DISABLE_CACHE_HIT (to disable a cache hit):

SET VARIABLE DISABLE_CACHE_HIT=1;SELECT "Customer"."Name" FROM SH ...

3.2.1.2 - With a presentation variable

You can then use the value of a presentation variable to initialize a request variable.

Example of prefix with multiple presentation variables and request variables:

SET VARIABLE MyRequestVariable1='@{MyPresentationVariableName1}{DefaultValue}', 

MyRequestVariable2='@{MyPresentationVariableName2}{DefaultValue}';

In a Dashboard prompt

SET VARIABLE DISABLE_CACHE_HIT=1, MY_SESSION_VARIABLE=MyValue;
SELECT Calendar."Calendar Year" 
FROM SH