Is there anyway to download TB/Reclass files automatically using Business Rules?

[Yashwant]

I have application which has 47 entities and users who have Entity wise rights to upload TB/Reclass and Cube views.

So far Admin is downloading TB  manually (using view source document) by going through Entity. 

Now I want to download TB and Reclass files through some business rules (if possible to be schedule to run once in Month) or some procedure for all entities using user who has administrative privilege.

Thanks all in advance for your help.

Regards,

Yashwant

[PeterFu · Accepted solution]

Hi Yashwant,

Here you have the code, I have copied in both XFR_ExportStageDat and XFR_ExportStageArchives

XFR_ExportStageData:

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.Extender.XFR_ExportStageData
Public Class MainClass
    '------------------------------------------------------------------------------------------------------------
    'Reference Code: XFR_ExportStageSourceData
    '
    'Description: Extender buiness rule that exports the stage source data to a single delimited files
    ' for all import child workflow pofiles. The files are written to the application
    ' Data Mgmt Export folder in the file share directory.
    '
    'Usage: Can be executed from Business Rule editor or run as part of a Data Management sequence.
    ' Note: If called from a Data Management sequence, the business rule step must supply 3
    ' paramaters named as follows (WfProfileName, ScenarioName, TimeName)
    '
    'Created By: Tom Shea
    'Date Created: 5-9-2013
    '------------------------------------------------------------------------------------------------------------

    'Module level variables
    Private m_ColNamesWritten As Boolean = False

    Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
        Try

            'Define the workflow cluster used as the starting point to extract from
            Dim wfClusterPk As New WorkflowUnitClusterPk
            Dim wfProfileName As String = String.Empty
            Dim scenarioName As String = String.Empty
            Dim timeName As String = String.Empty

            Select Case args.FunctionType
                Case Is = ExtenderFunctionType.Unknown
                    'Set the parent workflow unit to extract input profiles for
                    wfProfileName = "Houston"
                    scenarioName = "Actual"
                    timeName = "2011M2"
                    wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)

                Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
                    'Since this is being called from a DataManagement job, get the wfCluster from the parameters defined in the DataMgmt sequence
                    wfProfileName = args.NameValuePairs("WfProfileName")
                    scenarioName = args.NameValuePairs("ScenarioName")
                    timeName = args.NameValuePairs("TimeName")
                    wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)
            End Select

            'Prepare the Stage Data Extract File path
            Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
            Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName) & "\" & DateTime.UtcNow.ToString("yyyyMMdd") & "\StageData"
            If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)
            Dim filePath As String = folderPath & "\DataExtract_" & scenarioName & "_" & timeName & ".csv"
            If File.Exists(filePath) Then File.Delete(filePath)

            'Export data for each descendant workflow profile
            Dim profileInfos As List(Of WorkflowProfileInfo) = BRAPi.Workflow.Metadata.GetRelatives(si, wfClusterPk, WorkflowProfileRelativeTypes.Descendants, WorkflowProfileTypes.InputImportChild)
            If Not profileInfos Is Nothing Then
                'Create the list of columns to export
                Dim colList As New List(Of String)
                colList.Add("WFProfileName")
                colList.Add("EtT")
                colList.Add("AcT")
                colList.Add("RawAmount")

                Dim isFirstFile As Boolean = True
                For Each profileInfo As WorkflowProfileInfo In profileInfos
                    Dim wfClusterPkChild As New WorkflowUnitClusterPk(profileInfo.ProfileKey, wfClusterPk.ScenarioKey, wfClusterPk.TimeKey)
                    If isFirstFile Then
                        isFirstFile = False
                        BRAPi.Import.Data.ExportStageData(si, wfClusterPkChild, Nothing, Nothing, True, filePath, False, ".", True)
                    Else
                        BRAPi.Import.Data.ExportStageData(si, wfClusterPkChild, Nothing, Nothing, False, filePath, True, ".", True)
                    End If
                Next
            End If

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

End Class
End Namespace

XFR_ExportStageArchives:

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.Extender.XFR_ExportStageArchives
    Public Class MainClass
        '------------------------------------------------------------------------------------------------------------
        'Reference Code: XFR_ExportStageArchives
        '
        'Description: Extender buiness rule that exports the stage archive files for all import child workflow
        ' profiles of the specified parent workflow profile. The files are written to the application
        ' Data Mgmt Export folder in the file share directory.
        '
        'Usage: Can be executed from Business Rule editor or run as part of a Data Management sequence.
        ' Note: If called from a Data Management sequence, the business rule step must supply 3
        ' paramaters named as follows (WfProfileName, ScenarioName, TimeName)
        '
        'Created By: Tom Shea
        'Date Created: 5-9-2013
        '------------------------------------------------------------------------------------------------------------
        Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
            Try
                'Define the workflow cluster used as the starting point to extract from
                Dim wfClusterPk As New WorkflowUnitClusterPk
                Dim wfProfileName As String = String.Empty
                Dim scenarioName As String = String.Empty
                Dim timeName As String = String.Empty

                Select Case args.FunctionType
                    Case Is = ExtenderFunctionType.Unknown
                        'Set the parent workflow unit to extract input profiles for
                        wfProfileName = "Houston"
                        scenarioName = "Actual"
                        timeName = "2011M2"
                        wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)

                    Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
                        'Since this is being called from a DataManagement job, get the wfCluster from the parameters defined in the DataMgmt sequence
                        wfProfileName = args.NameValuePairs("WfProfileName")
                        scenarioName = args.NameValuePairs("ScenarioName")
                        timeName = args.NameValuePairs("TimeName")
                        wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)
                End Select

                'Prepare the Stage Data Extract File path
                Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
                Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName) & "\" & DateTime.UtcNow.ToString("yyyyMMdd") & "\StageArchives\" & scenarioName & "\" & timeName
                If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)

                'Export data for each descendant workflow profile
                Dim profileInfos As List(Of WorkflowProfileInfo) = BRAPi.Workflow.Metadata.GetRelatives(si, wfClusterPk, WorkflowProfileRelativeTypes.Descendants, WorkflowProfileTypes.InputImportChild)
                If Not profileInfos Is Nothing Then
                    For Each profileInfo As WorkflowProfileInfo In profileInfos
                        Dim wfClusterPkChild As New WorkflowUnitClusterPk(profileInfo.ProfileKey, wfClusterPk.ScenarioKey, wfClusterPk.TimeKey)
                        BRAPi.Import.Data.ExportStageArchives(si, wfClusterPkChild, folderPath, False)
                    Next
                End If

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

You can get the Golfstream application from the MarketPlace as well.

Regards,

Peter

[PeterFu]

Hi Yashwant,

If you look in the Golfstream application, you will find an Extensibility Rule that Tom Shea have created to extract Stage data. You should be able to use this rule, amend it and get what you want. See print screen below.

Where the file is stored currently,

I hope this helps.

Regards,

Peter

[Yashwant]

Thanks a lot Peter.

Sincere request.

Unfortunately I don't have GulfStream application in our setup. If possible, please share this business rule here.

Our version is 4.1 which is old hope this code is Extensible rule is compatible to our version.

Regards,

Yashwant

[PeterFu · Accepted solution]

Hi Yashwant,

Here you have the code, I have copied in both XFR_ExportStageDat and XFR_ExportStageArchives

XFR_ExportStageData:

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.Extender.XFR_ExportStageData
Public Class MainClass
    '------------------------------------------------------------------------------------------------------------
    'Reference Code: XFR_ExportStageSourceData
    '
    'Description: Extender buiness rule that exports the stage source data to a single delimited files
    ' for all import child workflow pofiles. The files are written to the application
    ' Data Mgmt Export folder in the file share directory.
    '
    'Usage: Can be executed from Business Rule editor or run as part of a Data Management sequence.
    ' Note: If called from a Data Management sequence, the business rule step must supply 3
    ' paramaters named as follows (WfProfileName, ScenarioName, TimeName)
    '
    'Created By: Tom Shea
    'Date Created: 5-9-2013
    '------------------------------------------------------------------------------------------------------------

    'Module level variables
    Private m_ColNamesWritten As Boolean = False

    Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
        Try

            'Define the workflow cluster used as the starting point to extract from
            Dim wfClusterPk As New WorkflowUnitClusterPk
            Dim wfProfileName As String = String.Empty
            Dim scenarioName As String = String.Empty
            Dim timeName As String = String.Empty

            Select Case args.FunctionType
                Case Is = ExtenderFunctionType.Unknown
                    'Set the parent workflow unit to extract input profiles for
                    wfProfileName = "Houston"
                    scenarioName = "Actual"
                    timeName = "2011M2"
                    wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)

                Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
                    'Since this is being called from a DataManagement job, get the wfCluster from the parameters defined in the DataMgmt sequence
                    wfProfileName = args.NameValuePairs("WfProfileName")
                    scenarioName = args.NameValuePairs("ScenarioName")
                    timeName = args.NameValuePairs("TimeName")
                    wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)
            End Select

            'Prepare the Stage Data Extract File path
            Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
            Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName) & "\" & DateTime.UtcNow.ToString("yyyyMMdd") & "\StageData"
            If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)
            Dim filePath As String = folderPath & "\DataExtract_" & scenarioName & "_" & timeName & ".csv"
            If File.Exists(filePath) Then File.Delete(filePath)

            'Export data for each descendant workflow profile
            Dim profileInfos As List(Of WorkflowProfileInfo) = BRAPi.Workflow.Metadata.GetRelatives(si, wfClusterPk, WorkflowProfileRelativeTypes.Descendants, WorkflowProfileTypes.InputImportChild)
            If Not profileInfos Is Nothing Then
                'Create the list of columns to export
                Dim colList As New List(Of String)
                colList.Add("WFProfileName")
                colList.Add("EtT")
                colList.Add("AcT")
                colList.Add("RawAmount")

                Dim isFirstFile As Boolean = True
                For Each profileInfo As WorkflowProfileInfo In profileInfos
                    Dim wfClusterPkChild As New WorkflowUnitClusterPk(profileInfo.ProfileKey, wfClusterPk.ScenarioKey, wfClusterPk.TimeKey)
                    If isFirstFile Then
                        isFirstFile = False
                        BRAPi.Import.Data.ExportStageData(si, wfClusterPkChild, Nothing, Nothing, True, filePath, False, ".", True)
                    Else
                        BRAPi.Import.Data.ExportStageData(si, wfClusterPkChild, Nothing, Nothing, False, filePath, True, ".", True)
                    End If
                Next
            End If

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

End Class
End Namespace

XFR_ExportStageArchives:

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.Extender.XFR_ExportStageArchives
    Public Class MainClass
        '------------------------------------------------------------------------------------------------------------
        'Reference Code: XFR_ExportStageArchives
        '
        'Description: Extender buiness rule that exports the stage archive files for all import child workflow
        ' profiles of the specified parent workflow profile. The files are written to the application
        ' Data Mgmt Export folder in the file share directory.
        '
        'Usage: Can be executed from Business Rule editor or run as part of a Data Management sequence.
        ' Note: If called from a Data Management sequence, the business rule step must supply 3
        ' paramaters named as follows (WfProfileName, ScenarioName, TimeName)
        '
        'Created By: Tom Shea
        'Date Created: 5-9-2013
        '------------------------------------------------------------------------------------------------------------
        Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
            Try
                'Define the workflow cluster used as the starting point to extract from
                Dim wfClusterPk As New WorkflowUnitClusterPk
                Dim wfProfileName As String = String.Empty
                Dim scenarioName As String = String.Empty
                Dim timeName As String = String.Empty

                Select Case args.FunctionType
                    Case Is = ExtenderFunctionType.Unknown
                        'Set the parent workflow unit to extract input profiles for
                        wfProfileName = "Houston"
                        scenarioName = "Actual"
                        timeName = "2011M2"
                        wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)

                    Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
                        'Since this is being called from a DataManagement job, get the wfCluster from the parameters defined in the DataMgmt sequence
                        wfProfileName = args.NameValuePairs("WfProfileName")
                        scenarioName = args.NameValuePairs("ScenarioName")
                        timeName = args.NameValuePairs("TimeName")
                        wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)
                End Select

                'Prepare the Stage Data Extract File path
                Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
                Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName) & "\" & DateTime.UtcNow.ToString("yyyyMMdd") & "\StageArchives\" & scenarioName & "\" & timeName
                If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)

                'Export data for each descendant workflow profile
                Dim profileInfos As List(Of WorkflowProfileInfo) = BRAPi.Workflow.Metadata.GetRelatives(si, wfClusterPk, WorkflowProfileRelativeTypes.Descendants, WorkflowProfileTypes.InputImportChild)
                If Not profileInfos Is Nothing Then
                    For Each profileInfo As WorkflowProfileInfo In profileInfos
                        Dim wfClusterPkChild As New WorkflowUnitClusterPk(profileInfo.ProfileKey, wfClusterPk.ScenarioKey, wfClusterPk.TimeKey)
                        BRAPi.Import.Data.ExportStageArchives(si, wfClusterPkChild, folderPath, False)
                    Next
                End If

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

You can get the Golfstream application from the MarketPlace as well.

Regards,

Peter

[Yashwant]

Thank You

[sdayringer]

Hi Peter, What adjustments to this code need to be made in order to extra the stagearchives for multiperiod loads? I've tried changing the time parameter to the year, but it doesn't work. The filepath is created, but they are just empty.....

[Yashwant]

Thanks Peter for the code. Really appreciate your help.

Could configure Business Rule however upon execution found that the .csv file contains transformed data and not the original TB uploaded. 

We want to use same TB and Reclass files in another application instead of user uploading TB from their respective Laptop that's why we are exploring option to download TB automatically.

We are planning to upgrade Onestream version that time we will configure Golfstream in our development setup.

Thanks Once again.

Regards,

Yashwant

[Cosimo · Accepted solution]

If you're looking to download the original TB file as imported into a workflow-scenario-period, use ExportStageArchives function. Below is an example within an extender rule. The file is stored under File Share \ Data Management folder. You can specify a more convenient folder by updating the folderPath variable. 

Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try

Dim profileName As String = "Houston"
Dim scenarioName As String = "Actual"
Dim timeName As String = "2022M3"


Dim wfClusterPk As WorkflowUnitClusterPk = BRApi.Workflow.General.GetWorkflowUnitClusterPk(si, profileName, scenarioName, timeName)

Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, _
si.AppToken.AppName) & "\" & DateTime.UtcNow.ToString("yyyyMMdd") & "\StageArchives\" & scenarioName & "\" & timeName

If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)

brapi.Import.Data.ExportStageArchives(si, wfClusterPk, folderPath,False)

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

[Yashwant]

This business rule is working.

I have created Data Management Steps to call Business Rule however I am stuck in passing parameter WFProfile, Scenario and Time. 

I am not able to pass the Workflow profile name since my workflow profile names are different. Tried using "WFProfile" but its throwing error.

Is anyone has example on calling business rule with these 3  Workflow profile, Scenario and Time.

Thank You

Regards,

Yashwant

[PeterFu]

Hi Yashwant,

Can you try WFProfileName, WFScenarioName and  WFTimeName and see if that works.

Peter

[Yashwant]

Hi Peter,

Already tried, its throwing error 

Error processing Data Management Step 'ExportDB'. Unable to execute Business Rule 'XFR_ExportStageArchives'. The given key was not present in the dictionary.

Create following Dashboard parameters,

1. WFTime and used Member filter T#WF

2. WFScenario with Literal value "Actual"

Don't know how to pass Wokflow name since my workflow name and Entity names are different.

Next step I want to run this Data management on Parent workflow and extract TB/Re-class files from all chikdren.

If possible would like to add condition in Business Rule to download only from certified workflow.

Thanks.

Regards,

Yashwant

[sdayringer]

Is it possible to extract the uploaded file details? When exporting our TB files, the file explorer shows the create/modify times for when I ran the rule, which is causing confusion, is there a way to change the filename to include the timestamp of when it was actually uploaded?

[PeterFu]

Hi,

You can for example send the load information as e-mail, see print screen below. If you want to see when a file was loaded, you only need to check under the Import step to find the load information, see print screen below.

Peter

[sdayringer]

Yes, but that doesn't help when looking in the file explorer at all the uploaded files for all the workflows with multiple 'replacing' loads per period... we are trying to avoid having to go to each separate workflow, period, and step just to see which file is the actual final load. Is there a way to rename the loaded source file in my BR so that it includes the file timestamp?

[PeterFu]

Hi,

If I understand you correctly, you want to have the time for when the file was loaded into the filename when extracting it.

You can see in the print screen below that the Batch Name contains that information, so you should be able to extract only the Batch Name and put it (trim it down to the time) into the Name for the extracted files.

This is the syntax for the whole e-mail,

Dim messageBody As String = batchInfo.GetCompleteBatchStatusMessage(si, objTimeDimAppInfoEx, True, True).

If you use "GetBatchName" Instead of the GetCompleteBatchStatusMessage, that should give you what you are looking I think. 

I hope this will helps.

Peter

[sdayringer]

Is it possible to extract and compile all workflows' stagearchives source file into one excel workbook as tabs for a particular period? Or to have all periods in a year for one workflow as tabs into one workbook? Also, is there a way to define the file description in the business rule with the import timestamp since the file time associated with the extract is based on when the rule/dm job was ran instead of when the file was imported?

[Yashwant]

Below worked as parameters for Business Rule from Data Management

WfProfileName= <Parent Workflow profile Name>, ScenarioName = Actual, TimeName = 2019M4

After execution Data Management has extracted TB's and Re-class files for all children workflow.