During one of my previous projects, we needed to delete approximately 200 accounts that were distributed throughout a large Account dimension. This blog discusses how we solved it.
One approach would have been to delete each account one time. Obviously, that wasn’t appealing as it is time-consuming and can be prone to error. Another approach is discussed in OneStream knowledge base article KB0010541, which provides a method to create an XML file for one or more members. However, the KB method requires typing the member name one at a time.
Our approach was to create an XML file to mass delete the members. This method will take an input file and generate the necessary XML syntax. All you need is access to Microsoft SQL Server with the appropriate permissions. This solution is applicable for other dimensions UD1-UD8 and can be re-used whenever the need arises if properly setup. The approach assumes that:
1. All the members are base members
2. You have already identified which members need to be deleted
3. You have a list of these members in a text file or format that can be imported
4. The members identified are not a member in a Journal template