6/5/2015

[Link]/search/label/INFORMATICA

PUSHDOWNOPTIMISATION
[Link]
optimization,[Link]
targetdatabaseexecutestheSQLqueriestoprocessthetransformations.
Theamountoftransformationlogicyoucanpushtothedatabasedependsonthedatabase,transformationlogic,andmappingandsession
[Link].
UsethePushdownOptimizationViewertopreviewtheSQLstatementsandmappinglogicthattheIntegrationServicecanpushtothesourceortarget
[Link].
Thefollowingfigureshowsamappingcontainingtransformationlogicthatcanbepushedtothesourcedatabase:

[Link]
thetransformationlogictothedatabase,theIntegrationServicegeneratesthefollowingSQLstatement:
INSERTINTOT_ITEMS(ITEM_ID,ITEM_NAME,ITEM_DESC)SELECTCAST((CASEWHEN5419ISNULLTHEN''ELSE5419END)+'_'
+(CASEWHENITEMS.ITEM_IDISNULLTHEN''ELSEITEMS.ITEM_IDEND)ASINTEGER),ITEMS.ITEM_NAME,ITEMS.ITEM_DESC
FROMITEMS2ITEMS
TheIntegrationServicegeneratesanINSERTSELECTstatementtoretrievetheID,name,anddescriptionvaluesfromthesourcetable,createnew
itemIDs,andinsertthevaluesintotheITEM_ID,ITEM_NAME,andITEM_DESCcolumnsinthetargettable.Itconcatenatesthestorenumber
5419,anunderscore,andtheoriginalITEMIDtogetthenewitemID.
PushdownOptimizationTypes
Youcanconfigurethefollowingtypesofpushdownoptimization:
[Link].
[Link].
[Link]
cannotpushalltransformationlogictothedatabase,itperformsbothsourcesideandtargetsidepushdownoptimization.
RunningSourceSidePushdownOptimizationSessions
Whenyourunasessionconfiguredforsourcesidepushdownoptimization,theIntegrationServiceanalyzesthemappingfromthesourcetothetarget
[Link]

1/51

6/5/2015

[Link]/search/label/INFORMATICA

oruntilitreachesadownstreamtransformationitcannotpushtothesourcedatabase.
TheIntegrationServicegeneratesandexecutesaSELECTstatementbasedonthetransformationlogicforeachtransformationitcanpushtothe
[Link],itreadstheresultsofthisSQLqueryandprocessestheremainingtransformations.
RunningTargetSidePushdownOptimizationSessions
Whenyourunasessionconfiguredfortargetsidepushdownoptimization,theIntegrationServiceanalyzesthemappingfromthetargettothesource
[Link],DELETE,orUPDATEstatementbased
[Link]
[Link],itexecutesthegeneratedSQLontheTargetdatabase.
RunningFullPushdownOptimizationSessions
Tousefullpushdownoptimization,[Link]
sessionconfiguredforfullpushdownoptimization,theIntegrationServiceanalyzesthemappingfromthesourcetothetargetoruntilitreachesa
[Link]
transformationlogicitcanpushtothedatabase.
Whenyourunasessionwithlargequantitiesofdataandfullpushdownoptimization,[Link]
followingdatabaseperformanceissueswhenyougeneratealongtransaction:
Alongtransactionusesmoredatabaseresources.
[Link].
[Link],consider
usingsourcesideortargetsidepushdownoptimization.
RulesandGuidelinesforFunctionsinPushdownOptimization
Usethefollowingrulesandguidelineswhenpushingfunctionstoadatabase:
IfyouuseADD_TO_DATEintransformationlogictochangedays,hours,minutes,orseconds,youcannotpushthefunctiontoaTeradata
database.
WhenyoupushLAST_DAY()toOracle,[Link],Oracletrimsthedate
tothesecond.
WhenyoupushLTRIM,RTRIM,orSOUNDEXtoadatabase,thedatabasetreatstheargument('')asNULL,buttheIntegrationServicetreats
theargument('')asspaces.
AnIBMDB2databaseandtheIntegrationServiceproducedifferentresultsforSTDDEVandVARIANCE.IBMDB2usesadifferent
algorithmthanotherdatabasestocalculateSTDDEVandVARIANCE.
WhenyoupushSYSDATEorSYSTIMESTAMPtothedatabase,thedatabaseserverreturnsthetimestampinthetimezoneofthedatabase
server,nottheIntegrationService.
IfyoupushSYSTIMESTAMPtoanIBMDB2oraSybasedatabase,andyouspecifytheformatforSYSTIMESTAMP,thedatabaseignores
theformatandreturnsthecompletetimestamp.
YoucanpushSYSTIMESTAMP(SS)toaNetezzadatabase,butnotSYSTIMESTAMP(MS)orSYSTIMESTAMP(US).
[Link]

2/51

6/5/2015

[Link]/search/label/INFORMATICA

WhenyoupushTO_CHAR(DATE)orTO_DATE()toNetezza,dateswithsubsecondprecisionmustbeintheYYYYMMDDHH24:MI:
[Link],theIntegrationServicedoesnotpushthefunctiontoNetezza.

PERFORMANCETUNINGOFLOOKUPTRANSFORMATIONS
[Link].
[Link],[Link].
(Meaningofcacheisgiveninpoint2ofthissectionandtheprocedurefordeterminingtheoptimumcachesizeisgivenattheendofthisdocument.)
[Link],[Link]
usingasqloverridewitharestriction.
Cache:[Link]
[Link].
Exampleforcachingbyauserdefinedquery:
Supposeweneedtolookuprecordswhereemployee_id=eno.
employee_idisfromthelookuptable,EMPLOYEE_TABLEandenoisthe
inputthatcomesfromthefromthesourcetable,SUPPORT_TABLE.
WeputthefollowingsqlqueryoverrideinLookupTransform
selectemployee_idfromEMPLOYEE_TABLE
Ifthereare50,000employee_id,thensizeofthelookupcachewillbe50,000.
Insteadoftheabovequery,weputthefollowing:
selectempemployee_idfromEMPLOYEE_TABLEe,SUPPORT_TABLEs
wheree.employee_id=[Link]
Ifthereare1000eno,[Link]
SUPPORT_TABLEisnothuge.Ourconcernistomakethesizeofthecacheaslessaspossible.
[Link],deleteallunusedcolumnsandkeeponlythefieldsthatareusedinthemapping.
[Link],[Link]
transformation.
[Link],thenplaceconditionsthatuseequalityoperator=firstintheconditionsthatappearinthe
conditionstab.
[Link],[Link]
ORDERBYlist.
[Link]:
Sourceissmallandlookuptableislarge.
Iflookupisdoneontheprimarykeyofthelookuptable.
[Link]:
Iflookuptableissmallandsourceislarge.
[Link],[Link]
[Link]

3/51

6/5/2015

[Link]/search/label/INFORMATICA

lookuptable,[Link],cachefileswillbebuiltfrommemory
cacheinsteadoffromthedatabase,whichwillimprovetheperformance.
[Link],thenalsousepersistentcache.
[Link],[Link].
[Link].
[Link],[Link]
maintable.
[Link],thensharethecaches.
15.Ifwearegoingtoreturnonly1row,thenuseunconnectedlookup.
[Link],theloadingof
theselookupscanbespeededup.
[Link](orifwehaveprivilegetoaddindextothetableinthedatabase,doso),thentheperformance
wouldincreasebothforcachedanduncachedlookups.

OptimizingtheBottlenecks
1. Ifthesourceisaflatfile,[Link],thentrynottousesynonymsor
aliases.
2. Ifthesourceisaflatfile,reducethenumberofbytes(Bydefaultitis1024bytesperline)[Link],wecan
decreasetheLineSequentialBufferLengthsettingofthesessionproperties.
3. Ifpossible,giveaconditionalqueryinthesourcequalifiersothattherecordsarefilteredoffassoonaspossibleintheprocess.
4. Inthesourcequalifier,ifthequeryhasORDERBYorGROUPBY,thencreateanindexonthesourcetableandorderbytheindexfieldofthe
sourcetable.
PERFORMANCETUNINGOFTARGETS
Ifthetargetisaflatfile,[Link],thentrynottousesynonymsoraliases.
1. Usebulkloadwheneverpossible.
2. Increasethecommitlevel.
3. Dropconstraintsandindexesofthetablebeforeloading.
PERFORMANCETUNINGOFMAPPINGS
[Link]
loadingprocess.
1. Avoidexecutingmajorsqlqueriesfrommappletsormappings.
[Link]

4/51

6/5/2015

[Link]/search/label/INFORMATICA

2. Useoptimizedquerieswhenweareusingthem.
3. [Link],joiner,filter,aggregatoretcshouldbeusedaslessas
possible.
4. Removealltheunnecessarylinksbetweenthetransformationsfrommapping.
5. Ifasinglemappingcontainsmanytargets,thendividingthemintoseparatemappingscanimproveperformance.
6. Ifweneedtouseasinglesourcemorethanonceinamapping,[Link]
differentdataflowsasrequiredintodifferenttargetsorsametarget.
7. Ifasessionjoinsmanysourcetablesinonesourcequalifier,thenanoptimizingquerywillimproveperformance.
8. InthesqlquerythatInformaticagenerates,[Link]
[Link].
9. Combinethemappingsthatusesamesetofsourcedata.
10. Onamapping,[Link]
onfieldconversions.
11. Insteadofdoingcomplexcalculationinquery,useanexpressiontransformeranddothecalculationinthemapping.
12. Ifdataispassingthroughmultiplestagingareas,removingthestagingareawillincreaseperformance.
13. [Link].
14. Unnecessarydatatypeconversionsshouldbeavoidedsincethedatatypeconversionsimpactperformance.
15. [Link]
significantlylesstimethanwiththetransformations,thenwehavetofinetunethetransformation.
16. Keepdatabaseinteractionsaslessaspossible.
PERFORMANCETUNINGOFSESSIONS
Asessionspecifiesthelocationfromwherethedataistobetaken,[Link]
variouspropertiesthathelpustoscheduleandrunthejobinthewaywewant.
1. Partitionthesession:Thiscreatesmanyconnectionstothesourceandtarget,[Link]
[Link]
[Link]
ofdata.
2. Runthesessionsinparallelratherthanserialtogaintime,iftheyareindependentofeachother.
3. [Link]
[Link],[Link],
stagealldata,precreatetheindex,useatransportabletablespaceandthenloadintodatabase.
4. Usebulkloading,[Link].
5. InasessionwehaveoptionstoTreatrowsasDataDriven,Insert,[Link],thenwehavetokeepitas
[Link],ithastobekeptasInserttoimproveperformance.
6. Increasethedatabasecommitlevel([Link]
[Link]

5/51

6/5/2015

[Link]/search/label/INFORMATICA

besetateveryevery50,000records)
7. Byavoidingbuiltinfunctionsasmuchaspossible,[Link],theoperator||isfasterthanthe
functionCONCAT().Souseoperatorsinsteadoffunctions,wherepossible.ThefunctionslikeIS_SPACES(),IS_NUMBER(),IFF(),
DECODE()[Link].
8. Stringfunctionslikesubstring,ltrim,[Link],usedelimitedstringsincasethesourceflatfilesoruse
varchardatatype.
9. [Link].
10. Localizeallsourceandtargettables,storedprocedures,views,[Link]
downtheperformance.
DATABASEOPTIMISATION
TogainthebestInformaticaperformance,thedatabasetables,storedproceduresandqueriesusedinInformaticashouldbetunedwell.
1. Ifthesourceandtargetareflatfiles,thentheyshouldbepresentinthesysteminwhichtheInformaticaserverispresent.
2. Increasethenetworkpacketsize.
3. TheperformanceoftheInformaticaserverisrelatedtonetworkconnections.Datagenerallymovesacrossanetworkatlessthan1MBper
second,[Link]
networkconnections.
4. Optimizetargetdatabases.

IDENTIFICATIONOFBOTTLENECKS
IDENTIFICATIONOFBOTTLENECKS
PerformanceofInformaticaisdependantontheperformanceofitsseveralcomponentslikedatabase,network,transformations,mappings,sessions
[Link],wehavetoidentifythebottleneckfirst.
Bottleneckmaybepresentinsource,target,transformations,mapping,session,[Link]
componentsintheordersource,target,transformations,[Link],applythetuningmechanismsin
whicheverwaytheyareapplicabletotheproject.
IdentifybottleneckinSource
Ifsourceisarelationaltable,putafiltertransformationinthemapping,[Link]
[Link],withoutthetestfilter,totaltimetakenisasfollows:
TotalTime=timetakenby(source+transformations+targetload)
Nowbecauseoffilter,TotalTime=timetakenbysource
Soifsourcewasfine,theninthelattercase,[Link],thenthereisa
sourcebottleneck.
[Link]

6/51

6/5/2015

[Link]/search/label/INFORMATICA

IdentifybottleneckinTarget
Ifthetargetisarelationaltable,[Link]
thesessiontoloadtotable,thenthetargettableisthebottleneck.
IdentifybottleneckinTransformation
[Link]
takennowissignificantlymorethanprevioustime,thenthetransformationisthebottleneck.
Butremovaloftransformationfortestingcanbeapainforthedevelopersincethatmightrequirefurtherchangesforthesessiontogetintothe
workingmode.
[Link]
thistestfilter,thentransformationisthebottleneck.
Identifybottleneckinsessions
Wecanusethesessionlogtoidentifywhetherthesource,[Link]
summaryrecordslikethefollowing:
MASTER>PETL_24018Thread[READER_1_1_1]createdforthereadstageofpartitionpoint[SQ_test_all_text_data]hascompleted:TotalRun
Time=[11.703201]secs,TotalIdleTime=[9.560945]secs,BusyPercentage=[18.304876].
MASTER>PETL_24019Thread[TRANSF_1_1_1_1]createdforthetransformationstageofpartitionpoint[SQ_test_all_text_data]hascompleted:
TotalRunTime=[11.764368]secs,TotalIdleTime=[0.000000]secs,BusyPercentage=[100.000000].
Ifbusypercentageis100,thenthatpartisthebottleneck.
[Link](Insession
Propertiestab)isenabled,alltheperformancerelatedinformationwouldappearinthelogcreatedbythesession.

PerformanceTuningOverview
PerformanceTuningOverview
[Link],first
identifyaperformancebottleneck,eliminateit,andthenidentifythenextperformancebottleneckuntilyouaresatisfiedwiththesessionperformance.
Youcanusethetestloadoptiontorunsessionswhenyoutunesessionperformance.
Ifyoutuneallthebottlenecks,[Link]
partitionscanimproveperformancebyutilizingmoreofthesystemhardwarewhileprocessingthesession.
Becausedeterminingthebestwaytoimproveperformancecanbecomplex,changeonevariableatatime,andtimethesessionbothbeforeandafter
[Link],youmightwanttoreturntotheoriginalconfiguration.
Completethefollowingtaskstoimprovesessionperformance:
1. [Link].
2. [Link].
3. [Link].
[Link]

7/51

6/5/2015

[Link]/search/label/INFORMATICA

4. [Link].
5. [Link].
6. [Link].
7. [Link].
8. [Link].

TRANSFORMATIONSPART2ININFORMATICA
SQLTRANSFORMATION:
[Link]
[Link].
WhenyoucreateanSQLtransformation,youconfigurethefollowingoptions:
Mode:TheSQLtransformationrunsinoneofthefollowingmodes:
[Link]
[Link].
[Link]
[Link].
[Link]
transformationwhenyoucreatethetransformation.
[Link].
[Link].
ScriptMode
[Link]
[Link].
Whenyouconfigurethetransformationtoruninscriptmode,[Link]
[Link].
RulesandGuidelinesforScriptMode
UsethefollowingrulesandguidelinesforanSQLtransformationthatrunsinscriptmode:
Youcanuseastaticordynamicdatabaseconnectionwithscriptmode.
Toincludemultiplequerystatementsinascript,youcanseparatethemwithasemicolon.
Youcanusemappingvariablesorparametersinthescriptfilename.
[Link]

8/51

6/5/2015

[Link]/search/label/INFORMATICA

[Link].
[Link]
thescript.
[Link]
doesnotoutputmorethanonerowofdataforeachinputrow.
YoucannotusescriptinglanguagessuchasOraclePL/SQLorMicrosoft/SybaseTSQLinthescript.
YoucannotusenestedscriptswheretheSQLscriptcallsanotherSQLscript.
Ascriptcannotacceptruntimearguments.
QueryMode
WhenyouconfiguretheSQLtransformationtoruninquerymode,youcreateanactivetransformation.
WhenanSQLtransformationrunsinquerymode,itexecutesanSQLquerythatyoudefineinthetransformation.
Youpassstringsorparameterstothequeryfromthetransformationinputportstochangethequerystatementorthequerydata.
YoucancreatethefollowingtypesofSQLqueriesintheSQLtransformation:
[Link],[Link]
thequeryonceandrunsthequeryforallinputrows.
[Link].
RulesandGuidelinesforQueryMode
UsethefollowingrulesandguidelineswhenyouconfiguretheSQLtransformationtoruninquerymode:
ThenumberandtheorderoftheoutputportsmustmatchthenumberandorderofthefieldsinthequerySELECTclause.
[Link]
IntegrationServicegeneratesarowerrorwhenthedatatypesdonotmatch.
WhentheSQLquerycontainsanINSERT,UPDATE,orDELETEclause,thetransformationreturnsdatatotheSQLErrorport,thepass
throughports,[Link].
WhentheSQLquerycontainsaSELECTstatementandthetransformationhasapassthroughport,thetransformationreturnsdatatothepass
[Link].
Youcannotaddthe"_output"suffixtooutputportnamesthatyoucreate.
YoucannotusethepassthroughporttoreturndatafromaSELECTquery.
WhenthenumberofoutputportsismorethanthenumberofcolumnsintheSELECTclause,theextraportsreceiveaNULLvalue.
WhenthenumberofoutputportsislessthanthenumberofcolumnsintheSELECTclause,theIntegrationServicegeneratesarowerror.
[Link],theinputportsmustbestringdatatypes.
SQLTransformationProperties
[Link]

9/51

6/5/2015

[Link]/search/label/INFORMATICA

AfteryoucreatetheSQLtransformation,youcandefineportsandsetattributesinthefollowingtransformationtabs:
[Link].
[Link].
[Link].
[Link].
Note:[Link],theydisplayonthePortstab.
PropertiesTab
[Link]
arenotconfigurable.
ThefollowingtabledescribestheSQLtransformationproperties:
Property

Description

RunTimeLocation

EnterapathrelativetotheIntegrationServicenodethatrunstheSQL
transformationsession.
Ifthispropertyisblank,theIntegrationServiceusestheenvironment
variabledefinedontheIntegrationServicenodetolocatetheDLLorshared
library.
YoumustcopyallDLLsorsharedlibrariestotheruntimelocationorto
[Link]
IntegrationServicefailstoloadtheprocedurewhenitcannotlocatethe
DLL,sharedlibrary,orareferencedfile.
Setstheamountofdetailincludedinthesessionlogwhenyourunasession
[Link]
tracingleveltoVerboseData,theIntegrationServicewriteseachSQL
queryitpreparestothesessionlog.

TracingLevel

IsPartitionable

[Link]
followingoptions:
[Link]
[Link]
mightchooseNoifthetransformationprocessesalltheinputdatatogether,
suchasdatacleansing.
[Link],buttheIntegrationService
[Link]
whendifferentpartitionsofthetransformationmustshareobjectsin

[Link]

10/51

6/5/2015

UpdateStrategy

TransformationScope

OutputisRepeatable

GenerateTransaction

RequiresSingle
ThreadPerPartition
OutputisDeterministic

[Link]/search/label/INFORMATICA

memory.
[Link],andtheIntegration
Servicecandistributeeachpartitiontodifferentnodes.
DefaultisNo.
[Link]
enablethispropertyforquerymodeSQLtransformations.
Defaultisdisabled.
ThemethodinwhichtheIntegrationServiceappliesthetransformation
[Link]:
Row
Transaction
AllInput
Settransactionscopetotransactionwhenyouusetransactioncontrolin
staticquerymode.
[Link]
querymodetransformations.
Indicatesiftheorderoftheoutputdataisconsistentbetweensessionruns.
[Link].
[Link]
whentheinputdataorderisconsistentbetweensessionruns.
[Link]
eveniftheorderoftheinputdataisinconsistentbetweensessionruns.
DefaultisNever.
[Link]
querymodeSQLtransformationsthatcommitdatainanSQLquery.
Defaultisdisabled.
IndicatesiftheIntegrationServiceprocesseseachpartitionofaprocedure
withonethread.
Thetransformationgenerateconsistentoutputdatabetweensessionruns.
Enablethispropertytoperformrecoveryonsessionsthatusethis
transformation.
Defaultisenabled.

CreateMapping:
Step1:Creatingaflatfileandimportingthesourcefromtheflatfile.
[Link]

11/51

6/5/2015

[Link]/search/label/INFORMATICA

CreateaNotepadandinitcreateatablebynamebikeswiththreecolumnsandthreerecordsinit.
[Link](C:\[Link])andsaveit.
Importthesource(secondnotepad)usingthesource>[Link]
windowsandfollowtheonscreeninstructionstocompletetheprocessofimportingthesource.

Step2:Importingthetargetandapplyingthetransformation.
Inthesamewayasspecifiedabovegotothetargets>importfromfileandselectanemptynotepadunderthenametargetforbikes(thisisonemore
blanknotepadwhichweshouldcreateandsaveundertheabovespecifiednameintheC:\).
Createtwocolumnsinthetargettableunderthenamereportanderror.
[Link].
InthefirstwindowwhenyouapplytheSQLtransformationweshouldselectthescriptmode.
ConnecttheSQtotheScriptNameunderinputsandconnecttheothertwofieldstotheoutputcorrespondingly.
Snapshotfortheabovediscussedthingsisgivenbelow.

[Link]

12/51

6/5/2015

[Link]/search/label/INFORMATICA

Step3:Designtheworkflowandrunit.
Createthetaskandtheworkflowusingthenamingconventions.
GotothemappingstabandclickontheSourceonthelefthandpanetospecifythepathfortheoutputfile.

Step4:Previewtheoutputdataonthetargettable.
================================================================
NORMALIZERTRANSFORMATION:
ActiveandConnectedTransformation.
TheNormalizertransformationnormalizesrecordsfromCOBOLandrelationalsources,allowingustoorganizethedata.
UseaNormalizertransformationinsteadoftheSourceQualifiertransformationwhenwenormalizeaCOBOLsource.
[Link]

13/51

6/5/2015

[Link]/search/label/INFORMATICA

WecanalsousetheNormalizertransformationwithrelationalsourcestocreatemultiplerowsfromasinglerowofdata.
Example1:Tocreate4recordsofeveryemployeeinEMPtable.
EMPwillbesourcetable.
CreatetargettableNormalizer_Multiple_Records.StructuresameasEMPanddatatypeofHIREDATEasVARCHAR2.
Createshortcutsasnecessary.
CreatingMapping:
1. Openfolderwherewewanttocreatethemapping.
2. ClickTools>MappingDesigner.
3. ClickMapping>Create>[Link]:m_Normalizer_Multiple_Records
4. DragEMPandTargettable.
5. Transformation>Create>SelectExpression>Givename,Clickcreate,done.
6. PassallportsfromSQ_EMPtoExpressiontransformation.
7. Transformation>Create>SelectNormalizer>Givename,create&done.
8. [Link].
9. [Link].
10. [Link]/f.Createoutputportout_hdateanddothe
conversion.
11. ConnectportsfromExpressiontoNormalizer.
12. EditNormalizerandNormalizerTab.AsEMPNOidentifiessourcerecordsandwewant4recordsofeveryemployee,giveOCCURfor
EMPNOas4.

13.
14. ClickApplyandthenOK.
15. Addlinkasshowninmappingbelow:
16. Mapping>Validate
17. Repository>Save
Makesessionandworkflow.
Giveconnectioninformationforsourceandtargettable.
Runworkflowandseeresult.
[Link]

14/51

6/5/2015

[Link]/search/label/INFORMATICA

Example2:Tobreakrowsintocolumns
Source:
Roll_Number Name
ENG
HINDI

MATHS

100

Amit

78

76

90

101
102

Rahul
Jessie

76
65

78
98

87
79

Target:
Roll_Number

Name

Marks

100
100

Amit
Amit

78
76

100
101

Amit
Rahul

90
76

101
101

Rahul
Rahul

78
87

102
102

Jessie
Jessie

65
98

102

Jessie

79

[Link].
[Link],createonly3portsRoll_Number,NameandMarksasthereare3columnsintargettable.
Alsoaswehave3marksinsource,giveOccursas3forMarksinNormalizertab.
Connectaccordinglyandconnecttotarget.
ValidateandSave
[Link]
tabofsession.
Seetheresult.
====================================================================
SEQUENCEGENERATORTRANSFORMATION:
[Link]

15/51

6/5/2015

[Link]/search/label/INFORMATICA

PassiveandConnectedTransformation.
TheSequenceGeneratortransformationgeneratesnumericvalues.
UsetheSequenceGeneratortocreateuniqueprimarykeyvalues,replacemissingprimarykeys,orcyclethroughasequentialrangeofnumbers.
[Link],thenweneedakeyotherthanPrimaryKeyto
uniquelyidentifytherecord.SowecreateaSequence1,2,3,[Link]:IfEMPNOisthekey,wecan
[Link].
SequenceGeneratorPorts:
TheSequenceGeneratortransformationprovidestwooutputports:NEXTVALandCURRVAL.
Wecannoteditordeletetheseports.
Likewise,wecannotaddportstothetransformation.
NEXTVAL:
UsetheNEXTVALporttogeneratesequencenumbersbyconnectingittoaTransformationortarget.
Forexample,wemightconnectNEXTVALtotwotargettablesinamappingtogenerateuniqueprimarykeyvalues.

SequenceinTable1willbegeneratedfirst.Whentable1hasbeenloaded,onlythenSequencefortable2willbegenerated.
CURRVAL:
CURRVALisNEXTVALplustheIncrementByvalue.
WetypicallyonlyconnecttheCURRVALportwhentheNEXTVALportisAlreadyconnectedtoadownstreamtransformation.
IfweconnecttheCURRVALportwithoutconnectingtheNEXTVALport,theIntegrationServicepassesaconstantvalueforeachrow.
whenweconnecttheCURRVALportinaSequenceGeneratorTransformation,theIntegrationServiceprocessesonerowineachblock.
WecanoptimizeperformancebyconnectingonlytheNEXTVALportinaMapping.
[Link]

16/51

6/5/2015

[Link]/search/label/INFORMATICA

Example:TouseSequenceGeneratortransformation
EMPwillbesource.
CreateatargetEMP_SEQ_GEN_EXAMPLEinsharedfolder.StructuresameasEMP.AddtwomoreportsNEXT_VALUEand
CURR_VALUEtothetargettable.
Createshortcutsasneeded.
CreatingMapping:
[Link].
[Link]>MappingDesigner.
[Link]>Create>[Link]:m_seq_gen_example
[Link].
5.ConnectallportsfromSQ_EMPtotargettable.
[Link]>Create>SelectSequenceGeneratorforlist>Create>Done
7.ConnectNEXT_VALandCURR_VALfromSequenceGeneratortotarget.
[Link]
[Link]>Save
CreateSessionandthenworkflow.
Giveconnectioninformationforalltables.
Runworkflowandseetheresultintable.
[Link]

17/51

6/5/2015

[Link]/search/label/INFORMATICA

SequenceGeneratorProperties:
Setting
Required/Optional Description
StartValue
Required
StartvalueofthegeneratedsequencethatwewantIStouseifwe
useCycleoption.Defaultis0.
IncrementBy
Required
DifferencebetweentwoconsecutivevaluesfromtheNEXTVAL
port.
EndValue
Optional
MaximumvaluetheIntegrationServicegenerates.
CurrentValue
Optional
[Link],thevaluemustbe
greaterthanorequaltothestartvalueandlesstheendvalue.
Cycle

Optional

Reset

Optional

Ifselected,theIntegrationServicecyclesthroughthesequence
[Link]:StartValue:1EndValue10Sequencewillbefrom110
andagainstartfrom1.
Bydefault,lastvalueofsequenceduringsessionissavedto
[Link]
saved.
Ifselected,theIntegrationServicegeneratesvaluesbasedonthe
originalcurrentvalueforeachsession.

PointstoPonder:
IfCurrentvalueis1andendvalue10,[Link].
IfweconnectjustCURR_VALonly,thevaluewillbesameforallrecords.
IfCurrentvalueis1andendvalue10,cycleoptionthere.Startvalueis0.
[Link].0123
Tomakeabovesequenceas110120,[Link].
IfCurrentvalueis1andendvalue10,cycleoptionthere.Startvalueis1.
[Link],sequencewillstartfrom8.
UseresetoptionifyouwanttostartsequencefromCURR_VALeverytime.
=====================================================================
AGGREGATORTRANSFORMATION:
[Link]

18/51

6/5/2015

[Link]/search/label/INFORMATICA

ConnectedandActiveTransformation
TheAggregatortransformationallowsustoperformaggregatecalculations,suchasaveragesandsums.
Aggregatortransformationallowsustoperformcalculationsongroups.
ComponentsoftheAggregatorTransformation
1. Aggregateexpression
2. Groupbyport
3. SortedInput
4. Aggregatecache
1)AggregateExpressions
Enteredinanoutputport.
Canincludenonaggregateexpressionsandconditionalclauses.
Thetransformationlanguageincludesthefollowingaggregatefunctions:
AVG,COUNT,MAX,MIN,SUM
FIRST,LAST
MEDIAN,PERCENTILE,STDDEV,VARIANCE
SingleLevelAggregateFunction:MAX(SAL)
NestedAggregateFunction:MAX(COUNT(ITEM))
NestedAggregateFunctions
InAggregatortransformation,therecanbemultiplesinglelevelfunctionsormultiplenestedfunctions.
AnAggregatortransformationcannothavebothtypesoffunctionstogether.
MAX(COUNT(ITEM))iscorrect.
MIN(MAX(COUNT(ITEM)))[Link]
ConditionalClauses
[Link]
clausethatevaluatestoTRUEorFALSE.
SUM(COMMISSION,COMMISSION>QUOTA)
[Link]

19/51

6/5/2015

[Link]/search/label/INFORMATICA

NonAggregateFunctions
Wecanalsousenonaggregatefunctionsintheaggregateexpression.
IIF(MAX(QUANTITY)>0,MAX(QUANTITY),0))
2)GroupByPorts
Indicateshowtocreategroups.
Whengroupingdata,theAggregatortransformationoutputsthelastrowofeachgroupunlessotherwisespecified.
TheAggregatortransformationallowsustodefinegroupsforaggregations,ratherthanperformingtheaggregationacrossallinputdata.
Forexample,wecanfindMaximumSalaryforeveryDepartment.
InAggregatorTransformation,OpenPortstabandselectGroupByasneeded.
3)UsingSortedInput
Usetoimprovesessionperformance.
Tousesortedinput,wemustpassdatatotheAggregatortransformationsortedbygroupbyport,inascendingordescendingorder.
Whenweusethisoption,wetellAggregatorthatdatacomingtoitisalreadysorted.
WechecktheSortedInputOptioninPropertiesTabofthetransformation.
Iftheoptionischeckedbutwearenotpassingsorteddatatothetransformation,thenthesessionfails.
4)AggregatorCaches
ThePowerCenterServerstoresdataintheaggregatecacheuntilitcompletesAggregatecalculations.
[Link],itstoresoverflow
valuesincachefiles.
Note:[Link]
needtoconfigurecachememoryforAggregatortransformationsthatusesortedports.
1)AggregatorIndexCache:
[Link],thenthiscachestoresvalues10,20,30etc.
[Link]
2)AggregatorDataCache:
DATACACHEisgenerallylargerthantheAGGREGATORINDEXCACHE.
[Link]

20/51

6/5/2015

[Link]/search/label/INFORMATICA

ColumnsinDataCache:
Variableportsifany
Nongroupbyinput/outputports.
Nongroupbyinputportsusedinnonaggregateoutputexpression.
Portcontainingaggregatefunction

1)Example:TocalculateMAX,MIN,AVGandSUMofsalaryofEMPtable.
EMPwillbesourcetable.
CreateatargettableEMP_AGG_EXAMPLEintargetdesigner.TableshouldcontainDEPTNO,MAX_SAL,MIN_SAL,AVG_SALand
SUM_SAL
Createtheshortcutsinyourfolder.
CreatingMapping:
[Link].
[Link]>MappingDesigner.
[Link]

21/51

6/5/2015

[Link]/search/label/INFORMATICA

[Link]>Create>[Link]:m_agg_example
[Link].
[Link]>Create>[Link].
6.PassSALandDEPTNOonlyfromSQ_EMPtoAGGREGATORTransformation.
[Link]
8.Create4outputports:OUT_MAX_SAL,OUT_MIN_SAL,OUT_AVG_SAL,
OUT_SUM_SAL
[Link]
[Link]:MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL)
[Link]>Ok.
[Link].
[Link].
[Link]>Validate
[Link]>Save
[Link].
Makesuretogiveconnectioninformationforalltables.
==================================================================
UNIONTRANSFORMATION:
ActiveandConnectedtransformation.
Uniontransformationisamultipleinputgrouptransformationthatyoucanusetomergedatafrommultiplepipelinesorpipelinebranchesintoone
[Link]
statements.
UnionTransformationRulesandGuidelines
wecancreatemultipleinputgroups,butonlyoneoutputgroup.
wecanconnectheterogeneoussourcestoaUniontransformation.
[Link],datatype,andscalemustbeidenticalacrossallgroups.
[Link],wemustaddanothertransformationsuchasaRouteror
FilterTransformation.
wecannotuseaSequenceGeneratororUpdateStrategytransformationupstreamfromaUniontransformation.
UnionTransformationComponents
WhenweconfigureaUniontransformation,definethefollowingcomponents:
Transformationtab:Wecanrenamethetransformationandaddadescription.
[Link]

22/51

6/5/2015

[Link]/search/label/INFORMATICA

Propertiestab:Wecanspecifythetracinglevel.
Groupstab:[Link].
GroupPortstab:[Link].
WecannotmodifythePorts,InitializationProperties,MetadataExtensions,orPortAttributeDefinitionstabsinaUniontransformation.
CreateinputgroupsontheGroupstab,[Link]
[Link].
Example:tocombinedataoftablesEMP_10,EMP_20andEMP_REST
ImporttablesEMP_10,EMP_20andEMP_RESTinsharedfolderinSources.
CreateatargettableEMP_UNION_EXAMPLEintargetdesigner.StructureshouldbesameEMPtable.
Createtheshortcutsinyourfolder.

CreatingMapping:
[Link]

23/51

6/5/2015

[Link]/search/label/INFORMATICA

1. Openfolderwherewewanttocreatethemapping.
2. ClickTools>MappingDesigner.
3. ClickMapping>Create>[Link]:m_union_example
4. DragEMP_10,EMP_20andEMP_RESTfromsourceinmapping.
5. ClickTransformation>Create>[Link].
6. PassportsfromSQ_EMP_10toUnionTransformation.
7. [Link]
8. OnegroupwillbealreadythereaswedraggedportsfromSQ_DEPT_10toUnionTransformation.
9. Aswehave3sourcetables,[Link]
10. Wecanalsomodifyportsinportstab.
11. ClickApply>Ok.
12. Dragtargettablenow.
13. ConnecttheoutputportsfromUniontotargettable.
14. ClickMapping>Validate
15. Repository>Save
[Link].
Makesuretogiveconnectioninformationforall3sourceTables.

[Link]

24/51

6/5/2015

[Link]/search/label/INFORMATICA

Samplemappingpicture
=======================================================================
JOINERTRANSFORMATION:
ConnectedandActiveTransformation
[Link],wecanjoindatafromthe
samesource.
Ifweneedtojoin3tables,thenweneed2JoinerTransformations.
[Link]
morepairsofPortsbetweenthetwosources.
Example:TojoinEMPandDEPTtables.
[Link]

25/51

6/5/2015

[Link]/search/label/INFORMATICA

EMPandDEPTwillbesourcetable.
CreateatargettableJOINER_EXAMPLEintargetdesigner.TableshouldContainallportsofEMPtableplusDNAMEandLOCasshown
below.
Createtheshortcutsinyourfolder.
CreatingMapping:
1. Openfolderwherewewanttocreatethemapping.
2. ClickTools>MappingDesigner.
3. ClickMapping>Create>[Link]:m_joiner_example
4. DragEMP,DEPT,[Link].

[Link].
[Link].
[Link]>Validate
[Link]>Save.
[Link].
Makesuretogiveconnectioninformationforalltables.
[Link]

26/51

6/5/2015

[Link]/search/label/INFORMATICA

JOINCONDITION:
ThejoinconditioncontainsportsfrombothinputsourcesthatmustmatchforthePowerCenterServertojointworows.
Example:DEPTNO=DEPTNO1inabove.
1. EditJoinerTransformation>ConditionTab
2. Addcondition
Wecanaddasmanyconditionsasneeded.
Only=operatorisallowed.
IfwejoinCharandVarchardatatypes,[Link]
following:
Char(40)=abcdandVarchar(40)=abcd
ThentheCharvalueisabcdpaddedwith36blankspaces,andthePowerCenterServerdoesnotjointhetwofieldsbecausetheCharfieldcontains
trailingspaces.
Note:TheJoinertransformationdoesnotmatchnullvalues.
MASTERandDETAILTABLES
InJoiner,onetableiscalledasMASTERandotherasDETAIL.
[Link].
EditJoinerTransformation>PortsTab>SelectMforMastertable.
TablewithlessnumberofrowsshouldbemadeMASTERtoimprovePerformance.
Reason:
WhenthePowerCenterServerprocessesaJoinertransformation,itreadsrowsfrombothsourcesconcurrentlyandbuildstheindexanddata
[Link].
Thefeweruniquerowsinthemaster,thefeweriterationsofthejoincomparisonoccur,whichspeedsthejoinprocess.
JOINERTRANSFORMATIONPROPERTIESTAB
CaseSensitiveStringComparison:Ifselected,thePowerCenterServerusescasesensitivestringcomparisonswhenperformingjoinson
stringcolumns.
CacheDirectory:Specifiesthedirectoryusedtocachemasterordetailrowsandtheindextotheserows.
JoinType:Specifiesthetypeofjoin:Normal,MasterOuter,DetailOuter,orFullOuter.
TracingLevel
JoinerDataCacheSize
[Link]

27/51

6/5/2015

[Link]/search/label/INFORMATICA

JoinerIndexCacheSize
SortedInput
JOINTYPES
InSQL,[Link]
manner,exceptthattablescanoriginatefromdifferentdatabasesorflatfiles.
TypesofJoins:
Normal
MasterOuter
DetailOuter
FullOuter
Note:Anormalormasterouterjoinperformsfasterthanafullouterordetailouterjoin.
Example:InEMP,wehaveemployeeswithDEPTNO10,20,[Link],wehaveDEPTNO10,20,[Link]
tableasithaslessrows.
NormalJoin:
Withanormaljoin,thePowerCenterServerdiscardsallrowsofdatafromthemasteranddetailsourcethatdonotmatch,basedonthecondition.
Allemployeesof10,20and30willbethereasonlytheyarematching.
MasterOuterJoin:
[Link]
source.
Alldataofemployeesof10,20and30willbethere.
TherewillbeemployeesofDEPTNO50andcorrespondingDNAMEandLOCColumnswillbeNULL.
DetailOuterJoin:
[Link]
source.
Allemployeesof10,20and30willbethere.
TherewillbeonerecordforDEPTNO40andcorrespondingdataofEMPcolumnswillbeNULL.
FullOuterJoin:
Afullouterjoinkeepsallrowsofdatafromboththemasteranddetailsources.
Alldataofemployeesof10,20and30willbethere.
[Link]

28/51

6/5/2015

[Link]/search/label/INFORMATICA

TherewillbeemployeesofDEPTNO50andcorrespondingDNAMEandLOCColumnswillbeNULL.
TherewillbeonerecordforDEPTNO40andcorrespondingdataofEMPColumnswillbeNULL.
USINGSORTEDINPUT
Usetoimprovesessionperformance.
tousesortedinput,wemustpassdatatotheJoinertransformationsortedbytheportsthatareusedinJoinCondition.
WechecktheSortedInputOptioninPropertiesTabofthetransformation.
IftheoptionischeckedbutwearenotpassingsorteddatatotheTransformation,thenthesessionfails.
WecanuseSORTERtosortdataorSourceQualifierincaseofRelationaltables.
JOINERCACHES
[Link].
1)JoinerIndexCache:
AllColumnsofMASTERtableusedinJoinconditionareinJOINERINDEXCACHE.
Example:DEPTNOinourmapping.
2)JoinerDataCache:
MastercolumnnotinjoinconditionandusedforoutputtoothertransformationortargettableareinDataCache.
Example:DNAMEandLOCinourmappingexample.
PerformanceTuning:
Performjoinsinadatabasewhenpossible.
Joinsorteddatawhenpossible.
ForasortedJoinertransformation,designateasthemastersourcethesourcewithfewerduplicatekeyvalues.
Joinercan'tbeusedinfollowingconditions:
1. EitherinputpipelinecontainsanUpdateStrategytransformation.
2. WeconnectaSequenceGeneratortransformationdirectlybeforetheJoinertransformation.
=====================================================================
UpdateStraegyTRANSFORMATION:
ActiveandConnectedTransformation
[Link]

29/51

6/5/2015

[Link]/search/label/INFORMATICA

Tillnow,[Link],deleteorrejectrowscomingfromsourcebasedonsome
condition?
Example:IfAddressofaCUSTOMERchanges,[Link]
[Link].
[Link],wecreateaLookupontargettabletodeterminewhetherarowalreadyexistsornot.
Thenweinsert,update,deleteorrejectthesourcerecordasperbusinessneed.
InPowerCenter,wesettheupdatestrategyattwodifferentlevels:
1. Withinasession
2. WithinaMapping
[Link]:
Whenweconfigureasession,wecaninstructtheIStoeithertreatallrowsinthesamewayoruseinstructionscodedintothesessionmappingtoflag
rowsfordifferentdatabaseoperations.
SessionConfiguration:
EditSession>Properties>TreatSourceRowsas:(Insert,Update,Delete,andDataDriven).[Link]
TargetTables:

[Link]

30/51

6/5/2015

[Link]/search/label/INFORMATICA

Youcansetthefollowingupdatestrategyoptions:
Insert:Selectthisoptiontoinsertarowintoatargettable.
Delete:Selectthisoptiontodeletearowfromatable.
Update:Wehavethefollowingoptionsinthissituation:
[Link].
[Link].
[Link],insertit.
Truncatetable:Selectthisoptiontotruncatethetargettablebeforeloadingdata.
[Link]
Withinamapping,weusetheUpdateStrategytransformationtoflagrowsforinsert,delete,update,orreject.
Operation
Constant
Numeric
Value
INSERT
DD_INSERT
0
[Link]

31/51

6/5/2015

[Link]/search/label/INFORMATICA

UPDATE
DD_UPDATE
1
DELETE
DD_DELETE
2
REJECT
DD_REJECT
3
UpdateStrategyExpressions:
Frequently,theupdatestrategyexpressionusestheIIForDECODEfunctionfromthetransformationlanguagetotesteachrowtoseeifitmeetsa
particularcondition.
IIF((ENTRY_DATE>APPLY_DATE),DD_REJECT,DD_UPDATE)
Or
IIF((ENTRY_DATE>APPLY_DATE),3,2)
TheaboveexpressioniswritteninPropertiesTabofUpdateStrategyT/f.
DDmeansDATADRIVEN
ForwardingRejectedRows:
WecanconfiguretheUpdateStrategytransformationtoeitherpassrejectedrowstothenexttransformationordropthem.
Steps:
1. CreateUpdateStrategyTransformation
2. Passallportsneededtoit.
3. SettheExpressioninPropertiesTab.
4. Connecttoothertransformationsortarget.
Performancetuning:
1. UseUpdateStrategytransformationaslessaspossibleinthemapping.
2. Donotuseupdatestrategytransformationifwejustwanttoinsertintotargettable,insteadusedirectmapping,directfilteringetc.
3. ForupdatingordeletingrowsfromthetargettablewecanuseUpdateStrategytransformationitself.
==================================================================
LookupTRANSFORMATION:
PassiveTransformation
[Link].
UseaLookuptransformationinamappingtolookupdatainaflatfileorarelationaltable,view,orsynonym.
WecanimportalookupdefinitionfromanyflatfileorrelationaldatabasetowhichboththePowerCenterClientandServercanconnect.
WecanusemultipleLookuptransformationsinamapping.
[Link]

32/51

6/5/2015

[Link]/search/label/INFORMATICA

[Link]
[Link].
WecanusetheLookuptransformationtoperformfollowing:
Getarelatedvalue:[Link]
Condition.
Performacalculation:WewantonlythoseEmployeeswhosSAL>Average(SAL).WewillwriteLookupOverridequery.
Updateslowlychangingdimensiontables:[Link]
inthetarget.
[Link]
WecanconfiguretheLookuptransformationtoperformthefollowingtypesoflookups:
ConnectedorUnconnected
RelationalorFlatFile
CachedorUncached
RelationalLookup:
WhenwecreateaLookuptransformationusingarelationaltableasalookupsource,wecanconnecttothelookupsourceusingODBCandimportthe
tabledefinitionasthestructurefortheLookuptransformation.
WecanoverridethedefaultSQLstatementifwewanttoaddaWHEREclauseorquerymultipletables.
Wecanuseadynamiclookupcachewithrelationallookups.
FlatFileLookup:
Whenweuseaflatfileforalookupsource,wecanuseanyflatfiledefinitionintherepository,[Link]
source,theDesignerinvokestheFlatFileWizard.
CachedorUncachedLookup:
[Link],lookupiscached.
ConnectedandUnconnectedLookup
ConnectedLookup
UnconnectedLookup
Receivesinputvaluesdirectlyfromthepipeline.
Receivesinputvaluesfromtheresultofa:LKP
expressioninanothertransformation.
Wecanuseadynamicorstaticcache.
Wecanuseastaticcache.
Cacheincludesalllookupcolumnsusedinthe
mapping.
[Link]

Cacheincludesalllookup/outputportsinthelookup
conditionandthelookup/returnport.
33/51

6/5/2015

[Link]/search/label/INFORMATICA

Ifthereisnomatchforthelookupcondition,the
PowerCenterServerreturnsthedefaultvalueforall
outputports.
Ifthereisamatchforthelookupcondition,the
PowerCenterServerreturnstheresultofthelookup
conditionforalllookup/outputports.
Passmultipleoutputvaluestoanother
transformation.
Supportsuserdefineddefaultvalues

Ifthereisnomatchforthelookupcondition,the
PowerCenterServerreturnsNULL.
Ifthereisamatchforthelookupcondition,the
PowerCenterServerreturnstheresultofthelookup
conditionintothereturnport.
Passoneoutputvaluetoanothertransformation.
Doesnotsupportuserdefineddefaultvalues.

[Link]/FCOMPONENTS
DefinethefollowingcomponentswhenweconfigureaLookuptransformationinamapping:
Lookupsource
Ports
Properties
Condition
[Link]

34/51

6/5/2015

[Link]/search/label/INFORMATICA

[Link]:
[Link]/f,wecanimportthelookupsourcefromthefollowing
locations:
Anyrelationalsourceortargetdefinitionintherepository
Anyflatfilesourceortargetdefinitionintherepository
AnytableorfilethatboththePowerCenterServerandClientmachinecanconnecttoThelookuptablecanbeasingletable,orwecanjoin
multipletablesinthesamedatabaseusingalookupSQLoverrideinPropertiesTab.
[Link]:
Ports
I
O
L

LookupType Number
Needed

Description

Connected
Unconnected
Connected
Unconnected
Connected
Unconnected

Minimum1

[Link]
areInputports.
PortsgoingtoanothertransformationfromLookup.

Unconnected

1Only

Minimum1
Minimum1

[Link]
Options
LookupType
LookupSQLOverride Relational
LookupTableName

Relational

LookupCaching
Enabled
LookupPolicyon
MultipleMatch

FlatFile,Relational
FlatFile,Relational

[Link]

[Link]
columninthelookupsourceasalookup(L)andoutput
port(O).
[Link]/fonly.

Description
OverridesthedefaultSQLstatementtoquerythelookup
table.
Specifiesthenameofthetablefromwhichthe
transformationlooksupandcachesvalues.
IndicateswhetherthePowerCenterServercacheslookup
valuesduringthesession.
DetermineswhathappenswhentheLookuptransformation
findsmultiplerowsthatmatchthelookupcondition.
Options:UseFirstValueorUseLastValueorUseAny
ValueorReportError
35/51

6/5/2015

[Link]/search/label/INFORMATICA

LookupCondition
Connection
Information
SourceType

FlatFile,Relational
Relational

DisplaysthelookupconditionyousetintheConditiontab.
Specifiesthedatabasecontainingthelookuptable.

FlatFile,Relational

Lookupisfromadatabaseorflatfile.

LookupCache
DirectoryName
LookupCache
Persistent
DynamicLookup
Cache
RecacheFrom
LookupSource
InsertElseUpdate

FlatFile,Relational

Locationwherecacheisbuild.

FlatFile,Relational

WhethertousePersistentCacheornot.

FlatFile,Relational

WhethertouseDynamicCacheornot.

FlatFile,Relational

LookupDataCache
Size

FlatFile,Relational

Torebuildcacheifcachesourcechangesandweareusing
PersistentCache.
[Link]
enteringtheLookuptransformationwiththerowtypeof
insert.
DataCacheSize

LookupIndexCache
Size
CacheFileName
Prefix

FlatFile,Relational

IndexCacheSize

FlatFile,Relational

[Link]
nameprefixtousewithpersistentlookupcachefiles.

Relational

SomeotherpropertiesforFlatFilesare:
DatetimeFormat
ThousandSeparator
DecimalSeparator
CaseSensitiveStringComparison
NullOrdering
SortedInput
4:ConditionTab
[Link]
[Link]

36/51

6/5/2015

[Link]/search/label/INFORMATICA

withvaluesinthelookupsourceorcache,representedbylookupports.
Thedatatypesinaconditionmustmatch.
Whenweentermultipleconditions,thePowerCenterServerevaluateseachconditionasanAND,notanOR.
ThePowerCenterServermatchesnullvalues.
Theinputvaluemustmeetallconditionsforthelookuptoreturnavalue.
=,>,<,>=,<=,!=Operatorscanbeused.
Example:IN_DEPTNO=DEPTNO
In_DNAME='DELHI'
Tip:Ifweincludemorethanonelookupcondition,placetheconditionswithanequalsignfirsttooptimizelookupperformance.
Note:
[Link]=operatorincaseofDynamicCache.
[Link].

[Link]
Example:TocreateaconnectedLookupTransformation
[Link].
CreateatargettableCONN_Lookup_EXAMPLEintargetdesigner.TableshouldcontainallportsofEMPtableplusDNAMEandLOCas
shownbelow.
Createtheshortcutsinyourfolder.
CreatingMapping:
[Link].
[Link]>MappingDesigner.
[Link]>Create>[Link]:m_CONN_LOOKUP_EXAMPLE
[Link].
5.ConnectallfieldsfromSQ_EMPtotargetexceptDNAMEandLOC.
[Link]>Create>[Link]
[Link]

37/51

6/5/2015

[Link]/search/label/INFORMATICA

Create.
[Link].
[Link],clickSourceandthenSelectDEPT.
[Link].

10.NowPassDEPTNOfromSQ_EMPtothisLookup.DEPTNOfromSQ_EMPwillbenamedasDEPTNO1.EditLookupandrenameitto
IN_DEPTNOinportstab.
[Link].
DEPTNO=IN_DEPTNOandClickApplyandthenOK.
Linkthemappingasshownbelow:
12.WearenotpassingIN_DEPTNOandDEPTNOtoanyothertransformationfromLOOKUPwecaneditthelookuptransformationandremove
theOUTPUTcheckfromthem.
[Link]>Validate
[Link]>Save
[Link].
Makesuretogiveconnectioninformationforalltables.
MakesuretogiveconnectionforLOOKUPTablealso.
[Link].
[Link]

38/51

6/5/2015

[Link]/search/label/INFORMATICA

SEEPROPERTYTABFORADVANCEDSETTINGS

[Link]
[Link]:LKPreferencequalifierto
callthelookupwithinanothertransformation.
StepstoconfigureUnconnectedLookup:
1. Addinputports.
2. Addthelookupcondition.
3. Designateareturnvalue.
4. Callthelookupfromanothertransformation.
Example:TocreateaunconnectedLookupTransformation
[Link].
CreateatargettableUNCONN_Lookup_EXAMPLEintargetdesigner.TableshouldcontainallportsofEMPtableplusDNAMEasshown
below.
Createtheshortcutsinyourfolder.
CreatingMapping:
[Link].
[Link]>MappingDesigner.
[Link]>Create>[Link]:m_UNCONN_LOOKUP_EXAMPLE
[Link]

39/51

6/5/2015

[Link]/search/label/INFORMATICA

[Link].
[Link]>Create>[Link]
[Link].
6.PassallportsfromSQ_EMPtoEXPRESSIONtransformation.
[Link].
[Link]>Create>[Link]
Create.
[Link].
[Link].
[Link].
[Link],createaportIN_DEPTNO
[Link].
[Link].

[Link]

40/51

6/5/2015

[Link]/search/label/INFORMATICA

[Link].
DEPTNO=IN_DEPTNOandClickApplyandthenOK.
[Link].
[Link]/fandcreateanewoutputportout_DNAMEofdatatypeasDNAME.OpentheExpressioneditorandcallLookupasgiven
below:
WedoubleclickUnconninbottomofFunctionstabandasweneedonly
DEPTNO,wepassonlyDEPTNOasinput.
[Link].
[Link]>Validate
[Link].
[Link].
Makesuretogiveconnectioninformationforalltables.
MakesuretogiveconnectionforLOOKUPTablealso.
[Link]
[Link](IS)buildsacacheinmemorywhenitprocessesthe
firstrowofdatainacachedLookuptransformation.
TheIntegrationServicealsocreatescachefilesbydefaultinthe$[Link],theISstorestheoverflow
[Link],ISreleasescachememoryanddeletesthecachefiles.
Ifweuseaflatfilelookup,theISalwayscachesthelookupsource.
[Link]

41/51

6/5/2015

[Link]/search/label/INFORMATICA

WesettheCachetypeinLookupProperties.
LookupCacheFiles
[Link]:
Storesdataforthecolumnsusedinthelookupcondition.
[Link]:
ForaconnectedLookuptransformation,storesdatafortheconnectedoutputports,notincludingportsusedinthelookupcondition.
ForanunconnectedLookuptransformation,storesdatafromthereturnport.
TypesofLookupCaches:
[Link]
Bydefault,[Link]
[Link].
[Link]
Tocacheatargettableorflatfilesourceandinsertnewrowsorupdateexistingrowsinthecache,useaLookuptransformationwithadynamiccache.
[Link]
performanceiftableishuge.
[Link]
Ifthelookuptabledoesnotchangebetweensessions,wecanconfiguretheLookuptransformationtouseapersistentlookupcache.
TheISsavesandreusescachefilesfromsessiontosession,eliminatingthetimeRequiredtoreadthelookuptable.
[Link]
Ifthepersistentcacheisnotsynchronizedwiththelookuptable,[Link]
tablehaschanged,wecanusethistorebuildthelookupcache.

[Link]
Unnamedcache:WhenLookuptransformationsinamappinghavecompatiblecachingstructures,[Link]
onlysharestaticunnamedcaches.
Namedcache:[Link]
[Link].
[Link]

42/51

6/5/2015

[Link]/search/label/INFORMATICA

BuildingConnectedLookupCaches
Wecanconfigurethesessiontobuildcachessequentiallyorconcurrently.
Whenwebuildsequentialcaches,theIScreatescachesasthesourcerowsentertheLookuptransformation.
Whenweconfigurethesessiontobuildconcurrentcaches,theISdoesnotwaitforthefirstrowtoentertheLookuptransformationbeforeit
[Link],itbuildsmultiplecachesconcurrently.
[Link]:

[Link]:

Toconfigurethesessiontocreateconcurrentcaches
EditSession>InConfigObjectTab>AdditionalConcurrentPipelinesfor
LookupCacheCreation>Giveavaluehere(AutoByDefault)
Note:TheISbuildscachesforunconnectedLookupssequentiallyonly
[Link]

43/51

6/5/2015

[Link]/search/label/INFORMATICA

====================================================================
ExpressionTRANSFORMATION:
Passiveandconnectedtransformation.
[Link],wemightneedtoadjustemployee
salaries,concatenatefirstandlastnames,orconvertstringstonumbers.
UsetheExpressiontransformationtoperformanynonaggregatecalculations.
Example:Addition,Subtraction,Multiplication,Division,Concat,Uppercaseconversion,lowercaseconversionetc.
WecanalsousetheExpressiontransformationtotestconditionalstatementsbeforeweoutputtheresultstotargettablesorothertransformations.
Example:IF,Then,Decode
Thereare3typesofportsinExpressionTransformation:
Input
Output
Variable:Usedtostoreanytemporarycalculation.
CalculatingValues:
TousetheExpressiontransformationtocalculatevaluesforasinglerow,wemustincludethefollowingports:
Inputorinput/outputportsforeachvalueusedinthecalculation:Forexample:TocalculateTotalSalary,weneedsalaryandcommission.
Outputportfortheexpression:[Link]
valueoftheexpression.
[Link].
Example:CalculatingTotalSalaryofanEmployee
[Link],thendontimport.
Insharedfolder,createthetargettableEmp_Total_SAL.KeepallportsasinEMPtableexceptSalandCommintargettable.AddTotal_SAL
porttostorethecalculation.
Createthenecessaryshortcutsinthefolder.

[Link]

44/51

6/5/2015

[Link]/search/label/INFORMATICA

CreatingMapping:
1. Openfolderwherewewanttocreatethemapping.
2. ClickTools>MappingDesigner.
3. ClickMapping>Create>[Link]:m_totalsal
4. DragEMPfromsourceinmapping.
5. ClickTransformation>Create>[Link].
6. LinkportsfromSQ_EMPtoExpressionTransformation.
7. [Link],removecheckfromoutputportforbothcolumns.
8. Nowcreateanewportout_Total_SAL.Makeitasoutputportonly.
9. ClickthesmallbuttonthatappearsintheExpressionsectionofthedialogboxandentertheexpressionintheExpressionEditor.
[Link]

45/51

6/5/2015

[Link]/search/label/INFORMATICA

10. EnterexpressionSAL+[Link].
11. ChecktheexpressionsyntaxbyclickingValidate.
12. ClickOK>ClickApply>ClickOk.
13. NowconnecttheportsfromExpressiontotargettable.
14. ClickMapping>Validate
15. Repository>Save
[Link].

AsCOMMisnull,Total_SALwillbenullinmostcases.[Link],InDefault
[Link].
[Link].
NowuseERRORinDefaultvalueofCOMMtoskiprowswhereCOMMisnull.
[Link]

46/51

6/5/2015

[Link]/search/label/INFORMATICA

Syntax:ERROR(Anymessagehere)
Similarly,wecanuseABORTfunctiontoabortthesessionifCOMMisnull.
Syntax:ABORT(Anymessagehere)
[Link].
Runworkflowaftervalidatingandsavingtheworkflow.
Performancetuning:
ExpressiontransformationisusedtoperformsimplecalculationsandalsotodoSourcelookups.
1. Useoperatorsinsteadoffunctions.
2. Minimizetheusageofstringfunctions.
3. Ifweuseacomplexexpressionmultipletimesintheexpressiontransformer,[Link]
thisvariableforallcomputations.
===================================================================
RouterTransformation:
Activeandconnectedtransformation.
[Link]
[Link],aRoutertransformationtestsdatafor
oneormoreconditionsAndgivesyoutheoptiontorouterowsofdatathatdonotmeetanyoftheconditionstoadefaultoutputgroup.
Example:IfwewanttokeepemployeesofFrance,India,USin3differenttables,thenwecanuse3Filtertransformationsor1Router
transformation.

MappingAusesthreeFiltertransformationswhileMappingBproducesthesameresultwithoneRoutertransformation.
ARoutertransformationconsistsofinputandoutputgroups,inputandoutputports,groupfilterconditions,andpropertiesthatweconfigureinthe
Designer.

[Link]

47/51

6/5/2015

[Link]

[Link]/search/label/INFORMATICA

48/51

6/5/2015

[Link]/search/label/INFORMATICA

WorkingwithGroups
ARoutertransformationhasthefollowingtypesofgroups:
Input:TheGroupthatgetstheinputports.
Output:[Link].
UserDefinedGroups:[Link]
[Link]
conditionthatwewanttospecify.
TheDefaultGroup:[Link]
[Link],theISpasses
therowtothedefaultgroup.
Example:FilteringemployeesofDepartment10toEMP_10,Department20toEMP_20andresttoEMP_REST
SourceisEMPTable.
Create3targettablesEMP_10,EMP_20andEMP_RESTinsharedfolder.StructureshouldbesameasEMPtable.
Createtheshortcutsinyourfolder.
CreatingMapping:
[Link].
[Link]>MappingDesigner.
[Link]>Create>[Link]:m_router_example
[Link].
[Link]>Create>[Link]
[Link].
6.PassportsfromSQ_EMPtoRouterTransformation.
[Link]
[Link],[Link]..
[Link].
[Link]:DEPTNO=10
[Link].

[Link]

49/51

6/5/2015

[Link]/search/label/INFORMATICA

12.CreateanothergroupforEMP_20.Condition:DEPTNO=20
[Link]
[Link]>ClickApply>ClickOk.
[Link].
[Link]>Validate
[Link]>Save
[Link].
Makesuretogiveconnectioninformationforall3targettables.
[Link]

50/51

6/5/2015

[Link]/search/label/INFORMATICA

SampleMapping:

DifferencebetweenRouterandFilter:
[Link].

========================================================

[Link]

51/51