Multi Period and Multi Year data Load Automation

vmanojrc30
New Contributor III

With SQL Connector as Data Source, can we automate to Import, Validate and Load multi period and multiyear data (Ex: Jan 2023 till Jan 2026) in such a way the Import, Validate and Load occurs one period after the other sequentially?

Query execution times are much lesser when pulling one period at a time Vs pulling ALL 36 periods together, so I want to explore this option.

 

 

 

2 ACCEPTED SOLUTIONS

NicolasArgente
Valued Contributor

Yes totally possible.
You would need obviously to customize all this. First you would need a function that list all the WF Steps that are needed to run (see below GetWFSteps). Once you have this you need to loop to each of them in the order by month right?

 

 

		Private Function GetWFSteps(ByVal si As SessionInfo, ByVal scenarioName As String, ByVal timeName As String, ByVal WFStep As String) As List(Of WorkflowUnitClusterPk)
			Try
			Dim wfClusterPks As New List(Of WorkflowUnitClusterPk)
			'Define the SQL Statement
			Dim sql As New Text.StringBuilder     
			sql.Append("Select Distinct ProfileName ")
			sql.Append("From ")           
			sql.Append("WorkflowProfileHierarchy ")
			sql.Append("Where (")
			sql.Append("ProfileName LIKE '%" & WFStep & "%'" )
			sql.Append(")")
			sql.Append("Order By ")
			sql.Append("ProfileName ")

			'Create the list of WorkflowUnitClusterPks
			Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
				Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString, False) 
					For Each dr As DataRow In dt.rows
						Dim year As String = left(timeName,4)
						Dim wfClusterPk As WorkflowUnitClusterPk = BRApi.Workflow.General.GetWorkflowUnitClusterPk(si, dr("ProfileName"), scenarioName, timeName)
						If Not wfClusterPk Is Nothing Then
							wfClusterPks.Add(wfClusterPk)
						End If
					Next 
				End Using
			End Using

			Return wfClusterPks

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try  
End Function

 

 

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

View solution in original post

SamRichards
Contributor

Hey Vmanojrc,

This possible, I would do this via an extender rule using the "BRApi.Utilities.ExecuteFileHarvestBatch" call.

You would Need to put the files out on the harvest folder to consume (This could be done manually or automated via a rule as well). If it is a one-time thing the I would just do it mannually, the format is specified in the reference guide. Then you would fire the brapi call and the file name that you put in the harvest folder would tell OS what workflow to load. The file you create would just need to have a character in it but when OS reads the file it would really only look at the name to tell it the workflow because based on what you said it has a connector on that workflow. Then OS would fire the connector for the given month and load the data into OS.

Thanks,

Sam

View solution in original post

2 REPLIES 2

NicolasArgente
Valued Contributor

Yes totally possible.
You would need obviously to customize all this. First you would need a function that list all the WF Steps that are needed to run (see below GetWFSteps). Once you have this you need to loop to each of them in the order by month right?

 

 

		Private Function GetWFSteps(ByVal si As SessionInfo, ByVal scenarioName As String, ByVal timeName As String, ByVal WFStep As String) As List(Of WorkflowUnitClusterPk)
			Try
			Dim wfClusterPks As New List(Of WorkflowUnitClusterPk)
			'Define the SQL Statement
			Dim sql As New Text.StringBuilder     
			sql.Append("Select Distinct ProfileName ")
			sql.Append("From ")           
			sql.Append("WorkflowProfileHierarchy ")
			sql.Append("Where (")
			sql.Append("ProfileName LIKE '%" & WFStep & "%'" )
			sql.Append(")")
			sql.Append("Order By ")
			sql.Append("ProfileName ")

			'Create the list of WorkflowUnitClusterPks
			Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
				Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString, False) 
					For Each dr As DataRow In dt.rows
						Dim year As String = left(timeName,4)
						Dim wfClusterPk As WorkflowUnitClusterPk = BRApi.Workflow.General.GetWorkflowUnitClusterPk(si, dr("ProfileName"), scenarioName, timeName)
						If Not wfClusterPk Is Nothing Then
							wfClusterPks.Add(wfClusterPk)
						End If
					Next 
				End Using
			End Using

			Return wfClusterPks

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try  
End Function

 

 

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

SamRichards
Contributor

Hey Vmanojrc,

This possible, I would do this via an extender rule using the "BRApi.Utilities.ExecuteFileHarvestBatch" call.

You would Need to put the files out on the harvest folder to consume (This could be done manually or automated via a rule as well). If it is a one-time thing the I would just do it mannually, the format is specified in the reference guide. Then you would fire the brapi call and the file name that you put in the harvest folder would tell OS what workflow to load. The file you create would just need to have a character in it but when OS reads the file it would really only look at the name to tell it the workflow because based on what you said it has a connector on that workflow. Then OS would fire the connector for the given month and load the data into OS.

Thanks,

Sam