Using Substitution Variables in Pipelines for Metadata Loads

Data Exchange Pipelines have now replaced the batch functionality previously available in Data Management, allowing administrators to have a more enhanced visual representation of scheduled jobs. Pipelines consist of a series of stages, which are made up of a range of jobs, including business rules, integrations or platform jobs such as database refreshes.

Substitution Variable Setup

In a previous blog post, I explained how substitution variables can be used in pipelines using format &DataLoad_Mnth#&DataLoad_Year where the variables are in the below format:

• DataLoad_Mnth – references the current month for loading actuals (e.g. Mar)
• DataLoad_Year – references the current financial year for loading actuals (e.g. FY26)

See my previous blog here for more detail: https://lydia-maksoud-epm.blogspot.com/2024/01/Substitution-Variables-Pipelines.html

This works great for data load integration jobs; however, I’ve recently found that this method does not work when attempting to reference substitution variables for a metadata integration.

After some digging, I found in the documentation that using &Month#&Year format is not supported for ‘Data Export to File’ target applications, which is technically the same method used for metadata integrations. This means we need to use a simpler format where the variable uses the calendar period for metadata integrations. See reference here: https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/diepm/integrations_pipeline_subvar.html

In this case, we need a specific calendar period format for the substitution variable as below: 

• DataLoad_Period – references the current calendar period (e.g. Mar-26)

 

Note! This variable definition should not include quotation marks.

We now have three substitution variables, two for data integrations (as explained in my previous blog referenced above) and one for metadata integrations, or other export to file integrations.

Pipeline Variable Setup

Once the new substitution variables are setup, we can then reference them in a new Pipeline variable. To view or edit the pipeline variables, open the pipeline and use the Edit Pipeline pencil.

Add the new variable for metadata loads, as below:

Variable Name	Display Name	Type	Value
META_PERIOD	Metadata Period	Text	&DataLoad_Period

Make sure to Save the changes.

Note! If the pipeline contains a mix of metadata and data loads, you may need to setup two pipeline variables as above, with different default value formats.

Job Parameters

Now that the META_PERIOD variable is setup in the Pipeline, we can reference it in the metadata integration jobs as the start and end period, as below:

The benefit of setting up pipeline periods with substitution variables, is that 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. For metadata loads, this means we can save the workbench for each period, to reference back to the metadata that was loaded each 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 adding a variable for the period, or 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