Batch Loads Freeze
I have a OS Batch File Load Setup which loads Multi Year and Multi Period Data(3 Years).I have an Extender Business Rule that creates the Batch File one period at a time and calls BRAPi.Utilities.Exe...
Forum Discussion
Please share your 2 BR.
Extender Rule:
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports System.Net.Mail
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database
Namespace OneStream.BusinessRule.Extender.RP_BDGT_CURRENT_HISTORY_LOAD
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
Select Case args.FunctionType
Case Is = ExtenderFunctionType.Unknown, ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
'Set global/workflow info
Dim swfName As String = "Revenue Planning"
Dim swfSub As String = ";RP_BDGT_CURRENT_HISTORY"
Dim swfProfile As String = swfName & swfSub
Dim swfScenario As String = "Budget"
'Set Parameters
Dim OSStartPeriod As String = args.NameValuePairs.XFGetValue("StartPeriod")
Dim OSEndPeriod As String = args.NameValuePairs.XFGetValue("EndPeriod")
'Define the Start and End Periods
Dim StartPeriodList As List (Of String) = StringHelper.SplitString(OSStartPeriod,"M")
Dim StartYear As Integer = StartPeriodList(0)
Dim StartMonth As Integer = StartPeriodList(1)
Dim EndPeriodList As List (Of String) = StringHelper.SplitString(OSEndPeriod,"M")
Dim EndYear As Integer = EndPeriodList(0)
Dim EndMonth As Integer = EndPeriodList(1)
Dim CalcStartPeriod As Integer = (StartYear * 100) + StartMonth
Dim CalcEndPeriod As Integer = (EndYear * 100) + EndMonth
Dim OSExecPeriod As String = OSStartPeriod
Dim CalcExecPeriod As Integer = CalcStartPeriod
While CalcExecPeriod <= CalcEndPeriod
'Set file name variables
Dim loadMethod As String = "R"
Dim strFilePath As String = "\\XXXX.corp.u-store-it.com\OneStreamShare\FileShare\Applications\XXXXX\Batch\Harvest\"
Dim strSuffix As String = "TriggerAuto-"
'Set Processing Switches
Dim valTransform As Boolean = True
Dim valIntersect As Boolean = True
Dim loadCube As Boolean = True
Dim processCube As Boolean = False
Dim confirm As Boolean = False
Dim autoCertify As Boolean = False
'----------------------------------------------------
'Create trigger file
Dim strFileName As String = strSuffix & swfProfile & "-" & swfScenario & "-" & OSExecPeriod & "-" & loadMethod
Dim fileTrigger1 As New StreamWriter(strFilePath & strFileName & ".txt")
fileTrigger1.Close()
'----------------------------------------------------
'Execute Data Load
Dim batchInfo As WorkflowBatchFileCollection = BRAPi.Utilities.ExecuteFileHarvestBatch(si, swfScenario, "2020M1" , valTransform, valIntersect, loadCube, processCube, confirm, autoCertify, True)
Dim fileTrigger2 As String = strFilePath & strFileName & ".txt"
While File.Exists(fileTrigger2)
System.Threading.Thread.Sleep(10000)
End While
'----------------------------------------------------
'Move to Next Period
Dim ExecPeriodList As List (Of String) = StringHelper.SplitString(OSExecPeriod,"M")
Dim ExecYear As Integer = ExecPeriodList(0)
Dim ExecMonth As Integer = ExecPeriodList(1)
If ExecMonth = 12 Then
ExecMonth = 1
ExecYear = ExecYear +1
Else
ExecMonth = ExecMonth +1
ExecYear = ExecYear
End If
OSExecPeriod = ExecYear & "M" & ExecMonth
Dim ExecPeriodList1 As List (Of String) = StringHelper.SplitString(OSExecPeriod,"M")
Dim ExecYear1 As Integer = ExecPeriodList1(0)
Dim ExecMonth1 As Integer = ExecPeriodList1(1)
CalcExecPeriod = (ExecYear1 * 100) + ExecMonth1
'End If
End While
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
Connector BR:
Private Function GetDBBudget_Import1SourceDataSQL(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As String Try 'Create the SQL Statement Dim statement As New Text.StringBuilder Dim wfScenario As String = api.CurrentDataClusterKey.ScenarioName Dim wfName As String = api.WorkflowProfile.Name Dim WFMonth As String = TimeDimHelper.GetSubComponentsFromId(api.WorkflowUnitPk.TimeKey).Month.ToString Dim WFYear As String = TimeDimHelper.GetYearFromId(api.WorkflowUnitPk.TimeKey).ToString Dim WFTime As String = WFYear & "M" & WFMonth 'Budget Model - Current and Historic Periods statement.Append("select Period , Facnumber , UnitGroup , Account , Amount from dbbudget.Test.BudgetModelInputs where Account in ('Rental' , 'Vacate' , 'BOMCustomers' , 'TotalUnits') and Facnumber in (select * from dbbudget.dbo.vw_GetActiveStores) And Period = '" & WFTime & "'") Return statement.ToString Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End FunctionWhile I was reindenting your code (next time, you might want to use the "Insert/edit code sample" option, that appears when you expand the list of editor icons by clicking on the three dots at the end), I noticed this line:
System.Threading.Thread.Sleep(10000)
i'd be willing to bet that's the source of your problems. Multithreading is hard in general, and when running in the context of an application with its own thread management (OneStream), that sort of call is likely to result in thread locks (because your code suspends execution, but maybe the app is waiting for your code to complete, so things never progress...). They typically end up manifesting in unpredictable lock-ups like the ones you're describing.
I would try removing that call. As a general approach, I would have a simple rule calling ExecuteFileHarvestBatch just once, set it up as a Data Management job that receives parameters, and then launch that job multiple times with different parameters from another rule, leaving the queueing and scheduling of those jobs to OneStream. I suspect it would end up being faster, since OS would parallelize things efficiently (unlike now, where you're doing it serially, one harvest after the other).
Hello,
I'm curious about the code below. Do you mind sharing where did you set up the key value pair so you could get the value from running the code below? Thank you
Dim OSStartPeriod As String = args.NameValuePairs.XFGetValue("StartPeriod") Dim OSEndPeriod As String = args.NameValuePairs.XFGetValue("EndPeriod")Hi
I set them up as a Data Management Step Parameter and call the BR from the DM Step.
Hope this helps. Let me know if you have any further questions.
