INFORMATICA
PREPARED BY:
Ammar Hasan
CONTENTS CHAPTER 1: TOOL KNOWLEDGE 1.1 Informatica PowerCenter 1.2 Product Overview 1.2.1 PowerCenter Domain 1.2.2 Administration Console 1.2.3 PowerCenter Repository 1.2.4 PowerCenter Client 1.2. Repository !ervice 1.2." I#$%&RA$IO# !%R'IC% 1.2.( )%* !%R'IC%! +,* 1.2.- DA$A A#A/0%R 1.2. %$ADA$A A#A&%R
CHAPTER 2: REPOSITORY MANAGER 2.1 Addin a Repository to te #aviator 2.2 Confiurin a Domain Connection 2.3 Connectin to a Repository R epository 2.4 'iewin O56ect Dependencies 2. 'alidatin ultiple O56ects 2." Comparin Repository O56ects 2.( $runcatin )or7flow and !ession o %ntries 2.- anain ,ser Connections and oc7s 2. anain ,sers and &roups 2.18 )or7in wit 9olders
CHAPTER 3: DESIGNER 3.1 !ource Analy:er 3.1.1 )or7in wit Relational !ources 3.1.2 )or7in wit 9lat 9iles 3.2 $aret Desiner 3.3 appins 3.4 $ransformations 3.4.1 )or7in wit Ports 3.4.2 ,sin Default 'alues for Ports 3.4.3 ,ser;Defined Default 'alues 3. $racin evels 3." *asic 9irst appin 3.( %
3.18 ,nion $ransformation 3.11 !orter $ransformation $ ransformation 3.12 Ran7 $ransformation 3.13 Areator $ransformation $ ransformation 3.14 =oiner $ransformation $ransformation 3.1 !ource >ualifier 3.1" oo7up $ransformation 3.1".1 oo7up $ypes 3.1".2 oo7up $ransformation Components 3.1".3 Connected oo7up $ransformation 3.1".4 ,nconnected oo7up $ransformation 3.1". oo7up Cace $ypes? Dynamic@ !tatic@ Persistent@ !ared 3.1( ,pdate !tratey 3.1- Dynamic oo7up Cace ,se 3.1 oo7up >uery 3.28 oo7up and ,pdate !tratey %
CHAPTER 4: WORKFLOW MANAGER 4.1 Informatica Arcitecture 4.1.1 Interation !ervice Process 4.1.2 oad *alancer 4.1.3 D$ Process 4.1.4 Processin $reads 4.1. Code Paes and Data ovement 4.1." Output 9iles and Caces 4.2 )or7in wit )or7flows 4.2.1 Assinin an Interation !ervice 4.2.2 )or7in wit in7s 4.2.3 )or7flow 'aria5les 4.2.4 !ession Parameters
4.3 )or7in wit $as7s 4.3.1 !ession $as7 4.3.2 %mail $as7 4.3.3 Command $as7 4.3.4 )or7in wit %vent $as7s 4.3. $imer $as7 4.3." Decision $as7 4.3.( Control $as7 4.3.- Assinment $as7 4.4 !cedulers 4. )or7lets 4." Partitionin 4.".1 Partitionin Attri5utes 4.".2 Partitionin $ypes 4.".3 !ome Points 4.( !ession Properties 4.- )or7flow Properties
Chapter 1
Informatica PowerCenter
CHAPTER 1: TOOL KNOWLEDGE 1.1 INFORMATICA POWERCENTER Informatica PowerCenter is a powerful %$ tool from Informatica Corporation. Informatica Corporation products are? Informatica PowerCenter Informatica on demand Informatica *2* Data %uality Informatica Data %
Infor!"#$! Po%&rC&n"&r is a sinle@ unified enterprise data interation platform for accessin@ discoverin@ and interatin data from virtually any 5usiness system@ in any format@ and deliverin tat data trouout te enterprise at any speed.
Infor!"#$! Po%&rC&n"&r E'#"#on( *ecause every data interation pro6ect is di fferent and includes many varia5les suc as data volumes@ latency reBuirements@ I$ infrastructure@ and metodoloies Informatica offers tree PowerCenter %ditions and a suite of PowerCenter Options to meet your pro6ectEs and orani:ationEs specific needs. !tandard %dition Real $ime %dition Advanced %dition • • •
Infor!"#$! Po%&rC&n"&r S"!n'!r' E'#"#on PowerCenter !tandard %dition is a sinle@ unified enterprise data interation platform for discoverin@ accessin@ and interatin data from virtually any 5usiness system@ in any format@ and deliverin tat data trouout te enterprise to improve operational efficiency. ey features include? A i;performance data interation server A lo5al metadata infrastructure 'isual tools for development and centrali:ed administration Productivity tools to facilitate colla5oration amon arcitects@ analysts@ and developers • • • •
Infor!"#$! Po%&rC&n"&r R&!) T#& E'#"#on Pac7aed for simplicity and fle
Infor!"#$! Po%&rC&n"&r R&!) T#& E'#"#on PowerCenter Advanced %dition addresses reBuirements for orani:ations tat are standardi:in data interation at an enterprise level@ across a num5er of pro6ects and departments. It com5ines all te capa5ilities of PowerCenter !tandard %dition and features additional capa5ilities tat are ideal for data overnance and Interation Competency Centers. ey features include? Dynamic partitionin wit data smart parallelism Powerful metadata analysis capa5ilities )e5;5ased data profilin and reportin capa5ilities • • •
Infor!"#$! Po%&rC&n"&r O*"#on( A rane of options are availa5le to e
D!"! C)&!n(& !n' M!"$+ O*"#on features powerful@ interated cleansin and matcin capa5ilities to correct and remove dupli cate customer data. D!"! F&'&r!"#on O*"#on ena5les a com5ination of traditional pysical and virtual data interation in a sinle platform.
D!"! M!(,#n- O*"#on protects sensitive@ private information 5y mas7in it in flit to produce realistic;loo7in data@ reducin te ris7 of security and compliance 5reaces.
En"&r*r#(& Gr#' O*"#on enances scala5ility and delivers optimal performance wile reducin environments.
te
administrative
overead
of
supportin
rid
computin
H#-+ A!#)!/#)#"0 O*"#on minimi:es service interruptions durin ardware andHor software outaes and reduces costs associated wit data downtime.
M&"!'!"! E$+!n-& O*"#on( coordinate tecnical and 5usiness metadata from data modelin tools@ 5usiness intellience tools@ source and taret data5ase catalos@ and PowerCenter repositories .
P!r"#"#on#n-
O*"#on elps I$ orani:ations ma
T&!B!(&'
D&&)o*&n"
O*"#on
facilitates colla5oration amon development@ Buality assurance@ and production administration teams and across eorapically disparate teams.
5n("r$"r&' D!"! O*"#on e
1.2 PROD5CT O6ER6IEW PowerCenter provides an environment tat allows you to load data into a centrali:ed location@ suc as a data wareouse or operational data store FOD!G. /ou can e
1.2.1 POWERCENTER DOMAIN: PowerCenter as a service;oriented arcitecture tat provides te a5ility to scale services and sare resources across multiple macines. PowerCenter provides te PowerCenter domain to support te administration of te PowerCenter services. A domain is te primary unit for manaement and administration of services in PowerCenter. A domain contains te followin components? One or more nodes !ervice anaer Application services • • •
On& or or& no'&(: A node is te loical representation of a macine in a domain. A domain may contain more tan one node. $e node tat osts te domain is te master ateway for te domain. /ou can add oter macines as nodes in te domain and confiure te nodes to run application services@ suc as te Interation !ervice or Repository !ervice. All service reBuests from oter nodes in te domain o trou te master ateway.
S&r#$& M!n!-&r: $e !ervice anaer is 5uilt in to te domain to support te domain and te application services. $e !ervice anaer runs on eac node in te domain. $e !ervice anaer starts and runs te application services on a macine.
$e !ervice anaer performs te followin functions? Alerts? Provides notifications a5out domain and service events. Autentication? Autenticates user reBuests Autori:ation? Autori:es user reBuests for services. Domain confiuration? anaes domain confiuration metadata. #ode confiuration? anaes node confiuration metadata. icensin? Reisters license information and verifies license information oin? Provides accumulated lo events from eac service in te domain. • • • • • • •
A**)#$!"#on (&r#$&(: A roup of services tat represent PowerCenter server; 5ased functionality. Repository !ervice? anaes connections to te PowerCenter repository. Interation !ervice? Runs sessions and wor7flows. )e5 !ervices +u5? %
•
1.2.2 ADMINISTRATION CONSOLE $e Administration Console is a we5 application tat we use to manae a PowerCenter domain. If we ave a user loin to te domain@ you can access te Administration Console. Domain o56ects include services@ nodes@ and licenses. ,se te Administration Console to perform te followin tas7s in te domain? M!n!-& !**)#$!"#on (&r#$&(: anae all application services in te domain@ suc as te Interation !ervice and Repository !ervice. Conf#-r& no'&(: Confiure node properties@ suc as te 5ac7up directory and resources. )e can also sut down and restart n odes. M!n!-& 'o!#n o/7&$"(: Create and manae o56ects suc as services@ nodes@ licenses@ and folders. 9olders allow you to orani:e domain o56ects and to manae security 5y settin permissions for domain o56ects. 6#&% !n' &'#" 'o!#n o/7&$" *ro*&r"#&(: /ou can view and edit properties for all o56ects in te domain@ includin te domain o56ect. 6#&% )o- &&n"(: ,se te o 'iewer to view domain@ Interation !ervice@ !AP *) !ervice@ )e5 !ervices +u5@ and Repository !ervice lo events. Oter domain manaement tas7s include appl yin licenses@ manain rids and resources@ and confiurin security.
1.2.3 POWERCENTER REPOSITORY $e PowerCenter repository resides in a relational data5ase. $e repository data5ase ta5les contain te instructions reBuired to e
G)o/!) r&*o(#"or0: $e lo5al repository is te u5 of te repository domain. ,se te lo5al repository to store common o56ects tat multiple developers can use trou sortcuts. $ese o56ects may include operational or Application source definitions@ reusa5le transformations@ mapplets@ and mappins.
Lo$!) r&*o(#"or#&(: A local repository is any repository witin te domain tat is not te lo5al repository. ,se local repositories for development. 9rom a local repository@ you can create sortcuts to o56ects in sared folders in te lo5al repository. $ese o56ects include source definitions@ common dimensions and loo7ups@ and enterprise standard transformations. /ou can also create copies of o56ects in non;sared folders. PowerCenter supports versioned repositories. A versioned repository can store multiple versions of an o56ect. PowerCenter version control allows you to efficiently develop@ test@ and deploy metadata into production.
1.2.4 POWERCENTER CLIENT $e PowerCenter Client consists of te followin applications tat we use to manae te repository@ desin mappins@ mapplets@ and create sessions to l oad te data? Desiner Data !tencil Repository anaer )or7flow anaer )or7flow onitor • • • • •
D&(#-n&r: ,se te Desiner to create mappins tat contain transformation instructions for te Interation !ervice. $e Desiner as te followin tools tat you use to analy:e sources@ desin taret scemas@ and 5uild source;to;taret mappins? Sor$& An!)0&r: Import or create source definitions. T!r-&" D&(#-n&r: Import or create taret definitions. Tr!n(for!"#on D&&)o*&r: Develop transformations to use in mappins. /ou can also develop user;defined functions to use in e
•
•
D!"! S"&n$#) ,se te Data !tencil to create mappin template tat can 5e used to enerate multiple mappins. Data !tencil uses te icrosoft Office 'isio interface to create mappin templates. No" (&' /0 ! '&&)o*&r (!))0.
R&*o(#"or0 M!n!-&r ,se te Repository anaer to administer repositories. /ou can naviate trou multiple folders and repositories@ and complete te followin tas7s? •
M!n!-& (&r( !n' -ro*(: Create@ edit@ and delete repository users and user roups. )e can assin and revo7e repository privilees and folder permissions.
•
P&rfor fo)'&r fn$"#on(: Create@ edit@ copy@ and delete folders. )or7 we perform in te Desiner and )or7flow anaer is stored in folders. If we want to sare metadata@ you can confiure a folder to 5e sared.
•
6#&% &"!'!"!: Analy:e sources@ tarets@ mappins@ and sortcut dependencies@ searc 5y 7eyword@ and view te properties of repository o56ects.
)e create repository o56ects usin te Desiner and )or7flow anaer Client tools. )e can view te followin o56ects in te #aviator window of te Repository anaer? Sor$& '&f#n#"#on(: Definitions of data5ase o56ects Fta5les@ views@ synonymsG or files tat provide source data. T!r-&" '&f#n#"#on(? Definitions of data5ase o56ects or files tat contain te taret data. M!**#n-(: A set of source and taret definitions alon wit transformations containin 5usiness loic tat you 5uild into te transformation. $ese are te instructions tat te Interation !ervice uses to transform and move data. R&(!/)& "r!n(for!"#on(? $ransformations tat we use in multiple mappins. M!**)&"(: A set of transformations tat you use in multiple mappins. S&((#on( !n' %or,f)o%(: !essions and wor7flows store information a5out ow and wen te Interation !ervice moves data. A wor7flow is a set of instructions tat descri5es ow and wen to run tas7s related to e
Wor,f)o% M!n!-&r ,se te )or7flow anaer to create@ scedule@ and run wor7flows. A wor7flow is a set of instructions tat descri5es ow and wen to run tas7s related to e
Wor,f)o% Mon#"or ,se te )or7flow onitor to monitor sceduled and runnin wor7flows for eac Interation !ervice. )e can view details a5out a wor7flow or tas7 in &antt Cart view or $as7 view. )e can run@ stop@ a5ort@ and resume wor7flows from te )or7flow onitor. )e can view sessions and wor7flow lo events in te )or7flow onitor o 'iewer. $e )or7flow onitor displays wor7flows tat ave run at least once. $e )or7flow onitor continuously receives information from te Interation !ervice and Repository !ervice. It also fetces information from te repository to display istoric information.
1.2.8 REPOSITORY SER6ICE All repository client applications access te repository data5ase ta5les trou te Repository !ervice. $e Repository !ervice protects metadata in te repository 5y manain repository connections and usin o56ect;loc7in to ensure o56ect consistency. $e Repository !ervice also notifies us wen anoter user modifies or deletes repository o56ects we are usin. %ac Repository !ervice manaes a sinle repository data5ase. )e can confiure a Repository !ervice to run on multiple macines@ or nodes@ in te domain. %ac instance runnin on a node is called a R&*o(#"or0 S&r#$& *ro$&((. *ro$&((. $is process accesses te data5ase ta5les and performs most repository;related tas7s. $e Repository !ervice uses native drivers to communicate wit te repository data5ase. A r&*o(#"or0 'o!#n #( ! -ro* of r&*o(#"or#&( "+!" 0o $!n $onn&$" "o (#)"!n&o()0 #n "+& Po%&rC&n"&r C)#&n". T+&0 (+!r& &"!'!"! "+ro-+ ! (*&$#!) "0*& of r&*o(#"or0 $!))&' ! -)o/!) r&*o(#"or0. $e Repository !ervice is a separate@ multi;treaded process tat retrieves@ inserts@ and updates metadata in te repository data5ase ta5les. $e Repository !ervice ensures te consistency of metadata in te repository. A Repository !ervice process is an instance of te Repository !ervice tat runs on a particular macine@ or node. $e Repository !ervice accepts connection reBuests from te followin applications?
Po%&rC&n"&r C)#&n": ,se te Desiner and )or7flow anaer to create and store mappin metadata and connection o56ect information in te repository. ,se te )or7flow onitor to retrieve wor7flow run status information and session los written 5y te Interation !ervice. ,se te Repository anaer to orani:e and secure metadata 5y creatin folders@ users@ and roups.
Co!n' )#n& *ro-r!( *r&* !n' #nf!$': ,se pmrep to perform repository metadata administration tas7s@ suc as listin repository o56ects or creatin and editin users and roups. ,se infacmd to perform service;related functions@ suc as creatin or removin a Repository R epository !ervice.
In"&-r!"#on S&r#$& 9IS? 9IS? )en we start te I!@ it connects to te repository to scedule wor7flows. )en we run a wor7flow@ te I! retrieves wor7flow tas7 and mappin metadata from te repository. I! writes wor7flow status to te repository .
W&/ S&r#$&( H/: )en we start te )e5 !ervices +u5@ it connects to te repository to access we5;ena5led wor7flows. $e )e5 !ervices +u5 retrieves wor7flow tas7 and mappin metadata from te repository and writes wor7flow status to te repository.
SAP BW S&r#$&: istens for R9C reBuests from !AP #et)eaver *) and initiates wor7flows to e
)e install te Repository !ervice wen we install PowerCenter !ervices. After we install te PowerCenter !ervices@ we can use te Administration Console to manae te Repository !ervice.
R&*o(#"or0 Conn&$"##"0: PowerCenter applications suc as te PowerCenter Client@ te Interation !ervice@ pmrep@ and infacmd connect to te repository trou te Repository !ervice.
$e followin process descri5es ow a repository client application connects to te repository data5ase? 1G $e repository client application sends a repository connection reBuest to te master ateway node@ wic is te entry point to te domain. $is is node * in te diaram. 2G $e !ervice anaer sends 5ac7 te ost name and port num5er of te node runnin te Repository !ervice. If you ave te i availa5ility option@ you can confiure te Repository !ervice to run on a 5ac7up node. #ode A in a5ove diaram. 3G $e repository client application esta5lises a l in7 wit te Repository !ervice process on node A. $is communication occurs over $CPHIP.
4G $e Repository !ervice process communicates communicates wit te repository data5ase and performs repository metadata transactions for te client application.
5n'&r("!n'#n- M&"!'!"! $e repository stores metadata tat descri5es ow to e
A'#n#("&r#n- R&*o(#"or#&( )e use te PowerCenter Administration Console@ te Repository anaer@ and te pmrep and infacmd command line prorams to administer repositories. • • • • • • • • •
*ac7 up repository to a 5inary file Restore repository from a 5inary file Copy repository data5ase ta5les Delete repository data5ase ta5les Create a Repository !ervice Remove a Repository !ervice Create folders to orani:e metadata Add repository users and roups Confiure repository security
1.2.; INTEGRATION SER6ICE $e Interation !ervice reads wor7flow information from te repository. $e Interation !ervice connects to te repository trou te Repository !ervice to fetc metadata from te repository. A wor7flow is a set of instructions tat descri5es ow and wen to run tas7s related to e
1.2.< WEB SER6ICES H5B $e )e5 !ervices +u5 is a we5 service ateway for e
B!"$+ %&/ (&r#$&(: Run and monitor we5;ena5led wor7flows. R&!)"#& %&/ (&r#$&(: Create service wor7flows tat allow you to read and write messaes to a we5 service client trou te )e5 !ervices +u5.
T+#( #( no" (&' /0 Infor!"#$! D&&)o*&r nor!))0 !n' no" #n ($o*& of o r "r!#n#n-.
1.2.= DATA ANALY>ER PowerCenter Data Analy:er provides a framewor7 to perform 5usiness analytics on corporate data. )it Data Analy:er@ we can e
1.2.? METADATA MANAGER PowerCenter etadata anaer is a metadata manaement tool tat you can use to 5rowse and analy:e metadata from disparate metadata repositories. etadata anaer elps us understand and manae ow information and processes are derived@ te fundamental relationsips 5etween tem@ and ow tey are used. etadata anaer uses Data Analy:er functionality. )e can use te em5edded Data Analy:er features to desin@ develop@ and deploy metadata reports and das5oards. etadata anaer uses PowerCenter wor7flows to e
Chapter 2
Repository Manager
CHAPTER 2: REPOSITORY MANAGER )e can naviate trou multiple folders and repositories and perform 5asic repository tas7s wit te Repository anaer. T+#( #( !n !'#n#("r!"#on "oo) !n' (&' /0 Infor!"#$! A'#n#("r!"or.
R&*o(#"or0 M!n!-&r T!(,(: • • • • • • • • • •
Add domain connection information Add and connect to a repository )or7 wit PowerCenter domain and repository connections !earc for repository o56ects or 7eywords 'iew o56ect dependencies Compare repository o56ects $runcate session and wor7flow lo entries 'iew user connections Release loc7s %
A'' ! r&*o(#"or0 "o "+& N!#-!"or@ !n' "+&n $onf#-r& "+& 'o!#n $onn&$"#on #nfor!"#on %+&n %& $onn&$" "o "+& r&*o(#"or0.
2.1 A''#n- ! R&*o(#"or0 "o "+& N!#-!"or 1. In any of te PowerCenter Client tools@ clic7 Repository K Add.
2. %nter te name of te repository and a valid repository user name. 3. Clic7 O. *efore we can connect to te repository for te first time@ we must confiure te connection information for te domain tat te repository 5elons to.
2.2 Conf#-r#n- ! Do!#n Conn&$"#on 1. In a PowerCenter Client tool@ select te Repositories node in te #aviator. 2. Clic7 Repository K Confiure Domains to open te Confiure Domains dialo 5o<.
3. Clic7 te Add 5utton. $e Add Domain dialo 5o< appears. 4. %nter te domain name@ ateway ost name@ and ateway port num5er. . Clic7 O to add te domain connection.
2.3 Conn&$"#n- "o ! R&*o(#"or0 1. aunc a PowerCenter Client tool. 2. !elect te repository in te #aviator and clic7 Repository K Connect@ or dou5le;clic7 te repository. 3. %nter a valid repository user name and password. 4. Clic7 Connect. Clic7 on more 5utton to add@ cane or view domain information.
2.4 6#&%#n- O/7&$" D&*&n'&n$#&( *efore we cane or delete repository o56ects@ we can view dependencies to see te impact on oter o56ects. 9or e
2.8 6!)#'!"#n- M)"#*)& O/7&$"( )e can validate multiple o56ects in te repository witout fetcin tem into te wor7space. )e can save and optionally cec7 in o56ects tat cane from invalid to valid status as a result of te validation. )e can validate sessions@ mappins@ mapplets@ wor7flows@ and wor7lets. !teps? 1. 2. 3. 4. .
!elect te o56ects you want to validate. Clic7 Analy:e and !elect 'alidate !elect validation options from te 'alidate O56ects dialo 5o< Clic7 'alidate. Clic7 a lin7 to view te o56ects in te results roup.
2.; Co*!r#n- R&*o(#"or0 O/7&$"( )e can compare two repository o56ects of te same type to identify differences 5etween te o56ects. 9or e
In te Repository anaer@ connect to te repository. In te #aviator@ select te o56ect you want to compare. Clic7 %dit K Compare O56ects. Clic7 Compare in te dialo 5o< displayed.
2.< Trn$!"#n- Wor,f)o% !n' S&((#on Lo- En"r#&( )en we confiure a session or wor7flow to arcive session los or wor7flow los@ te Interation !ervice saves tose los in local directories. $e repository also creates an entry for eac saved wor7flow lo and session lo. If we move or delete a session lo or wor7flow lo from te wor7flow lo directory or session lo directory@ we can remove te entries from te repository. !teps? 1. In te Repository anaer@ select te wor7flow in te #aviator window or in te ain window. 2. Coose %dit K $runcate o. $e $runcate )or7flow o dial o 5o< appears. 3. Coose to delete all wor7flow and session lo entries or to delete all wor7flow and session lo entries wit an end time 5efore a particular date. 4. If you want to delete all entries older tan a certain date@ enter te date and time. . Clic7 O.
2.= M!n!-#n- 5(&r Conn&$"#on( !n' Lo$,( In te Repository anaer@ we can view and manae te followin items? R&*o(#"or0 o/7&$" )o$,(: $e repository loc7s repository o56ects and folders 5y user. $e repository creates different types of loc7s dependin on te tas7. $e Repository !ervice loc7s and unloc7s all o56ects i n te repository. 5(&r $onn&$"#on(: ,se te Repository anaer to monitor user connections to te repository. )e can end connections wen necessary. T0*&( of )o$,( $r&!"&': 1. In;use loc7? Placed on o56ects we want to view 2. )rite;intent loc7? Placed on o56ects we want to modify. 3. %
aunc te Repository anaer and connect t o te repository. Clic7 %dit K !ow ,ser Connections or !ow loc7s $e loc7s or user connections will 5e displayed in a window. )e can do te rest as per our need.
2.? M!n!-#n- 5(&r( !n' Gro*( 1. 2. 3. 4. . ".
In te Repository anaer@ connect to a repository. Clic7 !ecurity K anae ,sers and Privilees. Clic7 te &roups ta5 to create &roups. or Clic7 te ,sers ta5 to create ,sers Clic7 te Privilees ta5 to ive permissions to roups and users. !elect te options availa5le to add@ edit@ and remove users and roups.
$ere are two default repository user roups? A'#n#("r!"or(: $is roup initially contains two users tat are created 5y default. $e default users are Administrator and te data5ase user tat created te repository. )e cannot delete tese users from te repository or remove tem from te Administrators roup. P/)#$: $e Repository anaer does not create any default users in te Pu5lic roup.
2.1 Wor,#n- %#"+ Fo)'&r( )e can create. %dit or delete folder as per our need. 1. In te Repository anaer@ connect to a repository. 2. Clic7 9older K Create. %nter te followin information?
3. Clic7 o7.
Chapter 3
esigner
CHAPTER 3: DESIGNER $e Desiner as tools to elp us 5uild mappins and mapplets so we can specify ow to move and transform data 5etween sources and tarets. $e Desiner elps us create source definitions@ taret definitions@ and transformations to 5uild te mappins. $e Desiner lets us wor7 wit multiple tools at one time and to wor7 in multiple folders and repositories at te same time. It also includes windows so we can view folders@ repository o56ects@ and tas7s.
D&(#-n&r Too)(: •
• • • •
Sor$& An!)0&r: ,se to import or create source definitions for flat file@ @ CO*O@ Application@ and relational sources. T!r-&" D&(#-n&r: ,se to import or create taret definitions. Tr!n(for!"#on D&&)o*&r: ,se to create reusa5le transformations. M!**)&" D&(#-n&r: ,se to create mapplets. M!**#n- D&(#-n&r: ,se to create mappins.
D&(#-n&r W#n'o%(: • •
• •
•
•
•
N!#-!"or: ,se to connect to and wor7 in multiple repositories and folders. Wor,(*!$&: ,se to view or edit sources@ tarets@ mapplets@ transformations@ and mappins. S"!"( /!r: Displays te status of te operation we perform. O"*": Provides details wen we perform certain tas7s@ suc as savin wor7 or validatin a mappin O&r#&%: An optional window to simplify viewin wor75oo7s containin lare mappins or a lare num5er of o56ects. In("!n$& D!"!: 'iew transformation data wile you run te De5uer to de5u a mappin. T!r-&" D!"!: 'iew taret data wile you run te De5uer to de5u a mappin.
O&r#&% W#n'o%
D&(#-n&r W#n'o%( D&(#-n&r T!(,(: • • • • • • • • • • • • • • •
Add a repository. Print te wor7space. 'iew date and time an o56ect was last saved. Open and close a folder. Create sortcuts. Cec7 out and in repository o56ects. !earc for repository o56ects. %nter descriptions for repository o56ects. 'iew older versions of o56ects in te wor7space. Revert to a previously saved o56ect version. Copy o56ects. %
3.1 SO5RCE ANALY>ER In !ource Analy:er@ we define te source definitions tat we will use in a mappin. W& $!n &#"+&r #*or" ! (or$& '&f#n#"#on or !n!))0 $r&!"& "+& '&f#n#"#on. )e can import or create te followin types of source definitions in te !ource Analy:er? • • • • •
Relational ta5les@ views@ and synonyms 9i
3.1.1 Wor,#n- %#"+ R&)!"#on!) Sor$&( S*&$#!) C+!r!$"&r H!n')#n-: )e can import@ create@ or edit source definitions wit ta5le and column names containin special caracters@ suc as te slas FHG caracter trou te Desiner. )en we use te !ource Analy:er to import a source definition@ te Desiner retains special caracters in ta5le and field names. +owever@ wen we add a source definition wit special caracters to a mappin@ te Desiner eiter retains or replaces te special caracter. Also@ wen we enerate te default !> statement in a !ource >ualifier transformation for a relational source@ te Desiner uses Buotation mar7s around some special caracters. $e Desiner andles special caracters differently for relational and non;relational sources.
I*or"#n- ! R&)!"#on!) Sor$& D&f#n#"#on 1. Connect to repository. 2. Rit clic7 te folder were you want to import source definition and clic7 open. $e folder wic is connected ets 5old. )e can wor7 in only one folder at a time. 3. In te !ource Analy:er@ clic7 !ources K Import from Data5ase. 4. !elect te OD*C data source used to connect to te source data5ase. If you need to create or modify an OD*C data source@ clic7 te *rowse 5utton to open te OD*C Administrator. Create te data source@ and clic7 O. !elect te new OD*C data source.
G "G (G -G G
%nter a data5ase user name and password to connect to te data5ase. Clic7 Connect. $a5le names will appear. !elect te relational o56ect or o56ects you want to import. Clic7 O. Clic7 Repository K !ave.
5*'!"#n- ! R&)!"#on!) Sor$& D&f#n#"#on )e can update a source definition to add 5usiness names or to reflect new column names@ datatypes@ or oter canes. )e can update a source definition in te followin ways? E'#" "+& '&f#n#"#on: anually edit te source definition if we need to confiure properties tat we cannot import or if we want to ma7e minor canes to te source definition. R&#*or" "+& '&f#n#"#on? If te source canes are sinificant@ we may need to reimport te source definition. $is overwrites or renames te e
E'#"#n- R&)!"#on!) Sor$& D&f#n#"#on( 1G !elect $ools ;K !ource Analy:er 2G Dra te ta5le you want to edit in wor7space. 3G In te !ource Analy:er@ dou5le;clic7 te title 5ar of te source definition. Or Rit clic7 te ta5le and clic7 edit. 4G In ta5le ta5@ we can rename@ add owner name@ 5usiness description or edit data5ase type. G Clic7 te Columns $a5. %dit column names@ datatypes@ and restrictions. Clic7 O.
3.1.2 Wor,#n- %#"+ F)!" F#)&( $o use flat files as sources@ tarets@ and loo7ups in a mappin we must import or create te definitions in te repository. )e can import or create flat file source definitions in te !ource Analy:er. )e can import fi
S*&$#!) C+!r!$"&r H!n')#n-: )en we import a flat file in te Desiner@ te 9lat 9ile )i:ard uses te file name as te name of te flat file definition 5y default. )e can import a flat file wit any valid file name trou te 9lat 9ile )i:ard. +owever@ te Desiner does not reconi:e some special caracters in flat file source and taret names. )en we import a flat file@ te 9lat 9ile )i:ard canes invalid caracters and spaces into underscores F L G. 9or e
To #*or" ! f#&'%#'"+ f)!" f#)& '&f#n#"#on: 1. Open te !ource Analy:er and clic7 !ources K Import from 9ile. $e Open 9lat 9ile dialo 5o< appears. 2. *rowse and !elect te file you want to use. 3. !elect a code pae. 4. Clic7 O. . %dit te followin settins?
"G Clic7 #e
To #*or" ! '&)##"&' f)!" f#)& '&f#n#"#on: Delimited flat files are always caracter;oriented and line seBuential. $e column precision is always measured in caracters for strin columns and in sinificant diits for numeric columns. %ac row ends wit a newli ne caracter. )e can import a delimited file tat does not contain 5inary data or multi5yte caracter data reater tan two 5ytes per caracter. !teps? 1G Repeat !teps 1; as in case of fi
ReBuired
$reat Consecutive Delimiters as One %scape Caracter
Optional
Remove %scape Caracter 9rom Data ,se Default $e
Optional
Optional
If selected@ te 9lat 9ile )i:ard uses te entered default te
$eualifier
ReBuired
>uote caracter tat defines te 5oundaries of teuote@ !inle >uote@ or Dou5le >uotes.
Optional
Caracter used to separate columns of data. ,se te Oter field to enter a different delimiter. If selected@ te 9lat 9ile )i:ard reads one or more consecutive column delimiters as one.
Caracter immediately precedin a column delimiter caracter em5edded in an unBuoted strin@ or immediately precedin te Buote caracter in a Buoted strin. Clear tis option to include te escape caracter in te output strin.
4G %nter column information for eac column in te file. G Clic7 9inis. "G Clic7 Repository K !ave.
E'#"#n- F)!" F#)& D&f#n#"#on( 1G !elect $ools ;K !ource Analy:er 2G Dra te file you want to edit in wor7space. 3G In te !ource Analy:er@ dou5le;clic7 te title 5ar of te source definition. )e can edit source or taret flat file definitions usin te followin definition ta5s? T!/)& "!/? %dit properties suc as ta5le name@ 5usiness name@ and flat file properties. Co)n( "!/: %dit column information suc as column names@ datatypes@ precision@ and formats. Pro*&r"#&( "!/? 'iew te default numeric and datetime format properties in te !ource Analy:er and te $aret Desiner. /ou can edit tese properties for eac source and taret instance in a mappin in te appin Desiner. M&"!'!"! E"&n(#on( "!/: %
•
•
•
•
T+& %!0 "o +!n')& "!r-&" f)!" f#)&( #( !)(o (!& !( '&($r#/&' #n "+& !/o& (&$"#on(. (" !,& (r& "+!" #n("&!' of Sor$& An!)0&r@ S&)&$" Too)( T!r-&" D&(#-n&r. R&(" #( (!&.
3.2 TARGET DESIGNER *efore we create a mappin@ we must define tarets in te repository. ,se te $aret Desiner to import and desin taret definitions. $aret definitions include properties suc as column names and data t ypes.
T0*&( of "!r-&" '&f#n#"#on(: • • •
R&)!"#on!): Create a relational taret for a parti cular data5ase platform. F)!" f#)&: Create fi
W!0( of $r&!"#n- "!r-&" '&f#n#"#on(: 1. I*or" "+& '&f#n#"#on for !n &#("#n- "!r-&"? Import te taret definition from a relational taret or a flat file. $e $aret Desiner uses a 9lat 9ile )i:ard to import flat files. 2. Cr&!"& ! "!r-&" '&f#n#"#on /!(&' on ! (or$& '&f#n#"#on? Dra a source definition into te $aret Desiner to ma7e a taret definition and edit it to ma7e necessary canes.. 3. Cr&!"& ! "!r-&" '&f#n#"#on /!(&' on ! "r!n(for!"#on or !**)&": Dra a transformation into te $aret Desiner to ma7e a taret definition. 4. M!n!))0 $r&!"& ! "!r-&" '&f#n#"#on: Create a taret definition in te $aret Desiner. . D&(#-n (&&r!) r&)!"&' "!r-&"(: Create several related taret definitions at te same time. /ou can create te overall relationsip@ called a scema@ and te taret definitions@ trou wi:ards in te Desiner. After we create a relational taret ta5le definition@ we need to create a ta5le in data5ase also. S"&*(: 1. In te $aret Desiner@ select te relational taret definition you want to create in te data5ase. If you want to create multiple ta5les@ select all relevant ta5le definitions. 2. Clic7 $arets K &enerateH%. 3. Clic7 Connect and select te data5ase were te taret ta5le sould 5e created. Clic7 O to ma7e te connection. 4. Clic7 &enerate !> 9ile if you want to create te !> script@ or &enerate and %
3.3 MAPPINGS A mappin is a set of source and taret definitions lin7ed 5y transformation o56ects tat define te rules for data transformation. appins represent te data flow 5etween sources and tarets. )en te Interation !ervice runs a session@ it uses te instructions confiured in te mappin t o read@ transform@ and write data.
M!**#n- Co*on&n"(: • •
•
•
•
• •
Sor$& '&f#n#"#on? Descri5es te caracteristics of a source ta5l e or file. Tr!n(for!"#on? odifies data 5efore writin it to tarets. ,se different transformation o56ects to perform different functions. T!r-&" '&f#n#"#on? Defines te taret ta5le or file. L#n,(? Connect sources@ tarets@ and transformations so te Interation !ervice can move te data as it transforms it.
$e wor7 of Informatica Developer is to ma7e mappins as per client reBuirements. )e dra source definition and taret defini tion in wor7space. )e create various transformations to modify te data as per te need. )e ten run te mappins 5y creatin session and wor7flow. )e also unit test te mappins.
S"&*( "o $r&!"& ! !**#n-: 1. Open te appin Desiner. 2. Clic7 appins K Create@ or dra a repository o56ect into te wor7space. 3. %nter a name for te new mappin and clic7 O.
3.4 TRANSFORMATIONS A transformation is a repository o56ect tat enerates@ modifies@ or passes data. /ou confiure loic in a transformation tat te Interation !ervice uses to transform data. $e Desiner provides a set of transformations tat perform specific functions. 9or e
T0*&( of Tr!n(for!"#on(: A$"#&: An active transformation can cane te num5er of rows tat pass trou it@ suc as a 9ilter transformation tat removes rows tat do not meet te filter condition.
P!((#&: A passive transformation does not cane te num5er of rows tat pass trou it@ suc as an %
Conn&$"&'? A connected transformation is connected to oter transformations in te mappin.
5n$onn&$"&': An unconnected transformation is not connected to oter transformations in te mappin. An unconnected transformation is called witin anoter transformation@ and returns a value to tat transformation.
R&(!/)&: Reusa5le transformations can 5e used in multiple mappins. $ese are created in Tr!n(for!"#on D&&)o*&r tool. Or *roo"& ! nonr&(!/)& "r!n(for!"#on from te appin Desiner. )e can create most transformations as a non;reusa5le or reusa5le. %ualifier is not reusa5le. • •
•
Non r&(!/)&: #on;reusa5le transformations e
S#n-)&Gro* Tr!n(for!"#on: $ransformations tat ave one input and one output roup.
M)"#Gro* Tr!n(for!"#on(: $ransformations tat ave multiple input roups@ multiple output roups@ or 5ot. A roup is te representation of a row of data enterin or leavin a transformation. %
3.4.1 Wor,#n- %#"+ Por"( After we create a transformation@ we need to add and confiure ports usin te Ports ta5. Ports are eBuivalent to columns in Informatica. Cr&!"#n- Por"(: )e can create a new port in te followin ways? •
•
Dra a port from anoter transformation. )en we dra a port from anoter transformation te Desiner creates a port wit te same properties@ and it lin7s te two ports. Clic7 ayout K Copy Columns to ena5le copyin ports. Clic7 te Add 5utton on te Ports ta5. $e Desiner creates an empty port you can confiure.
3.4.2 5(#n- D&f!)" 6!)&( for Por"( All transformations use default values tat determine ow te Interation !ervice andles input null values and output transformation errors. •
•
•
In*" *or": $e system default value for null input ports is #,. It displays as a 5lan7 in te transformation. If an input value is #,@ te Interation !ervice leaves it as #,. O"*" *or": $e system default value for output transformation errors is %RROR. $e default value appears in te transformation as %RRORFtransformation errorG. If a transformation error occurs@ te Interation !ervice s7ips te row. $e Interation !ervice notes all i nput rows s7ipped 5y te %RROR function in te session lo file. In*"o"*" *or": $e system default value for null input is te same as input ports@ #,. $e system default value appears as a 5lan7 in te transformation. $e default value for output transformation errors is te same as output ports.
No"&: 'aria5le ports do not support default values. $e Interation !ervice initiali:es varia5le ports accordin to te datatype. No"&: $e Interation !ervice inores user;defined default values for unconnected transformations.
3.4.3 5(&r'&f#n&' '&f!)" !)&( Con("!n" !)&: ,se any constant Fnumeric or te
Con("!n" &*r&((#on: )e can include a transformation function wit constant parameters. %
ERROR: &enerate a transformation error. )rite te row and a messae in te session lo or row error lo. $e Interation !ervice writes te row to session lo or row error lo 5ased on session confiuration. ,se te %RROR function as te default value wen we do not want null val ues to pass into a transformation. 9or e
ABORT: A5ort te session. !ession a5orts wen te Interation !ervice encounters a null input value. $e Interation !ervice does not increase te error count or write rows to te re6ect file. %
3.8 TRACING LE6ELS )en we confiure a transformation@ we can set te amount of detail te Interation !ervice writes in te session lo. •
)e set tracin level in Properties ta5 of a transformation.
L&&)
D&($r#*"#on
#ormal
Interation !ervice los initiali:ation and status information@ errors encountered and s7ipped rows due to transformation row errors. !ummari:es session results@ 5ut not at te level of individual rows.
$erse
Interation !ervice los initiali:ation information and error messaes and notification of re6ected data.
'er5ose Initiali:ation
In addition to normal tracin@ Interation !ervice los additional initiali:ation detailsS names of inde< and data fi les used@ and detailed transformation statistics.
'er5ose Data
In addition to ver5ose initiali:ation tracin@ Interation !ervice los eac row tat passes into te mappin. Allows te Interation !ervice to write errors to 5ot te session lo and error lo wen you ena5le row error loin. Interation !ervice writes row data for all rows in a 5loc7 wen it processes a transformation.
•
•
Cane te tracin level to a 'er5ose settin only wen we need to de5u a transformation tat is not 5eavin as e
3.; BASIC FIRST MAPPING 9irst ma7e sure tat we ave created a sared folder and a folder wit te name of developer alon wit user as descri5ed in Installation &uide. W& %#)) "r!n(f&r '!"! fro EMP "!/)& #n (or$& "o EMPT-" "!/)& #n "!r-&". Also create an OD*C connection for source and t aret data5ase.
I*or"#n- Sor$& D&f#n#"#on: 1. 2. 3. 4. . ". (. -. .
!elect te !ared folder. Rit clic7 on it and select open. !ared folder will 5ecome 5old. It means we are now connected to it. Clic7 on tools;K !ource Analy:er #ow we will import te source ta5le definitions in sared folder. Clic7 on !ource ;K Import from data5ase In 5o< displayed@ ive connection information for source data5ase. Clic7 Connect. $a5les in source data5ase will 5e displayed. !elect te ta5les of use and clic7 O. $a5le definition will 5e displayed. )e can edit it as per need.
No"&: )e can edit te source definition 5y drain te ta5le in !ource Analy:er only.
Cr&!"#n- T!r-&" T!/)& EMPT-" #n T!r-&" '!"!/!(& 1. Connect to te !ared folder. $ools;K $aret Desiner 2. #ow dra te %P ta5le definition from left side pane to taret desiner. 3. )e will see te %P ta5le definition in $aret Desiner. 4. Rit clic7 %P ;K %dit ;K Clic7 on rename and ive name as %PL$t . Apply ;K O7. ". #ow we will create tis ta5le in taret data5ase. (. Clic7 $aret ;K !elect enerateH e. -. Clic7 on connect and ive loin information for taret data5ase. . $en select te options of ta5le eneration. 18. Clic7 &enerateH%
• •
No% %& +!& !)) "+& "!/)&( %& n&&' #n (+!r&' fo)'&r. W& no% n&&' "o $r&!"& (+or"$" "o "+&(& #n or fo)'&r. 1. 2. 3. 4. .
Rit Clic7 on !ared folder and select disconnect. !elect te folder were we want to create te mappin. Rit clic7 on folder and clic7 open. $e folder will 5ecome 5old. )e will now create sortcut to te ta5les of need in our wor7 folder. Clic7 N sin on !ared folder and open N sin on !ources and !elect %P ta5le. ". #ow clic7 %dit ;K Copy (. #ow select te folder were wic is 5old. -. Clic7 %dit ;K Paste !ortcut . Do te same for all source and taret ta5les. 18. Also rename all te sortcuts and remove !ortcutLtoL from all. 11. Repository !ave
S+or"$" (&: •
•
• • •
• •
If we will select paste option@ ten te copy of %P ta5le definition will 5e created. !uppose@ we are 18 people and usin sortcut and are copyin te definition of %P. #ow suppose te definition of %P canes in data5ase. )e will now reimport te %P definition and old definition will 5e replaced. D&&)o*&r( %+o %&r& (#n- (+or"$"( %#)) (&& "+!" "+& $+!n-&( +!& /&&n r&f)&$"&' #n !**#n- !"o!"#$!))0. Developers usin copy will ave to reimport manually. !o for maintenance and ease@ we use sortcuts to source and taret definitions in our folder and sort to oter reusa5le transformations and mapplets.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. . ". (.
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin ;K Create ;K &ive mappin name. %L%P to %PL$t. Clic7 appin ;K 'alidate Repository ;K !ave
Cr&!"#n- S&((#on: #ow we will create session in wor7flow manaer. 1. 2. 3. 4. .
Open )or7flow anaer ;K Connect to repository Open te folder wit same name in wic we created mappin. a7e sure folder is 5old. #ow clic7 tool $as7 Developer Clic7 $as7 ;K Create ;K !elect !ession tas7 and ive name. sLmL5asicLmappin ". !elect te correct mappin from te list displayed. (. Clic7 Create and done. -. #ow rit clic7 session and clic7 edit. . !elect mappin ta5. 18. &o to !>L%P in source and ive te correct relational connection for it. 11. Do te same for %PL$t. 12. Also for taret ta5le@ &ive oad $ype option as #ormal and Also select $runcate $aret $a5le Option. 13. $as7 ;K 'alidate
Cr&!"#n- Wor,f)o%: 1. 2. 3. 4. . ". (. -.
#ow Clic7 $ools ;K )or7flow Desiner )or7flow ;K Create ;K &ive name li 7e wfL5asicLmappin Clic7 o7 !$AR$ tas7 will 5e displayed. It is te startin point for Informatica server. Dra session to wor7flow. Clic7 $as7;K in7 $as7. Connect !$AR$ to te session. Clic7 )or7flow ;K 'alidate Repository !ave.
No% o*&n Wor,f)o% Mon#"or f#r(". 1. &o 5ac7 to )or7flow anaer. !elect te wor7flow and rit clic7 on te wor7flow wfL5asicLmappin. 2. !elect !tart )or7flow. /ou can view te status in )or7flow onitor. Cec7 te data in taret ta5le. Command? select from ta5leLnameS
3.< EPRESSION TRANSFORMATION •
Passive and connected transformation.
,se te %
C!)$)!"#n- 6!)&( $o use te %
•
In*" or #n*"o"*" *or"( for &!$+ !)& (&' #n "+& $!)$)!"#on: 9or e
)e can enter multiple e
•
•
Import te source ta5le %P in !ared folder. If it is already tere@ ten donEt import. In sared folder@ create te taret ta5le %mpL$otalL!A. eep all ports as in %P ta5le e
Cr&!"#n- M!**#n-: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin ;K Create ;K &ive mappin name. %L%P to %
11. Cec7 te e
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&.
A( COMM #( n))@ To"!)SAL %#)) /& n)) #n o(" $!(&(. No% o*&n 0or !**#n- !n' &*r&((#on "r!n(for!"#on. S&)&$" COMM *or"@ In D&f!)" 6!)& -#& . No% !**)0 $+!n-&(. 6!)#'!"& M!**#n- !n' S!&. R&fr&(+ "+& (&((#on !n' !)#'!"& %or,f)o% !-!#n. Rn "+& %or,f)o% !n' (&& "+& r&()" !-!#n. No% (& ERROR #n D&f!)" !)& of COMM "o (,#* ro%( %+&r& COMM #( n)). !ynta
S##)!r)0@ %& $!n (& ABORT fn$"#on "o !/or" "+& (&((#on #f COMM #( n)). !ynta
3.= FILTER TRANSFORMATION •
Active and connected transformation.
)e can filter rows in a mappin wit te 9ilter transformation. )e pass all te rows from a source transformation trou te 9ilter transformation@ and ten enter a filter condition for te transformation. All ports in a 9ilter transformation are inputHoutput and only rows tat meet te condition pass trou te 9ilter transformation.
E!*)&: "o f#)"&r r&$or'( %+&r& SAL2 •
•
•
Import te source ta5le %P in !ared folder. If it is already tere@ ten donEt import. In sared folder@ create te taret ta5le 9ilterL%
Cr&!"#n- M!**#n-: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin ;K Create ;K &ive mappin name. %L%P to 9ilter $ransformation. (. %dit 9ilter $ransformation. &o to Pro*&r"#&( T!/ -. Clic7 te 'alue section of te 9ilter condition@ and ten clic7 te Open 5utton. . $e %
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&.
Ho% "o f#)"&r o" ro%( %#"+ n)) !)&( $o filter out rows containin null values or spaces@ use te ISN5LL and ISSPACES functions to test te value of te port. 9or e
3.? RO5TER TRANSFORMATION •
Active and connected transformation.
A Router transformation is similar to a 9ilter transformation 5ecause 5ot transformations allow you to use a condition to test data. A 9ilter transformation tests data for one condition and drops te rows of data tat do not meet te condition. +owever@ a Router transformation tests data for one or more conditions and ives you te option to route rows of data tat do not meet a ny of te conditions to a '&f!)" o"*" -ro*. %
appin A uses tree 9ilter transformations wile appin * produces te same result wit one Router transformation. A Router transformation consists of input and output roups@ input and output ports@ roup filter conditions@ and properties tat we confiure in te Desiner.
Wor,#n- %#"+ Gro*( A Router transformation as te followin types of roups? • •
In*": $e &roup tat ets te input ports. O"*": ,ser Defined &roups and Default &roup. )e cannot modify or delete output ports or teir properties.
5(&rD&f#n&' Gro*(: )e create a user;defined roup to test a condition 5ased on incomin data. A user;defined roup consists of output ports and a roup filter condition. )e can create and edit user;defined roups on te &roups ta5 wit te Desiner. Create one user;defined roup for eac condition tat we want to specify.
T+& D&f!)" Gro*: $e Desiner creates te default roup after we create one new user;defined roup. $e Desiner does not allow us to edit or delete te default roup. $is roup does not ave a roup filter condition associated wit it. If all of te conditions evaluate to 9A!%@ te I! passes te row to te default roup. E!*)&: F#)"&r#n- &*)o0&&( of D&*!r"&n" 1 "o EMP1@ D&*!r"&n" 2 "o EMP2 !n' r&(" "o EMPREST • •
•
!ource is %P $a5le. Create 3 taret ta5les %PL18@ %PL28 and %PLR%!$ in sared folder. !tructure sould 5e same as %P ta5le. Create te sortcuts in your folder.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive mappin name. %L%P to Router $ransformation. (. %dit Router $ransformation. &o to Gro*( T!/ -. Clic7 te &roups ta5@ and ten clic7 te Add 5utton to create a user;defined roup. $e default roup is created automatically.. . Clic7 te &roup 9ilter Condition field to open te %
12. Create anoter roup for %PL28. Condition? D%P$#OT28 13. $e rest of te records not matcin te a5ove two conditions will 5e passed to D%9A,$ roup. S&& (!*)& !**#n14. Clic7 O ;K Clic7 Apply ;K Clic7 O7. 1. #ow connect te ports from router to taret ta5les. 1". Clic7 appin ;K 'alidate 1(. Repository ;K !ave •
•
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) 3 "!r-&" "!/)&(.
S!*)& M!**#n-:
D#ff&r&n$& /&"%&&n Ro"&r !n' F#)"&r 1K )e cannot pass re6ected data forward in filter 5ut we can pass it in router. Re6ected data is in Default &roup of router. 2K 9ilter as no default roup.
3.1 5NION TRANSFORMATION •
Active and Connected transformation.
$e ,nion transformation is a multiple input roup transformation tat you can use to mere data from multiple pipelines or pipeline 5rances into one pipeline 5ranc. It meres data from multiple sources similar to te ,#IO# A !> statement to com5ine te results from two or more !> statements.
5n#on Tr!n(for!"#on R)&( !n' G#'&)#n&( • • •
•
•
)e can create multiple input roups@ 5ut only one output roup. )e can connect eteroeneous sources to a ,nion transformation. All input roups and te output roup must ave matcin ports. $e precision@ datatype@ and scale must 5e identical across all roups. $e ,nion transformation does not remove duplicate rows. $o remove duplicate rows@ we must add anoter transformation suc as a Router or 9ilter transformation. )e cannot use a !eBuence &enerator or ,pdate !tratey transformation upstream from a ,nion transformation.
5n#on Tr!n(for!"#on Co*on&n"( )en we confiure a ,nion transformation@ define te followin components? Tr!n(for!"#on "!/? )e can rename te transformation and add a description. Pro*&r"#&( "!/? )e can specify te tracin level. Gro*( "!/? )e can create and delete input roups. $e Desiner displays roups we create on te Ports ta5. Gro* Por"( "!/? )e can create and delete ports for te input roups. $e Desiner displays ports we create on te Ports ta5. )e cannot modify te Ports@ Initiali:ation Properties@ etadata %
•
Import ta5les %P 18@ %PL28 and %PLR%!$ in sared folder in !ources. Create a taret ta5le %PL,#IO#L%AP% in taret desiner. !tructure sould 5e same %P ta5le. Create te sortcuts in your folder.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive mappin name. %L%PL18 to ,nion $ransformation. (. %dit ,nion $ransformation. &o to Gro*( T!/ -. One roup will 5e already tere as we draed ports from !>LD%P$L18 to ,nion $ransformation. . As we ave 3 source ta5les@ we 3 need 3 input roups. Clic7 Add 5utton to add 2 more roups. S&& S!*)& M!**#n18. )e can also modify ports in ports ta5. 11. Clic7 Apply ;K O7. 12. Dra taret ta5le now. 13. Connect te output ports from ,nion to taret ta5le. 14. Clic7 appin ;K 'alidate 1. Repository ;K !ave •
•
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) 3 (or$& "!/)&(.
!ample mappin picture
3.11 SORTER TRANSFORMATION • • •
•
Connected and Active $ransformation $e !orter transformation allows us to sort data. )e can sort data in ascendin or descendin order accordin to a specified sort 7ey. )e can also confiure te !orter transformation for case;sensitive sortin@ and specify weter te output rows sould 5e distinct.
)en we create a !orter transformation in a mappin@ we specify one or more ports as a sort 7ey and confiure eac sort 7ey port to sort in ascendin or descendin order. )e also confiure sort criteria te PowerCenter !erver applies to all sort 7ey ports and te system resources it allocates to perform te sort operation. $e !orter transformation contains only inputHoutput ports. All data passin trou te !orter transformation is sorted accordin to a sort 7ey. $e sort 7ey is one or more ports tat we want to use as te sort criteria.
Sor"&r Tr!n(for!"#on Pro*&r"#&( 1. Sor"&r C!$+& S#&: $e PowerCenter !erver uses te !orter Cace !i:e property to determine te ma
•
•
•
)e can specify any amount 5etween 1 * and 4 &* for te !orter cace si:e. If it cannot allocate enou memory@ te PowerCenter !erver fails te session. 9or 5est performance@ confiure !orter cace si:e wit a value less tan or eBual to te amount of availa5le pysical RA on te PowerCenter !erver macine. Informatica recommends allocatin at least - * F-@3--@"8- 5ytesG of pysical memory to sort data usin te !orter transformation.
2. C!(& S&n(#"#&: $e Case !ensitive property determines weter te PowerCenter !erver considers case wen sortin data. )en we ena5le te Case !ensitive property@ te PowerCenter !erver sorts uppercase caracters ier tan lowercase caracters.
3. Wor, D#r&$"or0 Directory PowerCenter !erver uses to create temporary files wile it sorts data.
4. D#("#n$": Cec7 tis option if we want to remove duplicates. !orter will sort data accordin to all te ports wen it is selected.
E!*)&: Sor"#n- '!"! of EMP /0 ENAME • •
•
!ource is %P ta5le. Create a taret ta5le %PL!OR$%RL%AP% in taret desiner. !tructure same as %P ta5le. Create te sortcuts in your folder.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive mappin name. %L%P to !orter $ransformation. (. %dit !orter $ransformation. &o to Por"( T!/ -. !elect %#A% as sort 7ey. C+%C mar7 on %/ in front of %#A%. . Clic7 Pro*&r"#&( T!/ and !elect Properties as needed. 18. Clic7 Apply ;K O7. 11. Dra taret ta5le now. 12. Connect te output ports from !orter to taret ta5le. 13. Clic7 appin ;K 'alidate 14. Repository ;K !ave •
•
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) "!/)&(.
!ample !orter appin
3.12 RANK TRANSFORMATION •
Active and connected transformation
$e Ran7 transformation allows us to select only te top or 5ottom ran7 of data. It allows us to select a roup of top or 5ottom values@ not 6ust one value. Durin te session@ te PowerCenter !erver caces input data until it can perform te ran7 calculations.
R!n, Tr!n(for!"#on Pro*&r"#&( • • • • • •
Cace Directory were cace will 5e made. $opH*ottom Ran7 as per need #um5er of Ran7s %
Por"( #n ! R!n, Tr!n(for!"#on Por"(
N/&r R&J#r&'
D&($r#*"#on
I
1 inimum
Port to receive data from anoter transformation.
O
1 inimum
Port we want to pass to oter transformation.
'
#ot needed
Can use to store values or calculations to use in an e
R
Only 1
Ran7 port. Ran7 is calculated accordin to it. $e Ran7 port is an inputHoutput port. )e must lin7 te Ran7 port to anoter transformation. %
R!n, In'& $e Desiner automatically creates a RA#I#D% port for eac Ran7 transformation. $e PowerCenter !erver uses te Ran7 Inde< port to store te ran7in position for eac row in a roup. 9or e
•
D&f#n#n- Gro*( Ran7 transformation allows us to roup information. 9or e
1 E!*)&: F#n'#n- To* 8 S!)!r#&' E*)o0&&( • •
•
%P will 5e source ta5le. Create a taret ta5le %PLRA#L%AP% in taret desiner. !tructure sould 5e same as %P ta5le. =ust add one more port Ran7LInde< to store RA# I#D%. Create te sortcuts in your folder.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. . ".
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive mappin name. %
•
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) "!/)&(.
2 E!*)&: F#n'#n- To* 2 S!)!r#&' E*)o0&&( for &&r0 DEPARTMENT Open te mappin made a5ove. %dit Ran7 $ransformation. &o to Ports $a5. !elect &roup *y for D%P$#O. &o to Properties ta5. !et #um5er of Ran7s as 2. Clic7 Apply ;K O7. appin ;K 'alidate and Repository !ave. Refres te session 5y dou5le clic7in. !ave te caned and run wor7flow to see te new result. • • • • •
!ample Ran7 appin
RANK CACHE )en te PowerCenter !erver runs a session wit a Ran7 transformation@ it compares an input row wit rows in te data cace. If te input row out;ran7s a stored row@ te PowerCenter !erver replaces te stored row wit te input row. %
1 RANK INDE CACHE: $e inde< cace olds roup information from te roup 5y ports. If we are usin &roup *y on D%P$#O@ ten tis cace stores values 18@ 28@ 38 etc. •
All &roup *y Columns are in RA# I#D% CAC+%. %<. D%P$#O
2 RANK DATA CACHE: It olds row data until te PowerCenter !erver completes te ran7in and is enerally larer tan te inde< cace. $o reduce te data cace si:e@ connect only te necessary inputHoutput ports to su5seBuent transformations. •
•
All 'aria5le ports if tere@ Ran7 Port@ All ports oin out from RA# transformation are stored in RA# DA$A CAC+%. %
3.13 AGGREGATOR TRANSFORMATION • •
•
Connected and Active $ransformation $e Areator transformation allows us to perform areate calculations@ suc as averaes and sums. Areator transformation allows us to perform calculations on roups.
Co*on&n"( of "+& A--r&-!"or Tr!n(for!"#on 1K 2K 3K 4K
Areate e
1 A--r&-!"& E*r&((#on( • •
%ntered in an output port. Can include non;areate e
$e transformation lanuae includes te followin areate functions? A'&@ CO,#$ @ A@ I#@ !, 9IR!$@ A!$ %DIA#@ P%RC%#$I%@ !$DD%'@ 'ARIA#C% • • •
S#n-)& L&&) A--r&-!"& Fn$"#on: MA9SAL N&("&' A--r&-!"& Fn$"#on: MA9 CO5NT9 ITEM
N&("&' A--r&-!"& Fn$"#on( •
• • •
In Areator transformation@ tere can 5e multiple sinle level functions or multiple nested functions. An Areator transformation cannot ave 5ot types of functions toeter. AF CO,#$F I$% GG is correct. I#FAF CO,#$F I$% GGG is not correct. It can also include one areate function nested witin anoter areate function
Con'#"#on!) C)!(&( )e can use conditional clauses in te areate e,O$A G •
NonA--r&-!"& Fn$"#on( )e can also use non;areate functions in te areate e,A#$I$/ G K 8@ AF >,A#$I$/ G@ 8GG •
2 Gro* B0 Por"( Indicates ow to create roups. )en roupin data@ te Areator transformation outputs te last row of eac roup unless oterwise specified. $e Areator transformation allows us to define roups for areations@ rater tan performin te areation across all input data. 9or e
•
In Areator $ransformation@ Open Ports ta5 and select &roup *y as needed.
3 5(#n- Sor"&' In*" • •
•
• •
,se to improve session performance. $o use sorted input@ we must pass data to te Areator transformation sorted 5y roup 5y port@ in ascendin or descendin order. )en we use tis option@ we tell Areator tat data comin to it is already sorted. )e cec7 te !orted Input Option in Properties $a5 of te transformation. If te option is cec7ed 5ut we are not passin sorted data to te transformation@ ten te (&((#on f!#)(.
4 A--r&-!"or C!$+&( •
•
$e PowerCenter !erver stores data in te areate cace until it completes areate calculations. It stores roup values in an inde< cace and row data in te data cace. If te PowerCenter !erver reBuires more space@ it stores overflow values in cace files.
No"&: T+& Po%&rC&n"&r S&r&r (&( &or0 "o *ro$&(( !n A--r&-!"or "r!n(for!"#on %#"+ (or"&' *or"(. I" 'o&( no" (& $!$+& &or0. W& 'o no" n&&' "o $onf#-r& $!$+& &or0 for A--r&-!"or "r!n(for!"#on( "+!" (& (or"&' *or"(.
1 A--r&-!"or In'& C!$+&: $e inde< cace olds roup information from te roup 5y ports. If we are usin &roup *y on D%P$#O@ ten tis cace stores values 18@ 28@ 38 etc. •
All &roup *y Columns are in A&&R%&A$OR I#D% CAC+%. %<. D%P$#O
2 A--r&-!"or D!"! C!$+&: DA$A CAC+% is enerally larer tan te A&&R%&A$OR I#D% CAC+%. Columns in Data Cace? 'aria5le ports if any #on roup 5y inputHoutput ports. #on roup 5y input ports used in non;areate output e
%
1 E!*)&: To $!)$)!"& MA@ MIN@ A6G !n' S5M of (!)!r0 of EMP "!/)&. • •
•
%P will 5e source ta5le. Create a taret ta5le %PLA&&L%AP% in taret desiner. $a5le sould contain D%P$#O@ AL!A@ I#L!A@ A'&L!A and !,L!A Create te sortcuts in your folder.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. . ". (. -. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive mappin name. %L%P to A&&R%&A$OR $ransformation. %dit A&&R%&A$OR $ransformation. &o to Por"( T!/ Create 4 output ports? O,$LAL!A@ O,$LI#L!A@ O,$LA'&L!A@ O,$L!,L!A Open %
18. Clic7 Apply ;K O7. 11. Dra taret ta5le now. 12. Connect te output ports from Ran7 to taret ta5le. 13. Clic7 appin ;K 'alidate 14. Repository ;K !ave •
•
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) "!/)&(.
2 E!*)&: To $!)$)!"& MA@ MIN@ A6G !n' S5M of (!)!r0 of EMP "!/)& for &&r0 DEPARTMENT Open te mappin made a5ove. %dit Ran7 $ransformation. &o to Ports $a5. !elect &roup *y for D%P$#O. Clic7 Apply ;K O7. appin ;K 'alidate and Repository !ave. Refres te session 5y dou5le clic7in. !ave te caned and run wor7flow to see te new result. • • • •
S$&n&1: W+!" %#)) /& o"*" of "+& *#$"r& /&)o%
+ere we are not doin any calculation or roup 5y. In tis case@ te D%P$#O and !A of last record of %P ta5le will 5e passed to taret. S$&n&2: W+!" %#)) /& o"*" of "+& !/o& *#$"r& #f Gro* B0 #( 'on& on DEPTNO +ere we are not doin any calculation 5ut &roup *y is tere on D%P$#O. In tis case@ te last record of every D%P$#O from %P ta5le will 5e passed to taret. S$&n&3: W+!" %#)) /& o"*" of "+& EAMPLE 1 In %
3.14 OINER TRANSFORMATION • •
• •
Connected and Active $ransformation ,sed to 6oin source data from two related eteroeneous sources residin in different locations or file systems. Or@ we can 6oin data from te same source. If we need to 6oin 3 ta5les@ ten we need 2 =oiner $ransformations. $e =oiner transformation 6oins two sources wit at least one matcin port. $e =oiner transformation uses a condition tat matces one or more pairs of ports 5etween te two sources.
E!*)&: To 7o#n EMP !n' DEPT "!/)&(. • •
•
%P and D%P$ will 5e source ta5le. Create a taret ta5le =OI#%RL%AP% in taret desiner. $a5le sould contain all ports of %P ta5le plus D#A% and OC as sown 5elow. Create te sortcuts in your folder.
Cr&!"#n- M!**#n-: 1K 2K 3K 4K
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive mappin name. %
K "K (K -K
!pecify te 6oin condition in Condition ta5. !ee steps on ne
•
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) "!/)&(.
OIN CONDITION: $e 6oin condition contains ports from 5ot input sources tat must matc for te PowerCenter !erver to 6oin two rows. %
)e can add as many conditions as needed. Only T operator is allowed.
If we 6oin Car and 'arcar datatypes@ te PowerCenter !erver counts any spaces tat pad Car values as part of te strin. !o if you try to 6oin te followin? Car F48G T a5cdJ and 'arcar F48G T a5cdJ $en te Car value is a5cdJ padded wit 3" 5lan7 spaces@ and te PowerCenter !erver does not 6oin te two fields 5ecause te Car field contains trailin spaces. No"&: T+& o#n&r "r!n(for!"#on 'o&( no" !"$+ n)) !)&(.
MASTER !n' DETAIL TABLES In =oiner@ one ta5le is called as A!$%R and oter as D%$AI. A!$%R ta5le is always caced. )e can ma7e any ta5le as A!$%R. %dit =oiner $ransformation ;K Ports $a5 ;K !elect M for aster ta5le. • •
T!/)& %#"+ )&(( n/&r of ro%( (+o)' /& !'& MASTER "o #*ro& *&rfor!n$&. Reason: •
•
)en te PowerCenter !erver processes a =oiner transformation@ it reads rows from 5ot sources concurrently and 5uilds te inde< and data cace 5ased on te master rows. !o ta5le wit fewer rows will 5e read fast and cace can 5e made as ta5le wit more rows is still 5ein read. $e fewer uniBue rows in te master@ te fewer iterations of te 6oin comparison occur@ wic speeds te 6oin process.
OINER TRANSFORMATION PROPERTIES TAB •
•
•
• • • •
C!(&S&n(#"#& S"r#n- Co*!r#(on? If selected@ te PowerCenter !erver uses case;sensitive strin comparisons wen performin 6oins on strin columns. C!$+& D#r&$"or0? !pecifies te directory used to cace master or detail rows and te inde< to tese rows. o#n T0*&: !pecifies te type of 6oin? #ormal@ aster Outer@ Detail Outer@ or 9ull Outer. Tr!$#n- L&&) o#n&r D!"! C!$+& S#& o#n&r In'& C!$+& S#& Sor"&' In*"
OIN TYPES In !>@ a 6oin is a relational operator tat com5ines data from multiple ta5les into a sinle result set. $e =oiner transformation acts in muc te same manner@ e
No"&: A normal or master outer join performs faster than a full outer or detail outer join.
E!*)&: In %P@ we ave employees wit D%P$#O 18@ 28@ 38 and 8. In D%P$@ we ave D%P$#O 18@ 28@ 38 and 48. D%P$ will 5e A!$%R ta5le as it as less rows. Nor!) o#n: )it a normal 6oin@ te PowerCenter !erver discards all rows of data from te master and detail source tat do not matc@ 5ased on te condition. All employees of 18@ 28 and 38 will 5e tere as only tey are mat cin. •
M!("&r O"&r o#n: $is 6oin 7eeps all rows of data from te detail source and te matcin rows from te master source. It discards te unmatced rows from te master source. • •
All data of employees of 18@ 28 and 38 will 5e tere. $ere will 5e employees of D%P$#O 8 and correspondin D#A% and OC columns will 5e #,.
D&"!#) O"&r o#n: $is 6oin 7eeps all rows of data from te master source and te matcin rows from te detail source. It discards te unmatced rows from te detail source. • •
All employees of 18@ 28 and 38 will 5e tere. $ere will 5e one record for D%P$#O 48 and correspondin data of %P columns will 5e #,.
F)) O"&r o#n: A full outer 6oin 7eeps all rows of data from 5ot te master and detai l sources. • •
•
All data of employees of 18@ 28 and 38 will 5e tere. $ere will 5e employees of D%P$#O 8 and correspondin D#A% and OC columns will 5e #,. $ere will 5e one record for D%P$#O 48 and correspondin data of %P columns will 5e #,.
5SING SORTED INP5T • •
• •
•
,se to improve session performance. $o use sorted input@ we must pass data to te =oiner transformation sorted 5y te ports tat are used in o#n Con'#"#on. )e cec7 te !orted Input Option in Properties $a5 of te transformation. If te option is cec7ed 5ut we are not passin sorted data to te transformation@ ten te (&((#on f!#)(. W& $!n (& SORTER "o (or" '!"! or Sor$& !)#f#&r #n $!(& of r&)!"#on!) "!/)&(.
OINER CACHES =oiner always caces te A!$%R ta5le. )e cannot disa5le cacin. It 5uilds Inde< cace and Data Cace 5ased on A!$%R ta5le. 1 o#n&r In'& C!$+&: All Columns of A!$%R ta5le used in =oin condition are in =OI#%R I#D% CAC+%. %
•
2 o#n&r D!"! C!$+&: aster column not in 6oin condition and used for output to oter transformation or taret ta5le are in Data Cace. %
•
OINER TRANSFORMATION TIPS • • •
•
Perform 6oins in a data5ase wen possi5le. =oin sorted data wen possi5le. 9or a sorted =oiner transformation@ desinate as te master source te source wit fewer duplicate 7ey values. =oiner cant 5e used in followin conditions? 1. %iter input pipeline contains an ,pdate !tratey transformation. 2. )e connect a !eBuence &enerator transformation directly 5efore te =oiner transformation.
3.18 SO5RCE 5ALIFIER TF • •
• •
Active and Connected $ransformation. $e !ource >ualifier transformation represents te rows tat te PowerCenter !erver reads wen it runs a session. It is only transformation tat is not reusa5le. Default transformation e
T!(,( *&rfor&' /0 Sor$& !)#f#&r: •
•
•
•
•
•
o#n '!"! or#-#n!"#n- fro "+& (!& (or$& '!"!/!(&: )e can 6oin two or more ta5les wit primary 7ey;forein 7ey relationsips 5y lin7in te sources to one !ource >ualifier transformation. F#)"&r ro%( %+&n "+& Po%&rC&n"&r S&r&r r&!'( (or$& '!"!: If we include a filter condition@ te PowerCenter !erver adds a )+%R% clause to te default Buery. S*&$#f0 !n o"&r 7o#n r!"+&r "+!n "+& '&f!)" #nn&r 7o#n: If we include a user;defined 6oin@ te PowerCenter !erver replaces te 6oin information specified 5y te metadata in te !> Buery. S*&$#f0 (or"&' *or"(: If we specify a num5er for sorted ports@ te PowerCenter !erver adds an ORD%R */ clause to te default !> Buery. S&)&$" on)0 '#("#n$" !)&( fro "+& (or$&? If we coose !elect Distinct@ te PowerCenter !erver adds a !%%C$ DI!$I#C$ statement to te default !> Buery. Cr&!"& ! $("o J&r0 "o #((& ! (*&$#!) SELECT ("!"&&n" for "+& Po%&rC&n"&r S&r&r "o r&!' (or$& '!"!: 9or e
The entire above are possible in Properties Tab of Source Qualifier t/f.
SAMPLE MAPPING TO BE MADE:
• • •
!ource will 5e %P and D%P$ ta5les. Create taret ta5le as sowed in Picture a5ove. Create sortcuts in your folder as needed.
Cr&!"#n- M!**#n-: 1K 2K 3K 4K K "K (K
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive mappin name. %LeL%P and !elect Delete from te mappin. Rit Clic7 !>LD%P$ and !elect Delete from te mappin. Clic7 $ransformation ;K Create ;K !elect !ource >ualifier from ist ;K &ive #ame ;K Clic7 Create -K !elect %P and D%P$ 5ot. Clic7 O. K in7 all as sown in a5ove picture. 18K %dit !> ;K Properties $a5 ;K Open ,ser defined =oin ;K &ive =oin condition EMP.DEPTNODEPT.DEPTNO. Clic7 Apply ;K O (More details after pa!es"
11K appin ;K 'alidate 12K Repository ;K !ave •
•
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) "!/)&(.
S PROPERTIES TAB
1 SO5RCE FILTER: )e can enter a source filter to reduce te num5er of rows te PowerCenter !erver Bueries. #ote? )en we enter a source filter in te session properties@ we override te customi:ed !> Buery in te !ource >ualifier transformation. !teps? 1K 2K 3K 4K K
In te appin Desiner@ open a !ource >ualifier transformation. !elect te Properties ta5. Clic7 te Open 5utton in te !ource 9ilter field. In te !> %ditor Dialo 5o<@ enter te filter. %
'alidate te mappin. !ave it. #ow refres session and save te canes. #ow run te wor7flow and see output.
2 N5MBER OF SORTED PORTS: )en we use sorted ports@ te PowerCenter !erver adds te ports to te ORD%R */ clause in te default Buery. *y default it is 8. If we cane it to 1@ ten te data will 5e sorted 5y column tat is at te top in !>. %
!teps? 1K In te appin Desiner@ open a !ource >ualifier transformation. 2K !elect te Properties ta5. 3K %nter any num5er instead of :ero for #um5er of !orted ports. 4K Clic7 Apply ;K Clic7 O. 'alidate te mappin. !ave it. #ow refres session and save te canes. #ow run te wor7flow and see output.
3 SELECT DISTINCT: If we want te PowerCenter !erver to select uniBue values from a source@ we can use te !elect Distinct option. =ust cec7 te option in Properties ta5 to ena5le it. •
4 PRE !n' POST SL Co!n'( •
•
•
$e PowerCenter !erver runs pre;session !> commands aainst te source data5ase 5efore it reads te source. It runs post;session !> commands aainst te source data5ase after it writes to te taret. ,se a semi;colon FSG to separate multiple statements.
8 5SER DEFINED OINS %nterin a user;defined 6oin is similar to enterin a custom !> Buery. +owever@ we only enter te contents of te )+%R% clause@ not te entire Buery. •
)e can specify eBui 6oin@ left outer 6oin and rit outer 6oin only. )e cannot specify full outer 6oin. $o use full outer 6oin@ we need to write !> >uery.
!teps? 1K Open te !ource >ualifier transformation@ and clic7 te Properties ta5. 2K Clic7 te Open 5utton in te ,ser Defined =oin field. $e !> %ditor Dialo 5o< appears. 3K%nter te synta< for te 6oin.
4K Clic7 O ;K Aain O7. 'alidate te mappin. !ave it. #ow refres session and save te canes. #ow run te wor7flow and see output. o#n T0*&
S0n"!
%Bui =oin
D%P$.D%P$#OT%P.D%P$#O
eft Outer =oin
U%P %9$ O,$%R =OI# D%P$ O# D%P$.D%P$#OT%P.D%P$#OV
Rit Outer =oin
U%P RI&+$ O,$%R =OI# D%P$ O# D%P$.D%P$#OT%P.D%P$#OV
Curly braces are needed in Syntax. Try all the above & also FULL outer. Session fails for FULL OUTER. #$MP %&'' &T$R )*+ ,$PT + ,$PT.,$PT+-$MP.,$PT+
; SL 5ERY 9or relational sources@ te PowerCenter !erver enerates a Buery for eac !ource >ualifier transformation wen it runs a session. $e default Buery is a !%%C$ statement for eac source column used in te mappin. In oter words@ te PowerCenter !erver reads only te columns tat are connected to anoter transformation.
In mappin a5ove@ we are passin only !A and D%P$#O from !>L%P to Areator transformation. Default Buery enerated will 5e? •
SELECT EMP.SAL@ EMP.DEPTNO FROM EMP
6#&%#n- "+& D&f!)" &r0 1. Open te !ource >ualifier transformation@ and cli c7 te Properties ta5. 2. Open !> >uery. $e !> %ditor displays. 3. Clic7 &enerate !>.
4. $e !> %ditor displays te default Buery te PowerCenter !erver uses to select source data. . Clic7 Cancel to e
No"&: If %& 'o no" $!n$&) "+& SL J&r0@ "+& Po%&rC&n"&r S&r&r o&rr#'&( "+& '&f!)" J&r0 %#"+ "+& $("o SL J&r0. )e can enter an !> statement supported 5y our source data5ase. *efore enterin te Buery@ connect all te input and output ports we want to use in te mappin. % Buery for 9, O,$%R =OI#? !%%C$ D%P$.D%P$#O@ D%P$.D#A%@ D%P$.OC@ %P.%P#O@ %P.=O*@ %P.!A@ %P.CO@ %P.D%P$#O 9RO %P 9, O,$%R =OI# D%P$ O# D%P$.D%P$#OT%P.D%P$#O WHERE SAL2 •
%P.%#A%@
)e also added )+%R% clause. )e can enter more conditions and write more comple< !>.
W& $!n %r#"& !n0 J&r0. W& $!n 7o#n !( !n0 "!/)&( #n on& J&r0 !( r&J#r&' #f !)) !r& #n (!& '!"!/!(&. I" #( &r0 +!n'0 !n' (&' #n o(" of "+& *ro7&$"(.
I*or"!n" Po#n"(: •
•
•
•
)en creatin a custom !> Buery@ te !%%C$ statement must list te port names in te order in wic tey appear in te transformation. % mappin. !o wen we write !> >uery@ !%%C$ statement ave name D#A% first@ D#A% second and so on. !%%C$ D%P$.D%P$#O@ D%P$.D#A% W Once we ave written a custom Buery li7e a5ove@ ten tis Buery will always 5e used to fetc data from data5ase. In our e >uery. If Buery is not runnin in data5ase@ ten it wonEt wor7 in Informatica too. Also always connect to te data5ase and !)#'!"& te !> in !> Buery editor.
3.1; LOOK5P TRANSFORMATION • • •
•
•
Passive $ransformation Can 5e Connected or ,nconnected. Dynamic loo7up is connected. ,se a oo7up transformation in a mappin to loo7 up data in a flat file or a relational ta5le@ view@ or synonym. )e can import a loo7up definition from any flat file or relational data5ase to wic 5ot te PowerCenter Client and !erver can connect. )e can use multiple oo7up transformations in a mappin.
$e PowerCenter !erver Bueries te loo7up source 5ased on te loo7up ports in te transformation. It compares oo7up transformation port values to loo7up source column values 5ased on te loo7up condition. Pass te result of te loo7up to oter transformations and a taret. )e can use te oo7up transformation to perform followin? •
•
•
G&" ! r&)!"&' !)&: %P as D%P$#O 5ut D#A% is not tere. )e use oo7up to et D#A% from D%P$ ta5le 5ased on oo7up Condition. P&rfor ! $!)$)!"#on? )e want only tose %mployees woEs !A K Averae F!AG. )e will write oo7up Override Buery. 5*'!"& ()o%)0 $+!n-#n- '#&n(#on "!/)&(: ost important use. )e can use a oo7up transformation to determine weter rows already e
3.1;.1 LOOK5P TYPES )e can confiure te oo7up transformation to perform te followin types of loo7ups? • • •
Connected or ,nconnected Relational or 9lat 9ile Caced or ,ncaced
R&)!"#on!) Loo,*: )en we create a oo7up transformation usin a relational ta5le as a loo7up source@ we can connect to te loo7up source usin OD*C and import te ta5le definition as te structure for te oo7up transformation. )e can override te default !> statement if we want to add a )+%R% clause or Buery multiple ta5les. )e can use a dynamic loo7up cace wit relational loo7ups. •
•
F)!" F#)& Loo,*: )en we use a flat file for a loo7up source@ we can use any flat file definition in te repository@ or we can import it. )en we import a flat file loo7up source@ te Desiner invo7es te 9lat 9ile )i:ard.
C!$+&' or 5n$!$+&' Loo,*: )e can cec7 te option in Properties $a5 to Cace to loo7up or not. *y default@ loo7up is caced.
Conn&$"&' !n' 5n$onn&$"&' Loo,* Conn&$"&' Loo,*
5n$onn&$"&' Loo,*
Receives input values directly from te pipeline.
Receives input values from te result of a :LKP e
)e can use a dynamic or static cace.
)e can use a static cace.
Cace includes all loo7up columns used in te mappin.
Cace includes all loo7upHoutput ports in te loo7up condition and te loo7upHreturn port.
If tere is no matc for te loo7up condition@ te PowerCenter !erver returns te default value for all output ports.
If tere is no matc for te loo7up condition@ te PowerCenter !erver returns #,.
If tere is a matc for te condition@ te PowerCenter returns te result of te condition for all loo7upHoutput
If tere is a matc for te loo7up condition@ te PowerCenter !erver returns te result of te loo7up condition into te return port.
Pass multiple output anoter transformation.
loo7up !erver loo7up ports.
values
to
!upports user;defined default values.
Pass one output transformation.
value
to
anoter
Does not support user;defined default values.
3.1;.2 LOOK5P TF COMPONENTS Define te followin components wen we confiure a oo7up transformation in a mappin? oo7up source Ports Properties Condition • • • •
1. Loo,* Sor$&: )e can use a flat file or a relational ta5le for a loo7up source. )en we create a oo7up tHf@ we can import te loo7up source from te followin locations? Any relational source or taret definition in te repository Any flat file source or taret definition in te repository Any ta5le or file tat 5ot te PowerCenter !erver and Client macine can connect to • • •
$e loo7up ta5le can 5e a sinle ta5le@ or we can 6oin multiple ta5les in te same data5ase usin a loo7up !> override in Properties $a5.
2. Por"(: Por"(
Loo,* T0*&
N/&r N&&'&'
D&($r#*"#on
I
Connected ,nconnected
inimum 1
Input port to oo7up. ,sually ports used for =oin condition are Input ports.
O
Connected ,nconnected
inimum 1
Ports oin to anoter transformation from oo7up.
Connected ,nconnected
inimum 1
oo7up port. $e Desiner automatically desinates eac column in te loo7up source as a loo7up FG and output port FOG.
R
,nconnected
1 Only
Return port. ,se only in unconnected oo7up tHf only.
3. Pro*&r"#&( T!/ O*"#on(
Loo,* T0*&
D&($r#*"#on
oo7up !> Override
Relational
Overrides te default !> statement to Buery te loo7up ta5le.
oo7up $a5le #ame
Relational
!pecifies te name of te ta5le from wic te transformation loo7s up and caces values.
oo7up Cacin %na5led
9lat 9ile@ Relational
Indicates weter te PowerCenter !erver caces loo7up values durin te session.
oo7up Policy on ultiple atc
9lat 9ile@ Relational
Determines wat appens wen te oo7up transformation finds multiple rows tat matc te loo7up condition. O*"#on(: ,se 9irst 'alue or ,se ast 'alue or ,se Any 'alue or Report %rror
oo7up Condition
9lat 9ile@ Relational
Displays te loo7up condition you set in te Condition ta5.
Connection Information
Relational
!pecifies te data5ase containin te loo7up ta5le.
!ource $ype
9lat 9ile@ Relational
oo7up is from a data5ase or flat file.
oo7up Cace Directory #ame
9lat 9ile@ Relational
ocation were cace is 5uild.
oo7up Cace Persistent
9lat 9ile@ Relational
)eter to use Persistent Cace or not.
Dynamic oo7up Cace
9lat 9ile@ Relational
)eter to use Dynamic Cace or not.
Recace 9rom oo7up !ource
9lat 9ile@ Relational
$o re5uild cace if cace source canes and we are usin Persistent Cace.
Insert %lse ,pdate
Relational
,se only wit dynamic cacin ena5led. Applies to rows enterin te oo7up transformation wit te row type of insert .
,pdate %lse Insert
Relational
,se only wit dynamic cacin ena5led. Applies to rows enterin te oo7up transformation wit te row type of update.
oo7up Data Cace !i:e
9lat 9ile@ Relational
Data Cace !i:e
oo7up Inde< Cace !i:e
9lat 9ile@ Relational
Inde< Cace !i:e
Cace 9ile #ame Prefi<
9lat 9ile@ Relational
,se only wit persistent loo7up cace. !pecifies te file name prefi< to use wit persistent loo7up cace files.
So& o"+&r *ro*&r"#&( for F)!" F#)&( !r&: • • • • • •
Datetime 9ormat $ousand !eparator Decimal !eparator Case;!ensitive !trin Comparison #ull Orderin !orted Input
4: Con'#"#on T!/ )e enter te oo7up Condition. $e PowerCenter !erver uses te loo7up condition to test incomin values. )e compare transformation input values wit values in te loo7up source or cace@ represented 5y loo7up ports. • •
• • • •
$e datatypes in a condition must matc. )en we enter multiple conditions@ te PowerCenter !erver evaluates eac condition as an A#D@ not an OR. $e PowerCenter !erver matces null values. $e input value must meet all conditions for te loo7up to return a value. T@ K@ X@ KT@ XT@ YT Operators can 5e used. %
T#*: If we include more tan one loo7up condition@ place te conditions wit an eBual sin first to optimi:e loo7up performance. No"&: 1. )e can use T operator in case of Dynamic Cace. 2. $e PowerCenter !erver fails te session wen it encounters multiple 7eys for a oo7up transformation confiured to use a dynamic cace.
3.1;.3 Conn&$"&' Loo,* Tr!n(for!"#on E!*)&: To $r&!"& ! $onn&$"&' Loo,* Tr!n(for!"#on • •
•
%P will 5e source ta5le. D%P$ will 5e OO,P ta5le. Create a taret ta5le CO##Loo7upL%AP% in taret desiner. $a5le sould contain all ports of %P ta5le plus D#A% and OC as sown 5elow. Create te sortcuts in your folder.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. . ".
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %L%P to taret e
-. As DEPT is te !ource definition@ clic7 !ource and ten !elect D%P$.
K Clic7 O7.
18K #ow Pass D%P$#O from !>L%P to tis oo7up. D%P$#O from !>L%P will 5e named as D%P$#O1. %dit oo7up and rename it to I#LD%P$#O in ports ta5. 11K #ow o to CONDITION ta5 and add CO#DI$IO#. DEPTNO INDEPTNO and Clic7 Apply and ten O. in7 te mappin as sown 5elow?
12K )e are not passin I#LD%P$#O and D%P$#O to any oter transformation from OO,PS we can edit te loo7up transformation and remove te O,$P,$ cec7 from tem. 13K appin ;K 'alidate 14K Repository ;K !ave •
• •
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) "!/)&(. M!,& (r& "o -#& $onn&$"#on for LOOK5P T!/)& !)(o.
)e use Connected oo7up wen we need to return more tan one column from oo7up ta5le. $ere is no use of Return Port in Connected oo7up.
SEE PROPERT T!" FOR !#$!%CE# SETT%'S
3.1;.4 5n$onn&$"&' Loo,* Tr!n(for!"#on An unconnected oo7up transformation is separate from te pipeline in te mappin. )e write an e
•
%P will 5e source ta5le. D%P$ will 5e OO,P ta5le. Create a taret ta5le ,#CO##Loo7upL%AP% in taret desiner. $a5le sould contain all ports of %P ta5le plus D#A% as sown 5elow. Create te sortcuts in your folder.
Cr&!"#n- M!**#n-: S&& S!*)& !**#n- *#$"r& /&)o%: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %L%P to %PR%!!IO# transformation. (. Connect all fields from %PR%!!IO# to taret e
14. #ow !'' ! $on'#"#on #n Con'#"#on T!/. DEPTNO INDEPTNO and Clic7 Apply and ten O. 1. #ow we need to call tis oo7up from %
)e dou5le clic7 ,nconn in 5ottom of 9unctions ta5 and as we need only D%P$#O@ we pass only D%P$#O as input. 1(. 'alidate te call in %
•
• •
Cr&!"& S&((#on !n' Wor,f)o% !( '&($r#/&' &!r)#&r. Rn "+& %or,f)o% !n' (&& "+& '!"! #n "!r-&" "!/)&. M!,& (r& "o -#& $onn&$"#on #nfor!"#on for !)) "!/)&(. M!,& (r& "o -#& $onn&$"#on for LOOK5P T!/)& !)(o.
3.1;.8 Loo,* C!$+&( )e can confiure a oo7up transformation to cace te loo7up ta5le. $e Interation !ervice FI!G 5uilds a cace in memory wen it *ro$&((&( "+& f#r(" ro% of '!"! in a caced oo7up transformation. $e Interation !ervice also creates cace files 5y default in te ZPCaceDir. If te data does not fit in te memory cace@ te I! stores te overflow values in te cace files. )en session completes@ I! releases cace memory and deletes te cace files. • •
If %& (& ! f)!" f#)& )oo,*@ "+& IS !)%!0( $!$+&( "+& )oo,* (or$&. W& (&" "+& C!$+& "0*& #n Loo,* Pro*&r"#&(.
Loo,* C!$+& F#)&( 1. Loo,* In'& C!$+&: !tores data for te columns used in te loo7up condition. •
2. Loo,* D!"! C!$+&: 9or a connected oo7up transformation@ stores data for te connected output ports@ not includin ports used in te loo7up condition. 9or an unconnected oo7up transformation@ stores data from te return port. •
•
T0*&( of Loo,* C!$+&(: 1. S"!"#$ C!$+& *y default@ te I! creates a static cace. It caces te loo7up file or ta5le and loo7s up values in te cace for eac row tat comes into te transformation. $e I! does not update te cace wile it processes te oo7up transformation.
2. D0n!#$ C!$+& $o cace a taret ta5le or flat file source and insert new rows or update e
3. P&r(#("&n" C!$+& If te loo7up ta5le does not cane 5etween sessions@ we can confiure te oo7up transformation to use a persistent l oo7up cace. $e I! saves and reuses cace files from session to session@ eliminatin te time reBuired to read te loo7up ta5le.
4. R&$!$+& fro Sor$& If te persistent cace is not syncroni:ed wit te loo7up ta5le@ we can confiure te oo7up transformation to re5uild te loo7up cace. If oo7up ta5le as caned@ we can use tis to re5uild te loo7up cace.
8. S+!r&' C!$+& •
•
5nn!&' $!$+&: )en oo7up transformations in a mappin ave compati5le cacin structures@ te I! sares te cace 5y default. /ou can only sare static unnamed caces. N!&' $!$+&: ,se a persistent named cace wen we want to sare a cace file across mappins or sare a dynamic and a static cace. $e cacin structures must matc or 5e compati5le wit a named cace. /ou can sare static and dynamic named caces.
B#)'#n- Conn&$"&' Loo,* C!$+&( )e can confiure te session to 5uild caces seBuentially or concurrently. )en we 5uild seBuential caces@ te I! creates caces as te source rows enter te oo7up transformation. )en we confiure te session to 5uild concurrent caces@ te I! does not wait for te first row to enter te oo7up transformation 5efore it creates caces. Instead@ it 5uilds multiple caces concurrently. •
•
1. B#)'#n- Loo,* C!$+&( S&J&n"#!))0:
2. B#)'#n- Loo,* C!$+&( Con$rr&n")0:
•
To $onf#-r& "+& (&((#on "o $r&!"& $on$rr&n" $!$+&( %dit !ession ;K In Confi O56ect $a5;K Additional Concurrent Pipelines for oo7up Cace Creation ;K &ive a value ere FAuto *y DefaultG
No"&: T+& IS /#)'( $!$+&( for n$onn&$"&' Loo,*( (&J&n"#!))0 on)0.
3.1< 5PDATE STRATEGY TRANSFORMATION •
Active and Connected $ransformation
T#)) no%@ %& +!& on)0 #n(&r"&' ro%( #n or "!r-&" "!/)&(. W+!" #f %& %!n" "o *'!"&@ '&)&"& or r&7&$" ro%( $o#n- fro (or$& /!(&' on (o& $on'#"#on E!*)&: If Address of a C,!$O%R canes@ we can update te old address or 7eep 5ot old and new address. One row is for old and one for new. $is way we maintain te istorical data. ,pdate !tratey is used wit oo7up $ransformation. In D)+@ we create a oo7up on taret ta5le to determine weter a row already e
1. 5*'!"& S"r!"&-0 %#"+#n ! (&((#on: )en we confiure a session@ we can instruct te I! to ei ter treat all rows in te same way or use instructions coded into te session mappin to fla rows for different data5ase operations. S&((#on Conf#-r!"#on: %dit !ession ;K Properties ;K $reat !ource Rows as? FInsert@ ,pdate@ Delete@ and Data DrivenG. Insert is default. S*&$#f0#n- O*&r!"#on( for In'##'!) T!r-&" T!/)&(:
Yo $!n (&" "+& fo))o%#n- *'!"& ("r!"&-0 o*"#on(:
In(&r": !elect tis option to insert a row into a taret ta5le. D&)&"&: !elect tis option to delete a row from a ta5le. 5*'!"&: )e ave te followin options in tis situation? 5*'!"& !( 5*'!"&. ,pdate eac row flaed for update if it e
2. F)!--#n- Ro%( %#"+#n ! M!**#n)itin a mappin@ we use te ,pdate !tratey transformation to fla rows for insert@ delete@ update@ or re6ect. O*&r!"#on
Con("!n"
N&r#$ 6!)&
I#!%R$
DDLI#!%R$
8
,PDA
%$DDL,PDA
%$1
D%%
%$DDLD%%
%$2
R%=%C$
DDLR%=%C$
3
5*'!"& S"r!"&-0 E*r&((#on(: 9reBuently@ te update stratey e
$e a5ove e
For%!r'#n- R&7&$"&' Ro%(: )e can confiure te ,pdate !tratey transformation to eiter pass re6ected rows to te ne
!ee Properties $a5 for te Option.
S"&*(: 1. Create ,pdate !tratey $ransformation 2. Pass all ports needed to it. 3. !et te %
3.1= DYNAMIC CACHE WORKING )e can use a dynamic cace wit a relational loo7up or a flat file loo7up. 9or relational loo7ups@ we mit confiure te transformation to use a dynamic cace wen te taret ta5le is also te loo7up ta5le. 9or flat file loo7ups@ te dynamic cace represents te data to update in te taret ta5le. •
• •
Dynamic cace is used to INSERT n&% ro%( #n "!r-&" or 5PDATE $+!n-&' rows only. It is not ood to use wen data is ue. Dynamic Cace is ,PDA$%D as Interation !ervice reads rows from source.
)en te Interation !ervice reads a row from te source@ it updates te loo7up cace 5y performin one of te followin actions? Inserts te row into te cace if row is not in cace. ,pdates te row in te cace if row as caned. a7es no cane to te cace if tere is no cane in row. • • •
$o use Dynamic Cace@ first %dit oo7up $ransformation ;K Properties $a5 ;K !elect Dynamic Cace Option Also !elect Insert %lse ,pdate or ,pdate %lse Insert Option • •
• •
W& $!n *'!"& ! "!r-&" "!/)& on)0 %+&n #" +!( ! Pr#!r0 K&0. If "+&r& #( no Pr#!r0 K&0@ "+&n %& n&&' "o 5(& 5*'!"& O&rr#'& O*"#on #n Pro*&r"#&( T!/ of T!r-&" T!/)&.
D0n!#$ C!$+& Pro*&r"#&( N&%Loo,*Ro%: $e Desiner adds tis port to a oo7up transformation wen we select Dynamic Cace in Properties $a5. )e cannot edit or delete tis port. Interation !ervice FI!G assins a value to te port@ dependin on te action it performs to te loo7up cace. N&%Loo,*Ro% 6!)&
D&($r#*"#on
8
I! does not update or insert te row in te cace.
1
I! inserts te row into te cace.
2
I! updates te row into te cace.
A((o$#!"&' Por": Associate loo7up ports wit eiter an inputHoutput port or a seBuence ID. %ac oo7up Port is associated wit a source port so tat it can compare te canes. Also@ we can enerate of !eBuence 1@ 2@ 3 and so on wit it. !eBuence ID is availa5le wen datatype is Inteer or !mall Int.
I-nor& N)) In*"( for 5*'!"&( W& $an set tis property for every column. )e 6ust need to C+%C te port for wic we want to use tis property. !uppose@ in taret te CO of an %mployee is 88 5ut in !ource te new CO is #,@ and we do not want te #, to 5e updated in taret. )e use te a5ove property for it.
I-nor& In Co*!r#(on: )en we do not want to compare any column in source wit taret@ ten we can use tis option. %
In !/o&: $e top most port is #ewoo7upRow. ItEs idden. All oo7up ta5le ports ave 5een PR%'L 5efore tem. %#A% as 5een associated wit PR%'L%#A% and so are oters. PR%'LCO port as 5een cec7ed for Inore #ull Inputs for updates. PR%'L+IR%DA$% as 5een cec7ed for Inore in Comparison. • • • • •
E!*)&: Wor,#n- %#"+ D0n!#$ C!$+& (#n- 5*'!"& S"r!"&-0. • •
•
%P will 5e source ta5le. Create a taret ta5le D/#AICLOO,P. !tructure same as %P. a7e %P#O as Primary ey. Create !ortcuts as necessary.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %L%P to oo7up $ransformation. -. %dit loo7up transformation. %dit all ports and add PR%'L 5efore tem. . Also remove 1 added in names of all ports comin from !>L%P. 18. #ow &o to Properties ta5 ;K !elect Dynamic Cace and Insert %lse ,pdate Option. 11. #ow Associate ports and !et Inore #ull Inputs or Inore In Comparison Option as sown in Picture a5ove. 12. $ransformation ;K Create ;K !elect 9ilter ;K &ive name and Clic7 Done. 13. Pass all ports as sown in mappin 5elow to filter and ive condition? NewLookupRow!=0 14. $ransformation ;K Create ;K !elect ,pdate !tratey ;K &i ve name and Clic7 Done. 1. Pass all ports from 9ilter to ,pdate !tratey and ive ,pdate !tratey %
Conf#-r#n- S&((#on( %#"+ ! D0n!#$ Loo,* C!$+& %dit !ession ;K Properties ;K $reat !ource Rows as : D!"! Dr#&n as we used ,pdate !tratey. )e must also define te followin update stratey taret ta5le options? !elect Insert !elect ,pdate as ,pdate Do not select Delete • • •
•
Create !ession and )or7flow as usual. 9irst time all rows will 5e inserted. #ow Cane te data of taret ta5le in Oracle and Run wor7flow aain. /ou can see ow te data is updated as per te properties selected.
•
SESSION WILL FAIL FOR THIS. SEE 3.1? LOOK5P 5ERY
•
)e pass te data from oo7up Cace and not source to 9ilter. $is is 5ecause te Cace is updated reularly and contains te most updated data. E!*)& !ource? EMPNO 888 881 882 883
of $!$+&: N!& SAL Amit umar 888 Raul !in 88 !an6ay -888 !umit !in (888
DEPTNO 18 28 38 28
Data Already in taret? EMPNO N!& SAL 888 Amit umar -888 881 Raul !in 88
DEPTNO 18 28
Initial Cace? N&%)oo,*Ro%
,pdated Cace? N&%)oo,*Ro% 2 8 1 1
EMPNO 888 881
N!& Amit umar Raul !in
SAL -888 88
DEPTNO 18 28
EMPNO 888 881 882 883
N!& SAL Amit umar 888 Raul !in 88 !an6ay -888 !umit !in (888
DEPTNO 18 28 38 28
3.1? LOOK5P 5ERY $e wor7flow for D/#AIC CAC+% will fail. $e reason for tis is OO,P >uery. )e can see te default oo7up >uery in Pro*&r"#&( "!/ #n Loo,* O&rr#'&. !teps? 1. E'#" Loo,* Tr!n(for!"#on Pro*&r"#&( T!/ 2. Loo,* SL O&rr#'& G&n&r!"& SL $e default !> for a5ove e
!%%C$ DynamicLoo7up.ENAME as PRE6ENAME@ DynamicLoo7up.OB as PRE6OB@ DynamicLoo7up.&R as PR%'L&R@ DynamicLoo7up.+IR%DA$% as PR%'L+IR%DA$%@ DynamicLoo7up.!A as PR%'L!A@ DynamicLoo7up.CO as PR%'LCO@ DynamicLoo7up.D%P$#O as PR%'LD%P$#O@ DynamicLoo7up.%P#O as PR%'L%P#O 9RO DynamicLoo7up !ee te convention ere. %#A% is column name in ta5le in Data5ase 5ut in oo7up te column name is PR%'L%#A%. !o we need to write Buery in suc way tat column name in ta5le is matced to port name in oo7up. •
Also if in a5ove@ we will not write A!@ ten loo7up will not wor7 and an %RROR $%L(881 is displayed. It is mandatory to write A! after a column in loo7up.
!o we always need to write Buery as !%%C$ CO,#1 as COL1@ CO,#2 as COL2@ CO,#3 as COL3 9RO A*C H&r& COL1@ COL2@ COL3 !r& LOOK5P PORTS NAME.
DEFA5LT LOOK5P 5ERY $e default loo7up Buery contains te followin statements? SELECT: $e !%%C$ statement includes all te loo7up ports in te mappin. /ou can view te !%%C$ statement 5y eneratin !> usin te oo7up !> Override property. ORDER BY: $e ORD%R */ clause orders te columns in te same order tey appear in te oo7up transformation. $e Interation !ervice enerates te ORD%R */ clause. W& $!nno" #&% "+#( %+&n 0o -&n&r!"& "+& '&f!)" SL usin te oo7up !> Override property. )e can see tis after we run wor7flow and ten #&% S&((#on Lo-. $o increase performance@ we can suppress te default ORD%R */ clause and enter an override ORD%R */ wit fewer columns. P)!$& "%o '!(+&( !f"&r "+& ORDER */ override to suppress te enerated ORD%R */ clause. %
• •
•
Wr#"#n- ! Loo,* &r0 "+!" #no)&( or& "+!n 1 "!/)&: 9irst Create a oo7up and all te ports needed manually or create a loo7up on ta5le avin mauery? !%%C$ A as A@ * as *@ C as C@ D as D 9RO Fselect a@ 5@ c@ d from A*C@
3.2 Loo,* !n' 5*'!"& S"r!"&-0 E!*)&( E!*)&1: To #n(&r" #f r&$or' #( no" *r&(&n" #n "!r-&" !n' 5*'!"& #f r&$or' +!( $+!n-&'. • • •
%P will 5e source ta5le. Create a taret ta5le I#!L,PDL#OLPL%AP%. !tructure same as %P. Create !ortcuts as necessary.
Cr&!"#n- M!**#n-: 9P#$"r& n&" *!-& 1. 2. 3. 4. . ".
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %L%P t o it. $ransformation;K Create ;K !elect OO,P from list. &ive name and clic7 Create. (. Create an unconnected loo7up on ,PDLI#!L#OLPL%AP%. eep only %P#O in loo7up and set it as return port. Add I#L%P#O port and add condition in Condition $a5. -. Create an output port O,$L#%)L9A& and cal l te oo7up. IIFFISN5LLF:LKP.LKPTRANSF%P#OGG@8@1G . 8 means new record and 1 means old record tat are to 5e updated. 18. #ow create a router transformation and pass all ports from %P to Router. 11. Create two roups in Router. #%)LR%CORD and C+A#&%DLR%CORD. Condition for #%)LR%CORD? UT_NE7_FLA*=0an2 Condition for UT_NE7_FLA*=0 C+A#&%DLR%CORD? UT_NE7_FLA*=1 UT_NE7_FLA*=1 12. $ransformation ;K Create ;K !elect ,pdate !tratey ;K &i ve name ,PDLI#!LR%CORD and Clic7 Done. 13. $ransformation ;K Create ;K !elect ,pdate !tratey ;K &i ve name ,PDL,PDA$%LR%CORD and Clic7 Done. 14. Pass records from #%)LR%CORD roup to I#!L#%)LR%CORD and in ,pdate !tratey %
•
• • •
M!,& S&((#on. Do no" (&)&$" Trn$!"& !n' D&)&"& o*"#on for "!r-&"(. A)(o 5(& NORMAL o'&. M!,& Wor,f)o% Rn !n' S&& '!"!. No% $+!n-& "+& '!"! #n "!r-&" !n' *'!"& (o& f#&)'( !n' '&)&"& 34 ro%(. Rn %or,f)o% !-!#n !n' (&& "+& "!r-&" '!"!. T+&r& #( no *'!"&. T+&r& #( no *'!"& !( "+&r& #( no Pr#!r0 K&0.
A( "+&r& #( Pr#!r0 ,&0@ %& n&&' "o %r#"& 5*'!"& O&rr#'&. !teps? 1. %dit taret ta5le I#!L,PDL#OLPL%AP%1. $is is copy of ta5le definition. 2. Properties $a5 ;K ,pdate Override ;K &enerate !>. Default !> is? ,PDA$% I#!L,PDL#OLPL%AP% !%$ %P#O T ?$,.%P#O@ %#A% T ?$,.%#A%@ =O* T ?$,.=O*@ &R T ?$,.&R@ +IR%DA$% T ?$,.+IR%DA$%@ !A T ?$,.!A@ CO T ?$,.CO@ D%P$#O T ?$,.D%P$#O 3. $ere is no )ere clause in tis !>. )e need to modify it. UPDATEINS_UPD_N_P8_E9A%PLESETENA%E=:TU#ENA%E,'=:TU#',%*R =:TU#%*R,+IREDATE=:TU#+IREDATE,SAL=:TU#SAL,C%%=:TU#C%%,DEPTN =:TU#DEPTN7+EREE%PN=:TU#E%PN
4. . ". (.
Paste tis modified !> tere and clic7 O. Clic7 Apply ;K O. appin ;K 'alidate Repository !ave. • • •
Refres !ession 5y dou5le clic7in on it. !ave all te canes and run wor7flow aain. #ow see data as 5een updated.
E!*)&2: To In(&r" #f r&$or' #( no" *r&(&n" #n "!r-&" !n' D&)&"& #" #f SAL #n Sor$& S!) #n T!r-&". • •
• •
%P will 5e source ta5le. Create a taret ta5le I#!LD%%$%L%AP%. !tructure same as %P. a7e %P#O as Primary ey. W& $!nno" '&)&"& ! r&$or' !( "+&r& #( no Pr#!r0 K&0. Create !ortcuts as necessary.
Cr&!"#n- M!**#n-: FPicture on ne
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %L%P t o it. $ransformation;K Create ;K !elect OO,P from list. &ive name and clic7 Create. (. Create connected loo7up and eep only %P#O and !A in loo7up. Add I#L%P#O port and add condition in Condition $a5. -. Pass %P from !>L%P to oo7up. Pass %P#O and !A from oo7up to %
•
•
•
M!,& S&((#on. Do no" (&)&$" Trn$!"& !n' S&)&$" D&)&"& o*"#on for "!r-&"(. A)(o 5(& NORMAL o'&. M!,& Wor,f)o% Rn !n' S&& '!"!. No% $+!n-& "+& '!"! #n "!r-&" !n' *'!"& (o& SAL f#&)' #n "!r-&". D&)&"& 34 ro%(. Rn %or,f)o% !-!#n !n' (&& "+& "!r-&" '!"!.
3.21 STORED PROCED5RE TF • • •
Passive $ransformation Connected and ,nconnected $ransformation !tored procedures are stored and run witin te data5ase.
A !tored Procedure transformation is an important tool for populatin and maintainin data5ases. Data5ase administrators create stored procedures to automate tas7s tat are too complicated for standard !> statements. ,se of !tored Procedure in mappin? Cec7 te status of a taret data5ase 5efore loadin data into it. Determine if enou space e
D!"! P!((&( B&"%&&n IS !n' S"or&' Pro$&'r& One of te most useful features of stored procedures is te a5ility to send data to te stored procedure@ and receive data from te stored procedure. $ere are tree types of data tat pass 5etween te Interation !ervice and te stored procedure? In*"o"*" *!r!&"&r(? Parameters we ive as input and te parameters returned from !tored Procedure. R&"rn !)&(: 'alue returned 5y !tored Procedure if any. S"!"( $o'&(: !tatus codes provide error andlin for te I! durin a wor7flow. $e stored procedure issues a status code tat notifies weter or not te stored procedure completed successfully. W& $!nno" (&& "+#( !)&. $e I! uses it to determine weter to continue runnin te session or stop.
S*&$#f0#n- %+&n "+& S"or&' Pro$&'r& Rn( Nor!): $e stored procedure runs were te transformation e
5(#n- ! S"or&' Pro$&'r& #n ! M!**#n1. 2. 3. 4. .
Create te stored procedure in te data5ase. Import or create te !tored Procedure transformation. Determine weter to use te transformation as connected or unconnected. If connected@ map te appropriate input and output ports. If unconnected@ eiter confiure te stored procedure to run pre; or post; session@ or confiure it to run from an e
S"or&' Pro$&'r&(: Connect to !ource data5ase and create te stored procedures iven 5elow? CR%A$% OR R%PAC% procedure spLa FinLdeptno in num5er@ ma
CR%A$% OR R%PAC% procedure spLunconnL1LvalueFinLdeptno in num5er@ ma
3.21.1 Conn&$"&' S"or&' Pro$&'r& TF E!*)&: To -#& #n*" !( DEPTNO fro DEPT "!/)& !n' f#n' "+& MA@ MIN@ A6G !n' S5M of SAL fro EMP "!/)&. •
•
D%P$ will 5e source ta5le. Create a taret ta5le !PLCO##L%AP% wit fields D%P$#O@ AL!A@ I#L!A@ A'&L!A [ !,L!A. )rite !tored Procedure in Data5ase first and Create sortcuts as needed.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %
". Dra D%P$#O from !>LD%P$ to te stored procedure input port and also to D%P$#O port of taret. (. Connect te ports from procedure to taret as sown 5elow?
-. appin ;K 'alidate . Repository ;K !ave • • • •
Cr&!"& S&((#on !n' "+&n %or,f)o%. G#& $onn&$"#on #nfor!"#on for !)) "!/)&(. G#& $onn&$"#on #nfor!"#on for S"or&' Pro$&'r& !)(o. Rn %or,f)o% !n' (&& "+& r&()" #n "!/)&.
3.21.2 5n$onn&$"&' S"or&' Pro$&'r& TF An unconnected !tored Procedure transformation is not directly connected to te flow of data trou te mappin. Instead@ te stored procedure runs eiter? • •
Fro !n &*r&((#on: Called &*r&((#on: Called from an e
M&"+o' of r&"rn#n- "+& !)& of o"*" *!r!&"&r( "o ! *or": • •
Assin te output value to a local varia5le. Assin te output value to te system varia5le PROCRES5LT PROCRES5LT.. F!ee aterG
S !) !( o"*". E!*)& 1: DEPTNO !( #n*" !n' -&" MA of S!) • • •
D%P$ will 5e source ta5le. Create a taret ta5le wit fields D%P$#O and AL!A of decimal datatype. )rite !tored Procedure in Data5ase first and Create sortcuts as needed.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. . ". (. -. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %LD%P$ to %
Clic7 O and connect te port from e
18. appin ;K 'alidate 11. Repository !ave. • • • •
Cr&!"& S&((#on !n' "+&n %or,f)o%. G#& $onn&$"#on #nfor!"#on for !)) "!/)&(. G#& $onn&$"#on #nfor!"#on for S"or&' Pro$&'r& !)(o. Rn %or,f)o% !n' (&& "+& r&()" #n "!/)&.
PROCRES5LT (&: •
•
If te stored procedure returns a sinle output parameter or a return value@ val ue@ we te reserved varia5le PROCLR%!,$ as te output varia5le. %
E!*)& 2: DEPTNO !( In*" !n' MASAL@ MINSAL@ A6GSAL !n' S5MS AL !( OP. S"or&' Pro$&'r& "o 'ro* #n'& #n Pr& Lo!' of T!r-&" S"or&' Pro$&'r& "o $r&!"& #n'& #n Po(" Lo!' of T!r-&" •
•
D%P$ will 5e source ta5le. Create a taret ta5le SP5NCONNEAMPLE wit fields D%P$#O@ AL!A@ I#L!A@ A'&L!A [ !,L!A. )rite !tored Procedure in Data5ase first and Create sortcuts as needed.
Stored procedures are !iven belo bel o to drop and create inde0 on tar!et. Ma1e sure to create tar!et table first.
S"or&' Pro$&'r&( "o /& $r&!"&' #n n&" &!*)& #n T!r-&" D!"!/!(&:
Create or replace procedure p rocedure CR%A$%LI#D% As *ein %
Cr&!"#n- M!**#n-: 1. 2. 3. 4. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %LD%P$ to %
. Call te procedure in last varia5le port 'ARL!,L!A. 18. :SP.SPAGG FD%P$#O@ 'ARLAL!A@ 'ARLI#L!A@ 'ARLA'&L!A@ PROCLR%!,$G 11. Clic7 Apply and O7. 12. Connect to taret ta5le as needed. 13. $ransformation ;K Import !tored Procedure ;K &ive Data5ase Connection for taret ;K Connect ;K !elect te procedure CR%A$%LI#D% and DROPLI#D% from te list. Clic7 O. 14. %dit DROPLI#D% ;K Properties $a5 ;K !elect $aret Pre oad as !tored Procedure $ype and in call te
1". appin ;K 'alidate 1(. Repository ;K !ave
• • • •
•
Cr&!"& S&((#on !n' "+&n %or,f)o%. G#& $onn&$"#on #nfor!"#on for !)) "!/)&(. G#& $onn&$"#on #nfor!"#on for S"or&' Pro$&'r&( !)(o. A)(o !,& (r& "+!" 0o &&$"& "+& *ro$&'r& CREATEINDE on '!"!/!(& /&for& (#n- "+& #n !**#n-. $is is 5ecause@ if tere is no I#D% on taret ta5le@ DROPLI#D% will fail and !ession will also fail. Rn %or,f)o% !n' (&& "+& r&()" #n "!/)&.
3.22 SE5ENCE GENERATOR TF Passive and Connected $ransformation. $e !eBuence &enerator transformation enerates numeric values. ,se te !eBuence &enerator to create uniBue primary 7ey values@ replace missin primary 7eys@ or cycle trou a seBuential rane of num5ers. )e use it to enerate !urroate ey in D)+ environment mostly. )en we want to maintain istory@ ten we need a 7ey oter tan Primary ey to uniBuely identify te record. !o we create a !eBuence 1@2@3@4 and so on. )e use tis seBuence as te 7ey. %
S&J&n$& G&n&r!"or Por"( $e !eBuence &enerator transformation provides two output ports? #%$'A and C,RR'A. )e cannot edit or delete tese ports. i7ewise@ we cannot add ports to te transformation.
NET6AL: ,se te #%$'A port to enerate seBuence num5ers 5y connectin it to a transformation or taret. 9or e
!eBuence in $a5le 1 will 5e enerated first. )en ta5le 1 as 5een loaded@ only ten seBuence for ta5le 2 will 5e enerated.
C5RR6AL: C,RR'A is #%$'A plus te Increment *y value. )e typically only connect te C,RR'A port wen te #%$'A port is already connected to a downstream transformation. If we $onn&$" "+& C5RR6AL *or" witout connectin te #%$'A port@ te Interation !ervice *!((&( ! $on("!n" !)& for eac row. )en we connect te C,RR'A port in a !eBuence &enerator transformation@ te Interation !ervice processes one row in eac 5loc7. )e can optimi:e performance 5y connectin only t e #%$'A port in a mappin. •
•
•
E!*)&: To (& S&J&n$& G&n&r!"or "r!n(for!"#on • •
•
%P will 5e source. Create a taret %PL!%>L&%#L%AP% in sared folder. !tructure same as %P. Add two more ports #%$L'A,% and C,RRL'A,% to te taret ta5le. Create sortcuts as needed.
Cr&!"#n- M!**#n-: 1. 2. 3. 4. . ".
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %L%P to taret ta5le. $ransformation ;K Create ;K !elect !eBuence &enerator for list ;K Create ;K Done (. Connect #%$L'A and C,RRL'A from !eBuence &enerator to taret. -. 'alidate appin . Repository ;K !ave
• • •
Cr&!"& S&((#on !n' "+&n %or,f)o%. G#& $onn&$"#on #nfor!"#on for !)) "!/)&(. Rn %or,f)o% !n' (&& "+& r&()" #n "!/)&.
SEE Pro*&r"#&( for or& S&""#n-( on N&" P!-&:
S&J&n$& G&n&r!"or Pro*&r"#&(: S&""#n-
R&J#r&' O*"#on!)
D&($r#*"#on
!tart 'alue
ReBuired
!tart value of te enerated seBuence tat we want I! to (& #f %& (& C0$)& o*"#on. Default is 8.
Increment *y
ReBuired
Difference 5etween two consecutive values from te #%$'A port.
%nd 'alue
Optional
a
Current 'alue
Optional
F#r(" !)& #n "+& (&J&n$&. If cycle option used@ te value must 5e reater tan or eBual to te start value and less te end value.
Cycle
Optional
If selected@ te Interation !ervice cycles trou te seBuence rane. %
Reset
Optional
*y default@ last value of seBuence durin session is saved to repository. #e
P)&!(& "r0 "o (& *ro*&r"#&( 0or(&)f "o ,no% or&.
POINTS: •
• •
•
•
•
If Current value is 1 and end value 18@ no cycle option. $ere are 1( records in source. In "+#( $!(& (&((#on %#)) f!#). If we connect 6ust C,RRL'A only@ te value will 5e same for all records. If Current value is 1 and end value 18@ cycle option tere. S"!r" !)& #( . $ere are 1( records in source. !eBuence? 1 2 1. 1 2 3 $o ma7e a5ove seBuence as 1;18 1;28@ -#& S"!r" 6!)& !( 1. !tart value is used alon wit Cycle option only. If Current value is 1 and end value 18@ cycle option tere. S"!r" !)& #( 1. $ere are 1< r&$or'( in source. !ession runs. 1;18 1;(. < %#)) /& (!&' #n r&*o(#"or0. If we run session aain@ seBuence will start from -. ,se r&(&" o*"#on if you want to start seBuence from C,RRL'A every time.
3.23 MAPPLETS • •
•
A mapplet is a reusa5le o56ect tat we create in te applet Desiner. It contains a set of transformations and lets us reuse tat transformation loic in multiple mappins. Created in applet Desiner in Desiner $ool.
)e need to use same set of transformations in say 18 mappins. !o instead of ma7in transformations in every 18 mappin@ we create a mapplet of tese transformations. #ow we use tis mapplet in all 18 mappins. E!*)&: To $r&!"& ! (rro-!"& ,&0 #n "!r-&". )e create a mapplet usin a stored procedure to create Primary 7ey for taret ta5le. )e ive taret ta5le name and 7ey column name as input to mapplet and et te !urroate 7ey as output. applets elp simplify mappins in te followin ways? •
• • •
•
In$)'& (or$& '&f#n#"#on(: ,se multiple source definitions and source Bualifiers to provide source data for a mappin. A$$&*" '!"! fro (or$&( #n ! !**#nIn$)'& )"#*)& "r!n(for!"#on(? As many transformations as we need. P!(( '!"! "o )"#*)& "r!n(for!"#on(: )e can create a mapplet to feed data to multiple transformations. %ac Output transformation in a mapplet represents one output roup in a mapplet. Con"!#n n(&' *or"(: )e do not ave to connect all mapplet input and output ports in a mappin.
M!**)&" In*": applet input can oriinate from a source definition andHor from an Input transformation in te mapplet. )e can create multiple pipelines in a mapplet. • •
)e use M!**)&" In*" transformation to ive input to mapplet. ,se of M!**)&" In*" transformation is optional.
M!**)&" O"*": $e output of a mapplet is not connected to any taret ta5le. )e must use M!**)&" O"*" transformation to store mapplet output. A mapplet must contain at least one Output transformation wit at least one connected port in te mapplet. • •
E!*)&1: W& %#)) 7o#n EMP !n' DEPT "!/)&. T+&n $!)$)!"& "o"!) (!)!r0. G#& "+& o"*" "o !**)&" o" "r!n(for!"#on. • •
%P and D%P$ will 5e source ta5les. Output will 5e iven to transformation appletLOut.
S"&*(: 1. 2. 3. 4. . ". (.
Open folder were we want to create te mappin. Clic7 $ools ;K applet Desiner. Clic7 applets;K Create;K &ive name. %
5(& of !**)&" #n !**#n-: •
•
•
)e can mapplet in mappin 5y 6ust drain te mapplet from mapplet folder on left pane as we dra source and taret ta5les. )en we use te mapplet in a mappin@ te mapplet o56ect displays only te ports from te Input and Output transformations. $ese are referred to as t e mapplet input and mapplet output ports. a7e sure "o -#& $orr&$" $onn&$"#on #nfor!"#on #n (&((#on.
M!,#n- ! !**#n-: W& %#)) (& *)"&!*)&1@ !n' "+&n $r&!"& ! f#)"&r "r!n(for!"#on "o f#)"&r r&$or'( %+o(& To"!) S!)!r0 #( 18. • •
mpltLe
Cr&!"#n- M!**#n1. 2. 3. 4. . ". (.
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %
• • • •
M!,& (&((#on !n' %or,f)o%. G#& $onn&$"#on #nfor!"#on for !**)&" (or$& "!/)&(. G#& $onn&$"#on #nfor!"#on for "!r-&" "!/)&. Rn %or,f)o% !n' (&& r&()".
E!*)&2: W& %#)) 7o#n EMP !n' DEPT "!/)&. T+& *or"( of DEPT "!/)& %#)) /& *!((&' "o !**)&" #n !**#n-. W& %#)) (& MAPPLETINP5T "o *!(( *or"( of DEPT "o 7o#n&r. T+&n $!)$)!"& "o"!) (!)!r0. G#& "+& o"*" "o !**)&" o" "r!n(for!"#on. • • •
%P will 5e source ta5le in mapplet desiner. D%P$ ports will 5e created in APP%$ I#P,$ and passed to 6oiner. Output will 5e iven to transformation appletLOut.
S"&*(: 1. Open folder were we want to create te mappin. 2. Clic7 $ools ;K applet Desiner. 3. Clic7 applets;K Create;K &ive name. %
M!,#n- ! !**#n-: W& %#)) (& *)"&!*)&2@ !n' "+&n $r&!"& ! f#)"&r "r!n(for!"#on "o f#)"&r r&$or'( %+o(& To"!) S!)!r0 #( 18. • •
mpltLe
Cr&!"#n- M!**#n1. 2. 3. 4. . ". (. -.
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %
• • • •
M!,& (&((#on !n' %or,f)o%. G#& $onn&$"#on #nfor!"#on for !**)&" (or$& "!/)&(. G#& $onn&$"#on #nfor!"#on for "!r-&" "!/)&. Rn %or,f)o% !n' (&& r&()".
3.24 NORMALI>ER TRANSFORMATION • •
•
•
Active and Connected $ransformation. $e #ormali:er transformation normali:es records from CO*O and relational sources@ allowin us to orani:e te data. ,se a #ormali:er transformation instead of te !ource >ualifier transformation wen we normali:e a CO*O source. )e can also use te #ormali:er transformation wit relational sources to create multiple rows from a sinle row of data.
E!*)& 1: To $r&!"& 4 r&$or'( of &&r0 &*)o0&& #n EMP "!/)&. • •
•
%P will 5e source ta5le. Create taret ta5le #ormali:erLultipleLRecords. !tructure same as %P and datatype of +IR%DA$% as 'ARC+AR2. Create sortcuts as necessary.
Cr&!"#n- M!**#n1. 2. 3. 4. . ". (. -. .
Open folder were we want to create te mappin. Clic7 $ools ;K appin Desiner. Clic7 appin;K Create;K &ive name. %L%P to %
13. Clic7 Apply and ten O. 14. Add lin7 as sown in mappin 5elow?
1. appin ;K 'alidate 1". Repository ;K !ave • • •
M!,& (&((#on !n' %or,f)o%. G#& $onn&$"#on #nfor!"#on for (or$& !n' "!r-&" "!/)&. Rn %or,f)o% !n' (&& r&()".
E!*)& 2: To /r&!, ro%( #n"o $o)n( Sor$&: RollL#um5er 188 181 182
#ame Amit Raul =essie
%#& ("( "
T!r-&": RollL#um5er 188 188 188 181 181 181 182 182 182
#ame Amit Amit Amit Raul Raul Raul =essie =essie =essie
ar7s ("( 8 "( -( (" - (
• •
•
• • •
•
+I#DI "( -( -
A$+! 8 ((
a7e source as a flat file. Import it and create taret ta5le. Create appin as 5efore. In #ormali:er ta5@ create only 3 ports RollL#um5er@ #ame and ar7s as tere are 3 columns in taret ta5le. Also as we ave 3 mar7s in source@ ive O$$r( !( 3 for M!r,( #n Nor!)#&r "!/. Connect accordinly and connect to taret. 'alidate and !ave a7e !ession and wor7flow and Run it. &ive !ource 9ile Directory and !ource 9ile name for source flat file in source properties in mappin ta5 of session. !ee te result.
3.28 ML Sor$&( I*or" !n' (!-& • • •
$is is to import sources and use it in our mappin. In case of sources@ >ualifier is used 5y default. Active and Connected $ransformation.
S"&*(: 1. Open !ared 9older ;K $ools ;K !ource Analy:er 2. !ources ;K Import Definition. 3. *rowse for location were file is present. $o import te definition@ we sould ave file in our local system on wic we are wor7in. 4. !elect te file and clic7 open. . Option for Override Infinite ent is not set. Do you want to set it i s displayed. ". Clic7 /es. (. Cec7 O&rr#'& !)) #nf#n#"& )&n-"+( %#"+ !)& and ive value as 2. -. Do not modify oter options and Clic7 O7. . Clic7 #%$ and ten clic7 9I#I!+ 18. Definition as 5een imported and can 5e used in mappin as we select oter sources.
SESSION PROPERTIES • • • •
Open te session for mappin were we used sources. In mappin ta5@ select te source. In properties@ we do not i ve relational connection ere. )e ive !ource 9ile Directory and !ource 9il ename information.
3.2; MAPPING WI>ARDS $e Desiner provides two mappin wi:ards to elp us create mappins Buic7ly and easily. *ot wi:ards are desined to create mappins for loadin and maintainin star scemas@ a series of dimensions related to a central fact ta5le. No"&: 2e do not use them in projects and instead ma1e the mappin!s manuall3. $wo wi:ards are? 1. &ettin !tarted )i:ard 2. !lowly Canin Dimensions )i:ard ,se te followin sources wit a mappin wi:ard? 9lat file Relational Application !ortcut to a flat file@ relational@ or Application sources • • • •
3.2;.1 G&""#n- S"!r"&' W#!r' It creates mappins to load static fact and dimension ta5les and slowly rowin dimension ta5les. $e &ettin !tarted )i:ard can create two types of mappins? !imple Pass $rou !lowly &rowin $aret • •
1. SIMPLE PASS THRO5GH • •
•
oads a static fact or dimension ta5le 5y insertin all rows. ,se tis mappin wen we want to drop all e
S"&*(: 1. Open te folder were we want to create te mappin. 2. In te appin Desiner@ clic7 appins K )i:ards K &ettin !tarted. 3. %nter a mappin name and select !imple Pass $rou@ and clic7 ne
2. SLOWLY GROWING TARGET • •
•
oads a slowly rowin fact or dimension ta5le 5y insertin new rows. ,se tis mappin to load new data wen e
H!n')#n- K&0(: )en we use te !lowly &rowin $aret option@ te Desiner creates an additional column in taret@ PMPRIMARYKEY. In tis column@ te Interation !ervice enerates a primary 7ey for eac row written to te taret@ incrementin new 7ey values 5y 1. S"&*(: 1. Open te folder were we want to create te mappin. 2. In te appin Desiner@ clic7 appins K )i:ards K &ettin !tarted. 3. %nter a mappin name and select !lowly &rowin $aret@ and clic7 ne
•
)e select %P#O as it is 7ey column in te source to compare wit taret.
(. Clic7 9inis. -. $o save te mappin@ clic7 Repository K !ave. No"&: T+& F#&)'( "o Co*!r& for C+!n-&( f#&)' #( '#(!/)&' for "+& S)o%)0 Gro%#n- T!r-&"( !**#n-.
S)o%)0 Gro%#n- "!r-&" &!*)&
3.2;.2 S)o%)0 C+!n-#n- D#&n(#on W#!r' It creates mappins to load slowly canin dimension ta5les 5ased on te amount of istorical dimension data we want to 7eep and te metod we coose to andle istorical dimension data. $e !CD wi:ard can create followin types of mappins? !CD $ype 1 Dimension mappin !CD $ype 2 DimensionH'ersion Data mappin !CD $ype 2 DimensionH9la Current mappin !CD $ype 2 DimensionH%ffective Date Rane mappi n !CD $ype 3 Dimension mappin • • • • •
1. SCD TYPE 1 DIMENSION MAPPING • •
•
If row e
H!n')#n- K&0(: )en we use te !CD $ype1 option@ te Desiner creates an additional column in taret@ PMPRIMARYKEY. 'alue incremented 5y N1. S"&*(: 1. Open te folder were we want to create te mappin. 2. In te appin Desiner@ clic7 appins K )i:ards K !lowly Canin Dimension. 3. %nter a mappin name and select $ype 1 Dimension@ and clic7 #e
-. Clic7 9inis. . $o save te mappin@ clic7 Repository K !ave. Conf#-r#n- S&((#on: In te session properties@ clic7 te $aret Properties settins on te appins ta5. $o ensure te Interation !ervice loads rows to te taret properly@ select Insert and ,pdate as ,pdate for eac relational taret. F)o%1: N&% r&$or' #( #n(&r"&' #n"o "!r-&" "!/)&. F)o%2: C+!n-&' r&$or' #( *'!"&' #n"o "!r-&" "!/)&.
No"&: *n the T3pe 4 ,imension mappin!5 the ,esi!ner uses to instances of the same tar!et definition to enable insertin! and updatin! data in the same tar!et table. 6enerate onl3 one tar!et table in the tar!et database.
2. SCD TYPE 2 DIMENSION6ERSION DATA MAPPING •
•
•
$e $ype 2 DimensionH'ersion Data mappin filters source rows 5ased on user;defined comparisons and inserts 5ot new and caned records into te taret. Canes are trac7ed in te taret ta5le 5y versionin te primary 7ey and creatin a version num5er for eac record in te ta5le. In te $ype 2 DimensionH'ersion Data taret@ te latest record as te iest version num5er and te iest incremented primary 7ey.
)en we use tis option@ te Desiner creates "%o !''#"#on!) f#&)'( #n "+& "!r-&": 1. PMPRIMARYKEY: $e Interation !ervice enerates a primary 7ey for eac row written to te taret. 2. PM6ERSIONN5MBER: $e I! enerates a version num5er for eac row written to te taret. S"&*(: 1. 9ollow !teps 1;( as we did in !CD $ype1@ e
Conf#-r#n- S&((#on: In te session properties@ clic7 te $aret Properties settins on te appins ta5. $o ensure te Interation !ervice loads rows to te ta ret properly@ select Insert for eac relational taret. F)o%1: N&% r&$or' #( #n(&r"&' #n"o "!r-&" "!/)&. F)o%2: C+!n-&' r&$or' #( #n(&r"&' #n"o "!r-&" "!/)&.
3. SCD TYPE 2 DIMENSIONFLAG C5RRENT MAPPING •
•
$e $ype 2 DimensionH9la Current mappin filters source rows 5ased o n user;defined comparisons and inserts 5ot new and caned records into te taret. In te $ype 2 DimensionH9la Current taret@ te latest record as a current fla set to 1 and te iest incremented primary 7ey.
)en we use tis option@ te Desiner creates "%o !''#"#on!) f#&)'( #n "+& "!r-&": 1. PMPRIMARYKEY: $e Interation !ervice enerates a primary 7ey for eac row written to te taret. 2. PMC5RRENTFLAG: $e Interation !ervice flas te current row M1M and all previous versions M8M. S"&*(: 1. 9ollow !teps 1;( as we did in !CD $ype1@ e
Conf#-r#n- S&((#on: In te session properties@ clic7 te $aret Properties settins on te appins ta5. $o ensure te Interation !ervice loads rows to te taret properly@ select Insert and ,pdate as ,pdate for eac relational taret. F)o%1: N&% r&$or' #( #n(&r"&' #n"o "!r-&" "!/)&. F)o%2: C+!n-&' r&$or' #( #n(&r"&' #n"o "!r-&" "!/)&. F)o%2: Crr&n" F)!- of $+!n-&' r&$or' #( *'!"&' #n "!r-&" "!/)&.
4. SCD TYPE 2 DIMENSIONEFFECTI6E DATE RANGE •
•
$e $ype 2 DimensionH%ffective Date Rane mappin filters source rows 5ased on user;defined comparisons and inserts 5ot new and caned records into te taret. Canes are trac7ed in te taret ta5le 5y maintainin an effective date rane for eac version of eac record in te taret.
)en we use tis option@ te Desiner creates 3 !''#"#on!) f#&)'( #n "+& "!r-&": 1. PMPRIMARYKEY: $e Interation !ervice enerates a primary 7ey for eac row written to te taret. 2. PMBEGINDATE: 9or eac new and caned record@ it is populated wit !/!DA$%. $is !ysdate is te date on wic %$ process runs. 3. PMENDDATE: It is populated as #, wen record is inserted. A new record is inserted wen a record canes. +owever@ PL%#DLDA$% of caned record is updated wit !/!DA$%. S"&*(: 1. 9ollow !teps 1;( as we did in !CD $ype1@ e
8. SCD TYPE 3 DIMENSION MAPPING • •
• •
Inserts new records. ,pdates caned values in e
)en we use tis option@ te Desiner creates "%o !''#"#on!) f#&)'( #n "+& "!r-&": 1. PMPRIMARYKEY: $e Interation !ervice enerates a primary 7ey for eac row written to te taret. 2. PMPRE6Co)nN!&: $e Desiner enerates a previous column correspondin to eac column for wic we want i storical data. $e I! 7eeps te previous version of record data in tese columns. 3. PMEFFECTDATE: An optional field. $e I! uses te system date to indicate wen it creates or updates a dimension. S"&*(: 1. 9ollow !teps 1;( as we did in !CD $ype1@ e
3.2< MAPPING PARAMETERS Q 6ARIABLES appin parameters and varia5les represent values in mappins and mapplets. )en we use a mappin parameter or varia5le in a mappin@ first we declare te mappin parameter or varia5le for use in eac mapplet or mappin. $en@ we define a value for te mappin parameter or varia5le 5efore we run te session.
MAPPING PARAMETERS •
•
A mappin parameter represents a constant value tat we can define 5efore runnin a session. A mappin parameter retains te same value trouout te entire session.
% override. • • •
After we create a parameter@ it appears in te %
MAPPING 6ARIABLES •
•
• •
,nli7e mappin parameters@ mappin varia5les are values tat can cane 5etween sessions. $e Interation !ervice saves te latest value of a mappin varia5le to te repository at te end of eac successful session. )e can override a saved value wit te parameter file. )e can also clear all saved values for te session in te )or7flow anaer.
)e mit use a mappin varia5le to perform an incremental read of te source. 9or e
5(&' #n fo))o%#n- "r!n(for!"#on(:
• • • •
%
In#"#!) !n' D&f!)" 6!)&: )en we declare a mappin parameter or varia5le in a mappin or a mapplet@ we can enter an initial value. )en te Interation !ervice needs an initial value@ and we did not declare an initial value for te parameter or varia5le@ te Interation !ervice uses a default value 5ased on te datatype of te parameter or varia5le. D!"! #umeric !trin Datetime
6!r#!/)& 6!)&(:
D&f!)" 6!)& 8 %mpty !trin 1H1H1
!tart value and current value of a mappin varia5le
S"!r" 6!)&: $e start value is te value of te varia5le at te start of te session. $e Interation !ervice loo7s for te start value in te followin order? 1. 'alue in parameter file 2. 'alue saved in te repository 3. Initial value 4. Default value
Crr&n" 6!)&: $e current value is te value of te varia5le session starts@ te current value of a varia5le final current value for a varia5le is saved to te session. )en a session fails to complete@ te te value of te varia5le in te repository.
as te session proresses. )en a is te same as te start value. $e repository at te end of a successful Interation !ervice does not update
No"&: If a varia5le function is not used to calculate te current value of a mappin varia5le@ te start value of te varia5le is saved to te repository.
6!r#!/)& D!"!"0*& !n' A--r&-!"#on T0*& )en we declare a mappin varia5le in a mappin@ we need to confiure te datatype and areation type for te varia5le. $e I! uses te areate type of a mappin varia5le to determine te final current value of te mappin varia5le. A--r&-!"#on "0*&( !r&: Con"? Inteer and small inteer datatypes are vali d only. M!? All transformation datatypes e
6!r#!/)& Fn$"#on( 'aria5le functions determine ow te Interation !ervice calculates te current value of a mappin varia5le in a pipeline. S&"M!6!r#!/)&: !ets te varia5le to te ma
S&"6!r#!/)&: !ets te varia5le to te confiured value. At te end of a session@ it compares te final current value of te varia5le to te start value of te varia5le. *ased on te areate type of te varia5le@ it saves a final value to te repository.
Cr&!"#n- M!**#n- P!r!&"&r( !n' 6!r#!/)&( 1. Open te folder were we want to create parameter or varia5le. 2. In te appin Desiner@ clic7 appins K Parameters and 'aria5les. ;or; In te applet Desiner@ clic7 applet K Parameters and 'aria 5les. 3. Clic7 te add 5utton.
4. %nter name. Do no" r&o& fro n!&. . !elect $ype and Datatype. !elect Areation type for mappin varia5les. ". &ive Initial 'alue. Clic7 o7.
E!*)&: 5(& of M!**#n- of M!**#n- P!r!&"&r( !n' 6!r#!/)&( • •
•
• •
%P will 5e source ta5le. Create a taret ta5le PL'L%AP% avin columns? %P#O@ %#A%@ D%P$#O@ $O$AL!A@ AL'AR@ I#L'AR@ CO,#$L'AR and !%$L'AR. $O$AL!A T !AN CO N ZZ*O#,! F*onus is mappin parameter tat canes every montG !%$L'AR? )e will 5e added one mont to te +IR%DA$% of every employee. Create sortcuts as necessary.
Cr&!"#n- M!**#n1. Open folder were we want to create te mappin. 2. Clic7 $ools ;K appin Desiner. 3. Clic7 appin;K Create;K &ive name. %
12. 13. Create output ports outL $O$AL!A@ outLAL'AR@ outLI#L'AR@ outLCO,#$L'AR and outL!%$L'AR. 14. Open e
1(. Open %
21. in7 all ports from e
• • •
M!,& (&((#on !n' %or,f)o%. G#& $onn&$"#on #nfor!"#on for (or$& !n' "!r-&" "!/)&. Rn %or,f)o% !n' (&& r&()".
3.2= PARAMETER FILE •
•
•
•
A parameter file is a list of parameters and associated values for a wor7flow@ wor7let@ or session. Parameter files provide fle
A parameter file contains te followin types of parameters and varia5les? •
•
•
•
Wor,f)o% !r#!/)&: References values and records information in a wor7flow. Wor,)&" !r#!/)&: References values and records information in a wor7let. ,se predefined wor7let varia5les in a parent wor7flow@ 5ut we cannot use wor7flow varia5les from te parent wor7flow in a wor7let. S&((#on *!r!&"&r: Defines a value tat can cane from session to session@ suc as a data5ase connection or fi le name. M!**#n- *!r!&"&r !n' M!**#n- !r#!/)&
5SING A PARAMETER FILE Parameter files contain several sections preceded 5y a eadin. $e eadin identifies te Interation !ervice@ Interation !ervice process@ wor7flow@ wor7let@ or session to wic we want to assin parameters or varia5les.
S!*)& P!r!&"&r F#)& for Or &!*)&: In "+& *!r!&"&r f#)&@ fo)'&r !n' (&((#on n!&( !r& $!(& (&n(#"#&. Create a te
CONFIG5RING PARAMTER FILE )e can specify te parameter fil e name and directory in te wor7flow or session properties. To &n"&r ! *!r!&"&r f#)& #n "+& %or,f)o% *ro*&r"#&(: 1. 2. 3. 4. .
Open a )or7flow in te )or7flow anaer. Clic7 )or7flows K %dit. Clic7 te Properties ta5. %nter te parameter directory and name in te Parameter 9ilename field. Clic7 O.
To &n"&r ! *!r!&"&r f#)& #n "+& (&((#on *ro*&r"#&(: 1. 2. 3. 4. .
Open a session in te )or7flow anaer. Clic7 te Properties ta5 and open te &eneral Options settins. %nter te parameter directory and name in te Parameter 9ilename field. %
3.2? INDIRECT LOADING FOR FLAT FILES !uppose@ you ave 18 flat files of same structure. All te flat files ave same num5er of columns and datatype. #ow we need to transfer all te 18 files to same taret. #ames of files are say %P1@ %P2 and so on. So)"#on1: 1. Import one flat file definition and ma7e te mappi n as per need. 2. #ow in session ive te !ource 9ile name and !ource 9ile Directory location of one file. 3. a7e wor7flow and run. 4. #ow open session after wor7flow completes. Cane te 9ilename and Directory to ive information of second file. Run wor7flow aain. . Do te a5ove for all 18 files. So)"#on2: 1. Import one flat file definition and ma7e te mappi n as per need. 2. #ow in session ive te !ource Directory location of te files. 3. #ow in 9ieldname use In*"F#)&N!&. $is is a session parameter. !ee 4.2.4 for session parameters. 4. #ow ma7e a parameter file and ive te value of In*"F#)&N!&. In*"F#)&N!&EMP1."" . Run te wor7flow ". #ow edit parameter file and ive value of second file. Run wor7flow aain. (. Do same for remainin files. So)"#on3: 1. Import one flat file definition and ma7e te mappi n as per need. 2. #ow ma7e a notepad file tat contains te location and name of eac 18 flat files. !ample? D:EMP1."" E:EMP2."" E:FILESDWHEMP3."" and so on 3. #ow ma7e a session and in !ource file name and !ource 9ile Directory location fields@ ive te name and location of a5ove created file. 4. In Sor$& f#)&"0*& f#&)'@ (&)&$" In'#r&$". . Clic7 Apply. ". 'alidate !ession (. a7e )or7flow. !ave it to repository and run.
S!*)& M!**#n- "o /& !'&
Chapter !
"or#f$ow Manager
4.1 INTEGRATION SER6ICE ARCHITECT5RE •
•
•
$e Interation !ervice moves data from sources to tarets 5ased on wor7flow and mappin metadata stored in a repository. )en a wor7flow starts@ te Interation !ervice retrieves mappin@ wor7flow@ and session metadata from te repository. It e
$o move data from sources to tarets@ t e Interation !ervice uses te followin components? Interation !ervice process oad *alancer Data $ransformation anaer FD$G process • • •
4.1.1 INTEGRATION SER6ICE PROCESS $e Interation !ervice starts an Interation !ervice process to run and monitor wor7flows. $e Interation !ervice process accepts reBuests from te PowerCenter Client and from pmcmd. It performs te followin tas7s? • • • • • • • •
anaes wor7flow scedulin. oc7s and reads te wor7flow. Reads te parameter file. Creates te wor7flow lo. Runs wor7flow tas7s and evaluates te conditional lin7s connectin tas7s. !tarts te D$ process or processes to run te session. )rites istorical run information to te repository. !ends post;session email in te event of a D$ failure.
4.1.2 LOAD BALANCER $e oad *alancer is a component of te Interation !ervice tat dispatces tas7s to acieve optimal performance and scala5ility. )en we run a wor7flow@ te oad *alancer dispatces te !ession@ Command@ and predefined %vent;)ait tas7s witin te wor7flow. $e oad *alancer dispatces tas7s in te order it receives tem. )en te oad *alancer needs to dispatc more !ession and Command tas7s tan te Interation !ervice can run@ it places te tas7s it cannot run in a Bueue. )en nodes 5ecome availa5le@ te oad *alancer dispatces tas7s from te Bueue in te order determined 5y te wor7flow service level.
4.1.3 DTM PROCESS )en te wor7flow reaces a session@ te Interation !ervice process starts te D$ process. $e D$ is te process associated wit te session tas7. $e D$ process performs te followin tas7s? • •
•
•
• • • • •
•
• •
Retrieves and validates session information from te repository. Performs pusdown optimi:ation wen te session is confiured for pusdown optimi:ation. Adds partitions to te session wen te session is confiured for dynamic partitionin. %. !ends a reBuest to start wor7er D$ processes on oter nodes wen te session is confiured to run on a rid. Creates and run mappin@ reader@ writer@ and transformation treads to e@ and sell commands. !ends post;session email.
4.1.4 PROCESSING THREADS $e D$ allocates process memory for te session and divides it into 5uffers. $is is also 7nown as 5uffer memory. $e default memory allocation is 12@888@888 5ytes. $e D$ uses multiple treads to process data in a session. $e main D$ tread is called te !("&r "+r&!'. $e master tread can create te followin types of treads? M!**#n- T+r&!'(? One mappin tread for eac session. Pr& !n' Po("S&((#on T+r&!'(: One tread created. R&!'&r T+r&!'(: One tread for eac partition Tr!n(for!"#on T+r&!'(: One tread for eac partition Wr#"&r T+r&!'(: One tread for eac partition • • • • •
4.1.8 CODE PAGES !n' DATA MO6EMENT A code pae contains te encodin to specify caracters in a set of one or more lanuaes. An encodin is te assinment of a num5er to a caracter in te caracter set. $e Interation !ervice can move data in eiter A!CII or ,nicode data movement mode. $ese modes determine ow te Interation !ervice andles caracter data. )e coose te data movement mode in te Interation !ervice confiuration settins. If we want to move multi5yte data@ coose ,nicode data movement mode. ASCII D!"! Mo&&n" Mo'&: In A!CII mode@ te Interation !ervice reconi:es (;5it A!CII and %*CDIC caracters and stores eac caracter in a sinle 5yte. 5n#$o'& D!"! Mo&&n" Mo'&: ,se ,nicode data movement mode wen sources or tarets use -;5it or multi5yte caracter sets and contain caracter data.
4.1.; O5TP5T FILES !n' CACHES $e Interation !ervice creates te followin output fil es? )or7flow lo !ession lo !ession details file Performance details file Re6ect files Row error los Recovery ta5les and files Control file Post;session email Output file Cace files • • • • • • • • • • •
S&((#on D&"!#)(: )en we run a session@ te )or7flow anaer creates session details tat provide load statistics for eac taret in te mappin. )e can monitor session details durin te session or after te session completes. !ession details include information suc as ta5le name@ num5er of rows written or re6ected@ and read and write trouput. Con"ro) F#)&: )en we run a session tat uses an e
4.2 WORKING WITH WORKFLOWS A wor7flow is a set of instructions tat tells te Interation !ervice ow to run tas7s suc as sessions@ email notifications@ and sell commands.
4.2.1 ASSIGNING AN INTEGRATION SER6ICE *efore we can run a wor7flow@ we must assin an Interation !ervice to run it. S"&*( "o !((#-n IS fro Wor,f)o% Pro*&r"#&(: 1. 2. 3. 4. .
In te )or7flow Desiner@ open te )or7flow. Clic7 )or7flows K %dit. On te &eneral ta5@ clic7 te *rowse Interation !ervices 5utton. !elect te Interation !ervice tat you want to run te wor7flow. Clic7 O twice to select te Interation !ervice for te wor7flow.
S"&*( "o !((#-n IS fro M&n: 1. Close all folders in te repository. 2. Clic7 !ervice K Assin Interation !ervice. 3. 9rom te Coose Interation !ervice list@ select te service we want to assin. 4. 9rom te !ow 9older list@ select te folder we want to view. . Clic7 te !elected cec7 5o< for eac wor7flow you want te Interation !ervice to run. ". Clic7 Assin.
4.2.2 WORKING WITH LINKS • • •
,se lin7s to connect eac wor7flow tas7. )e can specify conditions wit lin7s to create 5rances in te wor7flow. $e )or7flow anaer does not allow us to use lin7s to create loops in te wor7flow. %ac lin7 in te wor7flow can run onl y once.
6!)#' Wor,f)o%:
E!*)& of )oo*:
S*&$#f0#n- L#n, Con'#"#on(: •
•
•
Once we create lin7s 5etween tas7s@ we can specify conditions for eac lin7 to determine te order of e
S"&*(: 1. In te )or7flow Desiner wor7space@ dou5le;clic7 te lin7 you want to specify. 2. $e %
5(#n- "+& E*r&((#on E'#"or: $e )or7flow anaer provides an %
4.2.3 WORKFLOW 6ARIABLES )e can create and use varia5les in a wor7flow to reference values and record information. $ypes of wor7flow varia5les? Predefined wor7flow varia5les ,ser;defined wor7flow varia5les • •
Pr&'&f#n&' %or,f)o% !r#!/)&( $e )or7flow anaer provides predefined wor7flow varia5les for tas7s witin a wor7flow. $ypes of Predefined wor7flow varia5les are?
S0("& !r#!/)&(: ,se te !/!DA$% and )OR9O)!$AR$$I% system varia5les witin a wor7flow.
T!(,(*&$#f#$ !r#!/)&(: $e )or7flow anaer provides a set of tas7;specific varia5les for eac tas7 in te wor7flow. $e )or7flow anaer lists tas7;specific varia5les under te tas7 name in te %
Sr$F!#)&'Ro%( Sr$S$$&((Ro%( S"!r"T#& S"!"(
T-"F!#)&'Ro%( T-"S$$&((Ro%( To"!)Tr!n(Error(
D&($r#*"#on Result of decision condition e
T!(, T0*& Decision $as7 All $as7s All $as7s All $as7s !ession !ession All $as7s
!ession !ession All $as7s All $as7s
!ession !ession !ession
5(&rD&f#n&' Wor,f)o% 6!r#!/)&( )e can create varia5les witin a wor7flow. )en we create a varia5le in a wor7flow@ it is valid only in tat wor7flow. ,se te varia5le in tas7s witin tat wor7flow. )e can edit and delete user;defined wor7flow varia5les. Interation !ervice olds two different values for a wor7flow varia5le durin a wor7flow run? !tart value of a wor7flow varia5le Current value of a wor7flow varia5le • •
$e Interation !ervice loo7s for te start value of a varia5le in te followin order? 1. 'alue in parameter file 2. 'alue saved in te repository Fif te varia5le is persistentG 3. ,ser;specified default value 4. Datatype default value Persistent (eans value is saved to the re)ository.
To $r&!"& ! %or,f)o% !r#!/)&: 1. 2. 3. 4. .
In te )or7flow Desiner@ create a new wor7flow or edit an e
". %nter te default value for te varia5le in te Default field. (. $o validate te default value of te new wor7flow varia5le@ clic7 te 'alidate 5utton. -. Clic7 Apply to save te new wor7flow varia5le. . Clic7 O to close te wor7flow properties.
4.2.4 SESSION PARAMETERS •
•
•
!ession parameters represent values we mit want to cane 5etween sessions@ suc as a data5ase connection or source file. ,se session parameters in te session properties@ and ten define te parameters in a parameter file. $e )or7flow anaer provides one 5uilt;in session parameter@ ZP!essiono9ile. ,sed to cane te name of lo file.
E!*)&: !uppose we want to read data from 18 different data5ases containin same ta5le and ten transfer to te same data5ase ta5le. So)"#on1: Open !ession and ive connection for eac data5ase 18 times and ten run te wor7flow. So)"#on2: Create a !ession parameter for source data5ase and ive its value in parameter file. S&((#on P!r!&"&r T0*& D!"!/!(& Conn&$"#on Sor$& F#)& T!r-&" F#)& Loo,* F#)& R&7&$" F#)& •
N!#n- Con&n"#on DBConn&$"#onN!& In*"F#)&N!& O"*"F#)&N!& Loo,*F#)&N!& B!'F#)&N!&
!ource file@ taret file@ loo7up file@ re6ect file parameters are used for 9lat 9iles.
S"&*( "o $onf#-r& ! '!"!/!(& $onn&$"#on *!r!&"&r? 1. Open any session and edit it. 2. In te session properties@ clic7 te appin ta5 and clic7 Connections settins for te sources or tarets node. 3. Clic7 te Open 5utton in te 'alue field. 4. In te Relational Connection *rowser@ select ,se Connection 'aria5le. . %nter a name for te data5ase connection parameter. #ame te connection parameter ZD*Connection#ame. ". In te &eneral Options settins of te Properties ta5@ enter a parameter file and directory in te Parameter 9ilename field. Clic7 O.
S"&*( for 5(#n- ! Sor$& F#)& P!r!&"&r 1. 2. 3. 4.
!elect a source under te !ources node on te appin ta5. &o to te Properties settins. In te !ource 9ilename field@ enter te source file parameter name. #ame all source file parameters ZInput9ile#ame. If you want te parameter to represent 5ot te source file name and location@ clear te !ource Directory field. . In te &eneral Options settins of te Properties ta5@ enter a parameter file and directory in te Parameter 9ilename field. Clic7 O. S##)!r)0 -#& "+& *!r!&"&r for "!r-&" !n' r&7&$" f#)& #n T!r-&" *ro*&r"#&(. For Loo,* f#)& *!r!&"&r@ (&)&$" Loo,* f#)& #n Tr!n(for!"#on( no'& !n' -#& "+& *!r!&"&r "+&r& for Loo,* f#)& n!&.
4.3 WORKING WITH TASKS $e )or7flow anaer contains many types of tas7s to elp you 5uild wor7flows and wor7lets. )e can create r&(!/)& "!(,( #n "+& T!(, D&&)o*&r. T0*&( of "!(,(: T!(, T0*& !ession %mail Command %vent;Raise %vent;)ait $imer Decision Assinment Control
Too) %+&r& "!(, $!n /& $r&!"&' $as7 Developer )or7flow Desiner )or7let Desiner )or7flow Desiner )or7let Desiner
R&(!/)& or no" /es /es /es #o #o #o #o #o #o
4.3.1 SESSION TASK •
• •
•
A session is a set of instructions tat tells te PowerCenter !erver ow and wen to move data from sources to tarets. $o run a session@ we must first create a wor7flow to contain te !ession tas7. )e can run as many sessions in a wor7flow as we need. )e can run te !ession tas7s seBuentially or concurrently@ dependin on our needs. $e PowerCenter !erver creates several files and in;memory caces dependin on te transformations and options used i n te session.
4.3.2 EMAIL TASK •
•
!teps? 1. 2. 3. 4. . ". (. -. .
$e )or7flow anaer provides an %mail tas7 tat allows us to send email durin a wor7flow. Created 5y Administrator usually and we 6ust dra and use it in our mappin.
In te $as7 Developer or )or7flow Desiner@ coose $as7s;Create. !elect an %mail tas7 and enter a name for te tas7. Clic7 Create. Clic7 Done. Dou5le;clic7 te %mail tas7 in te wor7space. $e %dit $as7s dialo 5o< appears. Clic7 te Properties ta5. %nter te fully Bualified email address of te mail recipient in te %mail ,ser #ame field. %nter te su56ect of te email in te %mail !u56ect field. Or@ you can leave tis field 5lan7. Clic7 te Open 5utton in te %mail $e
E!*)&: To (&n' !n &!#) %+&n ! (&((#on $o*)&"&(: !teps? 1. 2. 3. 4. . ". (. -.
Create a wor7flow wfLsampleLemail Dra any session tas7 to wor7space. E'#" S&((#on "!(, !n' -o "o Co*on&n"( "!/. !ee On !uccess %mail Option tere and confiure it. In $ype select reusa5le or #on;reusa5le. In 'alue@ select te email tas7 to 5e used. Clic7 Apply ;K O7. 'alidate wor7flow and Repository ;K !ave • •
W& $!n !)(o 'r!- "+& &!#) "!(, !n' (& !( *&r n&&'. W& $!n (&" "+& o*"#on "o (&n' &!#) on ($$&(( or f!#)r& #n $o*on&n"( "!/ of ! (&((#on "!(,.
4.3.3 COMMAND TASK $e Command tas7 allows us to specify one or more (+&)) $o!n'( #n 5NI or DOS $o!n'( #n W#n'o%( to run durin te wor7flow. 9or e
E!*)&: "o $o*0 ! f#)& (!*)&."" fro D 'r#& "o E. Command?
7P8 ,:9sample.t0t $:9 in windows
S"&*( for $r&!"#n- $o!n' "!(,: 1. In te $as7 Developer or )or7flow Desiner@ coose $as7s;Create. 2. !elect Command $as7 for te tas7 type. 3. %nter a name for te Command tas7. Clic7 Create. $en clic7 done. 4. Dou5le;clic7 te Command tas7. &o to commands t a5. . In te Commands ta5@ clic7 te Add 5utton to add a command. ". In te #ame field@ enter a name for te new command. (. In te Command field@ clic7 te %dit 5utton to open te Command %ditor. -. %nter only one command in te Command %ditor. . Clic7 O to close te Command %ditor. 18. Repeat steps ; to add more commands in te tas7. 11. Clic7 O.
S"&*( "o $r&!"& "+& %or,f)o% (#n- $o!n' "!(,: 1. Create a tas7 usin te a5ove steps to copy a file in $as7 Developer.
2. 3. 4. .
Open )or7flow Desiner. )or7flow ;K Create ;K &ive name and clic7 o7. !tart is displayed. Dra session say sLmL9ilterLe
. ;S
(. )or7flow;K 'alidate -. Repository ;K !ave
4.3.4 WORKING WITH E6ENT TASKS )e can define events in te wor7flow to specify te seBuence of tas7 e
•
S"&*( 1. 2. 3. 4.
for $r&!"#n- 5(&r D&f#n&' E&n": Open any wor7flow were we want to create an event. Clic7 )or7flow;K %dit ;K %vents ta5. Clic7 to Add 5utton to add events and ive te names as per need. Clic7 Apply ;K O7. 'alidate te wor7flow and !ave it.
T0*&( of E&n"( T!(,(: E6ENT RAISE: %vent;Raise tas7 represents a user;defined event. )e use tis tas7 to raise a user defined event. E6ENT WAIT: %vent;)ait tas7 waits for a file watcer event or user defined event to occur 5efore e
•
E!*)&1: ,se an event wait tas7 and ma7e sure tat session sLfilterLe
)or7flow ;K Create ;K &ive name wfLeventLwaitLfileLwatc ;K Clic7 o7. $as7 ;K Create ;K !elect %vent )ait. &ive name. Clic7 create and done. in7 !tart to %vent )ait tas7. Dra (f#)"&r&!*)& to wor7space and lin7 it to event wait tas7. Rit clic7 on event wait tas7 and clic7 %DI$ ;K %'%#$! ta5. !elect Pre Defined option tere. In te 5lan7 space@ ive directory and filename to watc. %
E!*)& 2: Raise a user defined event wen session sLmLfilterLe
)or7flow ;K Create ;K &ive name wfLeventLwaitLeventLraise ;K Clic7 o7. )or7flow ;K %dit ;K %vents $a5 and add events %'%#$1 tere. Dra sLmLfilterLe
4.3.8 TIMER TASK $e $imer tas7 allows us to specify te period of time ti me to wait 5efore te PowerCenter !erver runs te ne
•
E!*)&: Run session sLmLfilterLe
4.3.; DECISION TASK •
•
•
•
$e Decision tas7 allows us to enter a condition tat determines te e
E!*)&: Command $as7 sould run only if eiter sLmLfilterLe
(. 'alidate te condition ;K Clic7 Apply ;K O. -. Dra command tas7 and !LmLsampleLmappinL%P tas7 to wor7space and lin7 tem to D%CI!IO#L%. 'alidate [ clic7 O. 18. Dou5le clic7 lin7 5etween Command tas7 and D%CI!IO#L%
4.3.< CONTROL TASK •
•
•
)e can use te Control tas7 to stop@ a5ort@ or fail te top;level wor7flow or te parent wor7flow 5ased on an input lin7 condition. A parent wor7flow or wor7let is te wor7flow or wor7let tat contains te Control tas7. W& -#& "+& $on'#"#on "o "+& )#n, $onn&$"&' "o Con"ro) T!(,.
Con"ro) O*"#on
D&($r#*"#on
F!#) M&
9ails te control tas7.
F!#) P!r&n"
ar7s te status of te )9 or wor7let tat contains te Control tas7 as failed.
S"o* P!r&n"
!tops te )9 or wor7let tat contains te Control tas7.
A/or" P!r&n"
A5orts te )9 or wor7let tat contains te Control tas7.
F!#) To*L&&) WF
9ails te wor7flow tat is runnin.
S"o* To*L&&) WF
!tops te wor7flow tat is runnin.
A/or" To*L&&) WF
A5orts te wor7flow tat is runnin.
E!*)&: Dra any 3 sessions and if anyone fails@ ten A5ort te top level wor7flow. S"&*( for $r&!"#n- %or,f)o%: 1. )or7flow ;K Create ;K &ive name wfLcontrolLtas7Le
4.3.= ASSIGNMENT TASK •
• •
•
$e Assinment tas7 allows us to assin a value to a user;defined wor7flow varia5le. See 2or1flo variable topic to add user defined variables.
$o use an Assinment tas7 in te wor7flow@ first create and add te Assinment tas7 to te wor7flow. $en confiure te Assinment tas7 to assin values or e
S"&*( "o $r&!"& A((#-n&n" T!(,: 1. Open any wor7flow were we want to use Assinment tas7. 2. %dit )or7flow and add user defined varia5les. 3. Coose $as7s;Create. !elect Assinment $as7 for te tas7 type. 4. %nter a name for te Assinment tas7. Clic7 Create. $en clic7 Done. . Dou5le;clic7 te Assinment tas7 to open te %dit $as7 dialo 5o<. ". On te %
W& $!n (& "+& 5(&r D&f#n&' 6!r#!/)& #n or )#n, $on'#"#on( !( *&r "+& n&&' !n' !)(o $!)$)!"& or (&" "+& !)& of !r#!/)& #n A((#-n&n" T!(,.
4.4 SCHED5LERS )e can scedule a wor7flow to run continuously@ repeat at a iven time or interval@ or we can manually start a wor7flow. $e Interation !ervice runs a sceduled wor7flow as confiured. B0 '&f!)"@ "+& %or,f)o% rn( on '&!n'. )e can cane te scedule settins 5y editin te sceduler. If we cane scedule settins@ te Interation !ervice rescedules te wor7flow accordin to te new settins. A sceduler is a repository o56ect tat contains a set of scedule settins. !ceduler can 5e non;reusa5le or reusa5le. $e )or7flow anaer mar7s a wor7flow invalid if we delete te sceduler associated wit te wor7flow. If we coose a different Interation !ervice for te wor7flow or restart te Interation !ervice@ it rescedules all wor7flows. If we delete a folder@ te Interation !ervice removes wor7flows from te scedule.
• • •
•
•
T+& In"&-r!"#on S&r#$& 'o&( no" rn "+& %or,f)o% #f: $e prior wor7flow run fails. )e remove te wor7flow from te scedule $e Interation !ervice is runnin in safe mode • • •
Cr&!"#n- ! R&(!/)& S$+&')&r 9or eac folder@ te )or7flow anaer lets us create reusa5le scedulers so we can reuse te same set of scedulin settins for wor7flows in te folder. ,se a reusa5le sceduler so we do not need to confiure te same set of scedulin settins in eac wor7flow. )en we delete a reusa5le sceduler@ all wor7flows tat use te deleted sceduler 5ecomes invalid. $o ma7e te wor7flows valid@ we must edit tem and replace te missin sceduler.
•
•
•
S"&*(: 1. 2. 3. 4. . ".
Open te folder were we want to create te sceduler. In te )or7flow Desiner@ clic7 )or7flows K !cedulers. Clic7 Add to add a new sceduler. In te &eneral ta5@ enter a name for te sceduler. Confiure te sceduler settins in te !ceduler ta5. Clic7 Apply and O.
Conf#-r#n- S$+&')&r S&""#n-( Confiure te !cedule ta5 of te sceduler to set run options@ scedule options@ start options@ and end options for te scedule. $ere 1. 2. 3.
are 3 run options? Run on Demand Run Continuously Run on !erver initiali:ation
1. Rn on D&!n': Interation !ervice runs te wor7flow wen we start te wor7flow manuall y. 2. Rn Con"#no()0: Interation !ervice runs te wor7flow as soon as te service initiali:es. $e Interation !ervice ten starts te ne
Rn On$&: $o run te wor7flow 6ust once. Rn &&r0: Run te wor7flow at reular intervals@ as confiured. C("o#&' R&*&!": Interation !ervice runs te wor7flow on te dates and times specified in te Repeat dialo 5o<.
S"!r" o*"#on( for Rn on S&r&r #n#"#!)#!"#on: • •
S"!r" D!"& S"!r" T#&
En' o*"#on( for Rn on S&r&r #n#"#!)#!"#on: • •
•
En' on: I! stops scedulin te wor7flow in te selected date. En' Af"&r: I! stops scedulin te wor7flow after te set num5er of wor7flow runs. For&&r: I! scedules te wor7flow as lon as te wor7flow does not fail.
Cr&!"#n- ! NonR&(!/)& S$+&')&r 1. In te )or7flow Desiner@ open te wor7flow. 2. Clic7 )or7flows K %dit. 3. In te !ceduler ta5@ coose #on;reusa5le. !elect Reusa5le i f we want to select an e
. If we select Reusa5le@ coose a reusa5le sceduler from te !ceduler *rowser dialo 5o<. ". Clic7 O7.
So& Po#n"(: $o remove a wor7flow from its scedule@ rit;clic7 te wor7flow i n te #aviator window and coose 5n($+&')& Wor,f)o%. $o rescedule a wor7flow on its oriinal scedule@ rit;clic7 te wor7flow in te #aviator window and coose !cedule )or7flow. •
•
4.8 WORKLETS •
•
• •
A wor7let is an o56ect tat represents a set of tas7s tat we create in te )or7let Desiner. Create a wor7let wen we want to reuse a set of wor7flow loic in more tan one wor7flow. $o run a wor7let@ include te wor7let in a wor7flow. )or7let is created in te same way as we create )or7flows. $as7s are also added in te same way as we do in wor7flows. )e can lin7 tas7s and ive lin7 conditions in same way.
Wor,)&"( $!n /&: •
•
R&(!/)& Wor,)&": Crested in )or7let Desiner. 1. In te )or7let Desiner@ clic7 )or7let K Create. 2. %nter a name for te wor7let. 3. Clic7 O. 4. Add tas7s as needed. &ive lin7s and conditions. . )or7let ;K 'alidate ". Repository ;K !ave NonR&(!/)& Wor,)&": Crested in )or7flow Desiner. 1. In te )or7flow Desiner@ open a wor7flow. 2. Clic7 $as7s K Create. 3. 9or te $as7 type@ select )or7let. 4. %nter a name for te tas7. . Clic7 Create. ". Clic7 Done. To 1. 2. 3.
!'' "!(,( "o ! nonr&(!/)& %or,)&": Create a non;reusa5le wor7let in te )or7flow Desiner wor7space. Rit;clic7 te wor7let and coose Open )or7let. Add tas7s in te wor7let 5y usin te $as7s tool5ar or clic7 $as7s K Create in te )or7let Desiner. 4. Connect tas7s wit lin7s.
So& Po#n"(: )e cannot run two instances of te same wor7let concurrently in te same wor7flow. )e cannot run two instances of te same wor7let concurrently across two different wor7flows. %ac wor7let instance in te wor7flow can run o nce. •
•
•
4.; PARTITIONING •
•
A *#*&)#n& *#*&)#n& consists of a source Bualifier and all te transformations and tarets tat receive data from tat source Bualifier. )en te Interation !ervice runs te session@ it can !$+#&& +#-+&r *&rfor!n$& /0 *!r"#"#on#n- *!r"#"#on#n- te pipeline and performin te e
A *!r"#"#on *!r"#"#on is is a pipeline stae tat e
4.;.1 PARTITIONING ATTRIB5TES 1. P!r"#"#on *o#n"( *y default@ I! sets partition points at various transformations in te pipeline. Partition points mar7 tread 5oundaries and divide te pipeline into staes. A stae is a section of a pipeline 5etween any two partition points. • • •
2. N/&r of P!r"#"#on( )e can define up to "4 partitions at any partition point in a pipeline. )en we increase or decrease te num5er of partitions at any partition point@ te )or7flow anaer increases or decreases te num5er of partitions at all partition points in te pipeline. Increasin te num5er of partitions or partiti on points increases te num5er of treads. $e num5er of partitions we create eBuals eBual s te num5er of connections to te source or taret. 9or one partition@ one data5ase dat a5ase connection will 5e used. • •
•
•
3. P!r"#"#on "0*&( $e Interation !ervice creates a default partition type at eac partition point. If we ave te Partitionin option@ we can cane te partition type. $is option is purcased separately. $e partition type controls ow te Interation !ervice distri5utes data amon partitions at partition points. •
•
•
4.;.2 PARTITIONING TYPES 1. Ron' Ro/#n P!r"#"#on T0*& In round;ro5in partitionin@ te Interation !ervice distri5utes rows of data evenly to all partitions. %ac partition processes appro
• •
2. P!((T+ro-+ P!r"#"#on T0*& In pass;trou partitionin@ te Interation !ervice processes data witout redistri5utin rows amon partitions. All rows in a sinle partition stay in tat partition after crossin a pass; trou partition point. ,se pass;trou partitionin wen we want to increase data trouput@ 5ut we do not want to increase te num5er of partitions. •
•
•
3. D!"!/!(& P!r"#"#on#n- P!r"#"#on T0*& ,se data5ase partitionin for Oracle and I* D*2 sources and IBM DB2 "!r-&"( on)0. on)0. ,se any num5er of pipeline partitions and any num5er of data5ase partitions. )e can improve performance wen te num5er of pipeline partitions eBuals te num5er of data5ase partitions. •
• •
D!"!/!(& P!r"#"#on#n- %#"+ On& Sor$& )en we use data5ase partitionin wit a source Bualifier wit one source@ te Interation !ervice enerates !> Bueries for eac data5ase partition and distri5utes te data from te data5ase partitions amon te session partitions eBually. 9or e Bueries for data5ase partitions 5ased on te num5er of partitions in te data5ase ta5le wit te most partitions. If te session as tree partitions and te data5ase ta5le as two partitions@ one of te session partitions receives no data. 4. H!(+ A"oK&0( P!r"#"#on T0*& $e Interation !ervice uses all rouped or sorted ports as a compound partition 7ey. ,se as auto;7eys partitionin at or 5efore Ran7@ !orter@ =oiner@ and unsorted Areator transformations to ensure tat rows are rouped properly 5efore tey enter tese transformations. •
•
8. H!(+ 5(&rK&0( P!r"#"#on T0*& $e Interation !ervice uses a as function to roup rows of data amon partitions. )e define te num5er of ports to enerate te partition 7ey. )e coose te ports tat define te partition 7ey •
• •
;. K&0 r!n-& P!r"#"#on T0*& )e specify one or more ports to form a compound partition 7ey. $e Interation !ervice passes data to eac partition dependin on te ranes we specify for eac port. ,se 7ey rane partitionin were te sources or tarets in te pipeline are partitioned 5y 7ey rane. %
•
•
4.;.3 So& Po#n"( 1. On)0 on& *!r"#"#on #( $r&!"&' for fo))o%#n- "r!n(for!"#on(: Custom $ransformation %
2. If we partition a session wit a flat file taret te Informatica server creates one taret file for eac partition. )e can confiure session properties to mere tese taret files into one.
4.< SESSION PROPERTIES 1. GENERAL TAB *y default@ te &eneral ta5 appears wen we edit a session tas7. &eneral $a5 as followin options? R&n!&: Optional and can 5e used to rename a session. D&($r#*"#on? Optional and provides a description for session. M!**#n- n!&: ReBuired and represents name of te mappin associated wit te session tas7. F!#) P!r&n" #f "+#( "!(, f!#)(: Optional and 9ails te parent wor7let or wor7flow if tis tas7 fails. F!#) *!r&n" #f "+#( "!(, 'o&( no" rn? Optional and 9ails te parent wor7let or wor7flow if tis tas7 does not run. D#(!/)& "+#( "!(,: Optional and Disa5les te tas7. Tr&!" "+& #n*" )#n,( !( AND or OR ? ReBuired and Runs te tas7 wen all or one of te input lin7 conditions evaluate to $rue. • • •
•
•
•
'ast ? options appear onl3 in the 2or1flo ,esi!ner.
2. PROPERTIES TAB Pro*&r"0
R&J#r&' O*"#on!) Optional
)rite *ac7ward Compati5le !ession o 9ile !ession o 9ile #ame !ession o 9ile Directory Parameter 9ile #ame %na5le $est oad #um5er of Rows to $est Z!ource Connection 'alue
Optional ReBuired Optional Optional Optional Optional
Z$aret Connection 'alue
Optional
$reat !ource Rows As
ReBuired
Commit $ype
ReBuired
Commit Interval
ReBuired
Recovery !tratey
ReBuired
D&($r#*"#on !elect to write session lo to a file.
ocation were session lo is created. #ame and location of parameter file. $o test a mappin. #um5er of rows of source data to test. %nter te data5ase connection we want to use for Z!ource varia5le. %nter te data5ase connection we want to use for Z$aret varia5le. Indicates ow te I! treats all source rows. Can 5e Insert@ ,pdate@ Delete or Data Driven Determines weter te Interation !ervice uses a source; or taret;5ased or user;defined commit. Indicates te num5er of rows after wic commit is fired. S&& on N&" P!-&
W& $!n $onf#-r& *&rfor!n$& (&""#n-( on "+& Pro*&r"#&( "!/. In P&rfor!n$& (&""#n-(@ %& $!n #n$r&!(& &or0 (#&@ $o))&$" *&rfor!n$& '&"!#)(@ !n' (&" $onf#-r!"#on *!r!&"&r(.
RECO6ERY STRATEGY )or7flow recovery allows us to continue processin te wor7flow and wor7flow tas7s from te point of interruption. )e can recover a wor7flow if te Interation !ervice can access te wor7flow state of operation. The *nte!ration Service recovers tas1s in the or1flo based on the recover3 strate!3 of the tas1. •
• •
*y default@ te recovery stratey for !ession and Command tas7s is to fail te tas7 and continue runnin te wor7flow. )e can confiure te recovery stratey for !ession and Command tas7s. $e stratey for all oter tas7s is to restart te tas7.
R&$o&r0 S"r!"&-0 O*"#on( for (&((#on "!(,: Resume from te last cec7point. Restart tas7. 9ail tas7 and continue wor7flow. • • •
R&$o&r0 S"r!"&-0 O*"#on( for Co!n' "!(,: Restart tas7. 9ail tas7 and continue wor7flow. • •
T!r-&" R&$o&r0 T!/)&( )en te Interation !ervice runs a session tat as a resume recovery stratey@ it writes to recovery ta5les on te taret data5ase system. $e followin recovery ta5les are used? PLR%CO'%R/ PL$&$LR,#LID • •
R&$o&r0 O*"#on( !uspend )or7flow on %rror? Availa5le in )or7flow !uspension %mail? Availa5le in )or7flow %na5le +A Recovery? Availa5le in )or7flow Automatically Recover $erminated $as7s? Availa5le in )or7flow a
3. CONFIG OBECT TAB )e can confiure te followin settins in te Confi O56ect ta5? A'!n$&'. Advanced settins allow you to confiure constraint;5ased loadin@ loo7up caces@ and 5uffer si:es. Lo- O*"#on(. o options allow you to confiure ow you want to save te session lo. Error H!n')#n-. %rror +andlin settins allow us to determine if te session !tops or continues wen it encounters pre;session command errors@ stored procedure errors@ or a specified num5er of session errors. P!r"#"#on#n- O*"#on(. Partitionin options allow te Interation !ervice to determine te num5er of partitions to create at run time. •
•
•
•
Con("r!#n"/!(&' )o!'#n-: %na5le tis wen in same mappin@ tere are two taret ta5les and 5ot are related to eac oter in P and 9 relation. One is master ta5le and oter is cild ta5le. )en ena5led@ Informatica will write first to master ta5le and ten to cild ta5le maintainin referential interity. D0n!#$ P!r"#"#on#n-: Can confiure dynamic partitionin usin one of te followin metods? Disa5led *ased on num5er of partitions *ased on num5er of nodes in rid *ased on source partitionin • • •
4. MAPPING TAB 9Tr!n(for!"#on( 6#&% )e can confiure te followin? Connections !ources $arets $ransformations • • • •
8. MAPPING TAB 9P!r"#"#on#n- 6#&% )e can confiure te partitionin options ere.
;. COMPONENTS TAB In te Components ta5@ we can confiure te followin? Pre;!ession Command Post;!ession !uccess Command Post;!ession 9ailure Command On !uccess %mail On 9ailure %mail • • • • •
