Dimensions form the framework on which the reporting structure is built. OneStream Cube Dimensions are created based upon the reporting requirements for a client. Dimensions are assigned to cubes and are the framework that define a subsection of data. Dimensions can be shared and can vary across cubes.
There are three types of Dimensions available in OneStream: Customizable, Derived and System Dimensions. We can edit only the customizable dimensions which includes Entity, Account, Scenario, Flow & UD1-UD8.
Dimensions can be easily managed in OneStream from the dimension library. In this blog, I would like to talk about a quicker alternative to deleting members one by one, where you need to delete a large number of members from a dimension in one go.
When you delete a member from a dimension, the member is also deleted from all cubes that share the dimension. In case the member is a parent, then all relationships under it need to be removed before deleting it. ‘Remove relationship’ removes the shared members from their current relationship without affecting other relationships.
Bulk deletions of members of a dimension can be done using scripts.
Let’s consider the below example. We have a snippet of the accounts dimension hierarchy.
The requirement is to delete the entire KPI_AR rollup. To delete the highlighted members at one go including removal of the relationship of KPI_AR with its parent KPI, below is the script we can use.
For this, a list of base members to be deleted can be taken in an excel file.
Also, Cube View, Quick View or dimension library grid view can be used to generate the list.
Next, you need to get the members in syntax required in the script i.e., <member name=”” action=”Delete” /> using excel concatenate function and paste them into the script.
The same steps can be followed for creating the list of members for removal of relationship and get them in the required syntax. Paste this in the appropriate line in the script.
Once the script is complete, go to the OneStream application and load it from Application > Tools > Load/Extract. Wait for the load to be completed. Once done, you can verify the same in the dimension library.
The resultant hierarchy of members will look like this:
This solution is quite helpful for bulk deletes which may be required during development phase of the project. Hope you find it helpful too!
