Mappings > Visio templates > Configuring a Visio template
  

Configuring a Visio template

Configure a Visio template in the Cloud Integration Template Designer to define flexible, reusable data flow logic for use in mapping tasks.
A Visio template includes at least one source definition, source qualifier, target definition, and links that define how data moves between objects. A Visio template can include other Informatica objects, such as a Lookup object to define lookups or a Joiner object to join heterogeneous sources.
A pipeline consists of a source qualifier and all the objects and targets that receive data from the source qualifier. You can include one or more pipelines in a Visio template.
When you configure a Visio template, you can configure template parameters. Template parameters are values that you can define in mapping tasks.
In a Visio template, sources, targets, and lookups are always template parameters. You can create additional template parameters for other data flow logic, such as filter or join conditions or other expressions.
You can use expression macros in Expression and Aggregator objects. Expression macros allow you to define dynamic expression logic.
You can also use user-defined parameters in Visio templates and mapping tasks. User-defined parameters are values that you define in a parameter file. You can update user-defined parameter values without editing the Visio template or the mapping task. You associate a parameter file with a mapping task.
When you configure a Visio template, you can configure the following:

Creating a Visio template

Though you use the Cloud Integration Template Designer to configure Visio templates, you can use several methods to create the initial Visio template XML file.
The following table describes when and how to use each method. Use the method that best suits your requirements.
Visio template creation method
Description
Create the Visio template in the Cloud Integration Template Designer.
Use to create data flow logic entirely in the Cloud Integration Template Designer.
Export a mapping from PowerCenter.
Use when you have a PowerCenter mapping that you want to parameterize and use in Informatica Cloud.
To create a Visio template from an existing PowerCenter mapping:
  1. 1. In the PowerCenter Designer, export the mapping to XML.
  2. 2. In the Cloud Integration Template Designer, use the Create Template from Mapping XML button in the Informatica toolbar.
  3. 3. Configure the template in the Cloud Integration Template Designer.
Export a task from Data Integration.
Use when you have a Data Integration synchronization task or mapping task that you want to expand and parameterize.
To create a Visio template from an existing task:
  1. 1. On the Explore page, navigate to the task.
  2. 2. In the row for the task, click Actions > Download Mapping XML.
  3. 3. In the Cloud Integration Template Designer, use the Create Template from Mapping XML button in the Informatica toolbar.
  4. 4. Configure the template in the Cloud Integration Template Designer.

Visio template information in tasks

When you configure a Visio template, you can add information that displays when an Data Integration user uploads a Visio template or creates a mapping task based on the template. This information can guide the user on how to work with the Visio template.
You can add or update this information in Data Integration when you upload or edit a Visio template.
The following table describes how information in the Visio template displays in the mapping task:
Visio template element in the Cloud Integration Template Designer
Update in Data Integration
Display in the Mapping Task Wizard
Image of the Visio template data flow displays object and link names.
Descriptive link names can help explain data flow logic.
Template XML file that you upload when you create or edit a Visio template.
Definition page.
Source object name.
Label property on the Visio Template page.
Source connection and object on the Sources page.
Target object name.
Label property on the Visio Template page.
Target connection and object on the Targets page.
Template parameter name.
Label property on the Visio Template page.
Template parameter label.
Template parameter description in the Show Parameters dialog box.
Template parameter description on the Visio Template page.
Template parameter tooltip.

Template parameters

A template parameter is a placeholder for a value or values in a Visio template. You configure template parameters when you configure Informatica objects in the Visio template data flow.
You can define the value of the template parameter when you upload the Visio template to your Data Integration organization or when you create a mapping task based on the Visio template.
You can create a template parameter for any logical aspect of a data flow. Sources, targets, and lookups are always template parameters. You can create additional template parameters for other aspects of the data flow logic. Some template parameters you might want to create include the following:
For example, if you have regional lookup data in different lookup tables, you might create a $lookuptable$ template parameter in the Lookup object that represents the lookup table. When you configure the mapping task, you select the lookup connection and table that you want to use. You configure a different mapping task for each regional lookup table.
To create a template parameter in a Visio template, surround the template parameter name with dollar signs as follows: $<template_parameter_name>$. Template parameter names are case sensitive.
By default, the template parameter name displays as the template parameter label in the mapping task wizard. However, you can also configure a template parameter label in the template or after you upload the template.
You can use the Show Parameters button on the Informatica toolbar to see the template parameters defined using the $<template_parameter_name>$ syntax. If you do not use the template parameter name syntax to configure source, target, or lookup template parameters, they do not display in the Show Parameters dialog box.

Template parameter usage

Visio template parameters are flexible placeholders that you can use in many different ways. Some examples:

Expression macros in Visio templates

Expression macros are macros that can help create flexible expressions in Visio templates. You can use expression macros in Expression and Aggregator objects.
Use an expression macro to specify repetitive expression statements or complex expressions. Expression macros apply a common expression pattern across a set of fields, such as adding all or a set of fields together, checking if fields contain null values, or converting dates to a different format.
You can use an expression macro to generate output fields and variable fields.
An expression macro consists of several parts:
For example, you might use the following expression macro to check if any of the address fields that start with "addr" are null. The ISNULL output port will be set to a value 1 or higher if one or more fields are null:
Macro variable name: Declare_%addressports%
Macro variable fields: {"addrport":"Pattern:^addr"}
Output field names: ISNULL
Macro expression: %OPR_SUM[IIF(ISNULL(%addrport%),1,0)]%

Template parameters in expression macros

You can use template parameters in expression macros.
For example, you might use a template parameter in the following macro variable declaration to define the fields to be used in the expression macro:
Macro variable name: Declare_%input%
Macro variable fields: {"inputfields":"$salesdata$"}
When you configure the mapping task, $salesdata$ displays as a template parameter. The fields that you define for the template parameter are expanded where you use the %inputfields% variable in the Expression object.

Patterns in expression macros

You can use Mapping Architect for Visio patterns in expression macros.
For example, if you know that you want to use all fields that begin with SALES_, you might declare the macro variable fields as follows:
Macro variable name: Declare_%salesfields%
Macro variable fields: {"SalesFields":"Pattern:^SALES_"}
Or, if you know that you want to use all input fields, you might use the following expression:
Macro variable name: Declare_%salesfields%
Macro variable fields: {"SalesFields":"All Ports"}
For more information about patterns, see the Mapping Architect for Visio documentation.

Horizontal and vertical expansion

An expression macro can expand vertically or horizontally. You can use both horizontal and vertical expansion in an expression macro.
A vertical expansion performs the same calculation on multiple fields by generating multiple expressions. To use a vertical expansion, configure a macro input field that represents multiple incoming fields. When the task runs, the application performs the same calculations on each field that the macro input field represents.
For example, the following expression trims leading and trailing spaces from the address ports defined by the %addr% variable and writes the results to output fields with a suffix of _o:
%addr%_o = LTRIM(RTRIM(%addr%))
This results in the following expressions in the following output fields:
address1_o = LTRIM(RTRIM(address1))
address2_o = LTRIM(RTRIM(address2))
city_o = LTRIM(RTRIM(city))
state_o = LTRIM(RTRIM(state))
zipcode_o = LTRIM(RTRIM(zipcode))
Horizontal expansion performs a calculation across multiple fields while expanding a single expression. To use a horizontal expansion, configure a macro input field that represents a set of incoming fields or a set of constants. When the task runs, the task expands the macro input field and then uses the fields or constants to calculate a complex expression.
You can use the following horizontal expansion functions:
%OPR_CONCAT%
Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields. %OPR_CONCAT% creates calculations similar to the following expression:
FieldA || FieldB || FieldC...
%OPR_CONCATDELIM%
Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields, and adds a comma delimiter. %OPR_CONCATDELIM% creates calculations similar to the following expression:
FieldA || ", " || FieldB || ", " || FieldC...
%OPR_IIF%
Uses the IIF function and expands an expression in an expression macro to evaluate a set of IIF statements. %OPR_IIF% creates calculations similar to the following expression:
IIF(<field> >= <constantA>, <constant1>,
IIF(<field> >= <constantB>, <constant2>,
IIF(<field> >= <constantC>, <constant3>, 'out of range')))
%OPR_SUM%
Uses the SUM function and expands an expression in an expression macro to return the sum of all fields. %OPR_SUM% creates calculations similar to the following expression:
FieldA + FieldB + FieldC...
For example, the following expression checks if any of the fields are null. If a field is null, it sets the Isnull field to a positive number:
Isnull=%OPR_SUM{IIF(ISNULL(%fields%),1,0]%
When expanded, the expression macro generates the following expression, and expands the expression to include all fields defined by the %fields% variable.
Isnull=IIF(ISNULL (fieldA, 1,0) + IIF(ISNULL(fieldB, 1, 0)...

Expression macro configuration

Configure an expression macro on the Configuration tab of the Expression or Aggregator object properties dialog box.
When you configure an expression macro, use one row for the macro variable declaration and another for the macro statement. Enter expression macro elements in the Port Name and Expression columns as follows. Data type and port type information is not relevant:
Expression macro part
Port name
Expression
Macro variable declaration
Macro variable name.
For example:
Declare_%addressports%
Macro variable fields.
For example:
{"addrport":"Pattern:^addr"}
Macro statement
Output field names.
For example:
ISNULL
Macro expression.
%OPR_SUM[IIF(ISNULL(%addrport%),1,0)]%

Parameter files and user-defined parameters

A parameter file is a list of user-defined parameters and their associated values. You can use user-defined parameters in Visio templates and mapping tasks.
Use a parameter file to define values that you want to update without having to edit the Visio template or the mapping task. For example, you might use a user-defined parameter for a sales quota that changes quarterly. Or, you might configure a task to update user-defined parameter values in the parameter file at the end of the job, so the next time the job runs, it uses the new values.
You can include user-defined parameters for multiple Visio templates or mapping tasks in a single parameter file. You can also use multiple parameter files for different Visio templates or tasks. The mapping task reads the parameter file before a task runs to determine the start values for the user-defined parameters used in the task.
User-defined parameter values are treated as String values. When you use a user-defined parameter in an expression, use the appropriate function to convert the value to the necessary datatype. For example, you might use the following expression to define a quarterly bonus for employees:
IIF((EMP_SALES < TO_INTEGER($$SalesQuota), 200, 0)
To use a parameter file, perform the following steps:
  1. 1. Use a user-defined parameter in a Visio template or mapping task.
  2. 2. Use the following format for the parameter file:
  3. [Global]
    $$<user-defined_parameter>=value
    $$<user-defined_parameter2>=value2
    For example:
    [Global]
    $$SalesQuota=1000
    $$Region=NW
    Note: The value of a user-defined parameter includes any characters after the equals sign (=), including leading or trailing spaces. User-defined parameter names are case-sensitive.
    You can save the file as several different formats, such as *.txt, *.doc, or *.param.
  4. 3. Save the parameter file to a directory local to the Secure Agent to run the task.
  5. Use the following directory:
    <Secure Agent installation directory>/apps/Data_Integration_Server/data/userparameters
  6. 4. Enter the parameter file name on the Schedule page of the mapping task wizard.

Object-level session properties

Object-level session properties are advanced properties based on PowerCenter session properties for transformation objects. You can configure object-level session properties for source qualifier and target objects.
For source qualifier objects, you can configure object-level session properties such as a SQL query override or pipeline partitioning attributes. Target objects allow different object-level session properties based on target type, such as null characters or delimiters for flat file targets or target load type for database targets.
Configure object-level session properties in the Session Properties field on the Properties tab of a source qualfier or target object. Use XML to configure the session properties that you want to use. Use the following syntax:
<attribute name="<session property name>" value="<value>"/>
For example, you can use the following XML to define target properties in a target object:
<attribute name ="Append if Exists" value ="YES"/>
<attribute name ="Create Directory if Not Exists" value ="YES"/>
<attribute name ="Header Options" value ="No Header"/>
To define partition properties, use a slightly different format. For example, to define read partitions for a database table, you could enter the following XML in the the source qualifier object Session Properties field:
<partition name="Partition1"/>
<partition name="Partition2"/>
<partition name="Partition3"/>
<partitionPoint type="KEY_RANGE">
<ppField name="field1">
<range min="10" max="20" />
<range min="21" max="30" />
<range min="31" max="40" />
</ppField>
</partitionPoint>
Note: XML is case sensitive. Also, unlike in PowerCenter, use an underscore for the KEY_RANGE option.
Visit the Informatica Cloud Community for additional details and examples. You can browse or search for "session properties".

Optional objects

You can configure objects in a Visio template data flow as optional. When data is not passed to an optional object in a mapping task, the object is not included in the final data flow for the task.
You can configure any object as optional except source or source qualifier objects.
For example, you might have an optional Expression object that updates the date format of date data routed from the source. The updated data is routed back to the main data flow. If the source selected for the task does not include date data or a date field that is routed to the Expression object, IData Integration omits the optional Expression object from the final data flow used for the task.
When you use an optional object, make sure the data flow is still valid if the object is not included. If the data flow is not valid without the optional object, errors can occur when the task runs.
To configure an object as optional, on the Properties page of the object details dialog box, set the Optional property to True.

Rules and guidelines for configuring a Visio template

Use the following general rules and guidelines for configuring a Visio template:

Tips for working with the Cloud Integration Template Designer

Use the following tips when working with the Cloud Integration Template Designer:
The following Mapping Architect for Visio functionality is not relevant to creating Visio templates for Data Integration:

Template parameter rules and guidelines

Use the following rules and guidelines when configuring template parameters in a template:

Tips for PowerCenter mapping XML templates

Use the following tips for Visio templates created from PowerCenter mapping XML files:

Tips for Data Integration workflow XML templates

Use the following tips for Visio templates created from Data Integration workflow XML files: