Keeping metadata synchronized between source systems and OneStream XF applications can be a tedious task. It requires constant monitoring of metadata changes in both applications. Consider a situation where an ERP system is the source of the chart of accounts, business units, cost centers, products, projects, and more. In addition, the ERP system is also the source of hierarchies and trees used for reporting. As a downstream application, OneStream inherits all base-level members, as well as the trees/hierarchies from the ERP. The Refresh Metadata Utility can update and maintain dimension hierarchies in OneStream XF with data imported from source systems.
This is how it works: The utility uses the EventTransformationHandler, extender business rules, and data management sequences to create and update a specified hierarchy in a specified dimension. The sequence can be launched manually from Data Management, attached to a dashboard button, or launched from a PowerShell command for a lights-out process. It can import data using a direct connection or a file that is stored on the application server.
At a high level, the utility does the following:
- Clears data from the import workflow stage table by SourceID and TimeKey.
- Executes the import workflow to load the new hierarchy. This step can consume files stored on the server or launch a direct connect data import. (In this example, the process loaded the data from a file that was loaded to the harvest folder on the server.)
- Rebuilds the hierarchy using the data loaded into the import workflow stage table.
The same logic that adds members during the initial load also adds new members from the source system to the hierarchy in OneStream.
- Maintenance. Metadata is maintained in one central location.
- Synchronization. ERP and OneStream are always synchronized, providing “one version of the truth.”
- Reliability. There are no kick-outs during data load since all metadata members are loaded into the dimension before data is refreshed.
One limitation, however, is that changes to synchronized hierarchies in OneStream will be lost after each metadata load.
Let’s go over a step-by-step example of how the utility works.
We start with an empty dimension that has only one member called ALL_BU. This member is going to be the parent of all the hierarchies that will be imported from the ERP:
To run the utility, we launch a Data Management sequence that is configured to refresh a single hierarchy/tree from the source ERP system. The first time the sequence is run, the utility imports data from a file stored in the Harvest folder on the server. The process creates the TOT_BU hierarchy and all its descendants under the ALL_BU parent:
After the initial load is done, we make some changes to the source file. In this example, we move some members from one parent to another (rows 3, 5, and 6), and create a new member (row 7):
After the file is saved and loaded to the Harvest folder on the server, the BUSINESS_UNIT sequence is launched again.
In this example, the second execution of the sequence moved existing members 1000 from under the TOT_BU parent to under the FP&A REPORTING parent. It also created the new members 4341, 4342, and 3244 under the SS CORP CONSOL parent.
Here’s a quick video that shows the Metadata Builder Utility updating a dimension:
Metadata synchronization is always a challenge for system administrators, but it is an absolute requirement for successfully loading data into the system. If not done right, it can impact change control, compliance, and compliance. OneStream’s ability to inherit relationships and member properties from source systems ensures seamless data loads and facilitates “one version of the truth.” While this solution may not apply to every situation, it provides another option for the design.
How can you transform your business into a data-driven organization? For more information about how MorganFranklin can assist your company, visit our Data & Analytics services page.