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
MarcusH, thank you sharing with us the code from "ATB_ToolboxHelper".
We have been using "Reset Scenario" DM Jobs to achieve the goals beyond that Manju presented.
- We need to build new OS E# per company reorg.
- We need to recover the DB storage used by the old E#.
Our OS app is gigantic - just the metadata.
- Load/Extract took > 1 day and missed customizations.
- Currently, we can't make a copy only the shell of a OS app.
App Copy - Reduced Dataset
The problems with using "Reset Scenario" have been "Canceled By System" at ~24 hrs.
- Our datasets are enormous to Import and store.
- "Inactivity Timeout" = 24 hrs as logged and set by the DM Job code.
- Can't increase the hours for the "Inactivity Timeout" > 24.
I'm a novice to OS and coding.
In a hope to use "ATB_ToolboxHelper" to create a faster "Reset Scenario" (RS) DM Job, please share your expertise to help me with below:
1. Does the RS DM Job achieve the same goals as the code under "#Region Delete Scenario and Data Helpers"?
2. Is that "Region" of code run in parallel?
3. If not already "in parallel", how to use parallelism to improve the performance of the code?
4. What else to improve the speed of "Delete Scenario" / "Reset Scenario"?
Thank you, Marcus and other SMEs who want to share.
