TOP INFORMATICA SCENARIO QUESTION

 

 

76.How do you load only null records into target? Explain through mapping flow.

The target structure is also the same but, we have got two tables, one which will contain the NULL records and one which will contain non NULL records.

We can design the mapping as mentioned below.

SQ –> EXP –> RTR –> TGT_NULL/TGT_NOT_NULL
EXP – Expression transformation create an output port

O_FLAG= IIF ( (ISNULL(cust_id) OR ISNULL(cust_name) OR ISNULL(cust_amount) OR ISNULL(cust _place) OR ISNULL(cust_zip)), ‘NULL’,’NNULL’)
** Assuming you need to redirect in case  any of value is null

OR

O_FLAG= IIF ( (ISNULL(cust_name) AND ISNULL(cust_no) AND ISNULL(cust_amount) AND ISNULL(cust _place) AND ISNULL(cust_zip)), ‘NULL’,’NNULL’)
** Assuming you need to redirect in case all of  value is null

RTR – Router transformation two groups

Group 1 connected to TGT_NULL ( Expression O_FLAG=’NULL’)
Group 2 connected to TGT_NOT_NULL ( Expression O_FLAG=’NNULL’

77.How do you update the records with or without using Update Strategy?

We can use the session configurations to update the records. We can have several options for handling database operations such as insert, update, delete.

During session configuration, you can select a single database operation for all rows using the Treat Source Rows As setting from the ‘Properties’ tab of the session.

  • Insert: – Treat all rows as inserts.
  • Delete: – Treat all rows as deletes.
  • Update: – Treat all rows as updates.
  • Data Driven :- Integration Service follows instructions coded into Update Strategy flag rows for insert, delete, update, or reject.

Once determined how to treat all rows in the session, we can also set options for individual rows, which gives additional control over how each rows behaves. We need to define these options in the Transformations view on mapping tab of the session properties.

  • Insert: – Select this option to insert a row into a target table.
  • Delete: – Select this option to delete a row from a table.
  • Update :- You have the following options in this situation:
    • Update as Update: – Update each row flagged for update if it exists in the target table.
    • Update as Insert: – Insert each row flagged for update.
    • Update else Insert: – Update the row if it exists. Otherwise, insert it.
  • Truncate Table: – Select this option to truncate the target table before loading data.

Steps:

  1. Design the mapping just like an ‘INSERT’ only mapping, without Lookup, Update Strategy Transformation.Design-mapping-informatica-interview-questions
  2. First set Treat Source Rows As property as shown in below image.
    Treat-source-rows-informatica-interview-questions
  3. Next, set the properties for the target table as shown below. Choose the properties Insert and Update else Insert.
    Set-properties-informatica-interview-questions

These options will make the session as Update and Insert records without using Update Strategy in Target Table.

When we need to update a huge table with few records and less inserts, we can use this solution to improve the session performance.

The solutions for such situations is not to use Lookup Transformation and Update Strategy to insert and update records.

The Lookup Transformation may not perform better as the lookup table size increases and it also degrades the performance.

Scenario:There is a emp table and from that table insert  the data to targt where sal<3000 and reject other rows.

Following are the steps for achieving it

  1. connect out-puts from SQF to Update Strategy transformation.
  2. In properties of  Update Strategy write the condition like this
  3. Connect the Update Strategy to target

79.Explain in detail about SCD TYPE 1 through mapping.

SCD Type1 Mapping

The SCD Type 1 methodology overwrites old data with new data, and therefore does not need to track historical data.

  1. Here is the source.
    SCD-Type-1-informatica-interview-questions
  2. We will compare the historical data based on key column CUSTOMER_ID.
  3. This is the entire mapping:
    Entire-mapping-informatica-interview-questions
  4. Connect lookup to source. In Lookup fetch the data from target table and send only CUSTOMER_ID port from source to lookup.
    Lookup-informatica-interview-questions
  5. Give the lookup condition like this:
    Lookup-condition-informatica-interview-questions
  6. Then, send rest of the columns from source to one router transformation.
    Router-transformation-informatica-interview-questions
  7. In router create two groups and give condition like this:
    Create-groups-informatica-interview-questions
  8. For new records we have to generate new customer_id. For that, take a sequence generator and connect the next column to expression. New_rec group from router connect to target1 (Bring two instances of target to mapping, one for new rec and other for old rec). Then connect next_val from expression to customer_id column of target.
    CustomerID-informatica-interview-questions
  9. Change_rec group of router bring to one update strategy and give the condition like this:
    Update-strategy-informatica-interview-questionsCondition-informatica-interview-questions
  10. Instead of 1 you can give dd_update in update-strategy and then connect to target.

80. Explain in detail SCD TYPE 2 through mapping.

SCD Type2 Mapping

In Type 2 Slowly Changing Dimension, if one new record is added to the existing table with a new information then, both the original and the new record will be presented having new records with its own primary key.

  1. To identifying new_rec we should and one new_pm? and one vesion_no.
  2. This is the source:
    SCD-Type-2-mapping-informatica-interview-questions
  3. This is the entire mapping:
    SCD-type2-informatica-interview-questions
  4. All the procedures are similar to SCD TYPE1 mapping. The Only difference is, from router new_rec will come to one update_strategy and condition will be given dd_insert and one new_pm and version_no will be added before sending to target.
  5. Old_rec also will come to update_strategy condition will give dd_insert then will send to target

IMPLEMENT SLOWLY CHANGING DIMENSION OF TYPE 2 WHICH WILL LOAD CURRENT RECORD IN CURRENT TABLE AND OLD DATA
IN LOG TABLE. 

33

 In source  there are some record. Suppose I want to send three targets. First record will go to first target, Second one will go to second target and third record will go to third target and then 4th to 1st,5th to 2nd , 6th to  3rd and so on.

  1. Put the source to mapping and connect it to an expression transformation.
    final mapping
  2. Drag an sequence generator transformation and set properties like this And connect  the next value port to expression.
    sequence generator property
  3. Drag all output port of expression to router. In router make three groups and gve the conditions Like this router condition
  4. connect desire group to desire target .

Suppose we have a source table and we want to load three target tables based on source rows such that first row moves to first target table, second row in second target table, third row in third target table, fourth row again in first target table so on and so forth. Describe your approach.

We can clearly understand that we need a Router transformation to route or filter source data to the three target tables. Now the question is what will be the filter conditions.
First of all we need an Expression Transformation where we have all the source table columns and along with that we have another i/o port say seq_num, which gets sequence numbers for each source row from the port NEXTVAL of a Sequence Generator start value 0 and increment by 1.
Now the filter condition for the three router groups will be:

  •  MOD(SEQ_NUM,3)=1 connected to 1st target table
  •  MOD(SEQ_NUM,3)=2 connected to 2nd target table
  •  MOD(SEQ_NUM,3)=0 connected to 3rd target table

 

23

 

https://forgetcode.com/informatica/1448-count-number-of-rows-with-not-null-values