Using Substitution Variables in Pipelines

Pipelines allow administrators to schedule jobs in the required order, including pre-processing rules, data load rules and post-processing rules in an easy and accessible interface via Data Integration. This is replacing the existing Batch functionality which is currently available in Data Management, but will soon be decommissioned.

Pipeline Variables

For each pipeline, there are automatically a number of variables set with default values:

Variable Name	Description	Default Value (Example)
Start Period	The default start period for a data load rule	Jan-23
End Period	The default end period for a data load rule	Mar-23
Import Mode	The default import mode for a data load rule	Replace
Export Mode	The default export mode for a data load rule	Merge
Send Mail	Option for whether to send email notifications always, never or on success/failure only	Always
Send To	Email address to send email notifications to	Firstname.lastname@example.com
Attach Logs	Option for whether to attach logs to the email notifications	No

These variables can be referenced in the individual pipeline jobs or overwritten with a specific value at run time. Additional variables can also be added to each pipeline if required.

In this example, we will be adding custom variables which reference a month and year substitution variable to load data for the current month. First, we need to create the substitution variables, as below:

• Pipe_CurrMonth – references the current month for loading actuals (e.g. Jul)
• Pipe_CurrYear – references the current financial year for loading actuals (e.g. FY24)

We’ve used the prefix Pipe_ to make it clear that these sub vars are utilised in the pipeline loads. Then in the pipeline, we will add a new variable, in addition to the default variables above. To view or edit the pipeline variables, open the pipeline and use the Edit Pipeline pencil.

Add the new variable, as below:

Variable Name	Display Name	Type	Value
CURR_PERIOD	Current Period	Text	&Pipe_CurrMonth#&Pipe_CurrYear

Note: to reference variables, the variable value must be in format Month#Year, for example Jul#FY24.

Job Parameters

Each job has various parameter settings depending on the job type. These settings can reference Pipeline level variables, for example our new CURR_PERIOD variable which references sub vars, or use a specific setting for that particular job.

An integration job has the following parameters

• Name – name of the job
• Title – title of job
• Sequence – the order of the job within the stage, e.g. 1^st job of the stage
• Import Mode – the import mode, e.g. Replace or use $IMPORTMODE to use the pipeline default setting
• Export Mode – the export mode, e.g. Replace or use $EXPORTMODE to use the pipeline default setting
• Start Period – the start period, where we can use one of the following options:

1. Manually enter the month Jan-23
2. Use $STARTPERIOD to use the pipeline default setting
3. Or, we can now reference our new variable $CURR_PERIOD which is defined using our substitution variables

• End Period – the start period, where we can use one of the following options:

1. Manually enter the month Jan-23
2. Use $ENDPERIOD to use the pipeline default setting
3. Or, we can now reference our new variable $CURR_PERIOD which is defined using our substitution variables

Once the pipeline is setup referencing the substitution variables, they can be rolled forward like any other variable by editing the sub var in the Variables area. This will then reflect automatically in the pipeline definition to run the pipeline job for the new current month.

Scheduling a Pipeline

To schedule a pipeline, navigate to the Jobs console, add a new scheduled job and select the Pipeline job type. Once configured, the schedule pipeline will be displayed in the Pending Jobs list in the Jobs console.

Note: if updates are made to the Pipeline variables directly in the Pipeline definition, the Pipeline job must be deleted and re-scheduled in the Job Console to reflect the variable updates. This is not required for changes to Pipeline jobs, only the variables, for example a change of email address for notifications.

For more information on why to switch to pipelines, check out my blog here: https://brovanture.com/why-oracle-cloud-epm-pipelines-have-all-the-answers/