The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

Gabriel_08's avatar
Gabriel_08
New Contributor
2 years ago

Delete DM Sequences and Steps in bulk.

Hello~,

I am wondering if there are any solutions available for bulk deletion of DM Sequences and Steps. We have hundreds of Sequences and Steps that will not be used, and deleting them manually would require a significant amount of effort. Could you please assist us with this?

Thank you in advance!

Data Management

3 Replies

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    2 years ago

    There was an application on MarketPlace called Admin Solution Tools (AST) that has a script that deletes DM sequences. I can't find it on MarketPlace now though. That clears up DM steps and sequences using SQL statements. This is the function:

      

    Private Function DropSolutionDM(ByVal si As SessionInfo) As String
    Try
        'Delete business rules associated with this app
        Dim message As String = String.Empty

        If BRApi.Security.Authorization.IsUserInAdminGroup(si) Then

            Dim dmUniqueID As String = String.Empty
            'Get UniqueID from DataMgmtGroup
            Dim sql As String = "Select UniqueID From [DataMgmtSequence] Where Name Like '%(AST)'"
            
            'Execute Query on App DB
            Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)

                Dim dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql, True)

                If (dt.Rows.Count > 0) Then 'Check If the DataTable returns a valid record
                    dmUniqueID = dt.Rows(0)(0).ToString
                Else
                    dmUniqueID = Guid.Empty.ToString
                End If

                'Delete	DataMgmtSeqStepMember
                sql = $"Delete From [DataMgmtSeqStepMember] WHERE [SequenceID] = '{dmUniqueID}'"
                BRApi.Database.ExecuteSql(dbConnApp, sql, True)

                'Delete	DataMgmtGroup
                sql = $"Delete From [DataMgmtGroup] WHERE Name like '%(AST)'"
                BRApi.Database.ExecuteSql(dbConnApp, sql, True)

                'Delete	DataMgmtSequence
                sql = $"Delete From [DataMgmtSequence] WHERE Name like '%(AST)'"
                BRApi.Database.ExecuteSql(dbConnApp, sql, True)

                'Delete	DataMgmtStep
                sql = $"Delete From [DataMgmtStep] WHERE Name like '%_AST'"
                BRApi.Database.ExecuteSql(dbConnApp, sql, True)

            End Using
        Else
            message = "Security Error: Adminstration Rights Required to Execute Uninstall"
        End If
        Return message
    Catch ex As Exception
        Throw ErrorHandler.LogWrite(si, New XFException(si, "Unhandled Exception in DropSolutionDM() function.", ex.Message, ex.InnerException))
    End Try
End Function

     

    • Gabriel_08's avatar
      Gabriel_08
      New Contributor
      2 years ago

      Thank you so much! Will let you know if the code worked.

    • Gabriel_08's avatar
      Gabriel_08
      New Contributor
      2 years ago

      Hi MarcusH,

      Thank you again!  We deleted Sequences and Steps successfully. 

       

      Try
      Dim message As String = String.Empty
       
              If BRApi.Security.Authorization.IsUserInAdminGroup(si) Then
              Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
                   
               'Delete DataMgmtStep
                Dim sql As String = "Delete From [DataMgmtStep] WHERE [DataMgmtGroupID] = '......' "
                      BRApi.Database.ExecuteSql(dbConnApp, sql, True)
                  End Using
              Else
                  message = "Security Error: Adminstration Rights Required to Execute Uninstall"
              End If
              Return message
      Catch ex As Exception
      Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
      End Try