Working With Informatica Basics

Working with Informatica Basics
Makes you familiar to work with Informatica basics 327431 Gayathri R 5/2/2013
Ta !" of #ont"nts
Table of Contents....................................................................................................... 2 1. What is Informatica?............................................................................................... 3 2.W !"#$ W M %IT !........................................................................................... 3& 3.Issues an' (olutions.............................................................................................. 3)
2*+age
1$
What is Informatica%
Informatica is an ,T$ a tool to set u- .ata Warehouse. Informatica can communicate with all ma/or 'atabases0 can mo1e2transform 'ata between them. It can mo1e huge 1olumes of 'ata in a 1ery effecti1e way. It can throttle the transactions 3'o big u-'ates in small chunks to a1oi' long locking an' filling the transactional log4. It can effecti1ely 'o /oins between tables in 'ifferent 'atabases on 'ifferent ser1ers.
1$1 &ackag"s
Informatica comes in 'ifferent -ackages5
Informatica &ow"r #"nt"r license 6 has all o-tions7 inclu'ing 'istribute' meta'ata7 ability to organi8e re-ositories into a 'ata mart 'omain an' share meta'ata across re-ositories. Informatica +ower Mart 6 a limite' license
1$2 working with informatica

Informatica re-ository contains5 (ources Targets Ma--ings (ession Workflow Manager Workflow Monitor
'o(rc"s5 The (ource 'atabase to be connecte' Targ"ts5 The target 'atabase to be connecte' )a**ings5 Ma--ings re-resent the 'ata flow between sources an'
targets. When the Informatica (er1er e9ecutes a session7 it uses the instructions configure' in the ma--ing to rea'7 transform7 an' write 'ata. ,1ery ma--ing must contain the following com-onents5
Source definition5 It 'escribes the characteristics of a source table or file. Transformation+ It is use' to mo'ify 'ata before writing it to targets. We can use 'ifferent transformation ob/ects on the source 'ata an' obtain 'ifferent results Target definition+ It 'efines the target table or flat file
3*+age

Connectors+ Connect sources7 targets7 an' transformations so the Informatica (er1er can mo1e the 'ata as it transforms it
'"ssions+ : session is a set of instructions to mo1e 'ata from sources
to targets Workf!ow )anag"r+ : Workflow is set of instructions for the Informatica (er1er. It combines the logic of (ession Tasks to -erform 'ata transformation an' loa' Workf!ow )onitor+ Workflow Monitor is hel-ful in monitoring an' tracking the workflows create'
2 I,-.R)/TI#/ 01'IG,1R
2$1 #.)&.,1,T' .- 01'IG,1R

(ource Target Ma--ing
2$1$1
'o(rc"
(te-s to Connect To (ource .atabase5
;*+age
Step 1+ Tools 6 < (ource :naly8er
't"* 2+ (ource 6< Im-ort from 'atabase
't"* 3+
Set the ODBC data source Click the icon as shown in the figure
=*+age
'T1& 4+ Click on the >(ystem .(%? tab an' click >:''? to a'' a .(% as shown in the figure.
't"* 5+ (elect the .ata source an' click >finish as shown in the figure
&*+age

't"* 2+ Ty-e the 'ata source name an' click TestConnect as shown in the figure
't"* 7+ (elect the %etwork $ibrary7 ,nter the %etwork :''ress7 $ogin I.7 an' +asswor' an' select the .atabase an' click > k?
't"* 3+ (elect your .ata (ource an' click > k?.
@*+age
't"* 4+ Ty-e the Asername7 .wn"r name an' +asswor' an' click >Connect? as shown in the figure
't"* 10+ (elect the tables an' click > k? as shown in the figure
B*+age
The ta !"s get loa'e' to the (ource #ol'er in the .esigner Win'ow as shown in the figure.
2$1$2
Targ"t
(te-s to $oa' the Target Tables5 't"* 1+ (elect Tools 6< Target .esigner
)*+age
't"* 2+ (elect Targ"ts 6< Im-ort from .atabase
't"* 3 (elect the .BC 'ata source. Click the icon as shown in the figure
1C * + a g e
't"* 4+ (elect the >(ystem .(%? tab an' click >:''? as shown in the figure
't"* 5+ (elect the .ata (ource an' Click >#inish?.
11 * + a g e
't"* 2+ Ty-e th" .ata (ource %ame an' Click >Test Connect? as shown in the figure
't"* 7+ (elect the %etwork $ibrary7 Ty-e the %etwork :''ress7 $ogin I.7 +asswor'7 .atabase an' click > "?.
12 * + a g e

'T1& 3+ '"!"ct your .ata (ource an' Click > "?
'T1& 4+ Ty-e the Asername7 wner name an' +asswor' an' click >Connect? as shown in the figure
'T1& 10+ (elect the ta !"s an' click > "? as shown in the figure
13 * + a g e

The target tables get loa'e' to the Target fol'er as shown in the figure
2$1$3
)/&&I,G
(te-s to Create Ma--ing

'T1& 1+ (elect Tools 6< Ma--ing .esigner
1; * + a g e

'T1& 2
Ty-e the name for the ma--ing an' Click > k? .rag the (ource table into Ma--ing .esigner win'ow
'T1& 3+ (elect the Transformation from the toolbar an' 'rag it to the .esigner Win'ow
1= * + a g e

'T1& 4+ (elect the Target ta !" an' 'rag it to the .esigner Win'ow
'T1& 5+ (elect the column in from the (ource Dualifier an' 'rag it to the Transformation to form the connection
1& * + a g e

'T1& 2+ (elect the columns from the Transformation an' 'rag it to the Target table to form the connection
't"* 7+ !ight Click on the Transformation an' Click e'it an' select the -ro-erties to the Transformation
1@ * + a g e
'T1& 3+ &r"ss CT!$ E ( to sa1e the ma--ings.
2$2 TR/,'-.R)/TI.,'
+lease fin' below the freFuently use' transformations
2$2$1
'o(rc" 5(a!ifi"r Transformation
It is use' to filter sources 3(D$7 select 'istinct7 /oin7 etc.4 The source Fualifier transformation re-resents the recor's that the
informatica ser1er rea's when it runs a session.
To e'it the -ro-erties of (ource Dualifier7 right click 6< e'it.
1B * + a g e
In the -ro-erties7 you can s-ecify the sFl Fuery to be run 3which
selects 'ata from the source table4 Gou can s-ecify the filter con'itions Below are the freFuently use' tabs in the source Fualifier e'it win'ow5
+orts 5 s-ecifies the fiel' name7 'ata ty-e of the source table +ro-erties5 s-ecifies the sFl Fuery to be run7 -re sFl an' -ost sFl to be run. Has o-tion to select 'istinct 'ata from the source an' filter con'itions to be a--lie' to the source 'ata
1) * + a g e
2$2$2
6*7at" 'trat"gy transformation
Informatica -rocesses the source 'ata row6by6row. By 'efault e1ery row is marke' to be inserte' in the target table. If the row has to be u-'ate'2inserte' base' on some logic A-'ate
(trategy transformation is use'. The con'ition can be s-ecifie' in A-'ate (trategy to mark the -rocesse' row for u-'ate or insert. #ollowing o-tions are a1ailable for u-'ate strategy5
008I,'1RT+ If this is use' the A-'ate (trategy flags the row for insertion. ,Fui1alent numeric 1alue of ..II%(,!T is C. 0086&0/T1+ If this is use' the A-'ate (trategy flags the row for u-'ate. ,Fui1alent numeric 1alue of ..IA+.:T, is 1. 0080191T1+ If this is use' the A-'ate (trategy flags the row for 'eletion. ,Fui1alent numeric 1alue of ..I.,$,T, is 2. 008R1:1#T+ If this is use' the A-'ate (trategy flags the row for re/ection. ,Fui1alent numeric 1alue of ..I!,J,CT is 3.
2C * + a g e

'T1& 1+ (elect the A-'ate (trategy Transformation from the tool bar
an' 'rag it to the Ma--ing .esigner Win'ow.
'T1& 2+ Connect the A-'ate (trategy Transformation. 3#or ,95
Connecting (ource Dualifier to A-'ate Transformation4
'T1& 3+ Right click on the A-'ate (trategy Transformation an' click
>,'it? to set the -ro-erties. 21 * + a g e
Below are the ;ario(s -ro-erties that can be set5 &orts 5 (-ecifies the fiel' names7 'ataty-es &ro*"rti"s5 s-ecifies the ty-e of function to be -erforme' 3insert7 u-'ate7 'elete7 re/ect4
22 * + a g e
2$2$3
1<*r"ssion transformation
,9-ression Transformation is use' to calculate 1alues in a single row
before writing to the target.

'T1& 1+ (elect the ,9-ression transformation from the tool bar an'
'rag it to the ma--ing 'esigner win'ow
23 * + a g e
'T1& 2+ Connect the ,9-ression Transformation. 3#or ,95 Connect
(ource Dualifier to ,9-ression Transformation4
'T1& 3+ !ight click on 1<*r"ssion Transformation an' Click >,'it? to
set the -ro-erties.
2; * + a g e
B"!ow are the -ro-erties that can be set +orts5 (-ecifies the fiel' names7 'ataty-es an' the ty-e of the fiel' 3in-ut2out-ut21alue fiel'4 +ro-erties5 To set the tracing le1el of the logs to be 'is-laye' in the workflow monitor
2= * + a g e
Working with Informatica Basics 2$2$4

synonym
$ooku- 'efinition can be im-orte' either from source or from target
9ook(* Transformation
9ook(* Transformation is use' to looku- the 'ata in table7 1iew7 an'
tables
'T1& 1+ (elect the $ooku- Transformation from the tool bo9 an' 'rag
it to the ma--ing 'esigner win'ow
'T1& 2 9ook(* Transformation can be connecte' or unconnecte' Can select the table either from source or target an' im-ort it
2& * + a g e

'T1& 3+ !ight click on the $ooku- Transformation an' Click >,'it? to
set the -ro-erties
Below are the *ro*"rti"s that can be set5 +orts5 s-ecifies the fiel' names7 'ataty-es7 ty-e of
fiel's3in-ut7out-ut7looku-7return4
+ro-erties 5 can s-ecify the sFl Fuery to be run7can a'' a filter to the
recor's to be selecte'.
Con'ition 5 it must be s-ecifie' 3like a key column4 K the con'ition on
which the recor's are searche' an' selecte' 3#or ,g 5 tablea.i'Ltableb.i'4
2@ * + a g e
2$2$5
Ro(t"r Transformation
It is use' to filter the 'ata base' on the con'itions Multi-le con'itions can be use' to filter the 'ata It has in-ut7 out-ut an' 'efault grou-s It is similar to case statement in any -rogramming language
2B * + a g e

'T1& 1+ (elect the !outer Transformation from the toolbo9 an' 'rag it
to the ma--ing 'esigner win'ow
'T1& 2+ Connect the in-ut to the in-ut -orts of !outer Transformation
3#or ,95 (ource Dualifier is connecte' to !outer Transformation4.
'T1& 3+ Right click on !outer Transformation an' Click >,'it? to set
the -ro-erties an' a'' grou-s to the !outer Transformation
2) * + a g e
Below are the *ro*"rti"s that can be set5 +orts5 (-ecifies the fiel' names an' 'ataty-es +ro-erties5 (-ecifies the tracing le1el of the log to be 'is-laye' in the workflow monitor Mrou-5 s-ecifies the con'itions
3C * + a g e
3 Workf!ow )anag"r
: workflow is a set of instruction that tell the Informatica ser1er how to e9ecute the tasks It controls the e9ecution of tasks such as comman's emails an' sessions Click on the Workflow icon from the toolbo9
3$1 '"ssion
: session is a set of instructions to mo1e 'ata from sources to targets The following are the ste-s to create the session5 'T1& 1+ Click on the >Task .e1elo-er? icon from the toolbar
'T1& 3+ Ty-e a name to the session an' click Create
31 * + a g e
'T1& 4+ Click >0on"= after the session get create'.
'T1& 5+ !ight click on th" session an' Click >,'it? to set the -ro-erties
32 * + a g e
Working with Informatica Basics 3$2 #r"ation of th" workf!ow

#ollowing are the st"*s to create a workflow 'T1& 1+ Click on the Workflow 'esigner icon from the toolbo9
'T1& 2+ Click Workflow 6< Create
33 * + a g e
'T1& 3+ Ty-e the nam" of the workflow an' click > k?.
'T1& 4+ (elect the session an' 'rag it to the workflow 'esigner win'ow
3; * + a g e
'T1& 5+ !ight click on the session an' Click >,'it? to set the -ro-erties
'T1& 2+ $ink the session by clicking Task 6< $ink Task
3= * + a g e
'T1& 7+ !ight click on th" workflow an' Click >(tart Workflow from Task? to start the workflow
2$ W.R>-9.W ).,IT.R
The Workflow Monitor *ro;i7"s o-tions to 1iew information about workflow runs :fter you o-en the Workflow Monitor an' connect to a re-ository7 you can 1iew 'ynamic information about workflow runs by connecting to an Integration (er1ice Gou can customi8e the Workflow Monitor 'is-lay by configuring the ma9imum 'ays or workflow runs the Workflow Monitor shows Gou can also filter tasks an' Integration (er1ices in both Mantt chart an' Task 1iew
3& * + a g e
3$3 R(n *ro*"rti"s

In the workflow monitor7 you can get the run -ro-erties by right clicking the task an' selecting >Met !un +ro-erties? as shown in the figure
3@ * + a g e
3$4 '"ssion 9og

In the workflow monitor7 you can get the session log by right clicking the task an' selecting >Met (ession $og? as shown in the figure
3B * + a g e
3$ Iss("s an7 'o!(tions

3$5 0at" format
.escri-tion+ The 'ate format in (D$ an' (ybase is 'ifferent so the 'ata was not able to get loa'e' from (D$ ser1er to (ybase ser1er
(olution5 :''e' an e9-ression ma--ing in the ma--ing win'ow an' ty-e' the -ro-erties as5 To .ate 3column name7 GGGG6MM6.. HH2;5 MI5 ((.%(4
3$2 ,orma! mo7"

.escri-tion+ %ormal loa' for tables ha1ing more !ecor's took longer time. In ma--ing when the loa' o-tion is selecte' as N%ormalO7 Informatica took more time to loa' the 'ata from (D$ ser1er to (ybase. #or ,95 Table with !ecor's5 137@23 took C51)53@
(olution5 Bulk loa' o-tion is selecte' in Ma--ing. 3Table with !ecor's5 137@23 took C5CC5314
3) * + a g e
Working with Informatica Basics 3$7 6*7at" fai!"7

0"scri*tion+ A-'ate faile' for the tables in which all the columns are -rimary key #or e95 Consi'er a table t1 contains two columns col1 an' col2. Both col1 an' col2 is marke' as -rimary key :s both the columns are -rimary key7 Informatica 'oesn?t allow an u-'ate to be -erforme'
(olution5 In the target table7 select the -ro-erty5 A-'ate Menerate(D$
1erri'e 6<
3$3 ,o !ast (*7at" 7at" co!(mn

0"scri*tion+ #or tables without the $astu-'ate.ate column7 A-'ate must be 'one There were tables with no $astu-'ate.ate column for which the incremental loa' must be 'one
(olution5 Ase' +rimary "ey to check for the uniFue recor's an' use' !outer Transformation to -erform either Insert or A-'ate (ince we 'oesn?t ha1e any timestam- to say which column is newly a''e'7 +rimary "ey is been use' to check whether the 'ata is alrea'y -resent in the table or not. .ata from the target table is checke' with the 'ata in the (ource table using a $ooku- Transformation. If the 'ata is -resent A-'ate is 'one an' if the 'ata is not -resent Insert is 'one
3$4 ,o *rimary k"y

0"scri*tion+ There were tables without -rimary key for which the 'ata must be loa'e' from (D$ to (ybase
(olution5 Create' Informatica le1el -rimary key In source 'esigner7 select the table an' in -ro-erties7 select the -rimary key In target 'esigner7 select the table an' in -ro-erties7 select the -rimary key
;C * + a g e

Working With Informatica Basics

Uploaded by

Copyright:

Available Formats

Working With Informatica Basics

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Working With Informatica Basics

Uploaded by

Copyright:

Available Formats

Working with Informatica Basics