Generating Dynamic Multiple Target files in Informatica

Recently we came across a scenario to generate multiple dynamic Multiple Target files in Informatica. We receive vendor data through Legacy database in below table

1. Invoice_ID
2. Invoice_No
3. Invoice_Amount
4. Vendor_Id

We need to separate all details related to one vendor in separate file so that we can pass data to third part vendors in separate file.

INVOICE_DETAILS

INVOICE_ID	INVOICE_NO	INVOICE_AMOUNT	VENDOR_ID
1	A01	100.00	10
2	A02	125.00	10
3	A03	150.00	10
4	B01	200.00	20
5	B02	500.00	20
6	D01	345.00	40
7	C01	230.00	30
8	D02	450.00	40
9	E01	40.00	50

In order to achieve this we need to follow below steps in mapping

Step1 ) Import INVOICE_DETAILS and drag into mapping and connect all port from Source qualifier to expression and do below sql override

SELECT INVOICE_DETAILS.INVOICE_ID, INVOICE_DETAILS.INVOICE_NO, INVOICE_DETAILS.INVOIC_AMOUNT, INVOICE_DETAILS.VENDOR_ID

FROM

INVOICE_DETAILS

order by VENDOR_ID

Step2) Create expression transformation with as below with 4 input port plus

1. FILE_NAME (Output Port)  'Vendor_'||VENDOR_ID||'Details.csv'
2. CHECK_VENDOR_ID(Variable Port)  iif (VENDOR_ID <> PREV_VENDOR_ID,1,0)

3. OUTPUT_VENDOR_ID (Output)  CHECK_VENDOR_ID
4. PREV_VENDOR_ID (Variable) VENDOR_ID
   
   
   
   
   
   
   
   
   

Step 3 ) Create Transaction Control Transformation (TC_GENERATE_FILES) with below logic 

iif(OUTPUT_VENDOR_ID=1,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)

Step 4 ) Create a File Target with same structure as source table and new field in target using option “Add FileName to this Table”

Connect FileName column from transaction control to FileName Port.

Step5) Run the workflow and below files will be created

Read More

Informatica Port Order

Port Order

Informatica calculates ports in the following order:

1. Input ports. Informatica calculates all input ports first as they are not dependent on any other ports. So, we can create input ports in any order.
2. Variable ports. Variable ports can reference input ports and variable ports, but not output ports. As variable ports can reference input ports, the informatica calculates variable ports after input ports. Likewise, since variable can reference other variables, the display order for variable ports is the same as the order in which the PowerCenter Server evaluates each variable.

Suppose first we calculate the commission variable and then we need to calculate salary by using commission then we need to make sure port which hold commission should appear first before salary port.

3. Output ports. As output ports can reference both input ports and variable ports, the informatica evaluates output ports last. The display order for output ports does not matter, as output ports cannot reference other output ports. Output ports should appear at the bottom of the list of ports
   
   
   
   
   
   
   How to capture previous row values
   
   
   Suppose we have to check whether employee’s department is same as last employee’s department .For that we can do the following
   
   
   1. Create two variables VAL_OLD_DEPT, VAL_NEW_DEPT
   2. Place VAL_OLD_DEPT before Input IN_DEPT Port
   3. Place VAL_NEW_DEPT After Input Dept Port
   4. In port VAL_OLD_DEPT assign following IIF(ISNULL(IN_DEPT),’NODEPT’, IN_DEPT)
   5. Assign VAL_NEW_DEPT equal IN_DEPT
   6. Create one output port to compare

Read More

Informatica 9 new features for developers

Informatica 9 new features for developers

Informatica 9 has lot of new features including IDQ and Informatica Analyst etc ..in this post we will focus on features which are specially useful for developers

Lookup Transformation : Cache updates. We can update the lookup cache based on the results of an expression. When an expression is true, we can add to or update the lookup cache. We can update the dynamic lookup cache with the results of an expression. 

Multiple rows return We can configure the Lookup transformation to return all rows that match a lookup condition. A Lookup transformation is an active transformation when it can return more than one row for any given input row. 

SQL overrides for uncached lookups: In previous versions We could create a SQL override for cached lookups only. We can create an SQL override for uncached lookup. We can include lookup ports in the SQL query. 

Database deadlock resilience In previous releases, when the Integration Service encountered a database deadlock during a lookup, the session failed. Effective in 9.0, the session will not fail. When a deadlock occurs, the Integration Service attempts to run the last statement in a lookup. We can configure the number of retry attempts and time period between attempts. 

SQL transformation Auto-commit for connections. We can enable auto-commit for each database connection. Each SQL statement in a query defines a transaction. A commit occurs when the SQL statement completes or the next statement is executed, whichever comes first. 

Session Log files rollover We can limit the size of session logs for real-time sessions. We can limit the size by time or by file size. We can also limit the number of log files for a session. 

Passive transformation We can configure the SQL transformation to run in passive mode instead of active mode. When the SQL transformation runs in passive mode, the SQL transformation returns one output row for each input row. 

XML transformation XML Parser buffer validation. The XML Parser transformation can validate an XML document against a schema. The XML Parser transformation routes invalid XML to an error port. When the XML is not valid, the XML Parser transformation routes the XML and the error messages to a separate output group that We can connect to a target. 

Read More

User Defined Function (Informatica)

What is user defined function(UDF) ?

UDF is nothing like a expression logic , where you can use the same function or logic  multiple times in your mapping.

Why we need UDF ?

Example :

If you have 20 columns in your mapping in different expression where you want to check NULL constraint  for each column like (IIF( ISNULL(LTRIM(RTRIM(INPUT))),’TRUE’,’FALSE’) .

So we will use the same logic 20 times in our mapping.

Here (IIF( ISNULL(LTRIM(RTRIM(INPUT))),’TRUE’,’FALSE’)  logic is used lot many times in one expression and in downstream also .

To avoid multiple usage occurrence we can create one UDF with the same logic for different Inputs like

How to create UDF ?

Step 1 :   In Right-click the User-Defined Functions folder in a repository folder in the Designer and Right-click a user-defined function in a repository folder in the Designer.(We can also create UDF by Tools menu)

Step 2: In Editor add the inputs that u want do .(if concat we need two inputs ,so add two input in editor) . If you click the Launch Editor u can see all the functions that u r using in Expression Transformation. Click ok and Validate the UDF.

Type :

Public if the function is callable from any expression. Private if the function is only callable from another user-defined function.

How to use UDF?

After creating UDF , we can see the UDF functions in our expression as below.

Conclusion:

So whenever we need to implement the same logic in different times in our mapping or in our project UDF can be used effectively .

My Profile

Read More

Informatica Strange Problems

Some time we encounter strange problem while working with informatica.I would like to highlight some problem  which no way seems to be logical

Error : Informatica Debugger: Invalid session can not be debugged .Select a valid session instance to use

 

Problem : You are trying to run your mapping in debug mode by selecting valid session but you are getting below error even though your session is valid.

Solution :

1)      Copy your mapping Suppose M1 as M1_Debug

2)      Make your session as reusable

3)      Making your Initial Mapping M1 as invalid by someway

4)      Now refresh your session it will ask for new mapping then select M1_debug

5)      Revalidate your session

6)      Try to run your mapping(M1_debug) using second option use existing reusable instance for mapping

Now you should be able to run your mapping in debugger mode

Though it not a 100% correct method but it worked in my case but it is worth trying

 If you hit same issue but are not able to resolve please email to support@itnirvanas.com

 Or if you find some better solution please share with us

Error : CMN_1892 Error: Data for Lookup [] fetched from the file [] is not sorted on the condition ports

Problem : You are suddenly started getting this error though you are not using lookup with sorted ports. You also tried all the things like using sorted file etc.

Solution : For us suddenly file size increased almost 10 times and we started getting this error .We tried all the thing but it did not work.

In the end we increased Lookup Data Cache size and Lookup Index Cache size and it started working fine.

Though it not a 100% correct method but it worked in my case but it is worth trying

 If you hit same issue but are not able to resolve please email to support@itnirvanas.com

Please not both of these issues we encountered on Informatica 7.1.1

 Or if you find some better solution please share with us  

My Profile

Read More

How to validate all mappings in a particular folder

In this post we will discuss how to validate all mapping in a particular folder

Step1 : Go to the Repository manager client

Step2 :

Go to option Tools->Queries

Create a new query with name  ALL_MAP_QUERY

Create a query with parameter name and condition to retrieve the mappings in a folder
as the following:

folder = folder-name
object type = mapping

Step 3:

a)      Connect to power center repository using pmrep

pmrep connect -r RepositoryName -d DomainName -n UserId -x Passwd

b)  Create a Persistent output file using command

pmrep executequery -q  ALL_MAP_QUERY -u d:\infa8\PERS_OP_FILE

Step 4 :Use the persistent output file created in last step to validate mappings

pmrep validate -i d:\infa8\PERS_OP_FILE -u PERS_OP_FILE_LOG

My Profile

Read More

Informatica Useful Tips (Part1)

Tip 1 : Ignore the SQ SQL Override conditionally
It is possible by defining a mapping parameter for the WHERE clause of the SQL Override. When you need all records from the source, define this parameter as 1=1 in theparameter file and in case you need only selected data, set the parameter accordingly.

Tip 2 : Overcome size limit for a SQL Override in a PowerCenter mapping
The SQL editor for SQL query overrides has a limit of maximum of 32,767 characters.
To overcome this we can do following
To source a SQL of more than 32,767 characters do the following:
1. Create a database view using the SQL query override.
2. Create a Source Definition based on this database view.
3. Use this new Source Definition as the source in the mapping

Tip 3. :Export an entire Informatica folder to a xml file
We can do this in 8.1.1,
1) In designer Select Tools -> Queries and create a newquery. Set the Parameter Name "Folder" equal to the Folder you want to export and then run the query.
2) In the Query Results window, choose Edit -> Select All Then select Tools -> Export to XML File andenter a file name and location. Full Folder willbe exported to an XML file.
We can also use the query tool in Repository Manager, to geteverything in the folder (mappings, sessions, workflows, etc.)

Tip 4 : Validate all mappings in a folder

We can validate all mappings in a folder in following way:
1. Go to the Repository manager client
2. Create a query with parameter name and condition to retrieve the mappings in a folder
as the following:
folder = folder-name
object type = mapping
Use the following Pmrep Execute query command to get persistent output file:

executequery -q [-u ] [-a (append)]

We can write the result to a persistent output file.If the query is successful, it returns the total number of qualifying records.We can use newly created persistent output file as a input file in the following pmrep validate command :

Pmrep validate-i }[-p [-u ] [-a (append)][-b (verbose)]

Tip 5 : If you are getting following error
CMN_1022 [
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed[Microsoft][ODBC Excel Driver]Optional feature not implemented
Database driver error...
Function Name : SetConnectOption
Database driver error...
Function Name : AutoCommitOff]
Solution :to make an entry of excel ODBC in powermart.ini file in informatica folder

If you need some more tips please mail us at support@itnirvanas.com.My Profile

Read More

Informatica Metadata Queries (Part 2)

In this post we will continue with informatica metadata queries

Purpose : To Find Tracing Level for Session
Query :
select task_name,decode (attr_value,0,'None',1,'Terse',2,'Normal',3,'Verbose Initialisation',4,'Verbose Data','') Tracing_Level
from REP_SESS_CONFIG_PARM CFG,opb_task TSK
WHERE CFG.SESSION_ID=TSK.TASK_ID
and tsk.TASK_TYPE=68
and attr_id=204 and attr_type=6
Description : This query will give tracing information along with session names.This query is helpful in identifying the session which are having particular type of Tracing level like Verbose.

Purpose : To Find name of all stored procedure being used in stored procedure transformation
Query :
select attr_value from
OPB_WIDGET_ATTR
where widget_type=6 and attr_id=1
Description : This query is helpful when you require to know name of all stored procedure being used in informatica.

Purpose : To find who saved mapping last time
Query :
SELECT substr(rpl.event_time,7,4) substr(rpl.event_time,6,1) substr(rpl.event_time,1,5) ' ' substr(rpl.event_time,12,11) "EventTimestamp" ,
usr.user_name "Username",
DECODE(rpl.object_type_id,21,s21.subj_name,('('rpl.object_type_id')')) "Folder",
obt.object_type_name "Type",
DECODE(rpl.object_type_id,21,map.mapping_name,('('rpl.object_type_id')')) "Object"
FROM
opb_reposit_log rpl,opb_object_type obt,
opb_subject fld,opb_mapping map,opb_users usr,opb_subject s21
WHERE obt.object_type_name = 'Mapping'
AND rpl.object_type_id = obt.object_type_id
AND rpl.object_id = map.mapping_id(+)
AND rpl.object_id = fld.subj_id(+)
AND rpl.event_uid = usr.user_id
AND map.subject_id = s21.subj_id(+)
ORDER BY(substr(rpl.event_time,7,4) substr(rpl.event_time,6,1)
substr(rpl.event_time,1,5) ' ' substr(rpl.event_time,12,11)) DESC
Description : This query is helpful when you want to know who saved the mapping last time .

If you need some information and need query about something different related to metadata query please mail us at support@itnirvanas.com.My Profile

Read More

Informatica Metadata Queries (Part1)

Informatica Metadata Queries

In Informatica normally we come across situation like we need to find all mapping which use a particular table as source or something similar .It is very difficult to find it through Informatica tools.In such a situation Repository tables are very useful.

Repository tables name start with OPB and are continuously updated when we make changes.Along with OPB tables REP views are also present.

It is very dangerous to modify these tables so due care should be taken while dealing with OPB Tables.

In this article we will focus on some useful meta data queries

Purpose : Search for a table in Source Qualifiers Sql Override:

Query : 

select distinct SUB.subj_name, MAP.mapping_name

from  opb_widget_attr WID,  opb_mapping MAP,  opb_widget_inst WIDINST,

 opb_subject SUB

where WID.widget_id = WIDINST.widget_id

and WID.widget_type = WIDINST.widget_type

and WID.widget_type = 3

and WID.attr_id = 1

and WIDINST.mapping_id = MAP.mapping_id

and MAP.subject_id = SUB.subj_id

and upper(WID.attr_value) like '%TNAME%' ;

Description : This query will give list of all mappings where a particular table is being used in sql override.

Purpose : Search for a table in Sources and Targets :

Query : 

select SUB.subj_name, decode(WIDG.widget_type,1,'Source',2,'Target'),

MAP.mapping_name,

WIDG.instance_name

from  opb_widget_inst WIDG,  opb_mapping MAP, opb_subject SUB

where SUB.subj_id = MAP.subject_id

and WIDG.mapping_id = MAP.mapping_id

and WIDG.widget_type in (1,2)

and WIDG.instance_name like '%TNAME_%'

and SUB.subJ_NAME='YOUR_FOLDER_NAME'

Description : This query will give list of all folders,mappings where a particular table is being used as source or target instance.

Purpose : Query to give lookup information

Query :

Select distinct wid.WIDGET_ID, all_map.mapping_name, wid.INSTANCE_NAME Lkp_name, Decode(widat.attr_id,2,widat.attr_value) Table_name,

decode (widat.attr_id,6,widat.attr_value) src_tgt

FROM  rep_all_mappings ALL_MAP, rep_widget_inst wid,  OPB_WIDGET_ATTR widat

where all_map.mapping_id=wid.mapping_id

and wid.WIDGET_ID=widat.WIDGET_ID

and all_map.subject_area='DCM_SPP_UPL_DEVT'

and wid.WIDGET_TYPE=11

and widat.WIDGET_TYPE=11

and widat.ATTR_ID in (2,6)

Description : This query will give information about lookup transformations like lookup name,Tablename ,Mapping name etc. 

Purpose : Query to give Invalid workflows

Query :

select opb_subject.subj_name, opb_task.task_name

from  opb_task,   opb_subject

where task_type = 71 

and is_valid = 0 

and opb_subject.subj_id = opb_task.subject_id

and UPPER(opb_subject.SUBJ_NAME) like UPPER('YOUR_FOLDER_NAME')

Description : This query will list of all invalid 

If you have need some information and need query about something different related to metadata query please mail us at support@itnirvanas.com.

Read More

Best Practices in Informatica (Part1)

We have tried to come up with some of best practices in informatica

1) Always try to add expression transformation after source qualifier and before Target. If source or target definition changes it is easier to reconnect the ports
2) Always use Cobol File for normaliser in binary format otherwise there are lot of issues specially with comp-3 fields
3) Remove unused ports, though unused ports do not have any effect on performance bit it is always better to remove them for more visibility
4) If possible try to do calculation in Output Ports instead of variable ports as variable ports are need to assign/reassign each time and it can slow down the performance
5) Try to avoid complex filter expression instead of that try to evaluate filter expression in upstream expression transformation and pass it to filter transformation. If you use too many complex calculations in filter condition expression it can slow down performance.
6) In workflow Source/Target directory Property take advantage of Unix links. Instead of hard coding path in source/target directory specify path with Unix link

i.e. suppose in devt environment you are specifying Source directory path as /devserver/team/source and in prod server you specify it as /prodserver/team/source .You can get link created in $PMRootDir in as src_file_dir pointing to /devserver/team/source in dev server and /prodserver/team/source in prod server and in your source/Target file directory you can put path as $PMRootDir/src_file_dir In this case there is no need to change Source/Target directory every time you move between production and dev and testing

7) In sequence generator do not connect current value port to downstream (unless required) transformation as when we connect current value port from sequence generator transformation Informatica Server processes one row in each block. We can optimize performance by connecting only the NEXTVAL port in a mapping .
8) Improve lookup performance by putting all conditions that use the equality operator ‘=’ first in the list of conditions under the condition tab.
9) Always remember rule not to cache look tables having more than 550000 rows (Assuming row size 1024) .If your row size is less than or more than 1024 then adjust number of rows accordingly .
10) Avoid calculating same value again and again. Instead of that store it in a variable use it several times.

If you have further queries then please mail to support@itnirvanas.com

Read More