Work with variables

You can define and use variables in your project as values that configure the processes and mappings behavior.

Understand variables

There are three main types variables:

Metadata variables, defined in Variable Set metadata in the project. You can modify these variables as the process runs, store and refresh their value from database storage.
Substitution variables that expose environmental information, such as the Designer or workspace folder.
Session variables that expose process and action information in sessions.

Metadata variables

Metadata variables are used in projects to parameterize metadata, mappings, and processes. They are declared as project metadata and support storage and retrieval from database storage.

The specific Variable Manager action allows managing the metadata variables in processes.

Create a variable set

Variables are created in metadata having the Variable Set type. You must create a variable set metadata before defining variables.

To create a new variable set:

Click the New Metadata button in the Project Explorer toolbar. The New Model wizard opens.
Select the Variable Set type.
Select a folder to store the metadata, and name the variable set with the Metadata Model Name.
Click Finish.
The variable set is created and the corresponding metadata editor opens.
In the Standard section of the set editor, set the variable set Name. The default variable set name is Set You can optionally rename the set.
Press Ctrl+S to save the variable set.

Create variables

To add a variable to a variable set:

In the variable set metadata editor, right-click the Set node and select New > Variable.
The new variable is added to the set.
In the Standard section of the set editor, set the base variable properties:
- Enter the Name of the variable. Make sure to use a naming convention for your variables.
- Select a Type for the Variable: String (default), Integer, Boolean or Float.
- Enter a Default Value for the variable. If this value is defined as an expression, select the XPath Expression option.
To store and refresh the variable from a database connection, set the following properties.
- Refresh Query: Used if a Refresh Connection is defined. This query will be executed to retrieve a value for the Variable. In case of a query returning multiple rows or multiple columns (or both), the first column of the first row is used as the value
Press Ctrl+S to save the variable set.

Configure variables storage

You can retrieve or persist variable values in a storage location, for example, in a table in a database server.

This storage for variables is configured as Connections in the variable set metadata. You can link database metadata to connections:

Refresh variable values using a SQL query against the database.
Persist and retrieve variable values from a table stored in the database.

Create connections

To create a connection:

In the variable set metadata editor, right-click the Set node and select New > Connection.
The connection is added to the set.
In the Standard section of the set editor, set a Name for the connection.
In the Project Explorer, expand the metadata to show the artifact that will store the variable (for example, a database server)
Drag and drop that artifact from the Project Explorer view to the metadata editor, under the connection.
A metadata link is created under the connection, named after the metadata artifact.
Press Ctrl+S to save the variable set.

You can use different types of metadata artifacts in the connection. For example, a database server, a schema, a table, or even a column.

You can define multiple connections in the Variable Set.

Once the connections are created, you can configure the set and the variables to use these connections, to save or refresh variables.

Configure the set

On the Set, in the Standard section of the editor, define a Default Saving Connection and a Default Refresh Connection. These are used by all variables by default.

Configure variables

On each variable, you can also define connections the method used to refresh or store the variable.

To configure a variable for database storage:

In the variable set metadata editor, expand the set and then click the variable.
In the Standard section of the set editor, set the following properties to configure database storage:
- Saving Connection: The connection used to save the variable values. If unset, the default saving connection configured for the set is used.
- Refresh Connection: Connection used by the Refresh Query. If unset, the default refresh connection configured for the set is used.
- Refresh Query: This query is executed to retrieve a value for the variable. If the query returns multiple rows or columns, the first column of the first row is used.
- Default Operation: Operation used by default for this variable when invoking the Variable Manager.
Press Ctrl+S to save the variable set.

Write the Refresh Query

You can use the metadata artifact added under the connection to help write the refresh query.

Since it is a metadata link, you can use XPath and metadata functions between curly brackets to access the properties of this artifact.

For example:

For a schema artifact, get the schema name using:
```
{ ./tech:physicalName() }
```
For a schema artifact, get the qualified name of a table using:
```
{ ./md:objectPath(.,'MYTABLE') }
```
For table artifact, get the table qualified name using:
```
{ ./tech:physicalPath() }
```

Use metadata variables

You can use metadata variables at multiple locations in Semarchy xDI, as explained in the following sections.

Use variables in mappings

To use a variable in a mapping:

Open the mapping that you want to edit.
In the Project Explorer expand the variable set metadata to see the variable that you want to use.
Drag and drop the variable from the Project Explorer view to the Mapping editor.
The variable is added to the mapping as a new source.

Drag and drop the variable from the mapping editor diagram to the target column, join, or the filter into which you want to use the variable.
In the Expression Editor, the variable is added with the following syntax, where VARIABLE_NAME is the name of the variable.

%{VARIABLE_NAME}%

To retrieve or set variable value and use it a Mapping, instantiate the variable in the process as explained below with a SetValue or RefreshValue operation, then add the mapping to the process.

Manage variables in processes

You can use the Variable Manager action in a process to instantiate, set, refresh or save a variable.

To use a variable in a process:

Open the process that you want to edit.
In the Project Explorer expand the variable set metadata to see the variable that you want to use.
Drag and drop the variable from the Project Explorer view to the Process editor.
This instantiates a Variable Manager action linked to that variable. The variable predefined properties are automatically set for this action when the process is generated.
Modify this action’s parameters in the Properties view to perform a specific variable action (Var Operation property), such as:
- SetValue: Sets the variable value.
- SetDefaultValue: Sets the variable to its default value.
- RefreshValue: Refreshes the value with the Refresh Query.
- IncrementValue: Increments the variable value.
- SaveCurrentValue: Persist the current value of the variable.

Use variables in expressions

To use a variable in an expression, for example in action text, code, or parameter, instantiate the variable in the process, and use the %{VARIABLE_NAME}% syntax, where VARIABLE_NAME is the name of the variable.

Use variables in variables

You can use a variable to configure another variable in a variable set.

If the two variables are defined in the same Variable Set metadata, use the variable with the %{VARIABLE_NAME}% syntax, where VARIABLE_NAME is the name of the variable. This syntax can be used in the refresh query or default value properties.

If the two variables are defined in different Variable Sets, you must link the two metadata:

Open the destination variable set metadata (the one that will use the other variable).
In the Project Explorer expand the variable set metadata to see the variable that you want to use.
Drag and drop the variable from the Project Explorer to the set node in the destination metadata editor.
Use the variable with the %{VARIABLE_NAME}% syntax, where VARIABLE_NAME is the name of the variable. This syntax can be used in the refresh query or default value properties.

Use variables in metadata

To use a variable in the properties of a Metadata (for example, to use Variable in a Table condition), you must link the two metadata:

Open the destination metadata (the one that will use the variable).
In the Project Explorer expand the variable set metadata to see the variable that you want to use.
Drag and drop the variable from the Project Explorer to the destination metadata editor.
Use the variable with the %{VARIABLE_NAME}% syntax in properties of the destination metadata, where VARIABLE_NAME is the name of the variable.

Substitution variables

Substitution variables expose environment information, such as the Designer or workspace folder, and can be used in expressions. These variables may be your own, or may be predefined by xDI Designer.

Substitution variables are local. They contain workspace information that may be useful in certain cases.
Substitution variables are evaluated at generation time, or when using Metadata wizards.
Substitution variables cannot be externalized. Their values are inserted as-is in generated deliveries.

Substitution variables are not understood by external tools. Do not use them if you build your project through command line tools.

Use substitution variables in expressions

You can use a String Substitution variable with the following syntax, where variable_name is the name of the variable:

%{env:variable_name}%

For example, the following syntax retrieves the project name:

Example 1. Retrieve the project name

%{env:project_name}%

Create substitution variables

You can define your own substitution variables in the Designer preferences, in the String Substitution section.

Predefined substitution variables

These are a subset of substitution variables available by default in xDI Designer.

Variable Name Description

Variable Name	Description
`eclipse_home`	Absolute path of the Designer installation folder.
`workspace_loc`	Absolute path of current workspace folder.
`project_name`	Name of the Project from which the value is generated.
`project_loc`	Absolute path of the Project from which the value is generated.
`resource_name`	Name of the resource from which the value is generated, which can be for instance the name of the Process in which the value is generated.
`resource_loc`	Absolute path of the resource from which the value is generated, which can be for instance the path of the Process in which the value is generated.

eclipse_home

Absolute path of the Designer installation folder.

workspace_loc

Absolute path of current workspace folder.

project_name

Name of the Project from which the value is generated.

project_loc

Absolute path of the Project from which the value is generated.

resource_name

Name of the resource from which the value is generated, which can be for instance the name of the Process in which the value is generated.

resource_loc

Absolute path of the resource from which the value is generated, which can be for instance the path of the Process in which the value is generated.

Session variables

Semarchy xDI stores information about each action and process in session variables. You can use these variables in an action’s code or text, in its parameters, or its metadata. You can also use them in scripts, such as in conditions.

A session variable is defined in an action or process. A process may hold further nested processes or actions, similar to paths in a filesystem, with the main process as the root element.

For example, consider the following process:

LOAD_DW (process)
    -> LOAD_DIMENSIONS (sub-process)
        -> LOAD_TIME (sub-process)
            -> WRITE_FILE (action)
            -> READ_FILE (action)
            ...
    -> LOAD_CUSTOMERS (sub-process)
        ...
    -> LOAD_FACTS (sub-process)
    ...

The LOAD_DW process has a sub-process named LOAD_DIMENSIONS. LOAD_DIMENSIONS also holds the sub-process LOAD_TIME, which itself includes actions named WRITE_FILE and READ_FILE.

Use session variables in actions and scripts

To use a session variable, use the syntax ${variable_path}$ , where variable_path is the absolute or relative path to the relevant variable.

An absolute variable path always starts at the top-level process. It includes every sub-process or action, separated by the / forward slash, until reaching the desired variable name like so:

${TOP_LEVEL_PROCESS/SECOND_LEVEL_PROCESS/THIRD_LEVEL_ACTION/VARIABLE}$

A relative variable path depends on which process or actions uses the variable. You can use the characters .. to reference an object one level up, or . to reference the current object. You can also use ~ to return the full path to a reference if the name of the parent process is unknown.

${../SAME_LEVEL_ACTION/VARIABLE}$
${./VARIABLE}$
${~/THIRD_LEVEL_ACTION/VARIABLE}$

Examples

In the prior example, the absolute paths to the WRITE_FILE and READ_FILE actions are LOAD_DW/LOAD_DIMENSIONS/LOAD_TIME/WRITE_FILE and LOAD_DW/LOAD_DIMENSIONS/LOAD_TIME/READ_FILE.

You can access the actions' return code by using their own CORE_RET_CODE variable. The absolute path is as follows:

${LOAD_DW/LOAD_DIMENSIONS/LOAD_TIME/WRITE_FILE/CORE_RET_CODE}$
${LOAD_DW/LOAD_DIMENSIONS/LOAD_TIME/READ_FILE/CORE_RET_CODE}$

Suppose the READ_FILE action in the LOAD_TIME process needs to access the WRITE_FILE return code. As they are at the same level, you can reference the return code with the following relative path:

${../WRITE_FILE/CORE_RET_CODE}$

To use the current action’s return code in the action itself, you can use the . character:

${./CORE_RET_CODE}$

If you reference the LOAD_TIME process but do not know the name of the parent process, use the ~ syntax to return the full path:

${~/WRITE_FILE/CORE_RET_CODE}$

~ resolves to LOAD_DW/LOAD_DIMENSION/LOAD_TIME.