Handling Dynamic Source Column

Handling Dynamic Source Column - Using ODI

Hi ! ! ! I am an BI Lead by Profession. What I like about being part of IT industry is "bright side of the scariest part". Yes, you guessed it right, its job insecurity :) . Bright side to this part is eternal conquest for improvement. Thrill of overcoming a challenge cannot be put in words. In this post I am going to share something similar with readers.Other day, I was hanging out with my pack and one person came up with weird requirement that she faced as an Interview question. I came up with a solution to her problem, sharing the same with you. Do let know if you liked it or if you have some suggestions to enhance the same or in case you have some queries that I should answer. Hope you like it.
Assumptions:

I expect readers of this post are already aware of SQL & ODI environment and know how to create topology connections for the same.
I expect we are using Oracle Database for exercise.

Disclaimer: Source system type columns name and subject area of data has been changed than original and fake sample data has been created independently for this demo.
Problem Statement:
Lets assume source system will provide a file with following Employee information.
These columns may appear in any sequence in file.
We need to load the file into table (which off-course will be fixed sequence)
For this example target column sequence has been used as below (however using provided files, everyone is welcome to play around with source file):

Employee Number
Employee Name
Mobile Number
Department

NOTE : All required documents for this post is shared @Link
Solution:
Step - 1: Place source file in a folder and create respective topology connection.

Step - 2: Create a following tables in stage schema and create respective topology connection.
Table Name : EXT_SRC_EMP_DTLS_T
Significance: Used to load dump source file as is inclusive of headers. Will act as source data for further processing.
Table Name : TGT_EMP_DTLS_T
Significance: Target table to load data.
Table Name : REF_EMP_DTLS_T
Significance: Reference table used in processing logic. This table contains only two columns.
"COLUMN_NAME" column contains name of each column that exists in target table.
"COL_SEQ_NUM" column contains column number for respective column in Target Table.

Step - 3 : Create ODI interface to load file into "EXT_SRC_EMP_DTLS_T" table along with header.

Step - 4: Insert Target column names with their respective sequence number in "REF_EMP_DTLS_T"

Step - 5: Create a procedure using logic of below mentioned query. This query will create dynamic SQL for data load into dynamic table:
Command on Source:
Select 'Insert into TGT_EMP_DTLS_T (EMPLOYEE_NUMBER,EMPLOYEE_NAME,MOBILE_NUMBER,DEPARTMENT)
SELECT '||LISTAGG(SRC_COL_NM, ', ') WITHIN GROUP (ORDER BY TGT_COL_NUM)||' FROM EXT_SRC_EMP_DTLS_T WHERE NOT(COLUMN1 in ('||'''Employee_Number'''||','|| '''Employee_Name'''||','|| '''Department'''||','|| '''Mobile_Number'''||'))' DYNAMIC_SQL
/* Dynamic SQL generated for execution using ODI Procedure */
FROM
(
Select T1.*,T2.COL_SEQ_NUM TGT_COL_NUM FROM
(
Select * from
(
select * from EXT_SRC_EMP_DTLS_T WHERE COLUMN1 in ('Employee_Number', 'Employee_Name', 'Department', 'Mobile_Number')
) T0 /* Stage Table with Files First Row */
UNPIVOT
(
COL_NAME
for SRC_COL_NM in ("COLUMN1","COLUMN2","COLUMN3","COLUMN4")
)
) T1 /* Stage Table with Files First Row in Unpivot format */
,
REF_EMP_DTLS_T T2 /* Reference Table with Traget Columns with their numbering */
WHERE
UPPER(T1.COL_NAME) = T2.COLUMN_NAME

)