Informatica Interview Questions set-7

Difference between Informatica 8.x and 9.x?

In 9.0 version on wards

• Look up cache can be updated based on the results of the expression.
• When a database deadlock happens, in 9.0 retry attempts can be made in a look up.
• Multiple rows can be returned from a look up.
• Limit the size of the session logs
• Auto – commit can be configured.

Source level performance tuning in Informatica?

In Source Qualifier

• Use Indexes on source table key columns
• Use SQL overrides to include joins and filters
• Hints for larger tables
• Use Exists instead of In
• Limit the column rows by filter conditions in SQ.
• Check the SQL Override Query cost.

Mapping level performance tuning in Informatica?

In look ups:

• Use lookup override SQL to limit the rows.
• Create Indexes for joining columns in look up tables.
• Use Persistent cache if the lookup is used in multiple places.

In Joiners:

• Use joins only when the tables are heterogeneous.
• Smaller of two joining has to be master.

In Filters:

• Try to push the filter conditions to source.
• Otherwise use it near source.
• Sequence generator:
• Cache the sequence generator.

In Sessions:

• Disable high precision values if not used.
• Use Terse mode for log tracing.

In Expression:

• Use variables for the same calculations.
• Reduce the nested if conditions.
• Remove the unnecessary type conversions

Target level performance tuning in Informatica?

• Use Bulk load
• Drop Indexes and constraints before loading
• Use partitions in Oracle tables.
• Avoid deadlocks
• Analyze the tables
• Reduce the commit inter level
• Increase Database network packet size

Informatica Parameter and Variable differences?

• Parameter can not be changed during run time and assigned in a parameter file.
• Variable can be changed during run time and assigned in run time.
• Parameters split into mapping, session, system parameters
• Variables split into mapping, session variables

Difference between Unconnected and Connected look ups?

What is bulk load, difficulties & precautions involved?

• When a target doesn’t require any recovery during failure
• If the data is huge to load then the bulk load can be used
• Bulk load does not write a database log, so it improves the target loading performance.
• So if the recovery is needed in case of failure then the table has to be truncated and reloaded.

Which is better? Bulk loading or normal load?

• Bulk loading is better with respect to performance.
• Normal load is better with respect to failure recovery.

How to select distinct records from a flat file?

• Use sorter and select distinct option in informatica mapping.
• Otherwise use aggregator and group by with the key columns.

How to select distinct records using Informatica?

• Select distinct option in the source qualifier, if source is a database tables.
• Use sorter and select distinct option, is source is a flat file.

How to load multiple source files into one target table?

• For this, at Session, in Mapping tab, Under Sources, Select Source Qualifier and in right side pan, change Source Filetype property to Indirect
• Mention List File Name (for example MY_FILES.LST) for the property Source File Name.

What is the way to load multiple target files dynamically?

• If the no. of targets are unknown then use Transaction Control transformation.
• otherwise use router to do the same.

Types of caches in Informatica?

• Static Cache
• Dynamic Cache
• Persistent Cache

Difference between Static, Dynamic and Persistent caches?

• Static Cache -  is created at run time and can not be changed until the run the is completed.
• Dynamic Cache  - is created at run time and can be changed during run time. 
• Persistent Cache - is created at the start the of the workflow and can not be changed until the load is completed unless manually deleted. And it is used between sessions without reloading the cache.

Is there a possibility to return more than one value from an unconnected loo up?
        
        Yes, by concatenating the columns and return as a single port.

What is active and passive transformation?

• Active transformation - is the one which changes outgoing rows. Eg. - Filter, Source Qualifier.
• Passive transformation - is the one which does not changes the outgoing rows. Eg. Expression transformation.
• How to load the unique records into one table and a duplicate records into another table?
  
  
  Use the below mentioned logic.
  Src-->SQ-->Agg-->Rtr--> Tgt1(Unique Records)
                              |--> Tgt2(Duplicate Records)
  • In the aggregator transformation group by with primary key and count value.
  • In the router redirect to Tgt1 if count=1 else redirect the records to Tgt2.
  
  
  How to identify top 2 salaries without using rank transformation?

  
  

  Use the below mentioned logic.

  Src-->SQ-->Srt-->Exp--> Fil --> Tgt

  
  

  • In the Sorter, sort by salaries in descending order
  • Assign row_no  from 1 in the expression and increase the row_no for the next records by 1 like 1,2,3......etc
  • In the filter condition filter with row_no<=2.

  There are 3 records as mentioned below.

  
  

  Dept_Id Dept_No

  101        10

  102        10

  103        10

  
  

  How many records will be inserted into target table?

  
  

  If there are no unique constraints defined on Dept_No column on the target table then 3 records will be inserted, else only one record will be inserted.

  
  

  
  

  Table1 has 1000 records and Table2 has 1,00,000 records.

  When a joiner transformation is used which one should be taken as master source and how?

  
  

  Table1 has to be taken as a master source, because Informatica integration service compares each row of a master table with detail table.
• 
  

  How will you identify duplicates in Oracle table?

  
  

  Use the below mentioned query.

  
  

  select cust_no, count(1) from customer 

  group by cust_no

  having count(1)>1;

  
  

  How to select unique records in a table?

  
  

  Use the below mentioned query.

  
  

  select distinct cust_no from customer;

  
  

  How to delete duplicate records in a table?

  
  

  Use the below mentioned query.

  
  

  delete from customer 

                   where rowid in

                    (select rowid from 

                        (select rowid, row_number() over (partition by cust_no order by cust_no) dup

                          from customer)

                      where dup > 1);

  
  

  What are the types of Indexes?

  
  

  1. B-Tree Indexes - Constructed similar to binary trees, used for faster retrieval of unique values.

  2. Bitmap Indexes - Used for repeating values, single bitmap used to point to many rows.

  3. Function Based Indexes - Used to store computed values, 

  4. Partition Indexes - Used in partitioned tables.

  
  

  What are the types of Partitions in Oracle?

  
  

  1. Range Partition - Partitions created based on the particular range. For eg. Date, Numbers

  2. List Partition - Partitions created based on the listed values like country, department.

  3. Hash Partition - Partitions created based on the hash functions.
• 
  

  How to find the top 3 earnings in each department?

  
  

  select * from (
                 select empno, deptno, rank() over(partition by deptno order by sal desc) as r

                                                                                                        from emp e

                    ) t

  where t.r<=3;

  
  

  What are the analytical functions in Oracle?

  
  

  • Rank
  • Avg
  • Count
  • FirstValue
  • LastValue

  
  
  

  Difference between function and a procedure?

  
  

  Function returns a value, but procdure does not return a value but returns through Out parameters.
  
  

  What are the wildcards used in pattern matching?

  
  

  '_' is used for single character and '%' is used for multiple characters.
  
  Which system table contains all the constraints information in Oracle?
  
  "USER_CONSTRANTS" table.
• 
  

  How to make Informatica Code Reusable?

  
  

  The following are the components to make Informatica code reusable which can reduce development time.

  
  

  1. Mapplet

  2. Reusable Transformation

  3. Shared folder

  4. Global Repository

  5. Worklet

  6. Reusable Session

  7. Reusable Task

  8. Mapping Parameter

  9. Mapping Variable

  10. Workflow Variable

  11. Worklet Variable

  12. Source and Target Connections

  13. Session variables

  14. Service Variables.

  
  

  What are the performance coding standards?

  
  

  1. Turn off verbose logging

  2. Turn off 'Collect performance statistics', after performance testing

  3. Try not to read a file over network.

  4. Lookups and aggregators slow downs the performance because they involve in caching. Try to avoid the aggregators and check the Index and data cache size.

  5. Consider partitioning of files where ever required.

  6. Try to do push down optimization.

  7. Use variables/reusable transformations for repeated calculations

  
  
  

  Other coding standards?

  
  

  1. There should not be any unused ports.

  2. Connection information of the look up should be $Source and $Target

  3. When the strings are read from the source, they should be trimmed(LTRIM and RTRIM)

  4. Where ever the lookup output ports are received, null values should be handled properly.

  5. Common source and Target tables has to be placed under Common folder.

  6. For output only ports, all error messages must be removed.

  7. Check the status of pmcmd command, if non-zero then exit.

  8. All the components should be in commented.

  9. The mapping should not contain any unused sources or targets.

  10. Enable high precision should be disabled.

  11. Stop on error should be greater than '0'.

  12. The port names should be as per the standards.

  13. Nested if's has to be replaced with Decoded.

  14. Stored procedure should always return a value which can be used in Informatica to evaluate the success of the procedure.

  
  

  Name few naming conventions of Informatica?

  
  

  Repository Objects	Naming Convention
  Mapping Name	m_<target table name>  (Examples:  m_PERSON)
  Session Name	s_<mapping name> (Example: s_m_PERSON)
  Folder Name	Folder names for new users will be their department followed by their username. Existing users’ folder names are typically their group followed by their user name. Folder Names may also be based on project, subject area, and promotion group.
  Log Files	<session_name>.log OR <workflow_name>.log
  Bad Files	<session_name>.bad
  Source-name & Target-name	Do not make any distinction here - Informatica color-codes the sources and targets, so it's easy to tell. We do try to prefix these with a folder name, so that when shortcuts or global objects are created we can tell where they originally reside.
  Workflow Names	wflw_<meaningful name>

  
  

  
  

  Workflow Objects	Naming Convention
  Worklet Names	wklt_<meaningful name>
  Command Line Names	cmd_<meaningful name>
  Event Names	evtw_<meaningful name> for a wait event evtr_<meaningful name> for a raise event
  Decision Names	dcsn_<meaningful name>
  Control Names	cntrl_<meaningful_name>
  Email Names	email_<meaningful_name>
  Assignment Names	asgmnt_<meaningful_name>
  Timer Names	tmr_<meaningful_name>

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button.

•