top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

how to reduce the rework on Informatica ETLs due to change of table and column names

+2 votes
77 views

Currently, we have a lot of ETLs developed and the underlying table and column names are going to change. For example, the physical names used abbreviated names rather than full names since we wanted to deploy on Oracle, but for now, we decided we will use only SQL Server, hence there is a discussion on using full names at the DB level. This will impact the ETLs that we have already developed.

I was just wondering whether there is any efficient way to remap the ETL to the changed column names? I can provide a file with old and new table/column names as input.

Any help on this will be really appreciated.

posted Oct 13, 2014 by Sachin

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

One possible way is to use a SQL Override value in all of your Source Qualifier transformations that contains a SQL query with parameter file substitutions. This way you can change the column names and SELECT them in mapping sources without modifying the Source definitions. Of course this would require some up-front refactoring of your existing Source Qualifier transformations.

Alternatively, at the database level you could create views onto the existing tables that use the old abbreviated column names, effectively providing a translation between the new column name and the old one. This would not require any changes to your existing ETL.

However, I would generally advise against changing anything in this case, especially since the difference is purely cosmetic - it will only produce maintenance and complexity headaches.

answer Oct 15, 2014 by Shweta Singh
Similar Questions
+2 votes

I have this informatica requirement where, i am getting some files say 1000 files, these files gets divided into multiple folders on unix server. i want to rename this file, let me give you an eg:

File name:input
abc1.txt
abc2.txt
abc3.txt

Now I want to change these file names by adding account_no,client_no,datestamp(all these values resides in one table). eg:
abc1_123_123_date.txt
abc2_456_321_date.txt
abc3_789_765_date.txt

This is just an eg: ..like this I ll be having say 10 listfiles and each listfile ll contain 100 files.Do i need to create 10 mappings for each listfile or is there any way that i can implement this in single mapping.

Please note:I cannot club all files in one folder and then change the file names and then again divide into original directories, as sometimes we get files in separated directories and we have to change there name and keep them in that particular folder only(for other business logic).

Kindly provide solution for this.

+1 vote

In my source table data is

s_name,p_name,value
s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p_name,value 
p1,    10
p2,    xyz
p3,    abc 

Table s2

p_name,value 
p1,    20
p2,    xyz
p3,    abc 
0 votes

I have the following table with the shown data in it:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456
11-15-2014 | 789

I need to do 2 things:

1) Calculate the max value for send_date

2) Filter out any rows whose send_date does not match that value

In other words, I want the output to be:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456

Row number 3 should be filtered out as its send_date is not the max.

I tried creating an aggregate, grouping by all columns, and creating a new output port called MAX_DATE with an expression of MAX(SEND_DATE), then have a filter transformation with the condition MAX_DATE = SEND_DATE

This lets all rows through, though. What can I do to make this work....

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...