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