Interview Questions

HCL

1. Where did u use the Unix shell scripts in informatica projects?
2. how to u generate surrogate keys for tables with more than 2 billion records (surrogate key is a primary key field).
3. how do u propagate date coloumn to a flat file, if u need format to be DD-MON-YYYY?
4. if a look up returning multiple matching rows, then how Unconnected lookup & Connected lookup will perform? (Not using lookup by last value)
5. If you use sorted I/P option in Aggregator but gave it Unsorted I/P then what will happen?
6. If i have 100 rows given as I/P to aggregator & want 100 rows as O/P then how can u achieve that?(none of the coloumns are primary key)
7. If i have 100 rows given as I/P to aggregator & want just 100th row as O/P then how can u achieve that?
8. What kind of stored procedures are there in informatica
9. If i use pre-session stored procedure, then i need to activate another field, What would that be?
10. What are all the conditions (=, Not Between) you have in Lookup & in Joiner?
11. What is the use of Reusable sequence generator (not in the resuability sense), any other?
12. If i had a flat file, Can i over ride SQL in Source Qualifier or Lookup?
13. If i have a flat file target, When i click it in the Workflow manager, What all properties would i get?
14. I have to use Order by, I have a table with coloumns A,B,C...I need order by B,C ..Do u need to add some more thing to "select * from TABLE NAME order by B,C" ???
15. What is the use of Return Port & Output port in Lookup Transformation?
16. If i have used 2 update strategys in my mapping..1 for insert & other for delete, then i changed target option in session properties from datadriven to delete...then how is my mapping going to perform ? (all deletes or insert & delete)

Answers:

1. To concatinate 2 or more Flat files, Workflow scheduling, File watcher script.
2. Dont use sequence generator, But use a Expression variable increment & look up transformation to get the last value used from target.
3. Using To_date function before loading to flat file
5. Fails the session with error messg 'Expecting keys to be ascending'.
6. Aggregator is an active transformation. So you can't expect it to give exact number of output rows for all input rows you have given to it.
7. If you dont select any group by port in ports tab of aggregator transformation then informatica is only going to give last row as output for all the numerous records given to it as input.
10. Lookup (=, !=, >=, <=, >, <), Joiner (= only )
12. You can never over ride a SQL Query when you are playing with flat files.
13. File Writer, Merge Partionned file, Merge File name, Merge File Dir, O/P file name, O/P file dir, Reject file name, Reject file dir.
15. By default all the ports in lookup transformation are Look up & O/P ports. Return ports are only used in Unconnected lookup & You need have atleast 1 Output port in Connected lookup. (cant explain in detail here...)
16. Workflow succeds but you get an error mesg in logs saying target did not permit to insert & all the records marked for inserts are loaded into badfile.

NESS Technologies

How would u rate urself on oracle, UNIX, DWH Concepts, Informatica

Why you need surrogate key instead of OLTP primary key, Tell a scenario where it’s mandatory to use surrogate key

Ans: If prod key is numeric before & OLTP People decided to go for alphanumeric, we may have to change all data types. in warehouse where that key is involved. So best to keep it away from business defined values, To track SCD

Scenario for using dynamic lookup

Ans: Loading data from flat file to a table, But the data in file is violating primary key constraint of table due to
duplicate data

How would you tune performance for a given scenario, scenario explained by interviewer!

Scenario where you developed some procedures

Scenario where you developed some UNIX scripts

Scenario where you encountered toughest time in job

In your complete job profile, what are the documentations you have done?

What’s the default & Max size of data cache?

20 MB, 2 GB

What is materialized view, how it’s going to improve performance.

Ans: It creates physical table unlike normal view

When you use bitmap indexes

Ans: when cardinality is low, distinct rows <> Newfile.txt

what is implicit cursor & explicit cursor in oracle?

Ans: Implicit cursor is a defined for every query executed. Its attributes can be retrieved after executing query using sql as prefix Ex: sql%rowcount. Explicit cursor is the cursors what we define manually.

what is the cursor attributes

Ans:%found
%notfound
%rowcount

---------
what are procedures you follow for good performance while doing TDD

What are your roles & responsibilities in previous project

What are all the phases in the DWH project from bidding to delivery

How do you communicate any mistakes to team members, so that they won’t be repeated?

how do you ensure all the business rules defined are implemented in development (Traceability matrix)

how did you follow code reviews, what are all the things u check

at what levels in ur project you perform testing

How did u implement defect tracking in ur project?

At what time in the project, you perform the performance tuning

Did you face any situation where you coded mapping wrong & in later stages found it, if yes how did u rectify

Do you have any check list in doing TDD doc, or is it just your experience that helps

tell me the process you followed in developing a mapping, the documents you got & instructions you got

did you have any team members directly reporting to you

how is the work assigned to you in your project & who takes care of assigning

did you involve in status updates of your project to client, if not who does it

--------------------------
RBS
how would you stop session row if a value for particular column is matched to a given value

Ans: use abort function of informatica in expression transformation

how about error function in informatica

Ans: it logs the error message you defined in the session log if particular condition is satisfied

levels of logs you can maintain in informatica sessions

Ans: Terse,Normal,And Verbose

how to run sequential sessions only if previous session loads at least one row in target

Ans: Defined a link condition between sessions with TgtSuccessrows>0

difference between max & greatest functions in oracle

Ans: Greatest gives greatest of 2 values, max is the max value of all available values

how you get number of rows returned by lookup when a condition matches

Ans: Define a new column & use SQL override with count(*)

how can you configure informatica workflow to run only on first working day of the month

Ans: Define a calendar & go with a session or script

when you go for sql overrides

Ans: When you want to share some processing load with Database, When it’s more effective

what are types of triggers, difference between row level & statement level trigger

Ans: after/before statement level/row level insert/delete/update

how would you question, if you are in interview panel

What is the advantage of packages in PL/SQL

Ans: Modular approach, Security, Integrity

If you have some err in a procedure of package, how you would know which procedure is it

Ans:

Can you join 2 tables using SQL override in Lookup transformation

Ans: Yes

If a session fails, What are the steps you follow in resolving it

Ans: check session logs with various tracing levels

What’s your most difficult situation in development

How you filter records in TOAD

Ans: Use filter icon & Add rule on columns

What is persistent cache in lookup transformation.

Ans: Its remains even after session run, useful in incremental aggregation

how did you implement incremental extraction

Ans: Mostly using set variable or some way of truncating stage tables or use parameter files

tell me about 'set variable' function in informatica

Ans: Set a value to variable depending on the last row processed

if you change properties in session, which one takes preference, is it mapping level or session level

Ans: Session Level

syntax for defining a parameter in parameter file

Ans: Folder - workflow - session – mapplet

How did you move your code from development to UAT or Production?

Did you take back up of your repository at any time? If yes how is it stored & where

Did you install informatica anywhere?

Explain the architecture of informatica

How would you identify why a record is rejected (D,O,N,T Indicators)