Instead of a file or connector, use a data management export sequence in a workflow import. sequence data source in an Import workflow to move and export data. For example, these can be used in an Import workflow to:
-
Copy data from one OneStream cube or scenario to another cube or scenario, such as when cubes or scenarios contain different dimensionality.
-
Export OneStream data to an external source system and apply transformation rules through the workflow.
This can be comprised of multiple data management steps. See "Data Management" in Application Tools for more information.
Data Management Settings
Data Export Sequence Name
The name of the data management export sequence being used for this data source.
Use Case: Create a Data Management Export Sequence to Copy Data Within OneStream
This use case reviews how to copy OneStream data to another cube or scenario with differing dimensionality through a Data Management Export Sequence data source in an Import workflow. In this example, users will copy actuals data from a financial close and a consolidations cube to a planning cube with differing dimensionality on a monthly basis. Below are the configuration steps and the result.
-
Configure a data management step with the Export Data type to specify the source data to move. Assign the step to a data management sequence. See "Data Management" in Application Tools for more information.
-
Create a data source with the Data Mgmt Export Sequences type. Set the Data Export Sequence Name to the name of the data management sequence.
Configure the source dimensions for the data source. See "Source Dimension Properties" in Data Sources for more information.
In this example, actuals data will be copied from the financial close and consolidation cube (FinRptg) to the planning cube (MgmtRptg), which contains different dimensionality. The FinRptg’s legal entity data will be copied to the MgmtRptg planning cube’s UD1 dimension, and the FinRptg’s UD1 cost center data will be copied to the MgmtRptg planning cube’s Entity dimension as defined in the Source Field Name properties.
-
Create a transformation profile and groups to map the source data to the target. See “Transformation Rules” for more information.
-
Create an Import Workflow Profile and assign the Data Management Export Sequence data source and the Transformation Profile Name. Set the Workflow Name to Import, Validate, and Load. The Import task will copy and transform the source data to the Stage tables. The Validate and Load tasks will map and load the Stage data to the target. Also, ensure that Profile Active is set to True, and Can Load Unrelated Entities is set to True if data is loaded to multiple entities.
Result:
In OnePlace, when a user clicks Load and Transform in the Workflow Import task, the Data Source Name and Type show the assigned Data Management Export Sequence data source.
In this example, when a user completes the Import, Validate, and Load tasks in a workflow, the data from FinRptg is transformed and loaded into MgmtRptg cube. For the planning MgmtRptg cube, the FinRptg legal entities were loaded to the UD1 dimension, and the FinRptg UD1 cost centers were loaded to the Entity dimension.
Use Case: Create a Data Management Export Sequence to Export Data From OneStream
This use case reviews how to export OneStream data to an external source system with differing data structures through a Data Management Export Sequence data source in an Import Workflow. Below are the configuration steps and the result. In this example, users will export and transform data every month from OneStream to their external tax system, which has its own entity structure and chart of accounts.
-
Configure the Data Management Step and Sequence:
Configure a data management step with the Export Data type to specify the source data to export. Assign the step to a data management sequence. See "Data Management" in Application Tools for more information.
-
Configure the Data Source:
Create a data source with the Data Mgmt Export Sequences type. Set the Data Export Sequence Name to the name of the data management sequence.
Configure the source dimensions for the data source. See "Source Dimension Properties" in Data Sources for more information.
-
Configure the Transformation Rules:
Create a transformation profile and groups to map the source data to the target. For dimensions that need to be transformed, specify the Source Value and Target Value. Import external target values using a TRX file. See “Transformation Rules” for more information.
-
Configure an Import Workflow Profile:
Create an Import Workflow Profile and assign the Data Management Export Sequence data source and Transformation Profile Name. Ensure the Workflow Name contains an Import (Stage Only) task, which will import the transformed data to the Stage tables. Also, ensure that Profile Active is set to True.
-
Configure the Method to Retrieve Source Data
When a user completes the Import workflow task, the transformed data will be in the Stage tables. In this example, data is being exported from OneStream to an external source, so the data will need to be retrieved from the Stage tables. There are multiple methods to retrieve the transformed data, such as using a:
• Workflow Event Handler: This business rule can run code when the Workflow completes a certain step. For example, the Workflow Event Handler can automatically generate a file with the exported and transformed data when the user completes the Workflow Import task.
• Transformation Event Handler: This business rule can run code at various points of the Workflow from Import through Load. For example, the Transformation Event Handler can automatically generate a file with the exported and transformed data when the user completes the Import task.
• Extender Rule: Data can be retrieved from Stage ad hoc with an Extender business rule. The rule can be run as needed or scheduled to run on a recurring basis to export the data to a table or file.
• Data Adapter with OneStream Rest API: Stage data can be retrieved using an Application Workspace Data Adapter with the Command Type of Method and Method Type of StageSummaryTargetData. A OneStream Rest API can call the data adapter and give the data to the caller in JSON format.
Result:
In OnePlace, when a user clicks Load and Transform in the Workflow Import task, the Data Source Name and Type show the assigned Data Management Export Sequence data source. When the Import task is completed, the transformed data is populated in the Stage tables, and it can be retrieved for the external system.
For example, a Workflow Event Handler business rule can automatically retrieve the transformed data from Stage and export it to a file when the Import task is completed. In this example, the exported file contains the OneStram source entity (ET), transformed target entity (EtT), OneStream source account (Ac), transformed target account (AcT), and amount.
