About parameter file

Overview:

A parameter file is a list of parameters and variables and their associated values. These values are defined properties for a service, service process, workflow, worklet, or session. The Integration Service applies these values when you run a workflow or session that uses the parameter file.

Note: Parameters and Variables defined at mapping level are initialized through the session at the run time.

Parameter files provide the flexibility to changing parameter and variable values each time you run a session or workflow. You can include information for multiple services, service processes, workflows, worklets, and sessions in a single parameter file. You can also create multiple parameter files and use a different file each time you run a session or workflow.

The Integration Service reads the parameter file at the start of the workflow or session to determine the start values for the parameters and variables defined in the file. You can create a parameter file using a text editor such as WordPad or Notepad or EditPlus or Notepad ++.

Below is the following information when you use parameter files:

• Types of parameters and variables: You can define different types of parameters and variables in a parameter file. These include service variables, service process variables, workflow and worklet variables, session parameters, and mapping parameters and variables.
• Properties you can set in parameter files: You can use parameters and variables to define many properties in the Designer and Workflow Manager. The Integration Service expands the parameter when the session runs.
• Parameter file structure: You can assign a value for a parameter or variable in the parameter file by entering the parameter or variable name and value on a single line in the form name=value. Groups of parameters and variables must be preceded by a heading that identifies the service, service process, workflow, worklet, or session to which the parameters or variables apply.
• Parameter file location: You can specify the parameter file to use for a workflow or session. You can enter the parameter file name and directory in the workflow or session properties or in the pmcmd command line for manual.

Note:

Parameters can be defined in global or local section. The Parameter defined under global can be used in any of the session defined in that parameter file. Like database connection, database user name, database password etc. The parameters defined under session are application for only that particular session only. We will be see this more at below.

Parameter and Variable Types

A parameter file can contain different types of parameters and variables. When you run a session or workflow that uses a parameter file, the Integration Service reads the parameter file and expands the parameters and variables defined in the file.

We can define the following types of parameter and variable in a parameter file:

• Service variables: It defines general properties for the Integration Service such as email addresses, log file counts, and error thresholds.Example: $PMSuccessEmailUser, $PMSessionLogCount, and $PMSessionErrorThreshold. The service variable values you define in the parameter file override the values that are set in the Administrator tool.
• Service process variables: Itdefines the directories for Integration Service files for each Integration Service process. Example: $PMRootDir, $PMSessionLogDir, and $PMBadFileDir, $PMSourceFileDir, $PMTargetFileDir. The service process variable values you define in the parameter file override the values that are set in the Administrator tool. If the Integration Service uses operating system profiles, the operating system user specified in the operating system profile must have access to the directories you define for the service process variables.
• Workflow variables: It evaluates task conditions and record information in a workflow. Example: you can use a workflow variable in a Decision task to determine whether the previous task ran properly. In a workflow, $TaskName.PrevTaskStatus is a predefined workflow variable and $$VariableName is a user-defined workflow variable.
• Worklet variables: It evaluates task conditions and record information in a worklet. You can use predefined worklet variables in a parent workflow, but you cannot use workflow variables from the parent workflow in a worklet. In a worklet, $TaskName.PrevTaskStatus is a predefined worklet variable and $$VariableName is a user-defined worklet variable.
• Session parameters: It definesvalues that can change from session to session, such as database connections or file names. $PMSessionLogFile and $ParamName are user-defined session parameters.
• Mapping parameters: It defines values that remain constant throughout a session, such as state sales tax rates. When declared in a mapping or mapplet, $$ParameterName is a user-defined mapping parameter.
• Mapping variables: It defines values that can change during a session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session. When declared in a mapping or mapplet, $$VariableName is a mapping variable.

We cannot define the following types of variables in a parameter file:

• ·         $Source and $Target connection variables: Define the database location for a relational source, relational target, lookup table, or stored procedure.
  ·         Email variable: Define session information in an email message such as the number of rows loaded, the session completion time, and read and write statistics.
  ·         Local variables: Temporarily store data in variable ports in Aggregator, Expression, and Rank transformations.
  ·         Built-in variables: Variables that return run-time or system information, such as Integration Service name or system date.
  ·         Transaction control variables: Define conditions to commit or rollback transactions during the processing of database rows.
  ·         ABAP program variables: Represent SAP structures, fields in SAP structures, or values in the ABAP program.

Parameter File Structure

A parameter file contains a list of parameters and variables with assigned values. You group parameters and variables in different sections of the parameter file. Each section is preceded by a heading that identifies the Integration Service, Integration Service process, workflow, worklet, or session to which you want to define parameters or variables. You define parameters and variables directly below the heading, entering each parameter or variable on a new line. You can list parameters and variables in any order within a section.

Enter the parameter or variable definition in the form name=value.

Example, the following lines assign a value to InputFile, OutputFile

$InputFileName=sample_source.txt

$OutputFileName=sample_target.txt

The Integration Service interprets all characters between the beginning of the line and the first equals sign as the parameter name and all characters between the first equals sign and the end of the line as the parameter value. Therefore, if you enter a space between the parameter name and the equals sign, the Integration Service interprets the space as part of the parameter name. If a line contains multiple equals signs, the Integration Service interprets all equals signs after the first one as part of the parameter value.

Warning: The Integration Service uses the period character (.) to qualify folder, workflow, and session names when you run a workflow with a parameter file. If the folder name contains a period (.), the Integration Service cannot qualify the names properly and fails the workflow.

Parameter File Sections

We can define parameters and variables in any section in the parameter file. If you define a service or service process variable in a workflow, worklet, or session section, the variable applies to the service process that runs the task. Similarly, if you define a workflow variable in a session section, the value of the workflow variable applies only when the session runs.

The following table describes the parameter file headings that define each section in the parameter file and the scope of the parameters and variables that you define in each section:

Sample Parameter File:

[Global]

The global parameter and variable can be used any of the session, worklet or workflow mentioned in that parameter file without repeating them again and again.

[<folder name>.WF:<workflowname>.WT:<workletname>.ST:<session name>]

Note: [] will defined the entry for the respective job details like

Folder Name=you project folder

WF: is to identify the workflow name followed by wokflowname

WT: is to identify the worklet name followed by workletname

ST: is to identify the session name followed by sessionname

Below example for session details of the workflow in particular folder

[<folder name>.WF:<workflowname>.ST:<session name>]

Below example for session in a particular folder.

[<folder name>.<session name>]

Below example is global session which can be in worklet/workflow/folder in that particular repository.

[<session name>]

Example:

[Global]

$$SourceSystem=’Hyderabad’

$$ETLUSER=’gk1’

$$LOADTYPE=’Adhoc’

$DBConnection_Oracle=Scott

[Practice.WF:wf_s_m_employee_table2file.ST:s_m_employee_table2file]

$$LastRunDate=12/31/2013 01:12:11

$$Departments=(10,20)

$$Region=’INDIA’

Note: $ is defined session variable, $$ mapping variable/parameters and $$$ are pre-defined (default) variable like $$$SessStartTime

The variable defined under global can be used in any of the workflow mentioned in that same parameter file.

Comments

You can include comments in parameter files. The Integration Service ignores lines that are not valid headings and do not contain an equals sign character (=). The following lines are examples of parameter file comments:

---------------------------------------

Created 10/11/12 by xyz.

*** Update the parameters below this line when you run this workflow on Integration Service Int_01. ***

; This is a valid comment because this line contains no equals sign.

Null Values

You can assign null values to parameters and variables in the parameter file. When you assign null values to parameters and variables, the Integration Service obtains the value from the following places, depending on the parameter or variable type:

• Service and service process variables: The Integration Service uses the value set in the Administrator tool.
• Workflow and worklet variables: The Integration Service uses the value saved in the repository (if the variable is persistent), the user-specified default value, or the datatype default value.
• Session parameters: Session parameters do not have default values. If the Integration Service cannot find a value for a session parameter, it may fail the session, take an empty string as the default value, or fail to expand the parameter at run time. For example, the Integration Service fails a session where the session parameter $DBConnectionName is not defined.
• Mapping parameters and variables: The Integration Service uses the value saved in the repository (mapping variables only), the configured initial value, or the datatype default value.

To assign a null value, set the parameter or variable value to “<null>” or leave the value blank. For example, the following lines assign null values to service process variables $PMBadFileDir and $PMCacheDir:

$PMBadFileDir=<null>

$PMCacheDir=

Where to Use Parameters and Variables

We can use parameters and variables to assign values to properties in the Designer and Workflow Manager and to override some service and service process properties.

Example:

1. We can use a parameter to specify the Source/Target/Lookupfile name with folder path.
2. We can use a parameter to specify the relation Source/Target/Lookup table name and schema name.

If the property is a SQL statement or command, we can either use parameters and variables within the statement or command, or we can enter a parameter or variable in the input field for the property, and set the parameter or variable to the entire statement or command in the parameter file.

Example: We want to use a parameter or variable in a relational target override. We can enter a parameter or variable within the UPDATE statement of a relational target override and define the parameter or variable below the appropriate heading in the parameter file. Or, to define the UPDATE statement in a parameter file, complete the following steps:

1. In the Designer, edit the target instance, enter session parameter $ParamMyOverride in the Update Override field, and save the mapping.
2. In the Workflow Manager, configure the workflow or session to use a parameter file.
3. Set $ParamMyOverride to the SQL UPDATE statement below the appropriate heading in the parameter file.

We can also use a parameter file to override service and service process properties defined in the Administrator tool.

Example:

We can override the session log directory, $PMSessionLogDir. To do this, configure the workflow or session to use a parameter file and set $PMSessionLogDir to the new file path in the parameter file.

We can specify parameters and variables for the following PowerCenter objects:

Sources: You can use parameters and variables in input fields related to sources.

• Targets: You can use parameters and variables in input fields related to targets.
• Transformations: You can use parameters and variables in input fields related to transformations.
• Tasks: You can use parameters and variables in input fields related to tasks in the Workflow Manager.
• Sessions: You can use parameters and variables in input fields related to Session tasks.
• Workflows: You can use parameters and variables in input fields related to workflows.
• Connections: You can use parameters and variables in input fields related to connection objects. 
• Data profiling objects: You can use parameters and variables in input fields related to data profiling.

Some of the important session parameters:

·         $PMSessionLogFile is defines the name of the session log between session runs.

·         $InputFileName is defines a source file name and the parameter name using the appropriate prefix.

·         $LookupFileName is defines a lookup file name and the parameter name using the appropriate prefix.

·         $OutputFileNames is defines a target file name and the parameter name using the appropriate prefix.

·         $BadFileName is defines a reject file name and the parameter name using the appropriate prefix. 

·         $DBConnectionName is defines a relational database connection for a source, target, lookup, or stored procedure and Name the parameter using the appropriate prefix.

·         $ParamName is defines any other session property. For example, you can use this parameter to define a table owner name, table name prefix, FTP file or directory name, lookup cache file name prefix, or email address. You can use this parameter to define source, lookup, target, and reject file names, but not the session log file name or database connections and the parameter name using the appropriate prefix.

·         $PMFolderName will return the folder name.

·         $PMWorkflowName will return the workflow name.

In which conditions we can not use joiner transformation (Limitations of joiner transformation)?

In the conditions either input pipeline contains an Update Strategy transformation, you connect a Sequence Generator transformation directly before the Joiner transformation
1.Both input pipelines originate from the same Source Qualifier transformation.        

 2. Both input pipelines originate from the same Normalizer transformation.
3. Both input pipelines originate from the same Joiner transformation.
4. Either input pipeline contains an Update Strategy transformation.
5. We connect a Sequence Generator transformation directly before the Joiner transformation.

Interview Questions Set 6

1. How do you change parameter when u move it from development to production.
2. How does the session recovery work.
3. Why use shortcuts(Instead of making copies).
4. where is the reject loader .
5. How do you use reject loader.
6. Do u have to change the reject file b4 using reject loader utility.
7. Differences between version 7.x and 8.x.
8. Debugger – what are the modules, what are the options you can specify when using debugger, can you change the expression condition dynamically when the debugger is running.
9. Mapplets – can you use an active transformation in a mapplet, 
10. What are active transformations? Name them.
11. Can u use flat files in Mapplets.
12. How many transformations can be used in mapplets.
13. Can a joiner be used in a mapplet.
14. What are active transformations.
15. How can you join 3 tables? Why cant you use a single Joiner to join 3 tables.
16. Global and Local shortcuts. Advantages.
17. Mapping variables, parameters syntax, if you create mapping variables and parameters in mapplet can u use them in the mapping?
18. Have you worked with/created Parameter file
19. What’s the layout of parameter file (what does a parameter file contain?)?
20. Why do u use Mapping Parameter and mapping variable?
21. Where do u create/define mapping parameter and mapping variable?
22. Session Recovery. 1000 rows in the source of which 500 passed through and then I killed the session. Can you perform a recovery and how
23. Slowly changing dimensions, types and where will you use them
24. What are the modules in Power Center
25. Filter transformation in the condition one of the data is NULL would the record be dropped.
26. Parameter&variable differences
27. Reusable transformation & shortcut differences
28. Mapplets ( can u use source qyalifier, can u use sequence generator, can u use target)
29. Implementation methodology
30. Do u have knowledge in ralph kimball methodology
31. According to his methodology what all u need before u build a datawarehouse
32. How do u recover rows from a failed session
33. Sequence generator, when u move from develoment to production how will u reset
34. Whats there in global repository
35. Repository user profiles
36. How do u set a varible in incremental aggregation
37. Can u create a flatfile target
38. What did u do in source pre load stored procedure
39. Scheduling properties,whats the default (sequential)
40. In a concurrent batch if a session fails, can u start again from that session
41. When u move from devel to prod how will u retain a variable
42. Partition, what happens if the specified key range is shorter n longer
43. Performance tuning( what u did in performance tuning)
44. What are conformed dimensions?
45. what are factless facts? And in which scenario will you use such kinds of fact tables.
46. can you avoid static cache in the lookup transformation? I mean can you 
47. disable caching in a lookup transformation?
48. what is the meaning of complex transformation?
49. in any project how many mappings they will use(minimum)?
50. How do u implement unconn. Stored proc. In a mapping?
51. Can u access a repository created in previous version of Informatica?
52. What happens if the info. Server doesn’t find the session parameter in the parameter file?
53. How big was your fact table
54. How did you handle performance issues If you have data coming in from multiple sources, just walk thru the process of loading it into the target
55. How will u convert rows into columns or columns into rows
56. What are the steps involved in the migration from older version to newer version of Informatica Server?
57. What are the main features of Oracle 8i with context to datawarehouse?
58. What are the new features of Power Center 5.0?
59. How to run a session, which contains mapplet?
60. Differentiate between Load Manager and DTM?
61. What are session parameters ? How do u set them?
62. When do u use mapping parameters? (In which transformations)
63. What is a parameter When and where do u them when does the value will be created 
64. Desing time, run time. If u don’t create parameter what will happen
65. What are variable ports and list two situations when they can be used? 
66. What are the parts of Informatica Server? 
67. How does the server recognize the source and target databases. Elaborate on this. 
68. List the transformation used for the following: 
1. Heterogeneous Sources
2. Homogeneous Sources
3. Find the 5 highest paid employees within a dept.
4. Create a Summary table
5. Generate surrogate keys
69. What is the difference between sequential batch and concurrent batch and which is recommended and why? 
70. A session S_MAP1 is in Repository A. While running the session error message has displayed 
71. ‘server hot-ws270 is connect to Repository B ‘. What does it mean? 
72. How do you do error handling in Informatica? 
73. How do you implement scheduling in Informatica? 
74. What is the meaning of up gradation of repository? 
75. How can you run a session without using server manager? 
76. Consider two cases: 
77. 1. Power Center Server and Client on the same machine
78. 2. Power Center Sever and Client on the different machines
79. what is the basic difference in these two setups and which is recommended? 
80. Informatica Server and Client are in different machines. You run a session from the server manager by specifying the source and target databases. It displays an error. You are confident that everything is correct. Then why it is displaying the error?
81. When you connect to repository for the first time it asks you for user name & password of repository and database both. 
82. But subsequent times it asks only repository password. Why? 
83. What is the difference between normal and bulk loading?
84. Which one is recommended? 
85. What is a test load? 
86. How can you use an Oracle sequences in Informatica? You have an Informatica sequence generator transformation also. Which one is better to use? 
87. What is the difference between a shortcut of an object and copy of an object?
88. Compare them. 
89. What is mapplet and a reusable transformation? 
90. How do you implement configuration management in Informatica? 
91. What are Business Components in Informatica? 
92. Dimension Object created in Oracle can be imported in Designer 
93. Cubes contain measures 
94. COM components can be used in Informatica 
95. What is the advantage of persistent cache? When it should be used. 
96. When will you use SQL override in a lookup transformation? 
97. Two different admin users created for repository are ______ and_______ 
98. Two Default User groups created in the repository are ____ and ______ 
99. A mapping contains
1. Source Table S_Time ( Start_Year, End_Year )
2. Target Table Tim_Dim ( Date, Day, Month, Year, Quarter )
100. Stored procedure transformation : A procedure has two input parameters I_Start_Year, I_End_Year and output parameter as O_Date, Day , Month, Year, Quarter. If this session is running, how many rows will be available in the target and why? 
101. Two Sources S1, S2 containing measures M1,M2,M3, 4 Dimensions D1,D2,D3,D4, 
102. 1 Fact F1 containing measures M1, M2,M3 and Dimension Surrogate keys K1,K2,K3,K4
1. Write a SQL statement to populate Fact table F1
2. Design a mapping in Informatica for loading of Fact table F1. 
103. What is the difference between connected lookup and unconnected lookup?
105. How can you import a flat file or relational sources to your work area?
106. What is active and passive transformation and how many active and passive transformations are there?
107. What is meant of performance tuning and what are the parameters for increasing performance.
108. Why is meant by direct and indirect loading options in sessions?
109. What is scheduling and how many types of scheduling options are there?
110. What are batches and what are the types of batches?
111. What is difference between stored procedure transformation and lookup transformation and where to use which one?
112. What is difference between SQL override and lookup override?
113. What is staging area in informatica and where it exist and do we need it and how much of data can it store?
114. What is ODS and how much of information can it store in it and what are data marts?
115. Interviewer may ask draw your architecture of your project and informatica architecture?
116. How can you handle errors in informatica and how can you identify them?
117. What is auxiliary mapping and where in appears?
118. What is difference between Mapplets and reusable transformations?
119. In which scenario we can use unconnected and connected lookup transformation and stored procedure?
120. In which scenario we can use update strategy transformation and how many ways that we can use update strategy transformation.
121. What is difference between filter transformation and router transformation?
122. What is difference between source qualifier transformation and joiner transformation?
123. In a scenario I want to change the dimensions of a table and normalize the denoralized table which transformation can I use?
124. Why we use lookup condition in lookup transformation?
125. How can you delete duplicate records from a flat file and also from a relational file?
126. What is the method of loading 5 flat files of having same structure to a single target and which transformations I can use?
127. If am having n rows in my source and I want n+ 1 row in my target what is the logic that I can go for.
128. How many types of caches are there in informatica? And what is difference between static and dynamic cache?
129. Which caches are used when you use aggregator, filter, rank, join, sequence generator transformation?
130. How can u increase the cache size and what is the maximum size?
131. In a scenario I have col1, col2, col3, under that 1,x,y, and 2,a,b and I want in this form col1, col2 and 1,x and 1,y and 2,a and 2,b, what is the procedure?
132. What is informatica server background?
133. What is pmcmd? Where is works?
134. What are worklets?
135. When u use an update strategy transformation what are the parameters you had to go for? And what is forward reject rows and what happens if we not enable this option and what happens if we place a target next to the update transformation will they appear?
136. What is materialized view and in which scenario it works and have u implemented any MV?
137. If am having n tables how many joiner transformations I require to join them and what is the condition that I need to join any two tables?
138. How can we call an unconnected lookup and stored procedure in a mapping and from which transformation we can call them?
139. What is CDC and in which scenario in works?
140. How can u import a file which is having a header and footer into source analyzer?
141. What is fact constellation and how many facts and dimensions are there in your project and how many mappings you have done in your project, can u name them?
142. What is a complex mapping had u done any complex mapping in your project?
143. Explain what is SCD and types of scd’s and which one works in which scenario?
144. What is mapping parameters and mapping variables and where can we use them and can we use them in another mapping also?
145. Why we use stored procedures?
146. Can we create an n number of tasks from a single mapping?
147. What is event raise and event wait?
148. What are partitions in informatica and how many types of partitions are there and can u make partitions on a flat file?
149. Explain about cubes and dimensions?
150. What are the components of informatica?
151. What is debugger and what is the advantage of going for that?
152. If u have no accessible to debugger and run workflow monitor how can u know that your mapping is successful?
153. What is target load order and what is target load plan?
154. Think that your source is COBOL file and which transformation will u use to import?
155. What is the size of ur project and size of the data that u use in mappings, and size of the staging area?
156. How can u join a flat and relational without having any matching keys?
157. What is MOLAP, DOLAP?
158. What is slice and dicing?
159. What is table mutating?
160. What is xx_sh_alloc, where does this appear?
161. How can u identify that a table is partitioned or not?
162. What is purpose of “dd” in update strategy transformation?
163. What are DTM and LM?
164. How can u recover a session if it fails in between?
165. How many types are there to create a reusable transformation?
166. A table having 100 records in it and how can you copy these records to another table in oracle?
167. What are the output files that informatica server creates when session runs.

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button.

Some Advance topic on Mapping Parameters and Variables.

Advance topic on Mapping Parameters and Variables:

The Mapping parameters and variables are used to make the mappings more flexible/dynamic. Mapping parameters and variables represent values in mappings and mapplets.

If we declare mapping parameters and variables in a mapping, we can reuse a mapping by changing the parameter and variable values of the mapping in the session or through a parameter file. This will reduce the overhead of creating multiple mappings when only certain values of a mapping are different.

When we create a parameter or variable in a mapping/mapplet, then we need to define a value for that mapping parameter or variable in a parameter file before you run that session.

Mapping Parameter: It represents a constant value and retains the same value throughout the session that we define before running a session.

Mapping Variable: It represents a value that can change throughout the session based on the business logic define for the mapping variable in the mapping. The Integration Service saves that value of a mapping variable to the repository at the end of the each successful session run and uses that value the next time you run the session.

Note: If you don’t want to use the saved value of the mapping variable at the end of the session. You can override those values at the end of the session.

Mapping Parameter declaration:

Name: $$NEWVARIABLE (starts with $$)

Type: Parameter

Datatype: string/text/small integer/real/ntext/nstring/integer/double/decimal/datetime

Precision: as you required

Scale: as you required

Aggregation: NA

IsExpVar: FALSE/TRUE.

What is “IsExpVar”: IsExpVar is determines how the Integration Service will expands the parameter in an expression string.

• If true, the Integration Service expands the parameter before parsing the expression.

• If false, the Integration Service expands the parameter after parsing the expression.

• Default is false.

Note: If you set this field to true, you must set the parameter datatype to String, or the Integration Service fails the session. 

Mapping Variable declaration:

Name: $$NEWVARIABLE (starts with $$)

Type: Variable

Datatype: string/text/small integer/real/ntext/nstring/integer/double/decimal/datetime

Precision: as you required

Scale: as you required

Aggregation: Max/Min and Count for integer datatype.

IsExpVar: FALSE/TRUE.

 

Aggregation: Determines the type of calculation you can perform with the variable.

• Set the aggregation to Max if you want to use the mapping variable to determine a maximum value from a group of values.

• Set the aggregation to Min if you want to use the mapping variable to determine a minimum value from a group of values.

• Set the aggregation to Count if you want to use the mapping variable to count number of rows read from source.

Note: The Integration Service saves that value of a mapping variable to the repository at the end of the each successful session run and uses that value the next time you run the session.

Here is the sample mapping for how to use mapping variables of different types.

1. First Drag & Drop Source/Target definition into mapping designer workspace.

2. From the menu select: Mappings à Parameters and Variables

 

• Added 3 ports
• Name them $$Max_Value, $$Min_Value and $$Count_Value
• Select Type as Variable
• Datatype as integer
• Aggregation: $$Max_Value as Max, $$Min_Value as Min and $$Count_Value as Count

3. Added an Expression Transformation in between Source Qualifier and Target Instance.

4. Drag all port from SQ to Expression Transformation.

5. Select Expression and Edit it.

Added 3 output ports:

out_MAX_SALARY = SETMAXVARIABLE($$Max_Value, SAL)

out_MIN_SALARY = SETMINVARIABLE($$Min_Value, SAL)

out_Count_Variable = SETCOUNTVARIABLE($$Count_Value)

OR

3 variable ports and 3 port ports:

var_MAX_SALARY = SETMAXVARIABLE($$Max_Value, SAL)

out_MAX_SALARY = var_MAX_SALARY

var_MIN_SALARY = SETMINVARIABLE($$Min_Value, SAL)

out_MIN_SALARY = var_MIN_SALARY

var_Count_Variable = SETCOUNTVARIABLE($$Count_Value)

out_Count_Variable = var_Count_Variable

6. Map the ports from Expression to Target as below:

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button.

About Mapping Parameters & Variables ?

Use mapping parameters and variables to make mappings more flexible. Mapping parameters and variables represent values in mappings and mapplets. If you declare mapping parameters and variables in a mapping, you can reuse a mapping by altering the parameter and variable values of the mapping in the session. This can reduce the overhead of creating multiple mappings when only certain attributes of a mapping need to be changed.

To use a mapping parameter or variable in a mapping or mapplet, first we need to declare them in each mapping or mapplet. Then you can define a value for those mapping parameter or mapping variable before run the session.

Uses of Mapping Parameter:

• A mapping parameter represents a constant value that you can define before running a session. A mapping parameter retains the same value throughout the entire session.
• A mapping parameter cannot be change will session is using. It will retain the same values throughout the session.
• If mapping or mapplet is reusable then you change defines different values at parameter file.
• When you use a mapping parameter, you declare and use the parameter in a mapping or mapplet. Then define the value of the parameter in a parameter file. The Integration Service evaluates all references to the parameter to that value.

Mapping parameters and variables can be used in below transformations:

• Source qualifier
• Filter
• Expression
• User-Defined Join
• Router
• Update strategy
• Lookup override

Uses of Mapping Variable:

Unlike a mapping parameter, a mapping variable represents a value that can change through the session.

The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session.

A mapping variable can change dynamically 'N' no of the throughout the session.

Use a variable function in the mapping to change the value of the variable.

At the beginning of a session, the Integration Service evaluates references to a variable to determine the start value. At the end of a successful session, the Integration Service saves the final value of the variable to the repository. The next time you run the session, the Integration Service evaluates references to the variable to the saved value. To override the saved value, define the start value of the variable in a parameter file or assign a value in the pre-session variable assignment in the session properties.

Mapping parameters and variables can be used in below transformations:

• Filter
• Expression
• Router
• Update strategy

Initial and Default Values

When we declare a mapping parameter or variable in a mapping or a mapplet, we can enter an initial value. The Integration Service uses the configured initial value for a mapping parameter when the parameter is not defined in the parameter file. Similarly, the Integration Service uses the configured initial value for a mapping variable when the variable value is not defined in the parameter file, and there is no saved variable value in the repository.

When the Integration Service needs an initial value, and we did not declare an initial value for the parameter or variable, the Integration Service uses a default value based on the datatype of the parameter or variable.

The following table lists the default values the Integration Service uses for different types of data:

Data	Default Value
String	Empty string.
Numeric	0
Datetime	1/1/1753 A.D. or 1/1/1 when the Integration Service is configured for compatibility with 4.0.

Using String Parameters and Variables

For example, we might use a parameter named $$State in the filter for a Source Qualifier transformation to extract rows for a particular state:

            STATE = ‘$$State’

During the session, the Integration Service replaces the parameter with a string. If $$State is defined as MD in the parameter file, the Integration Service replaces the parameter as follows:

            STATE = ‘MD’

You can perform a similar filter in the Filter transformation using the PowerCenter transformation language as follows:

            STATE = $$State

If you enclose the parameter in single quotes in the Filter transformation, the Integration Service reads it as the string literal “$$State” instead of replacing the parameter with “MD.”

Variable Values

The Integration Service holds two different values for a mapping variable during a session run: 

1. Start value of a mapping variable
2. Current value of a mapping variable

The current value of a mapping variable changes as the session progresses. To use the current value of a mapping variable within the mapping or in another transformation, create the following expression with the SETVARIABLE function:

SETVARIABLE($$MAPVAR,NULL)

At the end of a successful session, the Integration Service saves the final current value of a mapping variable to the repository.

Start Value:

The start value is the value of the variable at the start of the session. The start value could be a value defined in the parameter file for the variable, a value assigned in the pre-session variable assignment, a value saved in the repository from the previous run of the session, a user defined initial value for the variable, or the default value based on the variable datatype. The Integration Service looks for the start value in the following order: 

1. Value in parameter file
2. Value in pre-session variable assignment
3. Value saved in the repository
4. Initial value
5. Datatype default value

For example, you create a mapping variable in a mapping or mapplet and enter an initial value, but you do not define a value for the variable in a parameter file. The first time the Integration Service runs the session, it evaluates the start value of the variable to the configured initial value. The next time the session runs, the Integration Service evaluates the start value of the variable to the value saved in the repository. If you want to override the value saved in the repository before running a session, you need to define a value for the variable in a parameter file. When you define a mapping variable in the parameter file, the Integration Service uses this value instead of the value saved in the repository or the configured initial value for the variable. When you use a mapping variable ('$$MAPVAR') in an expression, the expression always returns the start value of the mapping variable. If the start value of MAPVAR is 0, then $$MAPVAR returns 0.

Current Value

Variable Datatype and Aggregation Type

The current value is the value of the variable as the session progresses. When a session starts, the current value of a variable is the same as the start value. As the session progresses, the Integration Service calculates the current value using a variable function that you set for the variable. Unlike the start value of a mapping variable, the current value can change as the Integration Service evaluates the current value of a variable as each row passes through the mapping. The final current value for a variable is saved to the repository at the end of a successful session. When a session fails to complete, the Integration Service does not update the value of the variable in the repository. The Integration Service states the value saved to the repository for each mapping variable in the session log.

When you declare a mapping variable in a mapping, you need to configure the datatype and aggregation type for the variable.

The datatype you choose for a mapping variable allows the Integration Service to pick an appropriate default value for the mapping variable. The default is used as the start value of a mapping variable when there is no value defined for a variable in the parameter file, in the repository, and there is no user defined initial value.

The Integration Service uses the aggregate type of a mapping variable to determine the final current value of the mapping variable. When you have a pipeline with multiple partitions, the Integration Service combines the variable value from each partition and saves the final current variable value into the repository.

You can create a variable with the following aggregation types:

• Count: Integer and small integer datatypes only.
• Max: All transformation datatypes except binary datatype.
• Min: All transformation datatypes except binary datatype.

You can configure a mapping variable for a Count aggregation type when it is an Integer or Small Integer. You can configure mapping variables of any datatype for Max or Min aggregation types.

To keep the variable value consistent throughout the session run, the Designer limits the variable functions you use with a variable based on aggregation type. For example, use the SetMaxVariable function for a variable with a Max aggregation type, but not with a variable with a Min aggregation type.

Variable Functions

Variable functions determine how the Integration Service calculates the current value of a mapping variable in a pipeline. Use variable functions in an expression to set the value of a mapping variable for the next session run. The transformation language provides the following variable functions to use in a mapping:

• SetMaxVariable. Sets the variable to the maximum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMaxVariable with a mapping variable, the aggregation type of the mapping variable must be set to Max.
• SetMinVariable. Sets the variable to the minimum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMinVariable with a mapping variable, the aggregation type of the mapping variable must be set to Min.
• SetCountVariable. Increments the variable value by one. In other words, it adds one to the variable value when a row is marked for insertion, and subtracts one when the row is marked for deletion. It ignores rows marked for update or reject. To use the SetCountVariable with a mapping variable, the aggregation type of the mapping variable must be set to Count.
• SetVariable. Sets the variable to the configured value. At the end of a session, it compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final value to the repository. To use the SetVariable function with a mapping variable, the aggregation type of the mapping variable must be set to Max or Min. The SetVariable function ignores rows marked for delete or reject.

Use variable functions only once for each mapping variable in a pipeline. The Integration Service processes variable functions as it encounters them in the mapping. The order in which the Integration Service encounters variable functions in the mapping may not be the same for every session run. This may cause inconsistent results when you use the same variable function multiple times in a mapping.

The Integration Service does not save the final current value of a mapping variable to the repository when any of the following conditions are true:

• The session fails to complete.
• The session is configured for a test load.
• The session is a debug session.
• The session runs in debug mode and is configured to discard session output.

Sample Mapping:

If you want to fetch only those records which are modified/create newly after the previous run. Then you need to create a user-defined mapping variable $$LastRunDateTime (datetime datatype) that saves the Timestamp of the last row that Integration Service read in the previous session.

And in the source qualifier define the filter condition:.

Syntax:

Table.DateTime_column > $$LastRunDateTime

Note: In case if you define user mapping variable as string then you need to convert it into date datatype.

Syntax:

Table.DateTime_column > to_date($$LastRunDateTime, ‘YYYY-MM-DD HH:MM:SS’

1. In the Mapping Designer, click Mappings Or, in the Mapplet Designer.

2. Select ‘Parameters and Variables’

3. Click the Add button:

Field	Description
Name	Parameter name. The parameter name must be $$ followed by any alphanumeric or underscore characters.
Type	Variable/parameter. Select Parameter.
Datatype	Datatype of the parameter.
Precision or Scale	Precision and scale of the parameter.
Aggregation	Use for variables.
IsExprVar	Determines how the Integration Service expands the parameter in an expression string. If true, the Integration Service expands the parameter before parsing the expression. If false, the Integration Service expands the parameter after parsing the expression. Default is false. Note: If you set this field to true, you must set the parameter datatype to String, or the Integration Service fails the session.
Initial Value	Initial value of the parameter. If you do not set a value for the parameter in the parameter file, the Integration Service uses this value for the parameter during sessions. If this value is undefined, then the Integration Service uses a default value based on the datatype of the mapping variable. String=’’ Integer=0
Description	Description associated with the parameter.

Click on ‘OK’

4. Double click on Source Qualifier à Go to Properties tab.

From Source Filter click on Open Editorà Go to variables tab to added mapping variable to filter condition.

 

Select the Mapping Variable from Mapping variables folder and double click on it to add it to filter condition.

Click on ‘OK.

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button.

what are the Types of repositories ?

Informatica PowerCenter includes following type of repositories:

• Standalone Repository: A repository that functions individually and this is unrelated to any other repositories.

• Global Repository: This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts.
• Local Repository: Local repository is within a domain and it’s not a global repository. Local repository can connect to a global repository using global shortcuts and can use objects in it’s shared folders.
• Versioned Repository: This can either be local or global repository but it allows version control for the repository. A versioned repository can store multiple copies, or versions of an object. This features allows to efficiently develop, test and deploy metadata in the production environment.

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button.

How to Reset the Sequence Generator value between the Environments?

While migrating the code we will get a dialouge box in that there are two 
values retain and replace.
and if you are deploying into production environment, you may want to retain the 
current values for sequence Generator transformation in the target folder
instead of replacing them.

To retain values, check the box
uncheck for the replace.

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button.