Difference between Lookup and Joiner Transformation in Informatica

Difference between Lookup and joiner transformation in Informatica

Look up transformation :
a) Look up transformation can be used on a single table or a query to search for records that match incoming records. Matching condition can be specified in the lookup transformation. The result returned can be multiple columns or single column.
b) Lookup transformation can be unconnected or connected transformation. Unconnected transformation can return only single value.
c) Lookup transformation can be static or active. Dynamic lookups are active transformation.
d) Lookup transformation be used with more than one relational operator such as > , =, etc.

 

Joiner transformation :

a) Joiner transformation is used to usually to join data coming from two separate tables or source qualifiers.
b) The join can be left outer join, inner join, right outer join, etc.
c) The joiner returns all the results that match the join condition.
d) The master records in the joiner transformation is cached. The detail records are not cached. Hence, joiner transformation is active transformation.

When do you use joiner or lookup transformation?

a) If  the table size is not too large then preferable to use lookup.
b) If result from a single matching records needs to be returned then use a lookup. If there is a query that needs to be used in a lookup to find the result for lookup then good to use a lookup.
c) If you are doing lookup on a table that is updated in the session then use a lookup. Joiners are active so not preferable.
d) If look up table data does not change then the table can be made persistent and used in the lookup which gives even better performance.
e) If data from two different source qualifiers need to be joined then use a joiner.
f) If data from two different databases are read and need to be joined with a outer or inner join then use joiner transformation.

Creating DATABASE, GROUPS, USERS , GRANTING PERMISSIONS in Netezza

Creating DATABASE, GROUPS, USERS , GRANTING PERMISSIONS in Netezza

Some of the sample netezza commands to create database, user groups , users, add user to a group, grant permissions to netezza objects is below:

Creating a netezza database, group, and adding user group permissions to that database

\c SYSTEM
CREATE DATABASE <databasename>;
CREATE GROUP <groupname> WITH USER <username1>, <username2>;
GRANT LIST ON <databasename> TO <groupname> WITH GRANT OPTION;GRANT  Create External Table, Create Sequence, Create Synonym, Create Table, Create Temp Table, Create View TO <groupname> ;
GRANT backup, create sequence, create synonym, create table, create external table, create temp table, create view, restore  TO <groupname> ;

To create a user group in netezza database:

Syntax: CREATE GROUP <groupname>

Example: CREATE GROUP  MYTESTGROUP1;

To alter the settings of the group:

ALTER GROUP "<groupname>" WITH ROWSETLIMIT 0 SESSIONTIMEOUT 0 QUERYTIMEOUT 50 DEFPRIORITY High MAXPRIORITY High;

To create a user and assign to a netezza database user group:

CREATE USER  <username>  WITH PASSWORD '??' IN GROUP "<groupname>";

If user already exists or after you have added a user then you can use the command below to add that user to a group:

ALTER GROUP "<groupname>" ADD USER "<username>";

To provide permission on netezza objects such as tables, views, database etc to a user group or a user use the syntax below:

GRANT List ON "<databasename>" TO "groupname";

GRANT List, Select, ON "<tablename>" TO "groupname";

GRANT delete,genstats,groom,insert,list,select,truncate,update ON <tablename> TO <groupname>;
 

To copy permissions from one table to the other in Netezza:

ALTER TABLE <target_table_name> set privileges to  <target table name>;

To change ownership of a netezza table:

alter table <tablename> owner to <newownername>

The above command change the schema name associated with the table

Ways to Sort and aggregate data in Informatica

Sorting  and aggregating data in  Informatica

Sorting In Informatica:

In the Source qualifier of Informatica mapping you can sort the data by manually overiding the SQL and adding order by clause to the SQL. Also, group by clause can be added to the source qualifier.

Select col1, col2, col3 from table1 order by col1, col2


The other way to sort data in the source qualifier of Informatica mapping is to use the Number of sorted ports field in the properties tab of the source qualifier (as shown in screenshot). You can set it to any number of ports you want to sort it on and it will sort the data based on the order the ports are in the source qualifier. If you set the number of sorted ports to 2, then it going to sort in ascending order of port1 and port2 which is eqvilent to using order 1,2 clause in the sql.

Advantage of using deployment groups in Informatica

Advantage of using deployment groups in Informatica

Deployment groups- contains all the informatica objects that need to be migrated to the destination machine. It is one of the easiest method to migrate code from one repository to another if it involves few objects. 

To use deployment groups: 

Go to Repository Manager.
Under Tools>Deployment Groups > Create
Provide a name for the deployment group and add the desired permissions.
Select the informatica objects that you want to migrate , right click and select add to deployment group or you can also copy the objects and paste it to the deployment group
Now copy this deployment group from source repository to the destination repository using normal copy and paste.
The deployment wizard will start and you have to follow the on screen steps.


Advantage of using deployment groups:

1) It is easy to deploy since you can migrate all the objects in one shot just like doing a folder copy.
2) You do not have to create shortcuts manually in the destination folder since deployment group will automatically copy all the shortcuts.
3) You can migrate the changes without having to copy the dependencies such as source or target definition.
4) You can migrate only the portions of the workflow that have changed by adding only those objects from the workflow to the deployment group and remigrating the workflow. For example, if you have add 3 sessions to the workflow, then add the 3 sessions to the deployment group and also the workflow ( no dependencies) to the deployment group. This will migrate the workflow with those 3 sessions only to the target and add those 3 sessions to the workflow in the  target repository.
5) You can automatically set to validate all the objects in the target repository though I have found that this is time consuming.

Unconnected lookup in Informatica - Troubleshooting

Unconnected lookup in Informatica - Troubleshooting

Kept running into the below error message when I was using unconnected lookup and finally resolved it with proper setting on the lookup properties. In the below blog, I'm sharing the proper settings for unconnected lookups:

Transformation: exp_job_control Field: target_count

<<PM Parse Error>> [:LKP.lkp_counter_work(1),'2')]: : invalid function reference

  

The unconnected lookups are called from expression or some filter transformation like a function call. To call it as a function, you need to pass some input and get a return value. To call an unconnected lookup in informatica we use the below syntax:

:LKP.lookupname(parameter1, parameter2,..)

For example below it would be:

:LKP.lkp_target_work(employee_id)

The value returned from the lookup can be used in some expression or assigned to the port. 

The lookup transformation properties would like the screenshot below: In the lookup emp_id and emp_dept are from the look up table and input_emp_id is the input column whose value is sent by the calling lookup function. 

The correct setting is that the lookup table columns should be marked as output and lookup port and one of it should be marked as return port.  The input port should be marked as Input port.

  The join condition for the above lookup would be:

Dynamic Filenames and Transaction Control Transformation in Informatica

To create dynamic file names in Informatica, you need to create a target flat file definition with the file name column as one of the port.

To do this, open the target definition in target designer, go to columns tab and you will see an option (shown in screenshot below) to Add Filename column to this table. If you click on that option, a new port will be added for the file name. The value that you pass to this port will decide the file name.

Now to dynamically alter the filename in your mapping you will need a transaction control  transformation. Add transformation control protocal transformation to your mapping just before the target definition. Based on your application logic, you can alter the filename. In simple example shown below, if value of Input1 and Input2 changes you are setting the transaction control condition to start a new filename.The filename port in the transaction control  could be set to something like Input1+'_Filename' so that the value of the filename port changes every time the transaction is committed.

Informatica version 9.5.1 important features and enhancements for big data and social media

The enhancements listed in this blog are from the Informatica version 9.5.1 hotfix3 new features guide. Some important features for hadoop and social media integration is listed in this blog. For more details check the original new features guide:

Enhancements for Informatica version 9.5.1 hotfix3:

PowerExchange for Hadoop:  Supports IBM InfoSphere BigInsights version 2.1 to access Hadoop sources and targets.
PowerExchange for MongoDB for PowerCenter: Supports extracting data from and load to a MongoDB database.

Enhancements for Informatica version 9.5.1 hotfix2:

PowerExchange for Hadoop supports MapR 2.1.2 and EMC Greenplum PivotalHD 2.0.1 to
access Hadoop sources and targets.

PowerCenter Big Data Edition:You can run mappings in a Hive environment with MapR 2.1.2 and Hortonworks 1.1 distribution

PowerExchange for Facebook: uses the Facebook API to control the number of rows that you request when a mapping runs.

PowerExchange for LinkedIn: uses the LinkedIn API to control the number of rows that you request when a mapping runs.

PowerExchange for Twitter:uses the Twitter API to control the number of rows that you request when a mapping runs.


The Data Masking transformation can mask integer and bigint data for phone number masking.

Enhancements for Informatica version 9.5.1 hotfix1:

Pushdown Optimization Enhancements: You can push transformation logic for an Aggregator transformation and a Sorter transformation to a relational source database

Data Transformation with JSON Input:A Data Processor transformation can contain JSON input with an .xsd schema file that defines JSON input file hierarchies.

Recover Workflows: When you monitor workflows, you can recover aborted or canceled workflow instances that are enabled for recovery.

PowerExchange for Hadoop: supports Cloudera 4.1.2 and HortonWorks 1.1 to access Hadoop sources and targets.

Enhancements for Informatica version 9.5.1:

PowerExchange for HDFS:  access data in a Hadoop Distributed file System (HDFS) cluster.You can read and write fixed-width , delimited file formats and  compressed files. You can read text files and binary file formats such as a sequence file from HDFS with a complex file data object. You can specify the compression format of the files. You can use the binary stream output of the complex file data object as input to a Data Processor transformation, which can parse the file.

PowerExchange for Hive: access data in a Hive data warehouse.You can read data from Hive in native or Hive run-time environments. You can write to Hive only if the run-time environment is Hive.You can create a Hive connection to run Informatica mappings in the Hadoop cluster. You can specify the Hive validation and run-time environment for Informatica mappings.

PowerExchange for Facebook: You can access Facebook through an HTTP proxy server.You can specify a list of access tokens that the Data Integration Service can use at run time to authenticate access to Facebook.

PowerExchange for LinkedIn: You can access LinkedIn through an HTTP proxy server.You can specify a list of access tokens that the Data Integration Service can use at run time to authenticate access to LinkedIn.

PowerExchange for Teradata Parallel Transporter API: You can use PowerExchange for Teradata Parallel Transporter API to load large volumes of data into Teradata tables by using Load or Stream system operators.

PowerExchange for Twitter: You can access Twitter through an HTTP proxy server.
You can specify a list of access tokens that the Data Integration Service can use at run time to authenticate access to Twitter.