8/10/2019 Informatica by Deva
1/73
Mapping parameters and variables represent values in mappings and mapplets.
When we use a mapping parameter or variable in a mapping, first we declare the mapping
parameter or variable for use in each mapplet or mapping. Then, we define a value for the
mapping parameter or variable before we run the session.
MAPPING PARAMETERS
A mapping parameter represents a constant value that we can define before running a
session.
A mapping parameter retains the same value throughout the entire session.
Example: When we want to extract records of a particular month during ETL process, we will
create a Mapping arameter of data t!pe and use it in "uer! to compare it with the timestamp
field in #$L override.
After we create a parameter, it appears in the Expression Editor.
We can then use the parameter in an! expression in the mapplet or mapping.
We can also use parameters in a source "ualifier filter, user%defined &oin, or extract
override, and in the Expression Editor of reusable transformations.
MAPPING VARIABLES
'nli(e mapping parameters, mapping variables are values that can change between
sessions.
The )ntegration #ervice saves the latest value of a mapping variable to the repositor!
at the end of each successful session.
We can override a saved value with the parameter file.
We can also clear all saved values for the session in the Wor(flow Manager.
We might use a mapping variable to perform an incremental read of the source. *or example,
we have a source table containing time stamped transactions and we want to evaluate the
transactions on a dail! basis. )nstead of manuall! entering a session override to filter source
data each time we run the session, we can create a mapping variable, ++)ncludeateTime. )n
the source "ualifier, create a filter to read onl! rows whose transaction date e"uals +
+)ncludeateTime, such as:
8/10/2019 Informatica by Deva
2/73
T)ME#TAM - ++)ncludeateTime
)n the mapping, use a variable function to set the variable value to increment one da! each
time the session runs. )f we set the initial value of ++)ncludeateTime to /0/1223, the first
time the )ntegration #ervice runs the session, it reads onl! rows dated /0/1223. uring the
session, the )ntegration #ervice sets ++)ncludeateTime to /1/1223. )t saves /1/1223 to the
repositor! at the end of the session. The next time it runs the session, it reads onl! rows from
August 1, 1223.
Used in following transformations
Expression
*ilter
4outer
'pdate #trateg!
Initial and !efa"lt Val"e
When we declare a mapping parameter or variable in a mapping or a mapplet, we can enter
an initial value. When the )ntegration #ervice needs an initial value, and we did not declare an
initial value for the parameter or variable, the )ntegration #ervice uses a default value based
on the data t!pe of the parameter or variable.
ata %5efault 6alue
7umeric %52
#tring %5Empt! #tring
ate time %50/0/0
Varia#le Val"es#tart value and current value of a mapping variable
Start Val"e
The start value is the value of the variable at the start of the session. The )ntegration #ervice
loo(s for the start value in the following order:
0. 6alue in parameter file
1. 6alue saved in the repositor!
8/10/2019 Informatica by Deva
3/73
8. )nitial value
3. efault value
$"rrent Val"e
The current value is the value of the variable as the session progresses. When a session starts,
the current value of a variable is the same as the start value. The final current value for a
variable is saved to the repositor! at the end of a successful session. When a session fails to
complete, the )ntegration #ervice does not update the value of the variable in the repositor!.
Note)f a variable function is not used to calculate the current value of a mapping variable,
the start value of the variable is saved to the repositor!.
6ariable ata t!pe and Aggregation T!pe When we declare a mapping variable in a mapping,
we need to configure the ata t!pe and aggregation t!pe for the variable. The )# uses the
aggregate t!pe of a Mapping variable to determine the final current value of the mapping
variable.
Aggregation t!pes are:
$o"nt )nteger and small integer data t!pes are valid onl!.
MaxAll transformation data t!pes except binar! data t!pe are valid.
MinAll transformation data t!pes except binar! data t!pe are valid.
Varia#le %"n&tions
6ariable functions determine how the )ntegration #ervice calculates the current value of a
mapping variable in a pipeline.
SetMaxVaria#le #ets the variable to the maximum value of a group of values. )t ignores
rows mar(ed for update, delete, or re&ect. Aggregation t!pe set to Max.
SetMinVaria#le#ets the variable to the minimum value of a group of values. )t ignores rows
mar(ed for update, delete, or re&ect. Aggregation t!pe set to Min.
Set$o"ntVaria#le)ncrements the variable value b! one. )t adds one to the variable value
when a row is mar(ed for insertion, and subtracts one when the row is Mar(ed for deletion. )t
ignores rows mar(ed for update or re&ect. Aggregation t!pe set to 9ount.
8/10/2019 Informatica by Deva
4/73
SetVaria#le#ets the variable to the configured value. At the end of a session, it compares
the final current value of the variable to the start value of the variable. ased on the
aggregate t!pe of the variable, it saves a final value to the repositor!.
$reating Mapping Parameters and Varia#les
0. ;pen the folder where we want to create parameter or variable.
1. )n the Mapping esigner, clic( Mappings 5 arameters and 6ariables. %or% )n the
Mapplet esigner, clic( Mapplet 5 arameters and 6ariables.
8. 9lic( the add button.
3. Enter name. o not remove ++ from name.
ive )nitial 6alue. 9lic( o(.
Example: 'se of Mapping of Mapping arameters and 6ariables
EM will be source table.
9reate a target table M?M6?E@AMLE having columns: EM7;, E7AME, ET7;,
T;TAL?#AL, MA@?6A4, M)7?6A4, 9;'7T?6A4 and #ET?6A4.
T;TAL?#AL - #AL 9;MM ++;7'# Bonus is mapping parameter that changes
ever! monthC
#ET?6A4: We will be added one month to the D)4EATE of ever! emplo!ee.
9reate shortcuts as necessar!.
$reating Mapping
0. ;pen folder where we want to create the mapping.
1. 9lic( Tools %5 Mapping esigner.
8. 9lic( Mapping%5 9reate%5 >ive name. Ex: m?mp?mv?example
3. rag EM and target table.
ive initial value as 122.
. 9reate variable ++var?max of MA@ aggregation t!pe and initial value 0
8/10/2019 Informatica by Deva
5/73
02. 9reate variable ++var?count of 9;'7T aggregation t!pe and initial value 2. 9;'7T is
visible when datat!pe is )7T or #MALL)7T.
00. 9reate variable ++var?set of MA@ aggregation t!pe.
01. 9reate < output ports out? T;TAL?#AL, out?MA@?6A4, out?M)7?6A4,
out?9;'7T?6A4 and out?#ET?6A4.
08. ;pen expression editor for T;TAL?#AL. o the same as we did earlier for #AL 9;MM. To
add ++;7'# to it, select variable tab and select the parameter from mapping parameter. #AL
9;MM ++onus
03. ;pen Expression editor for out?max?var.
0
8/10/2019 Informatica by Deva
6/73
0F. ;pen Expression editor for out?min?var and write the following expression:
#ETM)76A4)ALEB++var?min,#ALC. 6alidate the expression.
0. ;pen Expression editor for out?count?var and write the following expression:
#ET9;'7T6A4)ALEB++var?countC. 6alidate the expression.
0G. ;pen Expression editor for out?set?var and write the following expression:
#ET6A4)ALEB++var?set,A?T;?ATEBD)4EATE,HMMH,0CC. 6alidate.
12. 9lic( ;I. Expression Transformation below:
http://lh4.ggpht.com/_MbhSjEtmzI8/TanPj1ojBzI/AAAAAAAAAKc/Ax30JuzIg_k/s1600-h/clip_image002%5B5%5D%5B5%5D.jpg8/10/2019 Informatica by Deva
7/73
10. Lin( all ports from expression to target and 6alidate Mapping and #ave it.
11. #ee mapping picture on next page.
PARAMETER %ILE
http://lh5.ggpht.com/_MbhSjEtmzI8/TanPmTHarpI/AAAAAAAAAKs/v8KbA4YEybI/s1600-h/clip_image002%5B9%5D%5B3%5D.jpghttp://lh5.ggpht.com/_MbhSjEtmzI8/TanPlLeDhbI/AAAAAAAAAKk/UjP54OqM8gU/s1600-h/clip_image002%5B7%5D%5B6%5D.jpg8/10/2019 Informatica by Deva
8/73
A parameter file is a list of parameters and associated values for a wor(flow, wor(let,
or session.
arameter files provide flexibilit! to change these variables each time we run a
wor(flow or session.
We can create multiple parameter files and change the file we use for a session or
wor(flow. We can create a parameter file using a text editor such as Wordad or
7otepad.
Enter the parameter file name and director! in the wor(flow or session properties.
A parameter file contains the following t!pes of parameters and variables:
'or(flow )aria#le4eferences values and records information in a wor(flow.
'or(let )aria#le4eferences values and records information in a wor(let. 'se
predefined wor(let variables in a parent wor(flow, but we cannot use wor(flow
variables from the parent wor(flow in a wor(let.
Session parameterefines a value that can change from session to session, such
as a database connection or file name.
Mapping parameter and Mapping variable
USING A PARAMETER %ILE
arameter files contain several sections preceded b! a heading. The heading identifies the
)ntegration #ervice, )ntegration #ervice process, wor(flow, wor(let, or session to which we
want to assign parameters or variables.
Ma(e session and wor(flow.
>ive connection information for source and target table.
4un wor(flow and see result.
8/10/2019 Informatica by Deva
9/73
Sample Parameter %ile for *"r example
)n the parameter file, folder and session names are case sensitive.
9reate a text file in notepad with name ara?*ile.txt
Jractice.#T:s?m?M?M6?ExampleK
++onus-0222
++var?max-
8/10/2019 Informatica by Deva
10/73
We can specif! the parameter file name and director! in the wor(flow or session properties.
To enter a parameter file in the wor(flow properties:
0. ;pen a Wor(flow in the Wor(flow Manager.
1. 9lic( Wor(flows 5 Edit.
8. 9lic( the roperties tab.
3. Enter the parameter director! and name in the arameter *ilename field.
eneral ;ptions settings.
8. Enter the parameter director! and name in the arameter *ilename field.
3. Example: :*ilesara?*ile.txt or +M#ource*ileirara?*ile.txt
8/10/2019 Informatica by Deva
11/73
cC 'se the session and wor(flow logs to capture the load statistics.
dC ou need to document all the load timing information.
Anal,-e t.e s"&&ess rows and re/e&tions0
aC Dave customiNed #$L "ueries to chec( the source/targets and here we will perform the4ecord 9ount 6erification.
bC Anal!Ne the re&ections and build a process to handle those re&ections. This re"uires a clear
business re"uirement from the business on how to handle the data re&ections. o we need to
reload or re&ect and inform etcO iscussions are re"uired and appropriate process must be
developed.
Performan&e Impro)ement
aC 7etwor( erformance
bC #ession erformance
cC atabase erformance
dC Anal!Ne and if re"uired define the )nformatica and partitioning re"uirements.
+"alitati)e Testing
Anal!Ne P validate !our transformation business rules. More of functional testing.
eC ou need review field b! field from source to target and ensure that the re"uired
transformation logic is applied.
fC )f !ou are ma(ing changes to existing mappings ma(e use of the data lineagefeatureAvailable with )nformatica ower 9enter. This will help !ou to find the conse"uences of Altering
or deleting a port from existing mapping.
gC Ensure that appropriate dimension loo(upQs have been used and !our development is in
#!nc with !our business re"uirements.
IN%*RMATI$A TESTING
!e#"gger: 6er! useful tool for debugging a valid mapping to gain troubleshooting information
about data and error conditions. 4efer )nformatica documentation to (now more about
debugger tool.
Test Load *ptions 4elational Targets.
4unning the )ntegration #ervice in #afe Mode
Test a de)elopment en)ironment0 4un the )ntegration #ervice in safe mode to test
a development environment before migrating to production
Tro"#les.oot t.e Integration Ser)i&e0 9onfigure the )ntegration #ervice to fail
over in safe mode and troubleshoot errors when !ou migrate or test a production
8/10/2019 Informatica by Deva
12/73
environment configured for high availabilit!. After the )ntegration #ervice fails over in
safe mode, !ou can correct the error that caused the )ntegration #ervice to fail over.
S,ntax TestingTest !our customiNed "ueries using !our source "ualifier before executing
the session. erformance Testing for identif!ing the following bottlenec(s:
Target
#ource
Mapping
#ession
#!stem
'se the following methods to identif! performance bottlenec(s:
R"n test sessions. ou can configure a test session to read from a flat file source or
to write to a flat file target to identif! source and target bottlenec(s.
Anal,-e performan&e details. Anal!Ne performance details, such as performance
counters, to determine where session performance decreases.
Anal,-e t.read statisti&s. Anal!Ne thread statistics to determine the optimal number
of partition points.
Monitor s,stem performan&e. ou can use s!stem monitoring tools to view the
percentage of 9' use, )/; waits, and paging to identif! s!stem bottlenec(s. ou can
also use the Wor(flow Monitor to view s!stem resource usage. 'se ower 9enter
conditional filter in the #ource $ualifier to improve performance.
S.are metadata0ou can share metadata with a third part!. *or example, !ou want
to send a mapping to someone else for testing or anal!sis, but !ou do not want to
disclose repositor! connection information for securit! reasons. ou can export the
mapping to an @ML file and edit the repositor! connection information before sendingthe @ML file. The third part! can import the mapping from the @ML file and anal!Ne the
metadata.
UAT:
)n this phase !ou will involve the user to test the end results and ensure that business is
satisfied with the "ualit! of the data.
An! changes to the business re"uirement will follow the change management process and
eventuall! those changes have to follow the #L9 process.
*ptimi-e !e)elopment1 Testing1 and Training S,stems
ramaticall! accelerate development and test c!cles and reduce storage costs b!
creating full! functional, smaller targeted data subsets for development, testing, and
training s!stems, while maintaining full data integrit!.
$uic(l! build and update nonproduction s!stems with a small subset of production
data and replicate current subsets of nonproduction copies faster.
#implif! test data management and shrin( the footprint of nonproduction s!stems to
significantl! reduce )T infrastructure and maintenance costs.
8/10/2019 Informatica by Deva
13/73
4educe application and upgrade deplo!ment ris(s b! properl! testing configuration
updates with up%to%date, realistic data before introducing them into production .
Easil! customiNe provisioning rules to meet each organiNationQs changing business
re"uirements.
Lower training costs b! standardiNing on one approach and one infrastructure.
Train emplo!ees effectivel! using reliable, production%li(e data in training s!stems.
S"pport $orporate !i)estit"res and Reorgani-ations
'ntangle complex operational s!stems and separate data along business lines to
"uic(l! build the divested organiNationQs s!stem.
Accelerate the provisioning of new s!stems b! using onl! data thatQs relevant to the
divested organiNation.
ecrease the cost and time of data divestiture with no reimplementation costs .
Red"&e t.e Total $ost of Storage *wners.ip
ramaticall! increase an )T teamQs productivit! b! reusing a comprehensive list of data
ob&ects for data selection and updating processes across multiple pro&ects, instead of
coding b! handRwhich is expensive, resource intensive, and time consuming .
Accelerate application deliver! b! decreasing 4P c!cle time and streamlining test
data management.
)mprove the reliabilit! of application deliver! b! ensuring )T teams have read! access
to updated "ualit! production data.
Lower administration costs b! centrall! managing data growth solutions across all
pac(aged and custom applications.
#ubstantiall! accelerate time to value for subsets of pac(aged applications.
ecrease maintenance costs b! eliminating custom code and scripting.
9onstraint%ased Loading:
$onstraint2Based Loading
)n the Wor(flow Manager, !ou can specif! constraint%based loading for a session. When !ou
select this option, the )ntegration #ervice orders the target load on a row%b!%row basis. *or
ever! row generated b! an active source, the )ntegration #ervice loads the corresponding
transformed row first to the primar! (e! table, then to an! foreign (e! tables. 9onstraint%
based loading depends on the following re"uirements:
A&ti)e so"r&e0 4elated target tables must have the same active source.
http://informaticatutorials-naveen.blogspot.com/2011/04/constraint-based-loading-in-workflow.htmlhttp://informaticatutorials-naveen.blogspot.com/2011/04/constraint-based-loading-in-workflow.htmlhttp://informaticatutorials-naveen.blogspot.com/2011/04/constraint-based-loading-in-workflow.html8/10/2019 Informatica by Deva
14/73
3e, relations.ips0 Target tables must have (e! relationships.
Target &onne&tion gro"ps0 Targets must be in one target connection group.
Treat rows as insert0 'se this option when !ou insert into the target. ou cannot use
updates with constraint based loading.
A&ti)e So"r&e
When target tables receive rows from different active sources, the )ntegration #ervice reverts
to normal loading for those tables, but loads all other targets in the session using constraint%
based loading when possible. *or example, a mapping contains three distinct pipelines. The
first two contain a source, source "ualifier, and target. #ince these two targets receive data
from different active sources, the )ntegration #ervice reverts to normal loading for both
targets. The third pipeline contains a source, 7ormaliNer, and two targets. #ince these twotargets share a single active source Bthe 7ormaliNerC, the )ntegration #ervice performs
constraint%based loading: loading the primar! (e! table first, then the foreign (e! table.
3e, Relations.ips
When target tables have no (e! relationships, the )ntegration #ervice does not perform
constraint%based loading.
#imilarl!, when target tables have circular (e! relationships, the )ntegration #ervice reverts to
a normal load. *or example, !ou have one target containing a primar! (e! and a foreign (e!
related to the primar! (e! in a second target. The second target also contains a foreign (e!
that references the primar! (e! in the first target. The )ntegration #ervice cannot enforce
constraint%based loading for these tables. )t reverts to a normal load.
Target $onne&tion Gro"ps
The )ntegration #ervice enforces constraint%based loading for targets in the same target
connection group. )f !ou want to specif! constraint%based loading for multiple targets that
receive data from the same active source, !ou must verif! the tables are in the same target
connection group. )f the tables with the primar! (e!%foreign (e! relationship are in different
target connection groups, the )ntegration #ervice cannot enforce constraint%based loading
when !ou run the wor(flow. To verif! that all targets are in the same target connection group,
complete the following tas(s:
8/10/2019 Informatica by Deva
15/73
6erif! all targets are in the same target load order group and receive data from the
same active source.
'se the default partition properties and do not add partitions or partition points.
efine the same target t!pe for all targets in the session properties.
efine the same database connection name for all targets in the session properties.
9hoose normal mode for the target load t!pe for all targets in the session properties.
Treat Rows as Insert
'se constraint%based loading when the session option Treat #ource 4ows As is set to insert.
ou might get inconsistent data if !ou select a different Treat #ource 4ows As option and !ou
configure the session for constraint%based loading.
When the mapping contains 'pdate #trateg! transformations and !ou need to load data to a
primar! (e! table first, split the mapping using one of the following options:
Load primar! (e! table in one mapping and dependent tables in another mapping. 'se
constraint%based loading to load the primar! table.
erform inserts in one mapping and updates in another mapping.
9onstraint%based loading does not affect the target load ordering of the mapping. Target load
ordering defines the order the )ntegration #ervice reads the sources in each target load order
group in the mapping. A target load order group is a collection of source "ualifiers,
transformations, and targets lin(ed together in a mapping. 9onstraint based loading
establishes the order in which the )ntegration #ervice loads individual targets within a set of
targets receiving data from a single source "ualifier.
Example
The following mapping is configured to perform constraint%based loading:
8/10/2019 Informatica by Deva
16/73
)n the first pipeline, target T?0 has a primar! (e!, T?1 and T?8 contain foreign (e!s
referencing the T0 primar! (e!. T?8 has a primar! (e! that T?3 references as a foreign (e!.
#ince these tables receive records from a single active source, #$?A, the )ntegration #ervice
loads rows to the target in the following order:
0. T?0
1. T?1 and T?8 Bin no particular orderC
8. T?3
The )ntegration #ervice loads T?0 first because it has no foreign (e! dependencies and
contains a primar! (e! referenced b! T?1 and T?8. The )ntegration #ervice then loads T?1
and T?8, but since T?1 and T?8 have no dependencies, the! are not loaded in an! particular
order. The )ntegration #ervice loads T?3 last, because it has a foreign (e! that references a
primar! (e! in T?8.After loading the first set of targets, the )ntegration #ervice begins reading
http://lh4.ggpht.com/_MbhSjEtmzI8/TbEoE9WHPoI/AAAAAAAAAaQ/xbeYAmMOzl0/s1600-h/clip_image002%5B4%5D.gif8/10/2019 Informatica by Deva
17/73
8/10/2019 Informatica by Deva
18/73
F. 4ow the )nformatica server sorts the string values in 4an( transformationO
. Is sorter an active or passive transformationO When do we consider it to be active and
passiveO
G. Explain about )nformatica server ArchitectureO
02. In update strateg! 4elational table or flat file which gives us more performanceO Wh!O
00. 'hat are the out put files that the )nformatica server creates during running a
sessionO
01. $an !ou explain what are error tables in )nformatica are and how we do error handling
in )nformaticaO
08. !ifference between constraint base loading and target load planO
03. !ifference between ))* and E9;E functionO
0
8/10/2019 Informatica by Deva
19/73
:=0In a &oiner transformation, !ou should specif! the table with lesser rows as the master
table. Wh!O
:>0!ifference between 9ached loo(up and 'n%cached loo(upO
:60Explain what TM does when !ou start a wor( flowO
:70Explain what Load Manager does when !ou start a wor( flowO
:80In a #e"uential batch how do i stop one particular session from runningO
:90'hat are the t!pes of the aggregations available in )nformaticaO
=;04ow do ) create )ndexes after the load process is doneO
=;04ow can !ou delete duplicate rows with out using !namic Loo(upO Tell me an! other
wa!s using loo(up delete the duplicate rowsO
>50'hat is tracing level and what are its t!pesO
>:0'hat is a command that used to run a batchO
>=0'hat are the unsupported repositor! ob&ects for a mappletO
>>0If !our wor(flow is running slow, what is !our approach towards performance tuningO
>60'hat are the t!pes of mapping wiNards available in )nformaticaO
>70After dragging the ports of three sources B#"l server, oracle, )nformixC to a single source
"ualifier, can we map these three ports directl! to targetO
>80'h! we use stored procedure transformationO
>90'hich ob&ect is re"uired b! the debugger to create a valid debug sessionO
8/10/2019 Informatica by Deva
20/73
6;0$an we use an active transformation after update strateg! transformationO
60'hat is the difference between + P ++ in mapping or parameter fileO )n which case the!
are generall! usedO
760'hile importing the relational source definition from database, what are the meta data of
source ' importO
770!ifference between ower mart P ower 9enterO
78.'hat (inds of sources and of targets can be used in )nformaticaO
790If a se"uence generator Bwith increment of 0C is connected to Bsa!C 8 targets and each
target uses the 7E@T6AL port, what value will each target getO
8;0'hat do !ou mean b! #$L overrideO
8
8/10/2019 Informatica by Deva
21/73
8504ow does )nformatica do variable initialiNationO 7umber/#tring/ate
8:04ow man! different loc(s are available for repositor! ob&ects
8=0'hat are the transformations that use cache for performanceO
8>0'hat is the use of *orward/4e&ect rows in MappingO
8604ow man! wa!s !ou can filter the recordsO
8704ow to delete duplicate records from source database/*lat *ilesO 9an we use post s"l to
delete these records. )n case of flat file, how can !ou delete duplicates before it starts loadingO
880?ou are re"uired to perform Sbul( loading using )nformatica on ;racle, what action would
perform at )nformatica ;racle level for a successful loadO
890'hat precautions do !ou need ta(e when !ou use reusable #e"uence generator
transformation for concurrent sessionsO
9;0Is it possible negative increment in #e"uence >eneratorO )f !es, how would !ou
accomplish itO
904ow can !ou limit number of running sessions in a wor(flowO
960An Aggregate transformation has 3 ports Bl sum Bcol 0C, group b! col 1, col8C, which port
should be the outputO
970'hat is a d!namic loo(up and what is the significance of 7ewLoo(up4owO Dow will use
them for re&ecting duplicate recordsO
980If !ou have more than one pipeline in !our mapping how will change the order of loadO
990'hen !ou export a wor(flow from 4epositor! Manager, what does this xml containO
Wor(flow onl!O
8/10/2019 Informatica by Deva
22/73
0 'hat is imensional ModellingO
60 'hat #now *la(e #chemaO
70 'hat are the ifferent methods of loading imension tablesO
80 'hat are Aggregate tablesO
90 'hat is the ifference between ;LT and ;LAO
8/10/2019 Informatica by Deva
23/73
5;0 'hat is 7ormaliNationO *irst 7ormal *orm, #econd 7ormal *orm , Third 7ormal *ormO
5ranularit! of a fact tableO What does this signif!OBWee(l! level
summariNation there is no need to have )nvoice 7umber in the fact table an!moreC
5>0 4ow are the imension tables designedO e%7ormaliNed, Wide, #hort, 'se #urrogate
Ie!s, 9ontain Additional date fields and flags.
560 'hat are slowl! changing dimensionsO
570 'hat are non%additive factsO B)nventor!,Account balances in ban(C
580 'hat are conformed dimensionsO
590 'hat is 6LO Batabase is too large to bac( up in a time frame then itHs a 6LC
:;0 'hat are #90, #91 and #98O
Target Load ;rder
22
Target Load lan
When !ou use a mapplet in a mapping, the Mapping esigner lets !ou set the target load plan
for sources within the mapplet.
Setting t.e Target Load *rder
http://informaticatutorials-naveen.blogspot.com/2011/04/target-load-order.htmlhttp://informaticatutorials-naveen.blogspot.com/2011/04/target-load-order.html8/10/2019 Informatica by Deva
24/73
ou can configure the target load order for a mapping containing an! t!pe of target definition.
)n the esigner, !ou can set the order in which the )ntegration #ervice sends rows to targets
in different target load order groups in a mapping. A target load order group is the collection
of source "ualifiers, transformations, and targets lin(ed together in a mapping. ou can set the
target load order if !ou want to maintain referential integrit! when inserting, deleting, or
updating tables that have the primar! (e! and foreign (e! constraints.
The )ntegration #ervice reads sources in a target load order group concurrentl!, and it
processes target load order groups se"uentiall!.
To specif! the order in which the )ntegration #ervice sends data to targets, create one source
"ualifier for each target within a mapping. To set the target load order, !ou then determine in
which order the )ntegration #ervice reads each source in the mapping.
The following figure shows two target load order groups in one mapping:
)n this mapping, the first target load order group includes )TEM#, #$?)TEM#, and T?)TEM#.
The second target load order group includes all other ob&ects in the mapping, including the
T;TAL?;4E4# target. The )ntegration #ervice processes the first target load order group,
and then the second target load order group.
When it processes the second target load order group, it reads data from both sources at the
same time.
To set t.e target load order
http://lh4.ggpht.com/_MbhSjEtmzI8/TbEmxvtEz2I/AAAAAAAAAaY/FSax0yDxQTE/s1600-h/clip_image002%5B1%5D.gif8/10/2019 Informatica by Deva
25/73
0. 9reate a mapping that contains multiple target load order groups.
1. 9lic( Mappings 5 Target Load lan.
8. The Target Load lan dialog box lists all #ource $ualifier transformations in the
mapping and the targets that receive data from each source "ualifier.
3. #elect a source "ualifier from the list.
8/10/2019 Informatica by Deva
26/73
9ontain unused ports: We do not have to connect all mapplet input and output ports in
a mapping.
Mapplet Inp"t
Mapplet input can originate from a source definition and/or from an )nput transformation in
the mapplet. We can create multiple pipelines in a mapplet.
We use Mapplet )nput transformation to give input to mapplet.
'se of Mapplet )nput transformation is optional.
Mapplet *"tp"t
The output of a mapplet is not connected to an! target table.
We must use Mapplet ;utput transformation to store mapplet output.
A mapplet must contain at least one ;utput transformation with at least one
connected port in the mapplet.
Exampleive the output to
mapplet out transformation.
EM and ET will be source tables.
;utput will be given to transformation Mapplet?;ut.
Steps
0. ;pen folder where we want to create the mapping.
1. 9lic( Tools %5 Mapplet esigner.
8. 9lic( Mapplets%5 9reate%5 >ive name. Ex: mplt?example0
3. rag EM and ET table.
8/10/2019 Informatica by Deva
27/73
G. ass all ports from expression to Mapplet output.
02. Mapplet %5 6alidate
00. 4epositor! %5 #ave
Use of mapplet in mapping
We can mapplet in mapping b! &ust dragging the mapplet from mapplet folder on left
pane as we drag source and target tables.
When we use the mapplet in a mapping, the mapplet ob&ect displa!s onl! the ports
from the )nput and ;utput transformations. These are referred to as the mapplet input
and mapplet output ports.
Ma(e sure to give correct connection information in session.
Ma(ing a mappingWe will use mplt?example0, and then create a filter
transformation to filter records whose Total #alar! is 5- 0ive name. Ex: m?mplt?example0
3. rag mplt?Example0 and target table.
8/10/2019 Informatica by Deva
28/73
Ma(e session and wor(flow.
>ive connection information for mapplet source tables.
>ive connection information for target table.
4un wor(flow and see result.
A4T)T);7)7>
A pipeline consists of a source "ualifier and all the transformations and Targets that
receive data from that source "ualifier.
When the )ntegration #ervice runs the session, it can achieve higher erformance b!
partitioning the pipeline and performing the extract, Transformation, and load for each
partition in parallel.
A partition is a pipeline stage that executes in a single reader, transformation, or Writer
thread. The number of partitions in an! pipeline stage e"uals the number of Threads in the
stage. ! default, the )ntegration #ervice creates one partition in ever! pipeline stage.
PARTITI*NING ATTRIBUTES
8/10/2019 Informatica by Deva
29/73
artition points mar( thread boundaries and divide the pipeline into stages.
A stage is a section of a pipeline between an! two partition points.
50 N"m#er of Partitions
we can define up to =3 partitions at an! partition point in a pipeline.
When we increase or decrease the number of partitions at an! partition point, the
Wor(flow Manager increases or decreases the number of partitions at all artition
points in the pipeline.
increasing the number of partitions or partition points increases the number of
threads.
The number of partitions we create e"uals the number of connections to the source or
target. *or one partition, one database connection will be used.
http://lh3.ggpht.com/_MbhSjEtmzI8/TapQ7ozy5HI/AAAAAAAAALI/fqfaVfWcuzg/s1600-h/clip_image001%5B2%5D.jpg8/10/2019 Informatica by Deva
30/73
8/10/2019 Informatica by Deva
31/73
The )ntegration #ervice creates a default partition t!pe at each partition point.
)f we have the artitioning option, we can change the partition t!pe. This option is
purchased separatel!.
The partition t!pe controls how the )ntegration #ervice distributes data among
partitions at partition points.
PARTITI*NING T?PES
8/10/2019 Informatica by Deva
32/73
8/10/2019 Informatica by Deva
33/73
Example9ustomer 0%022 in one partition, 020%122 in another and so on. We efine
the range for each partition.
W;4I)7> W)TD L)7I# 'se lin(s to connect each wor(flow tas(.
We can specif! conditions with lin(s to create branches in the wor(flow.
The Wor(flow Manager does not allow us to use lin(s to create loops in the wor(flow.
Each lin( in the wor(flow can run onl! once.
Valid 'or(flow
Example of loop
http://informaticatutorials-naveen.blogspot.com/2011/04/working-with-links.htmlhttp://lh5.ggpht.com/_MbhSjEtmzI8/TapUUKFWjDI/AAAAAAAAANw/HZUgWNF8wRs/s1600-h/clip_image001%5B4%5D.jpghttp://lh3.ggpht.com/_MbhSjEtmzI8/TapURHZocCI/AAAAAAAAANo/Vap4iYaPqL8/s1600-h/clip_image002%5B4%5D%5B3%5D.jpghttp://informaticatutorials-naveen.blogspot.com/2011/04/working-with-links.htmlhttp://informaticatutorials-naveen.blogspot.com/2011/04/working-with-links.html8/10/2019 Informatica by Deva
34/73
Spe&if,ing Lin( $onditions
;nce we create lin(s between tas(s, we can specif! conditions for each lin( to
determine the order of execution in the wor(flow.
)f we do not specif! conditions for each lin(, the )ntegration #ervice runs the next tas(
in the wor(flow b! default.
'se predefined or user%defined wor(flow variables in the lin( condition.
Steps
0. )n the Wor(flow esigner wor(space, double%clic( the lin( !ou want to specif!.
1. The Expression Editor appears.
8. )n the Expression Editor, enter the lin( condition. The Expression Editor provides
predefined wor(flow variables, user%defined wor(flow variables, variable functions, and
oolean and arithmetic operators.
3. 6alidate the expression using the 6alidate button.
Using t.e Expression Editor
The Wor(flow Manager provides an Expression Editor for an! expressions in the wor(flow. We
can enter expressions using the Expression Editor for the following:
Lin( conditions
http://lh4.ggpht.com/_MbhSjEtmzI8/TapUV5LWTzI/AAAAAAAAAN4/9-25GU_Mu6s/s1600-h/clip_image002%5B6%5D.jpg8/10/2019 Informatica by Deva
35/73
ecision tas(
Assignment tas(
The Wor(flow Manager contains man! t!pes of tas(s to help !ou build wor(flows and wor(lets.
We can create reusable tas(s in the Tas( eveloper.
T,pes of tas(s
Task Type Tool where task
can be created
Reusable or not
Session as! De"eloper #es
$mail %or!flow Designer #es
&ommand %or!let Designer #es
$"ent'Raise %or!flow Designer (o
$"ent'%ait %or!let Designer (o
imer (o
Decision (o
Assignment (o
&ontrol (o
SESSI*N TAS3
A session is a set of instructions that tells the ower 9enter #erver how and when to
move data from sources to targets.
To run a session, we must first create a wor(flow to contain the #ession tas(.
We can run as man! sessions in a wor(flow as we need. We can run the #ession tas(s
se"uentiall! or concurrentl!, depending on our needs.
The ower 9enter #erver creates several files and in%memor! caches depending on the
transformations and options used in the session.
EMAIL TAS3
The Wor(flow Manager provides an Email tas( that allows us to send email during a
wor(flow.
9reated b! Administrator usuall! and we &ust drag and use it in our mapping.
8/10/2019 Informatica by Deva
36/73
Steps
0. )n the Tas( eveloper or Wor(flow esigner, choose Tas(s%9reate.
1. #elect an Email tas( and enter a name for the tas(. 9lic( 9reate.
8. 9lic( one.
3. ouble%clic( the Email tas( in the wor(space. The Edit Tas(s dialog box appears.
8/10/2019 Informatica by Deva
37/73
Exampleto cop! a file sample.txt from drive to E.
$ommand 9; :sample.txt E: in windows
Steps for &reating &ommand tas(
0. )n the Tas( eveloper or Wor(flow esigner, choose Tas(s%9reate.1. #elect 9ommand Tas( for the tas( t!pe.
8. Enter a name for the 9ommand tas(. 9lic( 9reate. Then clic( done.
3. ouble%clic( the 9ommand tas(. >o to commands tab.
8/10/2019 Informatica by Deva
38/73
Pre2defined e)entA pre%defined event is a file%watch event. This event Waits for a
specified file to arrive at a given location.
User2defined e)ent A user%defined event is a se"uence of tas(s in the Wor(flow. We
create events and then raise them as per need.
Steps for &reating User !efined E)ent
0. ;pen an! wor(flow where we want to create an event.
1. 9lic( Wor(flow%5 Edit %5 Events tab.
8. 9lic( to Add button to add events and give the names as per need.
3. 9lic( Appl! %5 ;(. 6alidate the wor(flow and #ave it.
T,pes of E)ents Tas(s
EVENT RAISEEvent%4aise tas( represents a user%defined event. We use this tas( to
raise a user defined event.
EVENT 'AITEvent%Wait tas( waits for a file watcher event or user defined event to
occur before executing the next session in the wor(flow.
Example
8/10/2019 Informatica by Deva
39/73
Example 54aise a user defined event when session s?m?filter?example succeeds. 9apture
this event in event wait tas( and run session #?M?T;TAL?#AL?E@AMLE
Steps for &reating wor(flow
0. Wor(flow %5 9reate %5 >ive name wf?event?wait?event?raise %5 9lic( o(.
1. Wor(flow %5 Edit %5 Events Tab and add events E6E7T0 there.
8. rag s?m?filter?example and lin( it to #TA4T tas(.
3. 9lic( Tas(s %5 9reate %5 #elect E6E7T 4A)#E from list. >ive name
ive name EW?WA)T. 9lic(
9reate and then done.
G. Lin( EW?WA)T to #TA4T tas(.
02. 4ight clic( EW?WA)T %5 E)T%5 E6E7T# tab.
00. #elect 'ser efined there. #elect the Event0 b! clic(ing rowse Events button.
01. Appl! %5 ;I.
08. rag #?M?T;TAL?#AL?E@AMLE and lin( it to EW?WA)T.03. Mapping %5 6alidate
0
8/10/2019 Informatica by Deva
40/73
TIMER TAS3
The Timer tas( allows us to specif! the period of time to wait before the ower 9enter #erver
runs the next tas( in the wor(flow. The Timer tas( has two t!pes of settings:
A#sol"te timeWe specif! the exact date and time or we can choose a user%defined
wor(flow variable to specif! the exact time. The next tas( in wor(flow will run as per
the date and time specified.
Relati)e timeWe instruct the ower 9enter #erver to wait for a specified period of
time after the Timer tas(, the parent wor(flow, or the top%level wor(flow starts.
Example4un session s?m?filter?example relative to 0 min after the timer tas(.
Steps for &reating wor(flow
0. Wor(flow %5 9reate %5 >ive name wf?timer?tas(?example %5 9lic( o(.
1. 9lic( Tas(s %5 9reate %5 #elect T)ME4 from list. >ive name T)ME4?Example. 9lic(
9reate and then done.
8. Lin( T)ME4?Example to #TA4T tas(.
3. 4ight clic( T)ME4?Example%5 E)T %5 T)ME4 tab.
ive 0 min and #elect U*rom start time of this tas(Q
;ption.
=. Appl! %5 ;I.
F. rag s?m?filter?example and lin( it to T)ME4?Example.. Wor(flow%5 6alidate and 4epositor! %5 #ave.
http://lh5.ggpht.com/_MbhSjEtmzI8/TapYJ7xnh3I/AAAAAAAAAOQ/I-EI-o-YbSw/s1600-h/clip_image001%5B5%5D%5B3%5D.jpg8/10/2019 Informatica by Deva
41/73
!E$ISI*N TAS3
The ecision tas( allows us to enter a condition that determines the execution of the
wor(flow, similar to a lin( condition.
The ecision tas( has a pre%defined variable called +ecision?tas(?name.condition
that represents the result of the decision condition.
The ower 9enter #erver evaluates the condition in the ecision tas( and sets the pre%
defined condition variable to True B0C or *alse B2C.
We can specif! one decision condition per ecision tas(.
Example9ommand Tas( should run onl! if either s?m?filter?example or
#?M?T;TAL?#AL?E@AMLE succeeds. )f an! of s?m?filter?example or
#?M?T;TAL?#AL?E@AMLE fails then #?m?sample?mapping?EM should run.
Steps for &reating wor(flow
0. Wor(flow %5 9reate %5 >ive name wf?decision?tas(?example %5 9lic( o(.1. rag s?m?filter?example and #?M?T;TAL?#AL?E@AMLE to wor(space and lin( both
of them to #TA4T tas(.
8. 9lic( Tas(s %5 9reate %5 #elect E9)#);7 from list. >ive name E9)#);7?Example.
9lic( 9reate and then done. Lin( E9)#);7?Example to both s?m?filter?example and
#?M?T;TAL?#AL?E@AMLE.
3. 4ight clic( E9)#);7?Example%5 E)T %5 >E7E4AL tab.
8/10/2019 Informatica by Deva
42/73
=. 7ow edit decision tas( again and go to 4;E4T)E# Tab. ;pen the Expression editor
b! clic(ing the 6AL'E section of ecision 7ame attribute and enter the following
condition: +#?M?*)LTE4?E@AMLE.#tatus - #'99EEE ;4
+#?M?T;TAL?#AL?E@AMLE.#tatus - #'99EEE
F. 6alidate the condition %5 9lic( Appl! %5 ;I.
. rag command tas( and #?m?sample?mapping?EM tas( to wor(space and lin( themto E9)#);7?Example tas(.
G. ouble clic( lin( between #?m?sample?mapping?EM P E9)#);7?Example P give
the condition: +E9)#);7?Example.9ondition - 2. 6alidate P clic( ;I.
02. ouble clic( lin( between 9ommand tas( and E9)#);7?Example and give the
condition: +E9)#);7?Example.9ondition - 0. 6alidate and clic( ;I.
00. Wor(flow 6alidate and repositor! #ave.
01. 4un wor(flow and see the result.
$*NTR*L TAS3
We can use the 9ontrol tas( to stop, abort, or fail the top%level wor(flow or the parentwor(flow based on an input lin( condition.
A parent wor(flow or wor(let is the wor(flow or wor(let that contains the 9ontrol tas(.
We give the condition to the lin( connected to 9ontrol Tas(.
Control Option Description
)ail Me )ails the control tas!.
)ail *arent Mar!s the status of the %) or wor!let that contains the
&ontrol tas! as failed.
Stop *arent Stops the %) or wor!let that contains the &ontrol tas!.
Abort *arent Aborts the %) or wor!let that contains the &ontrol tas!.
)ail op'+e"el %) )ails the wor!flow that is running.
Stop op'+e"el %) Stops the wor!flow that is running.
http://lh6.ggpht.com/_MbhSjEtmzI8/Tapa0iS5qEI/AAAAAAAAAOg/idQukEa5Y6E/s1600-h/clip_image002%5B5%5D%5B3%5D.jpg8/10/2019 Informatica by Deva
43/73
Abort op'+e"el
%)
Aborts the wor!flow that is running.
Examplerag an! 8 sessions and if an!one fails, then Abort the top level wor(flow.
Steps for &reating wor(flow
0. Wor(flow %5 9reate %5 >ive name wf?control?tas(?example %5 9lic( o(.
1. rag an! 8 sessions to wor(space and lin( all of them to #TA4T tas(.
8. 9lic( Tas(s %5 9reate %5 #elect 9;7T4;L from list. >ive name cntr?tas(.
3. 9lic( 9reate and then done.
8/10/2019 Informatica by Deva
44/73
1. Edit Wor(flow and add user defined variables.
8. 9hoose Tas(s%9reate. #elect Assignment Tas( for the tas( t!pe.
3. Enter a name for the Assignment tas(. 9lic( 9reate. Then clic( one.
8/10/2019 Informatica by Deva
45/73
Supplier_KeySupplier_CodeSupplier_Nae Supplier_State
,2- A& Acme Supply &o&A
)n this example, #upplier?9ode is the natural (e!and #upplier?Ie! is asurrogate (e!.
Technicall!, the surrogate (e! is not necessar!, since the table will be uni"ue b! the natural
(e! B#upplier?9odeC. Dowever, the &oins will perform better on an integer than on a character
string.
7ow imagine that this supplier moves their head"uarters to )llinois. The updated table would
simpl! overwrite this record:
Supplier_KeySupplier_CodeSupplier_Nae Supplier_State
,2- A& Acme Supply &o/+
The obvious disadvantage to this method of managing #9s is that there is no historical
record (ept in the data warehouse. ou canHt tell if !our suppliers are tending to move to the
Midwest, for example. ut an advantage to T!pe 0 #9s is that the! are ver! eas! to
maintain.
Explanation wit. an Example
So"r&e Ta#le @;
8/10/2019 Informatica by Deva
46/73
The necessit! of the loo(up transformation is illustrated using the above source and target
table.
So"r&e Ta#le @;
8/10/2019 Informatica by Deva
47/73
8/10/2019 Informatica by Deva
48/73
What Loo(up transformation does in our mapping is it loo(s in to the target table
Bemp?tableC and compares it with the #ource $ualifier and determines whether to
insert, update, delete or re&ect rows.
)n the orts tab we should add a new column and name it as empno0 and this is
column for which we are gonna connect from the #ource $ualifier.
The )nput ort for the first column should be unch(ed where as the other ports li(e
;utput and loo(up box should be chec(ed. *or the newl! created column onl! input
and output boxes should be chec(ed.
)n the roperties tab BiC Loo(up table name %5Emp?Target.
BiiCLoo( up olic! on Multiple Mismatch %5 use *irst 6alue.
BiiiC 9onnection )nformation %5;racle.
)n the 9onditions tab BiC 9lic( on Add a new condition
BiiCLoo(up Table 9olumn should be Empno, Transformation port should be Empno0 and
;perator should U-Q.
http://lh5.ggpht.com/_MbhSjEtmzI8/TarFaG7GX3I/AAAAAAAAAO4/Ez28BmcUdS0/s1600-h/clip_image004%5B4%5D.jpg8/10/2019 Informatica by Deva
49/73
Expression Transformation After we are done with the Loo(up Transformation we are using
an expression transformation to chec( whether we need to insert the records the same
records or we need to update the records. The steps to create an Expression Transformation
are shown below.
rag all the columns from both the source and the loo( up transformation and drop
them all on to the Expression transformation.
7ow double clic( on the Transformation and go to the orts tab and create two new
columns and name it as insert and update. oth these columns are gonna be our
output data so we need to have chec( mar( onl! in front of the ;utput chec( box.
The #nap shot for the Edit transformation window is shown below.
The condition that we want to parse through our output data are listed below.
)nput Y )s7ullBEM7;0C
;utput Y iifB7ot isnull BEM7;0C and ecodeB#AL,#AL0,0,2C-2,0,2C .
We are all done here .9lic( on appl! and then ;I.
http://lh3.ggpht.com/_MbhSjEtmzI8/TarFbt4m-xI/AAAAAAAAAPA/M6J69YEurP8/s1600-h/clip_image006%5B4%5D.jpg8/10/2019 Informatica by Deva
50/73
%ilter Transformation we are gonnahave twofilter transformations one to insert and other
to update.
9onnect the )nsert column from the expression transformation to the insert column in
the first filter transformation and in the same wa! we are gonna connect the update
column in the expression transformation to the update column in the second filter.
Later now connect the Empno, Ename, #al from the expression transformation to both
filter transformation.
)f there is no change in input data then filter transformation 0 forwards the complete
input to update strateg! transformation 0 and same output is gonna appear in the
target table.
)f there is an! change in input data then filter transformation 1 forwards the complete
input to the update strateg! transformation 1 then it is gonna forward the updated
input to the target table.
>o to the roperties tab on the Edit transformation
BiC The value for the filter condition 0 is )nsert.
BiiC The value for the filter condition 0 is 'pdate.
The 9loser view of the filter 9onnection is shown below.
http://lh6.ggpht.com/_MbhSjEtmzI8/TarFc1tInFI/AAAAAAAAAPI/I-xiUmCPhtc/s1600-h/clip_image008%5B4%5D.jpg8/10/2019 Informatica by Deva
51/73
Update Strateg, Transformation etermines whether to insert, delete, update or re&ect
the rows.
rag the respective Empno, Ename and #al from the filter transformations and drop
them on the respective 'pdate #trateg! Transformation.
7ow go to the roperties tab and the value for the update strateg! expression is 2 Bon
the 0stupdate transformationC.
7ow go to the roperties tab and the value for the update strateg! expression is 0 Bon
the 1ndupdate transformationC.
We are all set here finall! connect the outputs of the update transformations to the
target table.
Step :: 9reate the tas( and 4un the wor( flow.
onQt chec( the truncate table option.
9hange ul( to the 7ormal.
4un the wor( flow from tas(.
Step =review the ;utput in the target table.
8/10/2019 Informatica by Deva
52/73
W;4I*L;W 6A4)ALE#
17We can create and use variables in a wor(flow to reference values and record information.
T,pes of wor(flow )aria#les
redefined wor(flow variables
'ser%defined wor(flow variables
Predefined wor(flow )aria#les
The Wor(flow Manager provides predefined wor(flow variables for tas(s within a wor(flow.
T!pes of redefined wor(flow variables are:
S,stem )aria#les
'se the ##ATE and W;4I*L;W#TA4TT)ME s!stem variables within a wor(flow.
http://informaticatutorials-naveen.blogspot.com/2011/04/workflow-variables.htmlhttp://lh5.ggpht.com/_MbhSjEtmzI8/TarFeYhJFeI/AAAAAAAAAPQ/vn7kX-BASes/s1600-h/clip_image010%5B4%5D.jpghttp://informaticatutorials-naveen.blogspot.com/2011/04/workflow-variables.htmlhttp://informaticatutorials-naveen.blogspot.com/2011/04/workflow-variables.html8/10/2019 Informatica by Deva
53/73
Tas(2spe&ifi& )aria#les
The Wor(flow Manager provides a set of tas(%specific variables for each tas( in the wor(flow.
The Wor(flow Manager lists tas(%specific variables under the tas( name in the Expression
Editor.
Task!speci"ic
#ariable
Description Task Type
&ondition Result of decision condition
e3pression. (U++ if tas! fails.
Decision as!
$ndime Date and time when a tas! ended. All as!s
$rror&ode +ast error code for the associatedtas!. 0 if there is no error. All as!s
$rrorMsg +ast error message for the
associated tas!. $mpty String if
there is no error.
All as!s
)irst$rror&ode $rror code for the first error
message in the session. 0 if thereis no error.
Session
)irst$rrorMsg )irst error message in the session.$mpty String if there is no error.
Session
*re"as!Status Status of the pre"ious tas! in the
wor!flow that /S ran. &an be
A4R$D5 )A/+$D5
S4**$D5
SU&&$$D$D.
All as!s
Src)ailedRows otal number of rows the
/ntegration Ser"ice failed to readfrom the source.
Session
SrcSuccessRows otal number of rows
successfully read from the
sources.
Session
Startime Date and time when tas! started. All as!s
8/10/2019 Informatica by Deva
54/73
Status Status of the pre"ious tas! in the
wor!flow. &an be A4R$D5
D/SA+$D5 )A/+$D5(4SAR$D5SAR$D5
S4**$D5 SU&&$$D$D.
All as!s
gt)ailedRows otal number of rows the
/ntegration Ser"ice failed to write
to the target.
Session
gtSuccessRows otal number of rows
successfully written to the target
Session
otalrans$rrors otal number of transformation
errors.
Session
User2!efined 'or(flow Varia#les
We can create variables within a wor(flow. When we create a variable in a wor(flow, it is valid
onl! in that wor(flow. 'se the variable in tas(s within that wor(flow. We can edit and delete
user%defined wor(flow variables.
)ntegration #ervice holds two different values for a wor(flow variable during a wor(flow run:
#tart value of a wor(flow variable
9urrent value of a wor(flow variable
The )ntegration #ervice loo(s for the start value of a variable in the following order:
0. 6alue in parameter file
1. 6alue saved in the repositor! Bif the variable is persistentC
8. 'ser%specified default value
3. ata t!pe default value
ersistent means value is saved to the repositor!.
To create a wor(flow variable:
0. )n the Wor(flow esigner, create a new wor(flow or edit an existing one.
1. #elect the 6ariables tab.
8. 9lic( Add and enter a name for the variable.
3. )n the ata t!pe field, select the data t!pe for the new variable.
8/10/2019 Informatica by Deva
55/73
8/10/2019 Informatica by Deva
56/73
transformations, mapping and session. After identif!ing the bottlenec(, appl! the tuning
mechanisms in whichever wa! the! are applicable to the pro&ect.
Identif, #ottlene&( in So"r&e
)f source is a relational table, put a filter transformation in the mapping, &ust after source
"ualifierZ ma(e the condition of filter to *AL#E. #o all records will be filtered off and none will
proceed to other parts of the mapping.)n original case, without the test filter, total time ta(en
is as follows:%
Total Time time ta(en #, @so"r&e C transformations C target load
7ow because of filter, Total Time = time taken by source
#o if source was fine, then in the latter case, session should ta(e less time. #till if the session
ta(es near e"ual time as former case, then there is a source bottlenec(.
Identif, #ottlene&( in Target
)f the target is a relational table, then substitute it with a flat file and run the session. )f the
time ta(en now is ver! much less than the time ta(en for the session to load to table, then the
target table is the bottlenec(.
Identif, #ottlene&( in Transformation
4emove the transformation from the mapping and run it. 7ote the time ta(en.Then put the
transformation bac( and run the mapping again. )f the time ta(en now is significantl! more
than previous time, then the transformation is the bottlenec(.
ut removal of transformation for testing can be a pain for the developer since that might
re"uire further changes for the session to get into the Uwor(ing modeQ.
#o we can put filter with the *AL#E condition &ust after the transformation and run the session.
)f the session run ta(es e"ual time with and without this test filter,then transformation is thebottlenec(.
Identif, #ottlene&( in sessions
We can use the session log to identif! whether the source, target or transformations are the
performance bottlenec(. #ession logs contain thread summar! records li(e the following:%
8/10/2019 Informatica by Deva
57/73
8/10/2019 Informatica by Deva
58/73
This mapping contains an Expression transformation that creates an item ) based on the
store number
8/10/2019 Informatica by Deva
59/73
When !ou run a session configured for source%side pushdown optimiNation, the )ntegration
#ervice anal!Nes the mapping from the source to the target or until it reaches a downstream
transformation it cannot push to the source database.
The )ntegration #ervice generates and executes a #ELE9T statement based on the
transformation logic for each transformation it can push to the database. Then, it reads the
results of this #$L "uer! and processes the remaining transformations.
R"nning Target2Side P"s.down *ptimi-ation Sessions
When !ou run a session configured for target%side pushdown optimiNation, the )ntegration
#ervice anal!Nes the mapping from the target to the source or until it reaches an upstream
transformation it cannot push to the target database. )t generates an )7#E4T, ELETE, or
'ATE statement based on the transformation logic for each transformation it can push to
the target database. The )ntegration #ervice processes the transformation logic up to the point
that it can push the transformation logic to the database. Then, it executes the generated #$L
on the Target database.
R"nning %"ll P"s.down *ptimi-ation Sessions
To use full pushdown optimiNation, the source and target databases must be in the same
relational database management s!stem. When !ou run a session configured for full pushdown
optimiNation, the )ntegration #ervice anal!Nes the mapping from the source to the target or
until it reaches a downstream transformation it cannot push to the target database. )t
generates and executes #$L statements against the source or target based on the
transformation logic it can push to the database.
When !ou run a session with large "uantities of data and full pushdown optimiNation, the
database server must run a long transaction. 9onsider the following database performance
issues when !ou generate a long transaction:
A long transaction uses more database resources.
A long transaction loc(s the database for longer periods of time. This reduces database
concurrenc! and increases the li(elihood of deadloc(.
A long transaction increases the li(elihood of an unexpected event. To minimiNe
database performance issues for long transactions, consider using source%side or
target%side pushdown optimiNation.
8/10/2019 Informatica by Deva
60/73
R"les and G"idelines for %"n&tions in P"s.down *ptimi-ation
'se the following rules and guidelines when pushing functions to a database:
)f !ou use A?T;?ATE in transformation logic to change da!s, hours, minutes, or
seconds, !ou cannot push the function to a Teradata database.
When !ou push LA#T?A BC to ;racle, ;racle returns the date up to the second. )f
the input date contains sub seconds, ;racle trims the date to the second.
When !ou push LT4)M, 4T4)M, or #;'7E@ to a database, the database treats the
argument BH HC as 7'LL, but the )ntegration #ervice treats the argument BH HC as spaces.
An )M 1 database and the )ntegration #ervice produce different results for
#TE6 and 6A4)A79E. )M 1 uses a different algorithm than other databases to
calculate #TE6 and 6A4)A79E.
When !ou push ##ATE or ##T)ME#TAM to the database, the database server
returns the timestamp in the time None of the database server, not the )ntegration
#ervice.
)f !ou push ##T)ME#TAM to an )M 1 or a #!base database, and !ou specif! the
format for ##T)ME#TAM, the database ignores the format and returns the complete
time stamp.
ou can push ##T)ME#TAM BU##QC to a 7eteNNa database, but not ##T)ME#TAM
BUM#QC or ##T)ME#TAM BU'#QC.
When !ou push T;?9DA4 BATEC or T;?ATE BC to 7eteNNa, dates with sub second
precision must be in the %MM% DD13: M): ##.'# format. )f the format is
different, the )ntegration #ervice does not push the function to 7eteNNa.
S&D 2 6&omplete7:
+et us dri"e the point home using a simple scenario. )or eg.5 in the current month ie.560,'
0,'20,07 we are pro"ided with an source table with the three columns and three rows in it
li!e 6$Mpno5$name5Sal7. here is a new employee added and one change in the records
in the month 60,'02'20,07. %e are gonna use the S&D'2 style to e3tract and load the
records in to target table.
The thing to be noticed here is if there is an! update in the salar! of an! emplo!ee
then the histor! of that emplo!ee is displa!ed with the current date as the start date
and the previous date as the end date.
8/10/2019 Informatica by Deva
61/73
Source Table: (01-01-11)
$mp no $name Sal
,0, A ,000
,02 2000
,0- & -000
Target Table: (01-01-11)
S!ey $mp no $name Sal S'date $'date 8er )lag
,00 ,0, A ,000 0,'0,',0 (ull , ,
200 ,02 2000 0,'0,',0 (ull , ,
-00 ,0- & -000 0,'0,',0 (ull , ,
Source Table: (01-02-11)
$mp no $name Sal
,0, A ,000
,02 2100
,0- & -000
,0 D 000
Target Table: (01-02-11)
S!ey $mp no $name Sal S'date $'date 8er )lag
,00 ,0, A ,000 0,'02',0 (ull , ,
200 ,02 2000 0,'02',0 (ull , ,
-00 ,0- & -000 0,'02',0 (ull , ,
20, ,02 2100 0,'02',0 0,'0,',0 2 0
00 ,0 D 000 0,'02',0 (ull , ,
8/10/2019 Informatica by Deva
62/73
)n the second Month we have one more emplo!ee added up to the table with the Ename and
salar! of the Emplo!ee is changed to the 1
8/10/2019 Informatica by Deva
63/73
8/10/2019 Informatica by Deva
64/73
target table. We specif! the condition here whether to insert or to update the table. The steps
to create an Expression Transformation are shown below.
rag all the columns from both the source and the loo( up transformation and drop
them all on to the Expression transformation.
7ow double clic( on the Transformation and go to the orts tab and create two new
columns and name it as insert and update. oth these columns are goanna be our
output data so we need to have unchec(ed input chec( box.
The #nap shot for the Edit transformation window is shown below.
The condition that we want to parse through our output data are listed below.
)nsert : )s7ullBEmp7;0C
'pdate: iifB7ot isnull B#(e!C and ecodeB#AL,#AL0,0,2C-2,0,2C .
We are all done here .9lic( on appl! and then ;I.
%ilter Transformation We need two filter transformations the purpose the first filter is to
filter out the records which we are goanna insert and the next is vice versa.
)f there is no change in input data then filter transformation 0 forwards the complete
input to Exp 0 and same output is goanna appear in the target table.
http://lh3.ggpht.com/_MbhSjEtmzI8/Ta-eGZS6P-I/AAAAAAAAAZI/dvnzaKaLwyo/s1600-h/clip_image006%5B4%5D.jpg8/10/2019 Informatica by Deva
65/73
)f there is an! change in input data then filter transformation 1 forwards the complete
input to the Exp 1 then it is gonna forward the updated input to the target table.
>o to the roperties tab on the Edit transformation
BiC The value for the filter condition 0 is )nsert.
BiiC The value for the filter condition 1 is 'pdate.
The closer view of the connections from the expression to the filter is shown below.
SeD"en&e Generator We use this to generate an incremental c!cle of se"uential range of
number.The purpose of this in our mapping is to increment the s(e! in the bandwidth of 0220
http://lh4.ggpht.com/_MbhSjEtmzI8/Ta-eH1Cl-dI/AAAAAAAAAZQ/i95sBXFn6W4/s1600-h/clip_image008%5B4%5D.jpg8/10/2019 Informatica by Deva
66/73
We are gonna have a se"uence generator and the purpose of the se"uence generator
is to increment the values of the s(e! in the multiples of 022 Bbandwidth of 022C.
9onnect the output of the se"uence transformation to the Exp 0.
Expression Transformation
Exp
8/10/2019 Informatica by Deva
67/73
Exp 5 )f same emplo!ee is found with an! updates in his records then #(e! gets added b! 0
and version changes to the next higher number,*
rag all the columns from the filter 1 to the Exp 1.
7ow add a new column as 7?s(e! and the expression for it is gonna be #(e!0.
oth the #?date and E?date is gonna be s!sdate.
Exp : )f an! record of in the source table gets updated then we ma(e it onl! as the output.
)f change is found then we are gonna update the E?ate to #?ate.
Update Strateg, This is place from where the update instruction is set on the target table.
The update strateg! expression is set to 0.
http://lh6.ggpht.com/_MbhSjEtmzI8/Ta-eMsDEg5I/AAAAAAAAAZw/4__4gjCWx-A/s1600-h/clip_image016%5B4%5D.jpghttp://lh6.ggpht.com/_MbhSjEtmzI8/Ta-eLT3tXpI/AAAAAAAAAZo/W0OHfOm3vlk/s1600-h/clip_image014%5B4%5D.jpg8/10/2019 Informatica by Deva
68/73
Step :9reate the tas( and 4un the wor( flow.
onQt chec( the truncate table option.
9hange ul( to the 7ormal.
4un the wor( flow from tas(.
9reate the tas( and run the wor( flow.
#9 T!pe 8
26
S$! T,pe :
This Method has limited histor! preservation, and we are goanna use s(e! as the rimar! (e!
here.
So"r&e ta#le @;
8/10/2019 Informatica by Deva
69/73
$pno $nae Sal
&'&
&'(
&')
A
*
C
&'''
+,--
)'''
Target Ta#le @;
8/10/2019 Informatica by Deva
70/73
8/10/2019 Informatica by Deva
71/73
%ilter 5
rag the update and other four ports which came from Loo( up in to the Expression in
to #econd filter.
)n the roperties tab specif! the *ilter condition as update.
Update Strateg, *inall! we need the update strateg! to insert or to update in to the target
table.
Update Strateg,
8/10/2019 Informatica by Deva
72/73
*inall! connect both the update strateg! in to two instances of the target.
Step : 9reate a session for this mapping and 4un the wor( flow.
Step = ;bserve the output it would same as the second target table
Step =review the ;utput in the target table.
http://lh6.ggpht.com/_MbhSjEtmzI8/Ta-eN9uP1mI/AAAAAAAAAZ4/5CTMRkAKW8E/s1600-h/clip_image018%5B4%5D.jpghttp://lh4.ggpht.com/_MbhSjEtmzI8/TbaWODLFVcI/AAAAAAAAAdc/DQR_DfVlso0/s1600-h/clip_image014%5B3%5D.jpg8/10/2019 Informatica by Deva
73/73
http://lh3.ggpht.com/_MbhSjEtmzI8/Ta-eOxmoiJI/AAAAAAAAAaA/48feVJN3jiw/s1600-h/clip_image020%5B4%5D.jpgTop Related