Dynamic Cache in Informatica Lookup Transformation

As we saw in our previous informatica tutorial , that  look transformation cache need to be enabled to boost the performance of Lookup transformation( by avoiding lookup in the lookup source again and again)

Now if Look up source is getting changed , then we  need to also refresh lookup cache. Dynamic cache in lookup Transformation solves our purpose  :).

Dynamic cache:

If you want to cache the target table and insert new rows into cache and the target,you can create a look up transformation to use dynamic cache.The informatica server dynamically inserts data to the target table. 

 

1. You cannot share the cache between a dynamic Lookup transformation and static Lookup transformation in the same target load order group.
2. You can create a dynamic lookup cache from a relational table, flat file, or source qualifier transformation.
3. The Lookup transformation must be a connected transformation.
4. Use a persistent or a non-persistent cache.
5. If the dynamic cache is not persistent, the Integration Service always rebuilds the cache from the database, even if you do not enable Re-cache from Lookup Source.
6. When you synchronize dynamic cache files with a lookup source table, the Lookup transformation inserts rows into the lookup source table and the dynamic lookup cache. If the source row is an update row, the Lookup transformation updates the dynamic lookup cache only.
7. You can only create an equality lookup condition. You cannot look up a range of data in dynamic cache.

Informatica Powercenter Architecture

After so many Informatica tutorial  on so many topics ( transformation  , tuning of transformation), question arises , what exactly is happening behind the scene. So time has come to discuss about architecture of Informatica in details. We will discuss about the various component of Informatica architure , various services and how they are interlinked to each other. Hope you will enjoy this Informatica tutorial. By the way , it is one of the most  asked interview question in Informatica.

 

Component of Informatica Architecture

Domain: Domain is the primary unit for management and administration of services in Powercenter. The components of domain are one or more nodes, service manager an application services.

Node: Node is logical representation of machine in a domain. A domain can have multiple nodes. Master gateway node is the one that hosts the domain. You can configure nodes to run application services like integration service or repository service. All requests from other nodes go through the master gateway node.

Service Manager: Service manager is for supporting the domain and the application services. The Service Manager runs on each node in the domain. The Service Manager starts and runs the application services on a machine.

Application services: Group of services which represents the informatica server based functionality. Application services include powercenter repository service, integration service, Data integration service, Metadata manage service etc.

Powercenter Repository: The metadata is store in a relational database. The tables contain the instructions to extract, transform and load data.

Powercenter Repository service: Accepts requests from the client to create and modify the metadata in the repository. It also accepts requests from the integration service for metadata to run workflows.

Powercenter Integration Service: The integration service extracts data from the source, transforms the data as per the instructions coded in the workflow and loads the data into the targets.

Informatica Administrator: Web application used to administer the domain and powercenter security.

Metadata Manager Service: Runs the metadata manager web application. You can analyze the metadata from various metadata repositories.

I also found a good article on Informatica Architecture here 

Informatica Interview question with answer for lookup Transformation

In below article , we will go through the some the Informatica Interview question based on the lookup transformation in Informatica. In last tutorial , we have gone through the detailed explanation of lookup Transformation.
You can find a good details of Lookup transformation here.

We also checked some of the example of connected and unconnected lookup transformation also.

Lets go through some of the Informatica Interview question on Lookup transformation.

Check here : Difference connected and unconnected Lookup Transformation here .

Que: What is a pipeline lookup transformation??

Ans :A pipeline lookup transformation is used to perform lookup on application sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source qualifier as the lookups source.

Que: What are the different type of Sources for Lookup ?

Ans :

• Relational source or target definition in the repository
• Flat file source or target definition in the repository
• Table or file that the Integration Service and PowerCenter Client machine can connect to
• Source qualifier definition in a mapping

Ques. What is a lookup transformation?
Ans : A lookup transformation is used to look up data in a flat file, relational table, view, and synonym.

Ques: What are the tasks of a lookup transformation?

Ans : The lookup transformation is used to perform the following tasks?

• Get a related value: Retrieve a value from the lookup table based on a value in the source.
• Perform a calculation: Retrieve a value from a lookup table and use it in a calculation.
• Update slowly changing dimension tables: Determine whether rows exist in a target.

Ques: . What is "Output Old Value on Update"?
Ans : This option is used when dynamic cache is enabled. When this option is enabled, the integration service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a new row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.

Ques: . How do you configure a lookup transformation?

Ans : Configure the lookup transformation to perform the following types of lookups:

• Relational or flat file lookup
• Pipeline lookup
• Connected or unconnected lookup
• Cached or uncached lookup

Ques: What are the options available to configure a lookup cache?
Ans : The following options can be used to configure a lookup cache:

• Persistent cache
• Recache from lookup source
• Static cache
• Dynamic cache
• Shared Cache
• Pre-build lookup cache

Check here : Difference Static Cache and dynamic Cache  in Informatica here .

I also found a good article on Informatica Interview question on Lookup Transformation  here and here

Slowly Changing Dimension Type 2 Examples (SCD 2)

  SCD Type-2 In Informatica[/caption] Slowly Changing  Dimension Type-2 ,(also known as SCD -2)  tracks historical changes  by  keeping multiple records for a given natural key in the dimensional tables .For example , we may need to track the current location of a supplier along with its previous location just to track his sales in different region .
 Example of SCD Type -2

For Example : If we want to keep track of DEPT of an employee , we can add two extra fields , Say Start-date , End_date to keep track of those records: EMP_SCD2

SRG_KEY	EMPNO	NAME	DEPT	START_DATE	END_DATE
101	1	JOHNSON	OPERATIONS	12/17/1990	4/13/1992
102	2	HARDING	ACCOUNTING	2/2/1998	9/17/1998
103	3	TAFT	RESEARCH	1/2/1996
104	4	HOOVER	SALES	4/2/1990	7/11/1993
105	1	JOHNSON	ACCOUNTING	4/13/1992
106	2	HARDING	SALES	9/17/1998
107	4	HOOVER	OPERATIONS	7/11/1993
108	5	LINCOLN	OPERATIONS	6/23/1994

As you see in above example , we can keep track of records by adding effective _date fields . Other way to accomplish this process is to

• Effective Date
• Flagging
• Versioning

I found a good article on "Slowly Changing Dimension Type 2 Examples (SCD 2)"  here.  and full implementation of SCD Type -2 in Informatica can be found here Other good article on SCD are as below :-

• Slowly Changing Dimension Type 2 Examples (SCD 2) 
• SCD Type 2 implementation in Informatica with example
• Slowly Changing Dimension Type 3 Examples (SCD 3)
• SCD Type 3 implementation in Informatica with example
• Slowly Changing Dimension Type 6 Examples (SCD6)
• SCD Type 6 implementation in Informatica with example

  Hope you enjoyed this small and useful article on SCD Type -2 (Slowly changing dimension type -2 ) and example of SCD Type-2 In Informatica  :)  

Type of SQL join in Oracle

SQL Joins are to combine data from different tables to show relation between them . Example you can join  EMP table with DEPT table to get department along with Employee records.

See Also : Learn about Oracle Architecture here 

There are basically below type of Joins :

1. INNER Join :

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
INNER JOIN dept
ON emp.deptno= dept.deptno;

2. Left Outer Join :

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
LEFT OUTER JOIN dept
ON emp.deptno= dept.deptno;

3. Right Outer Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
RIGHT OUTER JOIN dept
ON emp.deptno= dept.deptno;

4. Full Outer Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
FULL OUTER JOIN dept
ON emp.deptno= dept.deptno;

I Found a good article on "Type of Oracle join with Example"  here. 

See Also : Learn about Oracle Architecture here 

Hope you enjoyed this small and useful article on SQL joins in Oracle :)

XML Source in Informatica

XML is gaining its popularity as Data sharing and Data storage medium . With Newer version , Informatica started providing support to XML files as well,

We basically have 3 XML functionality in  Informatica :

1. XML Source Definition

2. XML Target Definition

3. XML transformation.

Here we will discuss about XML File source definition . First of all , e will need to import XML file source definition in Informatica . by

1. Clicking on Source -> Import XML Definition

2. Selecting the  sample XML File and then getting all node information .

3.Checking the format of various nodes

4.Closing the Dialog box

A more detailed example of XML File Source definition  in Informatica  can be found  here.

Another example to xreate XML Target file in Informatica can be found here.

XML Target in Informatica

XML is gaining its popularity as Data sharing and Data storage medium . With Newer version , Informatica started providing support to XML files as well,

We basically have 3 XML functionality in  Informatica :

1. XML Source Definition

2. XML Target Definition

3. XML transformation.

Here we will discuss about XML File Target  definition . First of all , e will need to import XML file source definition in Informatica . by

1. Clicking on Source -> Import XML Definition

2. Selecting the  sample XML File and then getting all node information .

3.Checking the format of various nodes

4.Closing the Dialog box

A more detailed example of XML File Source definition  in Informatica  can be found  here.

Another example to xreate XML Target file in Informatica can be found here.

Oracle database architecture tutorial

Oracle Instance

An oracle instance is a set of different memory structures and the processes which manages all database activities, such as transaction processing, database recovery, form generation, and so on. There is different type of memory block which are accessed by different type of background processes

SGA :

It is also commonly known as  System Global area. It solve many purpose like storing parsed SQL qyeries/ Parsed Pl-sqL code and redo buffer cache.

It has below memory pools

• shared pool : 
• Data Dictionary Cache:
• Buffer Cache:
• Redo Log Buffer:
• Large Pool:
• Java Pool :

PGA :

Although the result of SQL statement parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

Oracle Background Process:

There some back ground process which keep on running to fulfill DB requests.

• DBWR
• LGWR
• CKPT
• SMON
• PMON
• ARCH
• DISPATCHER PROCESSES
• RECO
• SNPn
• LCKn
• Pnnn

Thanks for reading article !! I found a full explanation of these component here

unix interview question with answer

 Below are some list of some unix  interview question , which are commonly asked in unix interview.

1) How to print/display the last line of a file?
A) $> tail -1 file.txt

2) How to display n-th line of a file?
A) $> sed –n ' p' file.txt

3) How to remove the first line / header from a file?
A) $> sed '1 d' file.txt

4) How to remove the last line/ trailer from a file in Unix script?
A) $> sed –i '$ d' file.txt

5) How to remove certain lines from a file in Unix?
A) $> sed –i '5,7 d' file.txt

6) How to remove the last n-th line from a file?
A) $> sed –i '96,100 d' file.txt # alternative to command [head -95 file.txt]

7) How to check the length of any line in a file?
A) $> sed –n ' p' file.txt

8) How to get the nth word of a line in Unix?
A) cut –f -d' '

9) How to reverse a string in unix?
A) $> echo "unix" | rev
xinu

10) How to get the last word from a line in Unix file?
A) $>echo "C for Cat" | rev | cut -f1 -d' ' | rev
Cat

11)What are some common shells and what are their indicators?

sh – Bourne shell
csh – C SHell
bash – Bourne Again Shell
tcsh – enhanced C Shell
zsh – Z SHell
ksh – Korn SHell

12) What is a directory?

Every file is assigned to a directory. A directory is a specialized form of file that maintains a list of all files in it.

13) What is inode?

An inode is an entry created on a section of the disk set aside for a file system. The inode contains nearly all there is to know about a file, which includes the location on the disk where the file starts, the size of the file, when the file was last used, when the file was last changed, what the various read, write and execute permissions are, who owns the file, and other information.

14) You have a file called tonky in the directory honky. Later you add new material to tonky. What changes take place in the directory, inode, and file?

The directory entry is unchanged, since the name and inode number remain unchanged. In the inode file, the file size, time of last access, and time of last modification are updated. In the file itself, the new material is added.

For more good article on unix interview question with answer , pls check here.

Top unix interview question with answer :Part -1

Top unix interview question with answer :Part -2

Top unix interview question with answer :Part -3

Rank Transformation in Informatica with Example

Rank Transformation in Informatica , is a connected and active /Passive transformation which select top/bottom rows of   input. It is something similar to Rank analytical data function or oracle. Only difference is that, it also filter out the remaining rows  (which are not a part of top/bottom threshold).

Must check : Please also check how to create Aggregator Transformation

How Rank Transformation works in Informatica?

It first cache all the input data and then performs the rank calculation per group , filter out the unwanted records.

Steps to create an Rank transformation:

• In the Mapping Designer, open a Mapping.
• Click Transformation > Create. Select Rank transformation.
• Enter a name and click Done.
• You will see one port RANKINDEX port already there. This port store the ranking of each   record  and can be used to populate target as well
• Add all additional port from source input which  are going to be use in following transformation.
• Open the port tab and first check the Group by option for desired column ( for example deptno in our case)
• 
• Also  check the Rank (R) option for the port which you want to do ranking. For example salary in our case.

Note: We can define Group by indicator for multiple port, but  Ranking can be done   on single port only.

• Go to the properties tab, select the Top/Bottom value as Top and the Number of Ranks property as per need.
• 
• Click OK.
• Connect output ports of Rank Transformation to other transformation or target

Must Read : Learn more about informatica coding standard Example of Rank Transformation in Informatica: A good example of Rank transformation with different type of join can be found here. For more explanation on Rank transformation , you can read it here and also check here for informatica interview question on Rank transformation

Top tar command example in unix

In Unix, the name of the tar command is short for tape archiving, the storing of entire file systems onto magnetic tape, which is one use for the command. However, a more common use for tar is to simply combine a few files into a single file, for easy storage and distribution. Must Read : Learn more about VI editor here To combine multiple files and/or directories into a single file, use the following command: tar -cvf file.tar inputfile1 inputfile2 Replace inputfile1 and inputfile2 with the files and/or directories you want to combine. You can use any name in place of file.tar, though you should keep the.tar extension. If you don't use the  f  option, tar assumes you really do want to create a tape archive instead of joining up a number of files. The  v  option tells tar to be verbose, which reports all files as they are added. To separate an archive created by tar into separate files, at the shell prompt, enter: tar -xvf file.tar Must Read : Learn more about find command here A good article of Tar/untar command can be found  here. [contact-form][contact-field label='Name' type='name' required='1'/][contact-field label='Email' type='email' required='1'/][contact-field label='Website' type='url'/][contact-field label='Comment' type='textarea' required='1'/][/contact-form]

Joiner Transformation in Informatica

Joiner Transformation in Informatica , is a connected and active transformation which let you join data from two heterogeneous source (same source system or different source system). Unlike normal SQL joins , we can join data from file system as well. [caption id="" align="alignnone" width="584"] Joiner Transformation in Informatica[/caption]

Must check : Please also check how to create Aggregator Transformation

Join Type

Normal Join A normal join will allow only those records which satisfy the joiner condition for both sources. So remaining records, who don’t match the condition get discarded Master Outer Join A master outer join will keeps all rows of data from the detail source and the matching rows from the master source. If any of master records don’t satisfy the condition , those rows get discarded. Detail Outer Join Just opposite to Master Outer join , it keep all rows of data from Master Source and the matching rows from Details Source. If  any of detail records don’t satisfy the condition , those rows get discarded. Full Outer Join A full outer join keeps all rows of data from both the master and detail sources.   Must Read : Learn more about informatica coding standard Example of Joiner Transformation in Informatica: A good example of Joiner transformation with different type of join can be found here. For explanation on joiner transformation , you can read it here Hope you enjoyed this tutorial on joiner transformation in informatica.

Java Transformation in Informatica with Example

Java Transformation in Informatica , is a connected and active /Passive transformation which provides a simple native programming interface to define transformation functionality with the Java programming language. You can use Java transformation in Informatica to quickly define simple or moderately complex transformation functionality without advanced knowledge of the Java programming language or an external Java development environment Must read : check more about Informatica Naming convention

Few of the sample scenarios are as follows:

• A Java transformation contains two input ports that represent a start date and an end date. You can generate an output row for each date between the start date and end date.
•  you can define transformation logic to loop through input rows and generate multiple output rows based on a specific condition.

Must check : Please also check how to create Aggregator Transformation Java Transformation: A very good example of Active Java transformation can be found here Extra read: Problem :  Create a Informatica mapping to convert subject wise marks (populated in different rows) for student  into  a single row for each student with marks in a separate column for each subject.

Source Data:

STUDENT_MARKS Table Data

STUDENT_NO	SUBJECT	MARKS
1	Math	87
1	Eng	66
1	Science	78
2	Math	45
2	Eng	64
2	Science	55
3	Math	46
3	Eng	89
4	Science	86

Target Data:

STUDENT_MARK_NEW :

STUDENT	MATH_MARK	SCIENCE_MARK	ENG_MARK
1	87	78	66
2	45	55	64
3	46	NULL	89
4	NULL	86	NULL

See solution here

Lookup Transformation in Informatica

Lookup Transformation in Informatica , is a connected/Unconnected and Passive transformation which let you look up data flat file/relation tables ,views or synonym. The Integration Service queries the lookup source based on the lookup ports in the transformation and a lookup condition. The Lookup transformation returns the result of the lookup to the target or another transformation . Example : you may lookup transformation on DEPT tables to get DEPT details based on DEPTNO as input port. Must check : Please also check how to create Aggregator Transformation Connected Lookup Transformation: A very well explained  example of Connected lookup transformation can be found here Unconnected Lookup Transformation: A very well explained  example of UnConnected lookup transformation can be found here Normally source of Lookup is either files or relational database, See here for explaination To improve the performance of lookup transformation , we will need to enable the cache , see here for  different type of cache lookup transformation. There are other guideline also given to improve the performance. A good article can be found here Also check here to see the  difference between Static cache and Dynamic Cache : http://www.tecktricks.com/difference-between-static-cache-and-dynamic-cache-in-informatica/

Expression Transformation in Informatica

Expression Transformation in Informatica , is a connected passive transformation (number of input and output rows is the same), which let you modify individual ports of a single row, or add or suppress them. It helps implement the complicated data transforms, applies business logic and performs checks and validations.  For example: calculating annual Salary A well detailed example of Expression Transformation can be found here. Hope you enjoyed this article, More details can be found at below site Performance tuning related guideline related expression Transformation can be found here  Must check : Please also check how to create Lookup Transformation http://www.tecktricks.com/expression-transformation-in-informatica-example-1/

Normalizer Transformation in Informatica with Example

Normalizer Transformation in Informatica , is a connected and active transformation  which let you to normalize your data  by receiving a row with information scatter in multiple columns to multiple row a for each instance of column data.For example a student have score for each subject scattered in 5 columns ,with the help of normalizer transformation you can create multiple rows for each subject (Normalization of Database) . One simple example of this transformation can be creating subject wise row for a student. A well detailed example of Normalization Transformation can be found here. Hope you enjoyed this article, More details can be found at below site Must check : Please also check how to create Expression Transformation http://www.tecktricks.com/example-of-normalization-transformation-in-informatica-example-1/

Update Strategy Transformation in Informatica , is a connected and active transformation.Update Strategy transformation let you  insert, update , delete data from target Data also. It can also reject the incoming data depending upon your condition.For example : when a customer is changing address , you can use this update strategy transformation to update data in your Customer Tables. In below article we will go through the properties of Update Strategy Transformation. We will also discuss the steps of adding /configuring Update Strategy  transformation in Informatica Mapping You can define update operation on below two levels:

Update Strategy on  Session level:

We can instruct Informatica Service to treat all rows in the same way or use same instruction by configuring Target mapping at session level. Update Strategy on  Mapping level: You can flag a row (for insert , update, delete or Reject depending upon some condition) by adding "Update strategy Expression" in the properties tab of Update Strategy Transformation in a Mapping. Below Flags can be set in Update strategy Expression.

Flag	Numeric Value	Type Operation
DD_INSERT	0	Insert row
DD_UPDATE	1	Update row
DD_DELETE	2	Delete row
DD_REJECT	3	Reject row

For more details on Update Transformation , Please check here A well explained example can be found here as well.

Transaction Control Transformation in Informatica with Example

Transaction Control Transformation in Informatica , is a connected and active transformation  which let you control the commit and rollback of transactions for a set of input data.Example : you may define a transaction on group of employee rows ordered on a common key that is Department_no. Transaction Control Transformation help you control the commit and rollback on a group of data.Transaction can be control at below two level in Informatica Center. We can control Transaction at two levels: SESSION LEVEL : It can be done via setting session properties for Target. MAPPING LEVEL:It can be done by adding the transaction control transformation in mapping.We can add the  required condition in "Transaction control expression" Example : IIF(DEPT_NO >4 , TC_COMMIT_AFTER,TC_CONTINUE_TRANSACTION) One simple example of this transformation can be splitting of file depending upon certain field values. A well detailed example of Transaction Control Transformation can be found here. Hope you enjoyed this article, More details can be found at below site Must check : Please also check how to create Target Definition here. http://www.tecktricks.com/transaction-control-transformation-in-informatica-with-example/