One of the frequently asked questions on the Informatica forums at Informatica forums is how to load data into different target tables based on the order of the source row data.
Eg..If you have an employee table that has 14 rows, the requirement is to load data into three different target tables in the following way.(The three target tables have the same description ).
Record 1 —————> Target table 1
Record 2 —————> Target table 2
Record 3 —————> Target table 3
Record 4 —————> Target table 1
… and so on…..
The number of target tables for this example is 3. This, of course, varies based on the question. I haven’t come across this requirement before, but since this is so frequently asked, I am writing this post. If you have come across a business reason for this, Please post it in the comments.
Here are the three transformations that you can use to implement this.
a) Sequence generator : to generate a series of sequence numbers.
Even if you have an ID as a primary key and it is incremented by 1 every time, I’d suggest you create a sequence to make sure there is no gap in the sequence numbers generated. This will ensure the distribution is uniform.
b) Router : To generate the groups for each of the rows to go into.
c) Expression Transformation : This is optional. You can write the same condition directly in the Router expression if you want to, thereby avoiding this transformation. Please see below how the mapping and condition would look in that case.
For my example, I’ll be using the emp table from oracle’s scott schema.
If you dont have oracle installed, you can get it from here : demobld.sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> select * from scott_emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 |
Create the three new target tables with the same structure as the source.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> create table scott_emp_tgt_1 2 as 3 select * from scott_emp 4 where 1=2; Table created. SQL> create table scott_emp_tgt_2 2 as 3 select * from scott_emp 4 where 1=2; Table created. SQL> create table scott_emp_tgt_3 2 as 3 select * from scott_emp 4 where 1=2; |
With an Expression Transformation:
>Import the source scott_emp, the three targets, sequence generator and expression transformation.
> Specify a start value of 1 for sequence transformation and increment by value of 1. Connect “next value” to the expression transformation.
> To get the group into which a record would go, use the mod function to get the reminder. If you have three targets, use mod(seq_num,3) to get reminders 0,1,2. If you have 2 targets, use mod(seq_num,2) to get two groups 0,1.
> Specify a start value of 1, increment by 1 and end value of 3 and check the Cycle Option. (so the output values would be 1,2,3 and then 1,2,3 and so on.)
> Pass these records to the router and route these records to the target tables as needed based on the group.
Here is how the mapping would look and the data after the load. Please note that I used the override query in the source qualifier to get the data ordered by empno. This may or may not be necessary based on your requirement.
|
1 2 3 4 |
SELECT scott_emp.empno, scott_emp.ename, scott_emp.job, scott_emp.mgr, scott_emp.hiredate, scott_emp.sal, scott_emp.comm, scott_emp.deptno FROM scott_emp ORDER BY scott_emp.empno |
Multiple Target Tables : Expression Transformation
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SQL> select * from scott_emp_tgt_1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 SQL> select * from scott_emp_tgt_2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 SQL> select * from scott_emp_tgt_3; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7839 KING PRESIDENT 17-NOV-81 5000 10 7900 JAMES CLERK 7698 03-DEC-81 950 30 |
You can of course implement the same thing without using the expression transformation. In that case, the ports from source qualifier and the sequence transformation would be passed directly to the the router and the router groups would be like this.
|
1 2 3 |
Group1 -> Sequence_number=1 Group2 -> Sequence_number=2 Group3 -> Sequence_number=3 |
Hope this helps..
Rajesh.