Batch Loads Freeze

[vmanojrc30]

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.ExecuteFileHarvestBatch to execute the data load one period at a time. Data Source is a connector.

The issue I have is every time when I run the batch one of the WF steps (Validate Transformation, Validate Intersection or Load Cube) freezes and make no progress after executing roughly 10-12 periods. It doesn't make progress even after an hour wait. I will have to cancel the running task and batch and then restart the data management job with the next period forward. Again, it runs fine for 10-12 periods and freezes again.

Appreciate any recommendations/suggestions to try resolve this issue. I am not sure if this related to any OS configuration that allows data loads for long time.

[NicolasArgente]

Please share your 2 BR.

[vmanojrc30]

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

[vmanojrc30]

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 Function

[JackLacava]

While 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).

[liyinan357]

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")

[vmanojrc30]

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.

[vmanojrc30]

I removed the piece of code related to sleep and its still behave the same way. Data loads freeze at 10th period. However when i run the data management job only for the Period 10 or Period 12 it completes successfully within 5-10 minutes. These periods have high volume of data in the range of 600-800K records.

It appears some memory related setting is preventing it to load sequentially beyond certain time when run via batch loads process.I tried direct import as well.But it freezes for long time and fails either at Validate Intersection or Load cube step.

[JackLacava]

If you're a Cloud customer, you should get in touch with Support.

If you're on-premises, I'd suggest looking in the Installation and Configuration Guide, particularly the troubleshooting chapter (which has a paragraph on " Long Running Server Process Hangs or Stops With Logging Errors"...). Beyond that, you can pull up your Application Server Config file and play around with variables (sql blocking timeouts, etc etc).