There are 3 kinds of variables available to pipelines: system variables, pipeline variables and mart variables.
System Variables
In your pipeline’s XML, a fixed list of special system variables are available:
${MART_ID}
: the mart Sys_ID (GUID)${BATCH_ID}
: the current Batch ID${ORIGIN_CODE}
: the current origin code${LAST_BATCH_SUCCESS_TIME}
new: the timestamp of the last extraction from source data which resulted in a successful batch for this origin. Evaluates to ISO format time(YYYY-MM-ddThh:mm:ss, fallback to ‘1970-01-01T00:00:00’ if no previous batch). Useful to sync only data modified since the last successful batch. Can be used in a web service filter like<GetOData ... Filter="UpdatedDateUtc gt DateTime'${LAST_BATCH_SUCCESS_TIME}'"
${DateTime.UtcNow.ToString('any valid date format')}
: the current UTC datetime printed with the user provided .NET date format. See the official documentation for more examples.${Yesterday.DateUtc}
: the date ISO (YYYY-MM-dd) of the day before${Now.DateUtc}
: the date ISO (YYYY-MM-dd) of the current UTC day${Now.TimeUtc}
: the datetime ISO (YYYY-MM-ddThh:mm:ss) of the current UTC time${Now.DateServer}
: the date ISO (YYYY-MM-dd) of the current Geneva day${Now.TimeServer}
: the datetime ISO (YYYY-MM-ddThh:mm:ss) of the current Geneva time
System variables are dynamically replaced at batch runtime in the XML by their actual values. For example, this batch xml
<LoadTable SourceTable="interviews" TargetTable="RAW_2019_INTERVIEWS" LoadStrategy="REPLACE">
<DataScope Operator="AND">
<Terms>
<Term Operator="OR" Field="DATE_CREATION" Rule="GreaterOrEqualTo">
<Value>${DateTime.UtcNow.ToString('YYYY-MM')}</Value>
</Term>
<Term Operator="OR" Field="Sys_FirstBatchID" Rule="EqualTo">
<Value>${BATCH_ID}</Value>
</Term>
</Terms>
</DataScope>
<Tranform>
<FindReplace Find="XXX" ReplaceWith="${WHO_REGION}" /> <!-- user defined variable -->
will be transformed at runtime like so
<LoadTable SourceTable="interviews" TargetTable="RAW_2019_INTERVIEWS" LoadStrategy="REPLACE">
<DataScope Operator="AND">
<Terms>
<Term Operator="OR" Field="DATE_CREATION" Rule="GreaterOrEqualTo">
<Value>2021-03</Value>
</Term>
<Term Operator="OR" Field="Sys_FirstBatchID" Rule="EqualTo">
<Value>40454</Value>
</Term>
</Terms>
</DataScope>
<Tranform>
<FindReplace Find="XXX" ReplaceWith="EMRO" />
Pipeline Variables
Pipeline variables are user-defined variables inside a particular pipeline. They are defined in the <Context><Inputs>
section of a pipeline, which must appear right before <Extract>
like so
<XmartPipeline>
<Context>
<Inputs>
<!-- free text input -->
<Add Key="WHO_REGION" Type="text" />
<!-- generates a dropdown with defined values -->
<Add Key="EPI_YEAR" Type="select" Source="csvList" Values="2021,2022" Placeholder="Please select the year"/>
<!-- generates a dropdown populated with values from table -->
<Add Key="EPI_WEEK" Type="select" Source="martQuery" TableCode="REF_DATES" PropertyTitleCode="ISO_WEEK" PropertyValueCode="ISO_WEEK"/>
</Inputs>
<OriginValues>
<Origin Code="MY_ORIGIN_AFRO_21"> <!-- inputs values can be set for a particular origin -->
<Add Key="WHO_REGION" Value="AFRO" />
<Add Key="EPI_YEAR" Value="2021" />
</Origin>
</OriginValues>
</Context>
<Extract>
...
When starting a batch, the inputs defined without an OriginValue for the select origin will be prompted to the user as defined above.
Mart Variables
Mart variables are user-defined variables that can be used in multiple pipelines and also when defining connections. They are defined on the Pipelines/Variables page:
Add a new Mart variable
When the user adds a new variable by clicking on the New Variable button, the following screen is displayed
Variable names must be unique in the mart but the same variable name can be used in different marts
If a variable is marked as Secret then
• Value will then be redacted from the generated batch XML in the batch preview page
• Value will not be displayed in the variables page.
• No one will be able to see a secret’s value
• Only mart admins can change it.
Once the variable has been created, it will be shown on the main screen. If it is marked as Secret then the value will be hidden.
Use a Mart variable in a pipeline
To use to a mart variable in a pipeline, use the following syntax: ${mart.VARIABLE_KEY}
An example of this can be seen below in the Headers section of the GetWebService command
Use a Mart variable in Web Service Connections
Mart variables can also be used in WebService connections to hide client secrets and passwords. To refer to a variable in a pipeline, use the following syntax: ${mart.VARIABLE_KEY}
An example of this can be seen below
User Variables
User variables are also user-defined variables inside a particular pipeline. Such user variables are the ones set in the user properties and can be called in a pipeline like this: ${user.VariableName}
Here is an example of some user variables set for a user
And below is an example on how to use a user variable. In this scenario we are using the user variable LAB_CODE to populate a pipeline variable, like we saw in the beginning of the article. Inside the
<XmartPipeline>
<Context>
<Inputs>
<Add Source="martQuery" TableCode="REF_AFP_EURO_LAB_LIST"
PropertyTitleCode="LAB_CODE" PropertyValueCode="LAB_CODE"
Key="LabName" Type="select"
Origins="EURO_SURV_AFP_LAB_SAMPLES_LAB,EURO_SURV_AFP_LAB_SAMPLES_RO"/>
</Inputs>
<OriginValues>
<Origin Code="EURO_SURV_AFP_LAB_SAMPLES_LAB">
<Add Key="LabName" Value="${user.LAB_CODE}"/>
</Origin>
</OriginValues>
</Context>
<Extract>
...
Another example, would be to use it in an
<AddColumn Name="Lab_CODE" FillWith="${user.LAB_CODE}" />
In general, as already mentioned you may call and use the user variable like this: ${user.VariableName}