Forum Discussion

Manju's avatar
Manju
New Contributor II
2 months ago

Best Practice to Clear Stage Data Globally and Cube Data for Selected Entities

Hi Team,

 

I’m looking for guidance on the best approach to clear data in OneStream.

 

My requirement is twofold:

 

Clear all data from the Stage area

For a selected set of entities, clear both Stage and Cube data

 

I’m particularly interested in understanding the most efficient and recommended way to handle this, while ensuring data integrity and minimizing any unintended impact.

 

Specifically, I would appreciate insights on:

 

Recommended methods to clear Stage data globally (e.g., via Data Management steps, Business Rules, or utilities)

The best approach to selectively clear Cube data for specific entities

Any considerations around workflow locks, data unit locking, or audit/history implications

Performance best practices and recommended sequence of operations

 

If anyone has implemented a similar requirement, I’d love to hear your approach or any lessons learned.

 

Thanks in advance for your help!

Data Management
Import
Workflow

2 Replies

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    2 months ago

    I found a bit of code called the ATB_ToolboxHelper which has stuff on clearing a scenario. It might be a good starting point.

    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 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.DashboardExtender.ATB_ToolboxHelper
	Public Class MainClass
		'------------------------------------------------------------------------------------------------------------
		'Reference Code: 		ATB_ToolboxHelper 
		'
		'Description:			Various helper functions for system administrators.
		'
		'Usage:					Provides custom funtions for the Admin Toolbox Solution that can be called from parameter 
		'						components (Buttons, Combo boxes, List boxes, etc). 
		'
		'DeleteMaintUnit Function
		'	Parameter ProtoType:	{ATB_ToolboxHelper}{DeleteMaintUnit}{} 
		'	Parameter Example:		{ATB_ToolboxHelper}{DeleteMaintUnit}{}
		'	Description:			Deletes the entire maintenance unit stored in the parameter: MaintUnitList_ATB
		'
		'DeleteScenarioData Function
		'	Parameter ProtoType:	{ATB_ToolboxHelper}{DeleteScenarioData}{} 
		'	Parameter Example:		{ATB_ToolboxHelper}{DeleteScenarioData}{}
		'	Description:			Deletes all data nd audit history associated with a scenario so that the scenario can be deleted.
		'
		'Created By:			Tom Shea
		'Date Created:			10-03-2013
		'------------------------------------------------------------------------------------------------------------		
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardExtenderArgs) As Object
			Try
				If (args.FunctionName.Equals("DeleteMaintUnit", StringComparison.InvariantCultureIgnoreCase)) Then
					'Execute the copy layout
					Me.DeleteMaintUnit(si, globals, api, args)
					
					'Just return standard result					
					Dim selectionChangedTaskResult As New XFSelectionChangedTaskResult()
					selectionChangedTaskResult.IsOK = True
					selectionChangedTaskResult.ShowMessageBox = False
					selectionChangedTaskResult.Message = ""
					selectionChangedTaskResult.ChangeSelectionChangedUIActionInDashboard = False
					selectionChangedTaskResult.ModifiedSelectionChangedUIActionInfo = Nothing
					selectionChangedTaskResult.ChangeSelectionChangedNavigationInDashboard = False
					selectionChangedTaskResult.ModifiedSelectionChangedNavigationInfo = Nothing
					selectionChangedTaskResult.ChangeCustomSubstVarsInDashboard = False
					selectionChangedTaskResult.ModifiedCustomSubstVars = Nothing
					selectionChangedTaskResult.ChangeCustomSubstVarsInLaunchedDashboard = False
					selectionChangedTaskResult.ModifiedCustomSubstVarsForLaunchedDashboard = Nothing
					Return selectionChangedTaskResult

				ElseIf (args.FunctionName.Equals("DeleteScenarioData", StringComparison.InvariantCultureIgnoreCase)) Then
					'Execute the Delete of a Form Instance 	
					Dim scenarioName As String = Me.DeleteScenarioData(si, globals, api, args)
					
					'Just return standard result					
					Dim selectionChangedTaskResult As New XFSelectionChangedTaskResult()
					selectionChangedTaskResult.IsOK = True
					selectionChangedTaskResult.ShowMessageBox = True
					If String.IsNullOrEmpty(scenarioName) Then
						selectionChangedTaskResult.Message = "Scenario data was not deleted, make sure a Scenario is selected."
					Else
						selectionChangedTaskResult.Message = "Data for Scenario [" & scenarioName & "] was deleted."
					End If
					selectionChangedTaskResult.ChangeSelectionChangedUIActionInDashboard = False
					selectionChangedTaskResult.ModifiedSelectionChangedUIActionInfo = Nothing
					selectionChangedTaskResult.ChangeSelectionChangedNavigationInDashboard = False
					selectionChangedTaskResult.ModifiedSelectionChangedNavigationInfo = Nothing
					selectionChangedTaskResult.ChangeCustomSubstVarsInDashboard = False
					selectionChangedTaskResult.ModifiedCustomSubstVars = Nothing
					selectionChangedTaskResult.ChangeCustomSubstVarsInLaunchedDashboard = False
					selectionChangedTaskResult.ModifiedCustomSubstVarsForLaunchedDashboard = Nothing
					Return selectionChangedTaskResult
										
				End If

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

#Region "Delete Maintenance Unit Helpers"
		Public Sub DeleteMaintUnit(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardExtenderArgs)
			Try
				'Get the parameters from the dashboard	
				Dim maintUnitName As String = args.SelectionChangedTaskInfo.CustomSubstVars("MaintUnitList_ATB")
				
				'Log the action
				BRAPi.ErrorLog.LogMessage(si, "Admin Toolbox was used to delete entire Maintenance Unit: [" & maintUnitName & "]")
				
				'Open a connection to the database				
				Using dbConnFW As DbConnInfo = EngineDb.CreateFrameworkDbConnInfo(si)
					Using dbConnApp As DbConnInfo = EngineDb.CreateApplicationDbConnInfo(si)										
						Dim maintUnit As DashboardMaintUnit = DashboardMaintUnitsWcf.GetMaintUnit(dbConnFW, dbConnApp, False, maintUnitName)
						Dim muInfo As DashboardMaintUnitInfo = DashboardMaintUnitsWcf.GetMaintUnitInfo(dbConnFW, dbConnApp, False, maintUnit.UniqueID)
						
						'Delete each Dashboard in each group
						For Each gi As DashboardGroupInfo In muInfo.DashboardGroupInfos
							'First remove all components from each dashboard to prevent embedded dashboard cross reference errors
							For Each db As Dashboard In gi.Dashboards										
								DashboardsWcf.SaveDashboardAndComponentMembers(dbConnFW, dbConnApp, False, db, New List(Of DashboardDbrdCompMember), False)										
							Next
							
							'Next Delete the dashboards
							For each db as Dashboard in gi.Dashboards																			
								DashboardsWcf.DeleteDashboard(dbConnFW, dbConnApp, False, db.UniqueID)
							Next
							
							'Removed for 8.0 compatibility
							'Delete the group
							'DashboardProfilesWcf.DeleteGroup(dbConnFW, dbConnApp, False, gi.Group.UniqueID)
						Next	
							
						'Delete the components
						For each dbComponent as DashboardCompSummaryInfo in muInfo.Components
							DashboardComponentsWcf.DeleteComponent(dbConnFW, dbConnApp, False, dbComponent.UniqueID)
						Next
						
						'Delete the Adapters
						For each dbAdapter as DashboardAdptrSummaryInfo in muInfo.Adapters
							DashboardAdaptersWcf.DeleteAdapter(dbConnFW, dbConnApp, False, dbAdapter.UniqueID)
						Next								
						
						'Delete the Parameters
						For each dbParam as DashboardParamSummaryInfo in muInfo.Parameters
							DashboardParametersWcf.DeleteParameter(dbConnFW, dbConnApp, False, dbParam.UniqueID)
						Next
						
						'Delete the MaintenanceUnit
						DashboardMaintUnitsWcf.DeleteMaintUnit(dbConnFW, dbConnApp, False, maintUnit.UniqueID,False,maintUnit.Name)
						
					End Using
				End Using	

			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Sub		
#End Region	

#Region "Delete Scenario and Data Helpers"

		Public Function DeleteScenarioData(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardExtenderArgs) As String
			Try

				Dim batchSize As Integer = 20000
				
				'Get the parameters from the dashboard	
				Dim scenarioName As String = args.SelectionChangedTaskInfo.CustomSubstVars("ScenarioList_ATB")
				
				'Log the action
				BRAPi.ErrorLog.LogMessage(si, "Admin Toolbox was used to delete ALL DATA and AUDIT HISTORY for scenario: [" & scenarioName & "]")

				If Not String.IsNullOrEmpty(scenarioName) Then
					'We have a scenarioId, so set the return name
					Dim scenarioId As String = ScenarioDimHelper.GetIdFromName(si, scenarioName).ToString
					
					Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
						'Remove Analytic Data
						For yearId As Integer = 1996 To 2050
							Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From DataRecord" & yearId.ToString & " Where ScenarioId = " & scenarioId & "")
						Next 
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From DataUnitTimestamp Where ScenarioId = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From DataAttachment Where Scenario = '" & scenarioName & "'")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From DataEntryAuditWFUnit Where WFScenarioId = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From DataEntryAuditCell Where ScenarioId = " & scenarioId & "")						
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From Form Where ScenarioId = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From JournalHeader Where ScenarioId = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From JournalLineItem Where ScenarioId = " & scenarioId & "")
						
						'Remove Stage Data
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageToFinanceValidationError Where WorkflowScenarioKey = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageToFinanceLoadResult Where WorkflowScenarioKey = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageSummaryTargetData Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageTargetData Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageSourceData Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageAttributeData Where Wsk = " & scenarioId & "")
						
						'Use stage API to delete stage archives since there is a recursive relationship within the table
						Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, "SELECT UniqueID FROM StageArchivesInformation WHERE (Wsk = " & scenarioId & ")", True) 
							For Each dr As DataRow In dt.Rows
								Dim archiveID As New Guid(dr("UniqueID").ToString)
								StageArchiveManager.Delete(dbConnApp, archiveID)
							Next
						End Using
						
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageRuleProfilesHistory Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageRuleProfileMembersHistory Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageRuleGroupsHistory Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From StageRulesHistory Where Wsk = " & scenarioId & "")
						
						'Remove Workflow Status
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From ICMatchStatus Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From WorkflowLock Where ScenarioId = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From TaskflowStepInstances Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From TaskflowStepLog Where Wsk = " & scenarioId & "")						
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From WorkflowProfileHierarchyHistory Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From WorkflowProfileAttributesHistory Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From WorkflowProfileEntitiesHistory Where Wsk = " & scenarioId & "")
						Me.ExecuteBatchDelete(dbConnApp, batchSize, "Delete From WorkflowProfileEntityCalcsHistory Where Wsk = " & scenarioId & "")
						
					End Using
				End If

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

        Private Sub ExecuteBatchDelete(ByVal dbConnApp As DbConnInfo, batchSize As Integer, ByVal deleteStatement As String)
            Try
								
				'Now keep executing and committing this statement until the records affected count = 0
                Dim rowsDeleted As Long = 0
                Try
                    dbConnApp.BeginTrans()
                    rowsDeleted = BRAPi.Database.ExecuteActionQuery(dbConnApp, deleteStatement, True, True)
					
                    dbConnApp.CommitTrans()
                Catch ex As Exception
                    'Cleanup and re-throw the error
                    dbConnApp.RollbackTrans()
                    Throw New XFException(dbConnApp.SI, ex)
                End Try

                'Keep deleting as long as the number of rows is equal to the batch size
                Do While rowsDeleted = CLng(batchSize)
                    Try
                        dbConnApp.BeginTrans()
                        rowsDeleted = BRAPi.Database.ExecuteActionQuery(dbConnApp, deleteStatement, True, True)
                        dbConnApp.CommitTrans()
                    Catch ex As Exception
                        'Cleanup and re-throw the error
                        dbConnApp.RollbackTrans()
                        Throw New XFException(dbConnApp.SI, ex)
                    End Try
                Loop

            Catch ex As Exception
                Throw ErrorHandler.LogWrite(dbConnApp.SI, New XFException(dbConnApp.SI, ex))
            End Try
        End Sub

#End Region	

End Class
End Namespace

     

    • KH1's avatar
      KH1
      Contributor III
      2 months ago

      MarcusH​, thank you sharing with us the code from "ATB_ToolboxHelper".

      App Copy - Reduced Dataset

      "Truncate" relevant Application Tables will enable:
      - Application Copy - Selectable / No - datasets
      - Reset Scenario - Within a few hours - No Timed Out

      Thank you, PF team.