Scribd
Upload
8 views6 pages

Understanding Dynamic Lookup Cache in Informatica

The document discusses dynamic lookup caches in Informatica. A dynamic lookup cache updates in real-time as the underlying source data changes, unlike a static cache which is fixed once built. This allows lookups to always reflect the current state of the source. Dynamic caches are useful when loading duplicate data, updating master tables, or loading dimension and fact tables simultaneously. The document explains how dynamic lookups work and provides an example mapping configuration.

Uploaded by

Vivek
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views6 pages

Understanding Dynamic Lookup Cache in Informatica

The document discusses dynamic lookup caches in Informatica. A dynamic lookup cache updates in real-time as the underlying source data changes, unlike a static cache which is fixed once built. This allows lookups to always reflect the current state of the source. Dynamic caches are useful when loading duplicate data, updating master tables, or loading dimension and fact tables simultaneously. The document explains how dynamic lookups work and provides an example mapping configuration.

Uploaded by

Vivek
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

7/16/2017 InformaticaDynamicLookupCache

InformaticaDynamicLookupCache
[Link] /etl/informatica/138dynamiclookupcache
WrittenbySauravMitra

[Link]
changesthedataafterthelookupcacheiscreated?Isthereawaysothatthecachealwaysremainuptodate
eveniftheunderlyingtablechanges?

WhydoweneedDynamicLookupCache?

Let'[Link]
aLookupandintheLookup,youareactuallylookingupthesametargettableyouareloading.

Youmayaskme,"So?What'sthebigdeal?Wealldoitquiteoften...".Andyesyouareright.

Thereisno"bigdeal"becauseInformatica(generally)cachesthelookuptableintheverybeginningofthe
mapping,sowhateverrecordgettinginsertedtothetargettablethroughthemapping,willhavenoeffectonthe
Lookupcache.

Thelookupwillstillholdthepreviouslycacheddata,eveniftheunderlyingtargettableischanging.

ButwhatifyouwantyourInformaticaLookupcachetogetupdatedasandwhenthedataintheunderlyingtarget
tablechanges?

Whatifyouwantyourlookupcachetoalwaysshowtheexactsnapshotofthedatainyourtargettableatthat
pointintime?[Link]
cachetohandlethis.

Butinwhichscenariowillsomeoneneedtouseadynamiccache?Tounderstandthis,let'sfirstunderstanda
staticcachescenario.

StaticLookupCacheScenario

Let'ssupposeyourunaretailbusinessandmaintainallyourcustomerinformationinacustomermastertable
(RDBMStable).Everynight,allthecustomersfromyourcustomermastertableisloadedintoaCustomer
[Link],probablyin
3rdnormalform,[Link],ifacustomerchangeshisaddress,theoldaddressis
updatedwiththenewaddress.

Butyourdatawarehousetablestoresthehistory(maybeintheformofSCDTypeII).Thereisamapthatloads
[Link](staticcache)andcheck
[Link]
customerisnotalreadyexistingintarget,youconcludethecustomerisnewandINSERTtherecordwhereasif
thecustomerisalreadyexisting,youmaywanttoupdatethetargetrecordwiththisnewrecord(iftherecordis
updated).Thisscenariocommonlyknownas'UPSERT'(updateelseinsert)scenarioisillustratedbelow.

AstaticLookupCachetodetermineifasourcerecordisneworupdatable
7/16/2017 InformaticaDynamicLookupCache

Youdon'tneeddynamicLookupcachefortheabovetypeofscenario.

DynamicLookupCacheScenario

[Link],this
ensuresthatyoursourcetabledoesnothaveanyduplicaterecord.

But,Whatifyouhadaflatfileassourcewithmanyduplicaterecordsinthesamebunchofdatathatyouaretrying
toload?(OrevenaRDBMStablemayalsocontainduplicaterecords)

WouldthescenariobesameifthebunchofdataIamloadingcontainsduplicate?

[Link]'[Link],thenewcustomer
"Linda"[Link]"Linda"isnotpresent
inyourtargetsystemandhencedoesnotexistinthetargetsidelookupcache.

Whenyoutrytoloadthetargettable,Informaticaprocessesrow3andinsertsittotargetascustomer"Linda"
doesnotexistintarget.ThenInformaticaprocessesrow4andagaininserts"Linda"intotargetsinceInformatica
lookup'sstaticcachecannotdetectthatthecustomer"Linda"[Link]
duplicaterowsintarget.

Theproblemarisingfromabovescenariocanberesolvedbyusingdynamiclookupcache

Herearesomemoreexampleswhenyoumayconsiderusingdynamiclookup,

Updatingamastercustomertablewithbothnewandupdatedcustomerinformationcomingtogetheras
shownabove
[Link],you
[Link].
Butusingdynamiclookup,youcanloadbothsimultaneously.
Loadingdatafromafilewithmanyduplicaterecordsandtoeliminateduplicaterecordsintargetby
[Link]
[Link]
[Link]
time,customerrecordLindawillcometwiceduringthesameload.

Howdoesdynamiclookupcachework

Onceyouhaveconfiguredyourlookuptousedynamiccache(wewillseebelowhowtodothat),whenIntegration
Servicereadsarowfromthesource,itupdatesthelookupcachebyperformingoneofthefollowingactions:

Insertstherowintothecache:Iftheincomingrowisnotinthecache,theIntegrationServiceinsertsthe
[Link]
insert.
Updatestherowinthecache:Iftherowexistsinthecache,theIntegrationServiceupdatestherowin
[Link].
7/16/2017 InformaticaDynamicLookupCache

Makesnochangetothecache:Thishappenswhentherowexistsinthecacheandthelookupis
configuredorspecifiedToInsertNewRowsonlyor,therowisnotinthecacheandlookupisconfiguredto
updateexistingrowsonlyor,therowisinthecache,butbasedonthelookupcondition,nothingchanges.
TheIntegrationServiceflagstherowasunchanged.

NoticethatIntegrationServiceactuallyflagstherowsbasedontheabovethreeconditions.

Andthat'sagreatthing,because,ifyouknowtheflagyoucanactuallyreroutetherowtoachievedifferentlogic.

Fortunately,[Link]
iscalled:

NewLookupRow

Usingthevalueofthisport,therowscanberoutedforinsert,[Link]
RouterorFiltertransformationfollowedbyanUpdateStrategy.

Oh,forgottotellyoutheactualvaluesthatyoucanexpectinNewLookupRowportare:

0=IntegrationServicedoesnotupdateorinserttherowinthecache.
1=IntegrationServiceinsertstherowintothecache.
2=IntegrationServiceupdatestherowinthecache.

WhentheIntegrationServicereadsarow,itchangesthelookupcachedependingontheresultsofthelookup
queryandtheLookuptransformationpropertiesyoudefine.Itassignsthevalue0,1,or2totheNewLookupRow
porttoindicateifitinsertsorupdatestherowinthecache,ormakesnochange.

ConfiguringaDynamicLookupMappingExample

Ok,[Link]
[Link],[Link].
7/16/2017 InformaticaDynamicLookupCache

Ifyoucheckthemappingscreenshot,thereIhaveusedaroutertoreroutetheINSERTgroupandUPDATE
[Link]
recordsareroutedtotheUPDATEgroup.

DynamicLookupSequenceID

Whileusingadynamiclookupcache,wemustassociateeachlookup/outputportwithaninput/outputportora
[Link]
[Link]/outputportswiththelookup/outputportsusedinthelookupcondition.
7/16/2017 InformaticaDynamicLookupCache

WhenweselectSequenceIDintheAssociatedPortcolumn,theIntegrationServicegeneratesasequenceIDfor
eachrowitinsertsintothelookupcache.

WhentheIntegrationServicecreatesthedynamiclookupcache,ittrackstherangeofvaluesinthecache
associatedwithanyportusingasequenceIDanditgeneratesakeyfortheportbyincrementingthegreatest
sequenceIDexistingvaluebyone,whentheinsertinganewrowofdataintothecache.

WhentheIntegrationServicereachesthemaximumnumberforageneratedsequenceID,itstartsoveratone
[Link]
ServicerunsoutofuniquesequenceIDnumbers,thesessionfails.

DynamicLookupPorts

Thelookup/outputportoutputvaluedependsonwhetherwechoosetooutputoldornewvalueswhenthe
IntegrationServiceupdatesarow:

Outputoldvaluesonupdate:TheIntegrationServiceoutputsthevaluethatexistedinthecachebefore
itupdatedtherow.
Outputnewvaluesonupdate:TheIntegrationServiceoutputstheupdatedvaluethatitwritesinthe
[Link]/outputportvaluematchestheinput/outputportvalue.

Note:WecanconfiguretooutputoldornewvaluesusingtheOutputOldValueOnUpdatetransformation
property.

HandlingNULLindynamicLookUp

IftheinputvalueisNULLandweselecttheIgnoreNullinputsforUpdatepropertyfortheassociatedinputport,
theinputvaluedoesnotequalthelookupvalueorthevalueoutoftheinput/[Link]
IgnoreNullproperty,thelookupcacheandthetargettablemightbecomeunsynchronizedifyoupassnullvalues
[Link].

Whenyouupdateadynamiclookupcacheandtargettable,[Link]
IntegrationServicecanhandlethenullvaluesinthefollowingways:

Insertnullvalues:TheIntegrationServiceusesnullvaluesfromthesourceandupdatesthelookup
cacheandtargettableusingallvaluesfromthesource.
IgnoreNullinputsforUpdateproperty:TheIntegrationServiceignoresthenullvaluesinthesource
andupdatesthelookupcacheandtargettableusingonlythenotnullvaluesfromthesource.

Ifweknowthesourcedatacontainsnullvalues,andwedonotwanttheIntegrationServicetoupdatethelookup
cacheortargetwithnullvalues,thenweneedtochecktheIgnoreNullpropertyforthecorresponding
lookup/outputport.

WhenwechoosetoignoreNULLs,wemustverifythatweoutputthesamevaluestothetargetthatthe
[Link]
IntegrationServicetooutputfromthelookup/outputportswhenitupdatesarowinthecache,sothatlookup
cacheandthetargettablemightnotbecomeunsynchronized.

[Link]/outputportsfromtheLookuptransformationtothetarget.
[Link]
[Link]
andcreateexpressionstoensurethatwedonotoutputnullinputvaluestothetarget.

SomeotherdetailsaboutDynamicLookup
7/16/2017 InformaticaDynamicLookupCache

Whenwerunasessionthatusesadynamiclookupcache,theIntegrationServicecomparesthevaluesinall
lookupportswiththevaluesintheirassociatedinputportsbydefault.

[Link]
inputportdiffersfromthevalueinthelookupport,theIntegrationServiceupdatestherowinthecache.

Butwhatifwedon'twanttocompareallports?

[Link]
enablesthispropertyforlookup/[Link]
performancebyignoringsomeportsduringcomparison.(Learnhowtoimproveperformanceoflookup
transformationhere)

Wemightwanttodothiswhenthesourcedataincludesacolumnthatindicateswhetherornottherowcontains
[Link]
indicateswhetherornottoupdatetherowinthecacheandtargettable.

Note:WemustconfiguretheLookuptransformationtocompareatleastoneportelsetheIntegrationServicefails
thesessionwhenweignoreallports.

You might also like