67.How do you remove Duplicate records in Informatica? And how many ways are there to do it?
There are several ways to remove duplicates.
- If the source is DBMS, you can use the property in Source Qualifier to select the distinct records.
Or you can also use the SQL Override to perform the same.
- You can use, Aggregator and select all the ports as key to get the distinct values. After you pass all the required ports to the Aggregator, select all those ports , those you need to select for de-duplication. If you want to find the duplicates based on the entire columns, select all the ports as group by key.
The Mapping will look like this.
- You can use Sorter and use the Sort Distinct Property to get the distinct values. Configure the sorter in the following way to enable this.
- You can use, Expression and Filter transformation, to identify and remove duplicate if your data is sorted. If your data is not sorted, then, you may first use a sorter to sort the data and then apply this logic:
- Bring the source into the Mapping designer.
- Let’s assume the data is not sorted. We are using a sorter to sort the data. The Key for sorting would be Employee_ID.
Configure the Sorter as mentioned below.
- Use one expression transformation to flag the duplicates. We will use the variable ports to identify the duplicate entries, based on Employee_ID.
- Use a filter transformation, only to pass IS_DUP = 0. As from the previous expression transformation, we will have IS_DUP =0 attached to only records, which are unique. If IS_DUP > 0, that means, those are duplicate entries.
- Add the ports to the target. The entire mapping should look like this.
v. When you change the property of the Lookup transformation to use the Dynamic Cache, a new port is added to the transformation. NewLookupRow.
The Dynamic Cache can update the cache, as and when it is reading the data.
If the source has duplicate records, you can also use Dynamic Lookup cache and then router to select only the distinct one.
68.How do you load unique records into one target table and duplicate records into a different target table?
Source Table:
| COL1 | COL2 | COL3 |
| a | b | c |
| x | y | z |
| a | b | c |
| r | f | u |
| a | b | c |
| v | f | r |
| v | f | r |
Target Table 1: Table containing all the unique rows
| COL1 | COL2 | COL3 |
| a | b | c |
| x | y | z |
| r | f | u |
| v | f | r |
Target Table 2: Table containing all the duplicate rows
| COL1 | COL2 | COL3 |
| a | b | c |
| a | b | c |
| v | f | r |
- Drag the source to mapping and connect it to an aggregator transformation.
- In aggregator transformation, group by the key column and add a new port. Call it count_rec to count the key column.
- Connect a router to the aggregator from the previous step. In router make two groups: one named “original” and another as “duplicate”.
In original write count_rec=1 and in duplicate write count_rec>1.
The picture below depicts the group name and the filter conditions.
Connect two groups to corresponding target tables 
69.How to load unique or distinct records from flat file to target?
How to delete duplicate records or rather to select distinct rows for flat file sources?
Using Dynamic Lookup on Target table:
If record doesn’t exit do insert in target_1 .If it is already exist then send it to Target_2 using Router.
Using Var port Approach:
Sort the data in sq based on EmpNo column then Use expression to store previous record information using Var ports after that use router to route the data into targets if it is first time then sent it to first target if it is already inserted then send it to Tartget_2.
| Product | O_Count |
| A | 1 |
| B | 1 |
| B | 2 |
| B | 3 |
| C | 1 |
| C | 2 |
| D | 1 |
A
B
C
C
B
D
B
A
D
The second target should contain the following output
B
B
B
C
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a dummy port O_dummy and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
The output of expression transformation will be
Product, O_dummy
A, 1
B, 1
B, 1
B, 1
C, 1
C, 1
D, 1
Pass the output of expression transformation to an aggregator transformation. Check the group by on product port. In the aggreagtor, create an output port O_count_of_each_product and write an expression count(product).
The output of aggregator will be
Product, O_count_of_each_product
A, 1
B, 3
C, 2
D, 1
Now pass the output of expression transformation, aggregator transformation to joiner transformation and join on the products port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
The output of joiner will be
product, O_dummy, O_count_of_each_product
A, 1, 1
B, 1, 3
B, 1, 3
B, 1, 3
C, 1, 2
C, 1, 2
D, 1, 1
Now pass the output of joiner to a router transformation, create one group and specify the group condition as O_dummy=O_count_of_each_product. Then connect this group to one table. Connect the output of default group to another table.
The idea is to add a sequence number to the records and then divide the record number by 2. If it is divisible, then move it to one target and if not then move it to other target.
- Drag the source and connect to an expression transformation.
- Add the next value of a sequence generator to expression transformation.
- In expression transformation make two port, one is “odd” and another “even”.
- Write the expression as below
- Connect a router transformation to expression.
- Make two group in router.
- Give condition as below
- Then send the two group to different targets. This is the entire flow.
Loading Multiple Target Tables Based on Conditions- Suppose we have some serial numbers in a flat filesource. We want to load the serial numbers in two target files one containing the EVEN serial numbers and the other file having the ODD ones.
After the Source Qualifier place a Router Transformation. Create two Groups namely EVEN and ODD, with
filter conditions as:
- MOD(SERIAL_NO,2)=0
- MOD(SERIAL_NO,2)=1
Then output the two groups into two flat file targets.
The mapping flow and the transformations used are mentioned below:
SRC->SQ->EXP->RTR->TGTS
- First create a new mapping and drag the source into the mapping.
- Create an expression transformation. Drag the ports of source qualifier into the expression transformation. Create the following additional ports and assign the corresponding expressions:
v_count (variable port) = v_count+1 o_count (output port) = v_count
- Create a router transformation and drag the ports (products, v_count) from expression transformation into the router transformation. Create an output group in the router transformation and specify the following filter condition:
MOD(o_count,2) = 1
- Now connect the output group of the router transformation to the target1 and default group to target2. Save the mapping.
In the above solution, I have used expression transformation for generating numbers. You can also use sequence generator transformation for producing sequence values.
This is how we have to load alternative records into multiple targets.
73.How can we distribute and load ‘n’ number of Source records equally into two target tables, so that each
have ‘n/2’ records?
Answer:
- After Source Qualifier use an expression transformation.
- In the expression transformation create a counter variable
V_COUNTER = V_COUNTER + 1 (Variable port)
O_COUNTER = V_COUNTER (o/p port)
This counter variable will get incremented by 1 for every new record which comes in. - Router Transformation:
Group_ODD: IIF(MOD(O_COUNTER, 2) = 1)
Group_EVEN: IIF(MOD(O_COUNTER, 2) = 0)
Half of the record (all odd number record) will go to Group_ODD and rest to Group_EVEN. - Finally the target tables.
SUPPOSE WE HAVE ‘N’ NUMBER OF ROWS IN THE SOURCE AND WE HAVE TWO TARGET TABLES. HOW CAN WE LOAD ‘N/2’ I.E. FIRST
HALF THE SOURCE DATA INTO ONE TARGET AND THE REMAINING HALF INTO THE NEXT TARGET?
74.How do you load first and last records into target table? How many ways are there to do it? Explain through mapping flows.
Solution:
Step 1: Drag and drop ports from source qualifier to two rank transformations.
Step 2: Create a reusable sequence generator having start value 1 and connect the next value to both rank transformations.
Step 3: Set rank properties as follows
In Rank1
In Rank2
Step 4: Make two instances of the target.
Step 5: Connect the output port to target.
75. I have 100 records in source table, but I want to load 1, 5,10,15,20…..100 into target table. How can I do this? Explain in detailed mapping flow.
This is applicable for any n= 2, 3,4,5,6… For our example, n = 5. We can apply the same logic for any n.
The idea behind this is to add a sequence number to the records and divide the sequence number by n (for this case, it is 5). If completely divisible, i.e. no remainder, then send them to one target else, send them to the other one.
- Connect an expression transformation after source qualifier.
- Add the next value port of sequence generator to expression transformation.
- In expression create a new port (validate) and write the expression as in the picture below.
- Connect a filter transformation to expression and write the condition in property as given in the picture below.
- Finally connect to target.
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:
- Design the mapping just like an ‘INSERT’ only mapping, without Lookup, Update Strategy Transformation.
- First set Treat Source Rows As property as shown in below image.
- Next, set the properties for the target table as shown below. Choose the properties Insert and Update else Insert.
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.
78.Insert and reject records using update strategy
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
- connect out-puts from SQF to Update Strategy transformation.
- In properties of Update Strategy write the condition like this
- 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.
- Here is the source.
- We will compare the historical data based on key column CUSTOMER_ID.
- This is the entire mapping:
- Connect lookup to source. In Lookup fetch the data from target table and send only CUSTOMER_ID port from source to lookup.
- Give the lookup condition like this:
- Then, send rest of the columns from source to one router transformation.
- In router create two groups and give condition like this:
- 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.
- Change_rec group of router bring to one update strategy and give the condition like this:
- 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.
- To identifying new_rec we should and one new_pm? and one vesion_no.
- This is the source:
- This is the entire mapping:
- 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.
- 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.
81.sending data one after another to three tables in cyclic order
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.
- Put the source to mapping and connect it to an expression transformation.
- Drag an sequence generator transformation and set properties like this And connect the next value port to expression.
- Drag all output port of expression to router. In router make three groups and gve the conditions Like this
- 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
82.Sending records to target tables in cyclic order
Scenario: There is a source table and 3 destination table T1,T2, T3. How to insert first 1 to 10 record in T1, records from 11 to 20 in T2 and 21 to 30 in T3.Then again from 31 to 40 into T1, 41 to 50 in T2 and 51 to 60 in T3 and so on i.e in cyclic order.
Solution:
- Drag the source and connect to an expression.Connect the next value port of sequence generator to expression.
- Send the all ports to a router and make three groups as bellow
Group1Group2Group3 - mod(NEXTVAL,30) >= 21 and mod(NEXTVAL,30) <= 29 or mod(NEXTVAL,30) = 0
- mod(NEXTVAL,30) >= 11 and mod(NEXTVAL,30) <= 20
- mod(NEXTVAL,30) >= 1 and mod(NEXTVAL,30) <= 10
- Finally connect Group1 to T1, Group2 to T2 and Group3 to T3.
83.Segregating rows on group count basis
Scenario : There are 4 departments in Emp table. The first one with 100,2nd with 5, 3rd with 30 and 4th dept has 12 employees. Extract those dept numbers which has more than 5 employees in it, to a target table.
Solution:
https://forgetcode.com/informatica/1448-count-number-of-rows-with-not-null-values
- Put the source to mapping and connect the ports to aggregator transformation.
- Make 4 output ports in aggregator as in the picture above : count_d10, count_d20, count_d30, count_d40.
For each port write expression like in the picture below.
- Then send it to expression transformation.
- In expression make four output ports (dept10, dept20, dept30, dept40) to validate dept no
And provide the expression like in the picture below.
- Then connect to router transformation. And create a group and fill condition like below.
- Finally connect to target table having one column that is dept no.
84.Get top 5 records to target without using rank
Scenario : How to get top 5 records to target without using rank ?
Solution:
- Drag the source to mapping and connect it to sorter transformation.
- Arrange the salary in descending order in sorter as follows and send the record to expression.
sorter properties
- Add the next value of sequence generator to expression.(start the value from 1 in sequence generator).
sorter to exp mapping
- Connect the expression transformation to a filter or router. In the property set the condition as follows-
- Finally connect to the target.
final mapping sc12
85.Separate rows on group basis
Scenario : In Dept table there are four departments (dept no 40,30,20,10). Separate the record to different target department wise.
Solution:
Step 1: Drag the source to mapping.
Step 2: Connect the router transformation to source and in router make 4 groups and give condition like below.
router transformation
Step 3: Based on the group map it to different target.The final mapping looks like below.
router to target
86.Separate the original records in target
Scenario 10: How to separate the original records from source table to separate target table by using rank transformation ?
Source Table
| col1 | col2 | col3 |
|---|---|---|
| a | b | c |
| x | y | z |
| a | b | c |
| r | f | u |
| a | b | c |
| v | f | r |
| v | f | r |
Target Table
| Col1 | Col2 | Col3 |
|---|---|---|
| a | b | c |
| x | y | z |
| r | f | u |
| v | f | r |
Solution:
Step 1: Bring the source to mapping.
src to rank mapping
Step 2: Connect the rank to source.
Step 3: In rank, set the property like this.
rank property
Step 4: Then send it to target.
Run the session to see the result.
87.Sending second half record to target
Scenario 8: How to send second half record to target?
Solution
Step 1: Drag and drop the source to mapping.
src to tgt mapping
Step 2: In source-Qualifier , go to property and write the SQL query like
|
1
|
select * from emp minus select * from emp where rownum <= ( select count(*)/2 from emp)) |
src qualifier sql query
Step:3 Then connect to target, and run mapping to see the results.
88.Sending first half record to target
Scenario 6: How to send first half record to target?
Solution:
-
- Drag and drop the source to mapping.
- Step:2 In source-Qualifier , go to property and write the SQL query like
1
select*fromempwhererownum <= (selectcount(*)/2fromemp)
- Then connect to target.Now you are ready to run the mapping to see it in action.
- Drag and drop the source to mapping.
89.Split the non-key columns to separate tables with key column in both discuss
Split the non-key columns to separate tables with key column in both / How to split the data of source table column-wise with respect to primary key. See the source and target tables below.
source table: ID is the key column, Name and Phone No are non-key columns
| ID | Name | Phone No |
| 10 | AAA | 123 |
| 20 | BBB | 234 |
| 30 | CCC | 434 |
| 40 | DDD | 343 |
| 50 | EEE | 442 |
Target Table 1
| ID | Name |
| 10 | AAA |
| 20 | BBB |
| 30 | CCC |
| 40 | DDD |
| 50 | EEE |
Target Table 2
| ID | Phone No |
| 10 | 123 |
| 20 | 234 |
| 30 | 434 |
| 40 | 343 |
| 50 | 442 |
Solution:
Step 1: Source qualifier: get the source table to the mapping area. See image below.
Step 2: Drag all the port from (from the previous step) to the Aggregator transformation and group by the key column. Since we have to split the columns to two different tables with the key column in each, so we are going use two expression transformation, each will take the key column and one non-key column. Connect aggregator transformation with each of the expression transformation as follows.
Step 3: We need another set of aggregator to be associated with each of the expression transformation from the previous step.
Step 4: In the final step connect the aggregators with the two target tables as follows.
Here is the iconic view of the entire mapping.
Step1: You have to assign row numbers to each record. Generate the row numbers using the expression transformation as mentioned above and call the row number generated port as O_count. Create a DUMMY output port in the same expression transformation and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
In the expression transformation, the ports are
V_count=V_count+1
O_count=V_count
O_dummy=1
The output of expression transformation will be
col, o_count, o_dummy
a, 1, 1
b, 2, 1
c, 3, 1
d, 4, 1
e, 5, 1
Step2: Pass the output of expression transformation to aggregator and do not specify any group by condition. Create an output port O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row by default. The output of aggregator contains the DUMMY port which has value 1 and O_total_records port which has the value of total number of records in the source.
In the aggregator transformation, the ports are
O_dummy
O_count
O_total_records=O_count
The output of aggregator transformation will be
O_total_records, O_dummy
5, 1
Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
In the joiner transformation, the join condition will be
O_dummy (port from aggregator transformation) = O_dummy (port from expression transformation)
The output of joiner transformation will be
col, o_count, o_total_records
a, 1, 5
b, 2, 5
c, 3, 5
d, 4, 5
e, 5, 5
Step 4: In the first group, the condition should be O_count=1 and connect the corresponding output group to table A. In the second group, the condition should be O_count=O_total_records and connect the corresponding output group to table B. The output of default group should be connected to table C.
91. Load the header record of the flat file into first target, footer record into second target and the remaining records into the third target.
The solution to this problem I have already posted by using aggregator and joiner. Now we will see how to implement this by reversing the contents of the file.
Solution:
- Connect the source qualifier transformation to the expression transformation. In the expression transformation create the additional ports as mentioned above.
- Connect the expression transformation to a router. In the router transformation create an output group and specify the group condition as o_count=1. Connect this output group to a target and the default group to sorter transformation.
- Sort the data in descending order on o_count port.
- Connect the output of sorter transformation to expression transformation (don’t connect o_count port).
- Again in the expression transformation create the same additional ports mentioned above.
- Connect this expression transformation to router and create an output group. In the output group specify the condition as o_count=1 and connect this group to second target. Connect the default group to the third group.
Then pass the output to filter transformation and specify the filter condition as O_count <=3
93. Design a mapping to load last 3 rows from a flat file into the target.
Step1: Assign row numbers to each record. Generate row numbers using expression transformation by creating a variable port and incrementing it by 1. After this assign this variable port to output port. After expression transformation, the ports will be as –
Variable_count= Variable_count+1
O_count=Variable_count
Create a dummy output port for same expression transformation and assign 1 to that port. This dummy port will always return 1 for each row.
Finally, the transformation expression will be as follows:
Variable_count= Variable_count+1
O_count=Variable_count
Dummy_output=1
The output of this transformation will be :
Column A O_count Dummy_output
Aanchal 1 1
Priya 2 1
Karishma 3 1
Snehal 4 1
Nupura 5 1
Step 2: Pass the above output to an aggregator and do not specify any group by condition. A new output port should be created as O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row. The final output of this step will have dummy port with value as 1 and O_total_records will have a total number of records in the source. The aggregator output will be:
O_total_records, Dummy_output
5 1
Step 3: Pass this output to joiner transformation and apply a join on dummy port. The property sorted input should be checked in joiner transformation. Only then the user can connect both expression and aggregator transformation to joiner transformation.
Joiner transformation condition will be as follows:
Dummy_output (port from aggregator transformation) = Dummy_output (port from expression transformation)
The output of joiner transformation will be
Column A o_count o_total_records
Aanchal 1 5
Priya 2 5
Karishma 3 5
Snehal 4 5
Nupura 5 5
Step 4: After the joiner transformation we can send this output to filter transformation and specify filter condition as O_total_records (port from aggregator)-O_count(port from expression) <=2
The filter condition, as a result, will be
O_total_records – O_count <=2
The final output of filter transformation will be :
Column A o_count o_total_records
Karishma 3 5
Snehal 4 5
Nupura 5 5
94.Load Last N Records of File into Target Table – Informatica
Q) How to load only the last N rows from source file into the target table using the mapping in informatica?
First take a look at the below data in the source file:
Products -------- Windows Linux Unix Ubuntu Fedora Centos Debian
I want to load only the last record or footer into the target table. The target should contain only the product “Debain”. Follow the below steps for implementing the mapping logic in informatica:
- The mapping flow and the transformations are shown below:
SRC->SQ->EXPRESSION->SORTER->EXPRESSION->FILTER->TGT
- Create a new mapping and drag the source into the mapping. By default, it creates the source qualifier transformation.
- Now create an expression transformation and drag the ports from source qualifier into the expression transformation. In the expression transformation, create the below additional ports and assign the corresponding expressions:
v_count (variable port) = v_count+1 o_count (output port) = v_count
- The output of expression transformation is
Products, o_count ----------------- Windows, 1 Linux, 2 Unix, 3 Ubuntu, 4 Fedora, 5 Centos, 6 Debian, 7
- Now connect the expression transformation to a sorter transformation and sort the rows on the o_count port in descending order. The output of sorter transformation is shown below:
Products -------- Debian Centos Fedora Ubuntu Unix Linux Windows
- Create another expression transformation and connect the Products port of sorter to expression transformation. Create the following ports in the expression transformation:
v_count (variable port) = v_count+1 o_count (output port) = v_count
- Connect the expression to a filter transformation and specify the filter condition as o_count = 1.
- Connect the filter to the target and save the mapping.
95.Load all records except last N – Informatica
Example: My source file contains the following records:
Name ---- A B C D E F G
After excluding the last 5 records, i want to load A,B into the target. How to implement a mapping logic for this in informatica?
Solution: Follow the below steps
- Connect the source qualifier transformation, NEXTVAL port of sequence generator to the sorter transformation.
- In the sorter transformation, check the key box corresponding to NEXTVAL port and change the direction to Descending.
- Create one more sequence generator transformation and a filter transformation.
- Connect the NEXTVAL port of the second sequence generator transformation to the filter and Name port of sorter transformation to filter.
- Specify the filter condition as NEXTVAL > 5.
- Save the mapping. Create a workflow and session. Save the workflow and run the workflow.
You can use the same approach to remove the footer record from the source by specifying the filter condition as NEXVAL>1. If you have any issues in solving this problem, please do comment here.
97. HOW CAN WE LOAD ‘X’ RECORDS (USER DEFINED RECORD NUMBERS) OUT OF ‘N’ RECORDS FROM SOURCE DYNAMICALLY,WITHOUT USING FILTER AND SEQUENCE GENERATOR TRANSFORMATION?
- Take a mapping parameter say $$CNT to pass the number of records we want to load dynamically by changing in the parameter file each time before session run.
- Next after the Source Qualifier use an Expression transformation and create one output port say CNTR with value CUME (1).
- Next use an Update Strategy with condition IIF ($$CNT >= CNTR, DD_INSERT, DD_REJECT).
98.How to create Target Files Dynamically.
OR
(How to load all Employees data based upon Deptno in different target file through single Target Instance and Single Mapping.)
Ex: If Deptno=10 then create Target file as DEPT10.txt
If Deptno=20 then create Target file as DEPT20.txt
If Deptno=30 then create Target file as DEPT30.txt
http://informaticachamp.blogspot.in/2014/03/scenario-4-how-to-create-target-files.html
Dynamic Target Flat File Name Generation in Informatica
- Create a new file for every session run
- create a new file for each transaction.
Informatica provides a special port,”FileName” in the Target file definition. This port you have to add explicitly. See the below diagram for adding the “FileName” port.
Go to the Target Designer or Warehouse builder and edit the file definition. You have to click on the button indicated in red color circle to add the special port.
Now we will see some informatica mapping examples for creating the target file name dynamically and load the data.
1. Generate a new file for every session run.
Whenever the session runs you need to create a new file dynamically and load the source data into that file. To do this just follow the below steps:
STEP1: Connect the source qualifier to an expression transformation. In the expression transformation create an output port (call it as File_Name) and assign the expression as ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDDHH24MISS’)||’.dat’
STPE2: Now connect the expression transformation to the target and connect eh File_Name port of expression transformation to the FileName port of the target file definition.
STEP3: Create a workflow and run the workflow.
Here I have used sessstarttime, as it is constant throughout the session run. If you have used sysdate, a new file will be created whenever a new transaction occurs in the session run.
The target file names created would look like EMP_20120101125040.dat.
2. Create a new file for every session run. The file name should contain suffix as numbers (EMP_n.dat)
In the above mapping scenario, the target flat file name contains the suffix as ‘timestamp.dat’. Here we have to create the suffix as a number. So, the file names should looks as EMP_1.dat, EMP_2.dat and so on. Follow the below steps:
STPE1: Go the mappings parameters and variables -> Create a new variable, $$COUNT_VAR and its data type should be Integer
STPE2: Connect the source Qualifier to the expression transformation. In the expression transformation create the following new ports and assign the expressions.
v_count (variable port) = v_count+1 v_file_count (variable port) = IIF(v_count = 1, SETVARIABLE($$COUNT_VAR,$$COUNT_VAR+1),$$COUNT_VAR) o_file_name (output port) = 'EMP_'||v_file_count||'.dat'
STEP3: Now connect the expression transformation to the target and connect the o_file_name port of expression transformation to the FileName port of the target.
3. Create a new file once a day.
You can create a new file only once in a day and can run the session multiple times in the day to load the data. You can either overwrite the file or append the new data.
This is similar to the first problem. Just change the expression in expression transformation to ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDD’)||’.dat’. To avoid overwriting the file, use Append If Exists option in the session properties.
4. Create a flat file based on the values in a port.
You can create a new file for each distinct values in a port. As an example consider the employees table as the source. I want to create a file for each department id and load the appropriate data into the files.
STEP1: Sort the data on department_id. You can either use the source qualifier or sorter transformation to sort the data.
STEP2: Connect to the expression transformation. In the expression transformation create the below ports and assign expressions.
v_curr_dept_id (variable port) = dept_id v_flag (variable port) = IIF(v_curr_dept_id=v_prev_dept_id,0,1) v_prev_dept_id (variable port) = dept_id o_flag (output port) = v_flag o_file_name (output port) = dept_id||'.dat'
STEP4: Now connect the expression transformation to the transaction control transformation and specify the transaction control condition as
IIF(o_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)
STEP5: Now connect to the target file definition.
99.Produce files as target with dynamic names
Scenario:How to generate file name dynamically with name of sys date ?
Solution:
- Drag your target file to target designer and add a column as show on the picture. It’s not a normal column .click on the ‘add file name to the table’ property. (I have given a red mark there)
- Then drag your source to mapping area and connect it to an expression transformation.
- In expression transformation add a new port as string data type and make it output port.
- In that output port write the condition like describe as bellow and then map it in to filename port of target. Also send other ports to target. Finally run the session. You will find two file one with sys date and other one is ‘.out’ file which one you can delete.
100. Explain SCD TYPE 3 through mapping.
SCD Type3 Mapping
In SCD Type3, there should be two columns added to identifying a single attribute. It stores one time historical data with current data.
- This is the source:
- This is the entire mapping:
- Up to router transformation, all the procedure is same as described in SCD type1.
- The only difference is after router, bring the new_rec to router and give condition dd_insert send to.
- Create one new primary key send to target. For old_rec send to update_strategy and set condition dd_insert and send to target.
- You can create one effective_date column in old_rec table
101: How to Implement SCD2 with Dynamic Lookup?
http://informaticachamp.blogspot.in/2014/03/scenario-8-how-to-implement-scd2-with.html
While Implementing SCD2 with Dynamic Lookup the challenge is to fetch only unique records from lookup table to cache because Dynamic Lookup Cache doesn’t support duplicate value and session will fail if any duplicate records is trying to insert into Lookup Cache. (Challenge 1)
102: Implementing SCD1 using MD5.
http://informaticachamp.blogspot.in/2014/03/scenario-10-implementing-scd1-using-md5.html
103: How to Implement Hybrid SCD or SCD Type 6
http://informaticachamp.blogspot.in/2014/03/scenario-15-how-to-implement-hybrid-scd.html
104: How to implement SCD1 along with delete. (Insert, Update, Delete)
http://informaticachamp.blogspot.in/2014/06/scenario-22-how-to-implement-scd1-along.html
