Automating WF Execution

sahilp
New Contributor II

Hi all,

I have connector rule set up to an external source table that is working fine to pull data into stage, but I would like to automate the execution of the workflow as well so that the data in the cube is refreshed nightly.  I found the below api but it looks to me like it's meant to load a flat file into stage via an extensibility rule, not from an external table. I'm not sure how to connect it to the connector/import step I want to automate. Any thoughts?

 

Dim WFTime As String = TimeDimHelper.GetNameFromId(api.WorkflowUnitPk.TimeKey)
Dim results As WorkflowBatchFileCollection = BRApi.Utilities.ExecuteFileHarvestBatch(si, "Actual", WFTime, True, True, True, True, False, False, False)

2 ACCEPTED SOLUTIONS

Cosimo
Contributor II

It is certainly possible to automate the import/validate/load of data from an external source. Here's the high level steps for setup:

1) Setup a Data Management job that will execute an Extender Business Rule. You will need to pass a few parameters to the business rule such as the Workflow you want to process and period.

2) In the extender business rule, you will call the following functions:

BRApi.Import.Process.ExecuteParseAndTransform <-- executes the workflow import. 

BRApi.Import.Process.ValidateTransformation <-- executes the mapping validation step

BRApi.Import.Process.ValidateIntersections <-- executes the validate intersection step

BRApi.Import.Process.LoadCube <-- loads to cube

BRApi.DataQuality.Process.ExecuteProcessCube <-- run workflow calculation if your workflow has this enabled.

 

Here is how I've got my ExecuteParseAndTransform function setup on my end:

Dim ImportInfo As LoadTransformProcessInfo = BRApi.Import.Process.ExecuteParseAndTransform(si, wFClusterPk, "", Nothing, TransformLoadMethodTypes.Replace, SourceDataOriginTypes.FromDirectConnection, False)

 

You will need to determine the wfClusterPk based on the parameters you've provided from the data management step. In the Connector rule that is being used in the Data Source, it will also need to know what workflow profile, scenario and period you are trying to process. 

The result of the import (successful or failed) is stored in the ImportInfo variable. We then check to see if the import was successful and without errors before proceeding to the validation steps:

    If ImportInfo.Status = WorkflowStatusTypes.Completed Then

 

Hopefully this provides the gist on how to get started down the import automation! 

 

 

View solution in original post

Cosimo
Contributor II

At first, we used the Global Time period to determine the period to process but then switched to using the System time. My scheduled tasks are setup with the following parameters:

Cosimo_0-1663591527275.png

 

The daysBefore parameter indicates the # of days prior to a new month that we can start the automation while daysAfter indicates the # of days after start of month that we run the process. excludeWeekend true/false tells the business rule if we should run the process over weekends.

 

The Extender rule then figures out what workflow period to run based on the current date and if the date falls between daysBefore >= current date <= daysAfter. The daysBefore/daysAfter is optional for determining the workflow period. if you're starting imports on the first day of every month, then the workflow period can readily be determined as:

Dim curDate as Date = Date.Now

Dim wfPeriod as string = curDate.Year & "M" & curDate.Month

 

Note that I write the wfPeriod to a dashboard parameter which is the read from within the Connector rule so as to run the SQL on the current wfPeriod. 

 

Hope this helps!

 

View solution in original post

4 REPLIES 4

Cosimo
Contributor II

It is certainly possible to automate the import/validate/load of data from an external source. Here's the high level steps for setup:

1) Setup a Data Management job that will execute an Extender Business Rule. You will need to pass a few parameters to the business rule such as the Workflow you want to process and period.

2) In the extender business rule, you will call the following functions:

BRApi.Import.Process.ExecuteParseAndTransform <-- executes the workflow import. 

BRApi.Import.Process.ValidateTransformation <-- executes the mapping validation step

BRApi.Import.Process.ValidateIntersections <-- executes the validate intersection step

BRApi.Import.Process.LoadCube <-- loads to cube

BRApi.DataQuality.Process.ExecuteProcessCube <-- run workflow calculation if your workflow has this enabled.

 

Here is how I've got my ExecuteParseAndTransform function setup on my end:

Dim ImportInfo As LoadTransformProcessInfo = BRApi.Import.Process.ExecuteParseAndTransform(si, wFClusterPk, "", Nothing, TransformLoadMethodTypes.Replace, SourceDataOriginTypes.FromDirectConnection, False)

 

You will need to determine the wfClusterPk based on the parameters you've provided from the data management step. In the Connector rule that is being used in the Data Source, it will also need to know what workflow profile, scenario and period you are trying to process. 

The result of the import (successful or failed) is stored in the ImportInfo variable. We then check to see if the import was successful and without errors before proceeding to the validation steps:

    If ImportInfo.Status = WorkflowStatusTypes.Completed Then

 

Hopefully this provides the gist on how to get started down the import automation! 

 

 

sahilp
New Contributor II

Cosimo, thanks that worked perfectly!

sahilp
New Contributor II

Quick follow up, is there a good way to automate the period selection? Or will someone have to update the time parameter each month?

Cosimo
Contributor II

At first, we used the Global Time period to determine the period to process but then switched to using the System time. My scheduled tasks are setup with the following parameters:

Cosimo_0-1663591527275.png

 

The daysBefore parameter indicates the # of days prior to a new month that we can start the automation while daysAfter indicates the # of days after start of month that we run the process. excludeWeekend true/false tells the business rule if we should run the process over weekends.

 

The Extender rule then figures out what workflow period to run based on the current date and if the date falls between daysBefore >= current date <= daysAfter. The daysBefore/daysAfter is optional for determining the workflow period. if you're starting imports on the first day of every month, then the workflow period can readily be determined as:

Dim curDate as Date = Date.Now

Dim wfPeriod as string = curDate.Year & "M" & curDate.Month

 

Note that I write the wfPeriod to a dashboard parameter which is the read from within the Connector rule so as to run the SQL on the current wfPeriod. 

 

Hope this helps!