Automation of workflow using connector BR

royari
Contributor

 

We are loading data using SQL connector business rule and it works fine manually. Now looking for a way to load it automatically using a extender business rule and then calling from DM step. All documentation in design ref guide point to using the following code. This works when loading from a file ( with specific file format) and the file needs to be in harvest folder. I can also make it work by keeping a file which is blank in the harvest folder and still executing the workflow using the connector BR. But is there a better method which does not involve a file.

 

Dim batchInfo As WorkflowBatchFileCollection = BRAPi.Utilities.ExecuteFileHarvestBatch(si, scenario, timeperiod, valTransform, valIntersect, loadCube, processCube, confirm, autoCertify, False)

 

1 ACCEPTED SOLUTION

TonyToniTone
Contributor II

If I understand this correspondence, you want to automate the data load of a Workflow that is bringing in data through a Data Connector.  If that is the case, yes, you can automate this process.  

1.  Create an Extensibility Rules Business Rule.  You will probably want to create the logic under Unknown and ExecuteDataMgmtBusinessRuleStep.  This is so the logic executes manually and during the execution of a Data Managment Step.  For example:

Select Case args.FunctionType
Case Is = ExtenderFunctionType.Unknown, ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep

The BR would use a trigger file with the naming convention for Workflow Name, Scenario, and Time to identify where to load data to.  Set the processing switches ( Import, Transform, Load Cube, Process, Confirm etc ) to perform as part of the data loading process.  Then execute the batch

2.  Create a Data Management Sequence and an Execute Business Rule Data Management Step and assign the Extensibility Business Rule to the Data Management Step.  There is an example of this setup in the Golfstream Reference app.  It is under Data Management called Batch File Loading

TonyToniTone_0-1651520016416.png

3.  To automate, setup a New Task in Task Scheduler.  This is under Application > Tools > Task Scheduler.  This is where you can automate how often you want this process to execute.  

I believe Task Schedule was a new feature starting in 6.4 or 6.5 so you will need to be on, at the minimum, one of those versions.  If not, you can use PowerShell to execute the automation.  

Hope this answer addresses your question and points you in the right direction.  

 

View solution in original post

13 REPLIES 13

Mustafa_A
Contributor II

After you define your Workflow and Current Period. Try using (BrAPI.Import.Process,.....) You should be able to define it to use your SQL data connector. 

brapi.Import.Process.ExecuteParseAndTransform(si, wfClusterpk, Nothing, Nothing, TransformLoadMethodTypes.Replace,sourcedataorigintypes.FromDirectConnection,False)

Repeat this syntax for each step (Process, Validate, Retransform, Load and Process, Certify etc. )  Depends on what you WF looks like. 

=we want light out automation. Having code on connector how will it help that. There is no way a connector BR runs from DM. ( unless i dont know of). The only way the connector runs , is when an user clicks on the import button. 

TonyToniTone
Contributor II

If I understand this correspondence, you want to automate the data load of a Workflow that is bringing in data through a Data Connector.  If that is the case, yes, you can automate this process.  

1.  Create an Extensibility Rules Business Rule.  You will probably want to create the logic under Unknown and ExecuteDataMgmtBusinessRuleStep.  This is so the logic executes manually and during the execution of a Data Managment Step.  For example:

Select Case args.FunctionType
Case Is = ExtenderFunctionType.Unknown, ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep

The BR would use a trigger file with the naming convention for Workflow Name, Scenario, and Time to identify where to load data to.  Set the processing switches ( Import, Transform, Load Cube, Process, Confirm etc ) to perform as part of the data loading process.  Then execute the batch

2.  Create a Data Management Sequence and an Execute Business Rule Data Management Step and assign the Extensibility Business Rule to the Data Management Step.  There is an example of this setup in the Golfstream Reference app.  It is under Data Management called Batch File Loading

TonyToniTone_0-1651520016416.png

3.  To automate, setup a New Task in Task Scheduler.  This is under Application > Tools > Task Scheduler.  This is where you can automate how often you want this process to execute.  

I believe Task Schedule was a new feature starting in 6.4 or 6.5 so you will need to be on, at the minimum, one of those versions.  If not, you can use PowerShell to execute the automation.  

Hope this answer addresses your question and points you in the right direction.  

 

Hello! 

Our import automation is set up as mentioned above but it does reference a parameter ("LoadMonth") which is manually maintained by the admins. So while it does work as a semi-"lights out" automation, it still does require a monthly update to change the period that's automatically loading. 

Hi Nicole,

You could try using a literal parameter with an Dashboard XFBR string to dynamically retrieve the parameter value based on some criteria.

With kind regards,

Tim Vierhout

Hi Tim, 

I could, but there's no criteria right now that's consistent with the timing of the load month changes. Thanks though.

JoakimK
New Contributor III

Nicole, often we see the use of the Global POV in these use cases. If its in use for the normal reporting, it can also be leveraged for the parameter for automation scripts.

Hi, 

It is in use and drives other tasks which do not coincide with the load month change. 

JoakimK
New Contributor III

Nicole,

 

I understand. However, as you mentioned there is no criteria that is consistent with the timing you are looking for I would say it would be near impossible to have this be part of a complete lights out approach. If a criteria can be used then this can be programmatically defined for sure, but as long as you do not have that, the only approach would be to have a manually updated parameter which you already do.

Hello!

This ticket wasn't created by me and I'm not looking for a lights out approach 🙂 

JoakimK
New Contributor III

Right you are! Apologies 😁

mansi
New Contributor

This can be done using below Api

BRApi.Import.Process.ExecuteParseAndTransform(si, wfClusterPk, String.Empty, Nothing, TransformLoadMethodTypes.Replace, SourceDataOriginTypes.FromDirectConnection, True)
Dim ValidateTrInfo As ValidationTransformationProcessInfo = BRApi.Import.Process.ValidateTransformation(si, wfClusterPk, True)
Dim ValidateIntInfo As ValidateIntersectionProcessInfo =BRApi.Import.Process.ValidateIntersections(si, wfClusterPk, True)
Dim LoadInfo As LoadCubeProcessInfo = BRApi.Import.Process.LoadCube(si, wfClusterPk)

BSK
New Contributor III

Hi Mansi,

I'm new to business rules, If possible, could you please post the complete code starting from "Namespace" to the "End Namespace". many thanks.