Consolidation Settings

[grillin7]

Is there a way to configure the system so that it will check if a consolidation is already running before allowing another user to start a consolidation?  If so, how do we configure the system to do this?

[IftaqarAhmed]

You could call a DataQualityEventHandler Business Rule to read the data management sequence in the calculation definitions page, followed by a consolidation within. The event handler business rule will evaluate the consolidation status before running a consolidation.

Here's an example of the evaluation code.

‘Evaluate the consolidation status.

If BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "ParamConsolFlagStatus").Contains("False")

Then

'Reset it to ‘True if No Consol is Running’

    BRApi.Dashboards.Parameters.SetLiteralParameterValue(si, False, " ParamConsolFlagStatus ","False")'"True")

Else

   brapi.ErrorLog.LogMessage(si," A job is currently being executed in the console. Kindly wait until the job is finished!")

Return Nothing

End If

[Henning · Accepted solution]

Hi, in case you want to check for a running consolidation before starting a new task executed from a dashboard button, you could try this Dashboard Extender rule. This reads the tasks from the task activity which you can adjust as needed.

Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Namespace OneStream.BusinessRule.DashboardExtender.TA_DashboardHelperQueries
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardExtenderArgs) As Object
			Try
				Select Case args.FunctionType
										
					Case Is = DashboardExtenderFunctionType.ComponentSelectionChanged
						If args.FunctionName.XFEqualsIgnoreCase("CheckTaskActivity") Then
							
							'Retrieve parameters passed to rule, possibly to be used to filter the current data unit, if needed
							Dim wfTimeParameter As String = args.NameValuePairs.XFGetValue("wfTimeParameter")
							Dim wfScenarioParameter As String = args.NameValuePairs.XFGetValue("wfScenarioParameter")
							Dim wfProfileParameter As String = args.NameValuePairs.XFGetValue("WFProfileParameter")
							Dim wfAppNameParameter As String = args.NameValuePairs.XFGetValue("wfAppNameParameter")
							
							'Define string to write to error log for further analysis of running / blocking tasks
							Dim errorLogMessage As New List(Of String)
							errorLogMessage.Add("The below tasks are running. Please wait for those to complete before executing your routine.")
							
							'Define SQL script to pull necessary information from TaskActivity table
							Dim sql As New Text.StringBuilder
							sql.AppendLine("SELECT top 10 TaskActivityType, 'Unknown' as TaskActivityTypeDesc, TaskActivityStatus, 'Unknown' as TaskActivityStatusDesc, Description, AppName, UserName ")
							sql.AppendLine("FROM [OneStream_Framework].dbo.[TaskActivity] ")
							
							'Pull rows from table for required task types and status
								'TaskActivityType 9000 = Calculate, 11000 = Consolidate
								'TaskActivityStatus 0 = Queued, 1000 = Running
							sql.AppendLine($"WHERE TaskActivityType = '11000' AND TaskActivityStatus = '1000' AND AppName = '{wfAppNameParameter}'")
							 

							'Using SQL syntax and ExecuteSqlUsingReader to get the Data Table
			                Dim dt As DataTable = Nothing
			                Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
			                    dt = BRApi.Database.ExecuteSqlUsingReader(dbConnApp, sql.ToString, False)
							End Using	
							
							Dim selectionChangedTaskResult As New XFSelectionChangedTaskResult()
							selectionChangedTaskResult.IsOK = True
							selectionChangedTaskResult.ShowMessageBox = True
							selectionChangedTaskResult.ChangeSelectionChangedUIActionInDashboard = False
							selectionChangedTaskResult.ModifiedSelectionChangedUIActionInfo = Nothing
							selectionChangedTaskResult.ChangeSelectionChangedNavigationInDashboard = False
							selectionChangedTaskResult.ModifiedSelectionChangedNavigationInfo = Nothing
							selectionChangedTaskResult.ChangeCustomSubstVarsInDashboard = False
							selectionChangedTaskResult.ModifiedCustomSubstVars = Nothing
							selectionChangedTaskResult.ChangeCustomSubstVarsInLaunchedDashboard = False
							selectionChangedTaskResult.ModifiedCustomSubstVarsForLaunchedDashboard = Nothing
						
							'Check if determined activity types are running and create message
'							If Not dt Is Nothing Then
							If Not dt.Rows.Count.XFToString.XFContainsIgnoreCase("0") Then
								selectionChangedTaskResult.Message = "Task is running, please check the task activity and wait for it to be completed."
								
								dt.Columns("TaskActivityTypeDesc").ReadOnly = False
								dt.Columns("TaskActivityTypeDesc").MaxLength = -1
								dt.Columns("TaskActivityStatusDesc").ReadOnly = False
								dt.Columns("TaskActivityStatusDesc").MaxLength = -1

								For Each dr As DataRow In dt.Rows

									dr.BeginEdit
										dr("TaskActivityTypeDesc") = TaskActivityType.GetName(GetType(TaskActivityType), dr("TaskActivityType"))
										dr("TaskActivityStatusDesc") = TaskActivityStatus.GetName(GetType(TaskActivityStatus), dr("TaskActivityStatus"))
									dr.EndEdit
									
									errorLogMessage.Add("Process: " & dr("TaskActivityTypeDesc").ToString & " - Status: " & dr("TaskActivityStatusDesc").ToString & " - Data Unit: " & dr("Description").ToString & " - User: " & dr("UserName").ToString)
								Next
								
								'Write running tasks into error log							
								brapi.ErrorLog.LogMessage(si,"Running Tasks: See details below.", vbCrLf & String.Join(vbCrLf, errorLogMessage.ToArray))
								
							Else
								selectionChangedTaskResult.Message = "Task successfully completed."
								'...run DM job or whatever is needed when no other process is blocking
								'...execute logic from here!
								
							End If
														
							Return selectionChangedTaskResult
							
						End If
					
				End Select

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

Please note the following things:

- I limited the check to the top 10 rows in the task activity table for testing purposes. 

- This rule needs to be executed from a dashboard that is attached to a workflow (Workspace). Otherwise, the WF parameters will be blank and the rule will not work as I set it up.

- Not all of those parameters are in use at the moment in the rule, but have been left in as an example to use to e.g. filter on the correct data unit in the task activity to be consolidated as I assume one wants to narrow it down and not block the process for ANY consolidation run (which is possible as well, of course).

- Only "Running" consolidations are detected as per task activity type '11000'. Queued and others may be added as needed. You can find task activity types in this post: Solved: Task Activity Type - OneStream Community (onestreamsoftware.com)

If running consolidations are blocking the process, the users can check the list of each running process in the error log as follows:

[grillin7]

Hello

It's working.

thank you a lot