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