powercenter level1 unit13
DESCRIPTION
Presentation of PowerCenter Level 1 Labs Unit13TRANSCRIPT
-
7/21/2019 PowerCenter Level1 Unit13
1/17
1
Unit 13. Unconnected Lookup,
Parameters and Variables
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
2/17
2
Unit Objectives
Understand why and how to use: Unconnected Lookup transformation
System variables
Mapping parameters and variables
Use these features in a mapping
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
3/17
3
Unconnected Lookup
Function in the !pressioncalls the Unconnected Lookup
Unconnected Lookup
Commonly used when a lookup not needed for every record No links fromto other transformations Lookup data is called at the point in the mapping that needs it Lookup function can be set within any transformation that supports
e!pressions
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
4/17
"
#allin$ an Unconnected Lookup
Condition is evaluated for each row but Lookup functionis called only if condition is satisfied
%%F & %'(ULL&customer)id*,+lkp.-LOOUP&order)no*, customer)id*
Condition
Lookup function
Row keys
(passed to Lookup)
Use Lookup function within a conditional statement
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
5/17
/
Unconnected Lookup 0dvanta$es
"ata lookup is performed only for those rows whichre#uire it$ Substantial performance can be gained
EXAMPLE: % Mapping will process &''(''' rows$ )or twopercent of those rows *+'(''', the item-id value is NULL$
.tem-." can be derived from the S/U-NUM0$
(et savin$s ", lookups
%%F & %'(ULL&item)id*, +lkp.-LOOUP &sku)numb*, item)id*
Condition(true for 2 percent o4 all
ro5s*
Lookup&called onl6 5hen condition is
true*
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
6/17
7
Unconnected Lookup Functionalit6
Onelookup port valuemay be returned for each lookup
Must check a
1eturn port in the2orts tab( ore!pression thatcalls lookup isinvalid$
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
7/17
8
#onnected versus Unconnected Lookups
#O((#9: LOOUP U(#O((#9: LOOUP
Part o4 the mappin$ data 4lo5 'eparate 4rom the mappin$ data4lo5
;eturns multiple values &b6linkin$ output ports to anothertrans4ormation*
;eturns one value < b6 checkin$the ;eturn &;* port option 4or theoutput port that provides thereturn value
!ecuted 4or ever6 recordpassin$ throu$h thetrans4ormation
Onl6 e!ecuted 5hen the lookup4unction is called
ore visible, sho5s 5here thelookup values are used
Less visible, as the lookup iscalled 4rom an e!pression 5ithinanother trans4ormation
:e4ault values are used :e4ault values are i$nored
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
8/17
=
>oins versus Lookups
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
9/17
'6stem Variables
''''90;99%
???'ess'tart9ime
1eturns the system date value on the2owerCenter Server
Used with any function that acceptstransformation datetime datatypes Not to be used in a S3L override 4as a constant value
1eturns the system date value as astring. Uses system clock on machine
hosting 2owerCenter Server )ormat of the string is database type
dependent Used in S3L override 4as a constant value
'-':09
2rovides current datetime on the2owerCenter Server machine Not a static value
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
10/17
1
appin$ Parameters and Variables
1epresent declared values: 5ariablescan change in value during run6time
2arametersremain constant during run6time
%pply to all transformations within a mapping
"eclared in Mappings menu )ormat is 77VariableName or 77ParameterName
Setinitiali8ed in a parameter file( specified for the session task
2rovide increased development fle!ibility Can be used in pre and post6S3L
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
11/17
11
:eclarin$ appin$ Parameters andVariables
Mappings 9 "eclare 2arametersand 5ariables menu option
Setaggregationtype
Set optional
initial value
User6defined
*77name,
Set datatype
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
12/17
12
Usin$ appin$ Parameters andVariables
Use parameters or variables in e!pression
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
13/17
13
Functions to 'et appin$ Variables
'90@V0;%0AL&??Variable,value*
'9%(V0;%0AL&??Variable,value*
'9V0;%0AL&??Variable,value*
'9#OU(9V0;%0AL&??Variable*
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
14/17
1"
Parameters B Variables C %nitialiDationPriorit6
Parameters
1. Parameter 4ile
2. :eclared initial value
3. :e4ault value
Variables
1. Parameter 4ile
2. ;epositor6 value
3. :eclared initial value
". :e4ault value
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
15/17
1/
Unit 13 LabLoad 'ales Fact 9able
Staging%rea
"ataarehouse
'9E)9;0('0#9%O(''9E)P0-(9
&tables*
F0#9)'0L'&table*
#reate a mappin$ usin$+'ource uali4ier 4or homo$eneous joinUnconnected Lookup trans4ormation0$$re$ator trans4ormationappin$ parameters
#reateGrun a 5ork4lo5 4or this mappin$
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
16/17
17
Lab ;evie5
hat did we accomplish with this lab;
3uestions;
Unit 13
-
7/21/2019 PowerCenter Level1 Unit13
17/17
18
Unit 13 uiD
hen would you use : Unconnected Lookup transformation;
Mapping parameters and variables;
Unit 13