Forum Discussion

James321's avatar
James321
New Contributor
11 days ago
Solved

Running Validate and Load Steps for Multiple Years

Hi everyone,

We are uploading portions of our budgets in Excel templates across multiple years.  The data is imported across all the years in one shot, but the validate and load steps need to be run year by year if imported through the workflows we have set up.  Does anyone know if there is a way to execute the validate and load steps across multiple years through a rule?

I know we can use the batch harvest folders, but we're hoping to keep the workflow steps as close to those used in other processes as possible.

Thanks, 

James

Data Integration
Import
Workflow
  • matt5150's avatar
    matt5150
    5 days ago

    I've done something like that before where we would load a csv file and then push a button or run this rule to loop through the years. This is an example of Extensibility Rule.  I had commented out scenarioName initially which was set to a specific scenario and later replaced it so the user pushing the button's Scenario would be used since it was done in the workflow. Hope this gives some ideas how you can use it for your process.

     

    		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
			Try

'Loops through profile names and years to allow faster load for the growth components process
'Each year will need to add new forecast year to the timeNames ex. "2028", "2029"

Dim profileNames = {"GC_Fiscal_Default.GC_Revenue_Fiscal"}
'Dim scenarioName = "FCST_08_04"
Dim scenarioName  As String = ScenarioDimHelper.GetNameFromID(si, si.WorkflowClusterPk.ScenarioKey) ' ' this gets the scenario from the user running the rule's workflow
Dim timeNames = {"2021", "2022", "2023", "2024", "2025", "2026", "2027", "2028"} ' add years by putting 

For Each profileName In profileNames
	
	For Each timeName In timeNames

		Dim wfClusterPk As WorkflowUnitClusterPk = BRApi.Workflow.General.GetWorkflowUnitClusterPk(si, profileName, scenarioName, timeName)

		Dim objValidationTransformationProcessInfo As ValidationTransformationProcessInfo = BRApi.Import.Process.ValidateTransformation(si, wfClusterPk, True)

		Dim objValidateIntersectionProcessInfo As ValidateIntersectionProcessInfo = BRApi.Import.Process.ValidateIntersections(si, wfClusterPk, True)

		Dim objLoadCubeProcessInfo As LoadCubeProcessInfo = BRApi.Import.Process.LoadCube(si, wfClusterPk)

       Next
Next

	Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))

			End Try
		End Function

     

3 Replies

  • SimonHesford's avatar
    SimonHesford
    Contributor II
    6 days ago

    Hi,

    There is usually a way in OneStream🙂 If you think of how it can be done via the web UI that will give you a good starting point as you should be able to call the same functionality via the BRApi i.e.

     

  • matt5150's avatar
    matt5150
    New Contributor
    5 days ago

    I've done something like that before where we would load a csv file and then push a button or run this rule to loop through the years. This is an example of Extensibility Rule.  I had commented out scenarioName initially which was set to a specific scenario and later replaced it so the user pushing the button's Scenario would be used since it was done in the workflow. Hope this gives some ideas how you can use it for your process.

     

    		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
			Try

'Loops through profile names and years to allow faster load for the growth components process
'Each year will need to add new forecast year to the timeNames ex. "2028", "2029"

Dim profileNames = {"GC_Fiscal_Default.GC_Revenue_Fiscal"}
'Dim scenarioName = "FCST_08_04"
Dim scenarioName  As String = ScenarioDimHelper.GetNameFromID(si, si.WorkflowClusterPk.ScenarioKey) ' ' this gets the scenario from the user running the rule's workflow
Dim timeNames = {"2021", "2022", "2023", "2024", "2025", "2026", "2027", "2028"} ' add years by putting 

For Each profileName In profileNames
	
	For Each timeName In timeNames

		Dim wfClusterPk As WorkflowUnitClusterPk = BRApi.Workflow.General.GetWorkflowUnitClusterPk(si, profileName, scenarioName, timeName)

		Dim objValidationTransformationProcessInfo As ValidationTransformationProcessInfo = BRApi.Import.Process.ValidateTransformation(si, wfClusterPk, True)

		Dim objValidateIntersectionProcessInfo As ValidateIntersectionProcessInfo = BRApi.Import.Process.ValidateIntersections(si, wfClusterPk, True)

		Dim objLoadCubeProcessInfo As LoadCubeProcessInfo = BRApi.Import.Process.LoadCube(si, wfClusterPk)

       Next
Next

	Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))

			End Try
		End Function

     

  • James321's avatar
    James321
    New Contributor
    4 days ago

    Thanks very much to you both for the guidance - I've got this working now. 

    We have multiple import steps under the parent workflows, so I added a new section to loop through all import type workflows under specific parent workflow.   Adding my update below.  Thanks again!              

    Dim parentWfProfileInfo As WorkflowProfileInfo = BRApi.Workflow.Metadata.GetProfile(si, _
    "Your Parent Workflow here")
    Dim baseWfProfileInfo As New List (Of WorkflowProfileInfo)( _
        BRApi.Workflow.Metadata.GetRelatives(si, _
             BRApi.Workflow.General.GetWorkflowUnitClusterPk(si, _
                  parentWfProfileInfo.Name, ScenarioName, firstTime), _
             WorkflowProfileRelativeTypes.Descendants, _
             WorkflowProfileTypes.InputImportChild ))
     
    'Loops through all workflow profiles from list, and then through each period in the timenames list  
    For Each profile In baseWfProfileInfo
     
     
     
    For Each timeName In timeNames
     
     
    Dim wfClusterPk As WorkflowUnitClusterPk = BRApi.Workflow.General.GetWorkflowUnitClusterPk(si, profile.name, scenarioName, timeName)
     
    Dim objValidationTransformationProcessInfo As ValidationTransformationProcessInfo = BRApi.Import.Process.ValidateTransformation(si, wfClusterPk, True)
     
    Dim objValidateIntersectionProcessInfo As ValidateIntersectionProcessInfo = BRApi.Import.Process.ValidateIntersections(si, wfClusterPk, True)
     
    Dim objLoadCubeProcessInfo As LoadCubeProcessInfo = BRApi.Import.Process.LoadCube(si, wfClusterPk)
     
     
        Next
    Next