Mass Delete Members in OneStream XF

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:

• All the members are base members
• You have already identified which members need to be deleted
• You have a list of these members in a text file or format that can be imported
• The members identified are not a member in a Journal template

If you don’t have a list, you can use a Cube View, Quick View, or another method to generate the list.  The next step will be to import the list of accounts into a Microsoft SQL Server database using the standard flat file import.

For reference, a sample of the imported data is displayed.

With the file successfully imported, the next step will be to create a SQL statement that will output an XML file in the correct format.  The format is shown below.

Notice that each <member > element has two attributes:  name which will be the member name to delete, and action which is specified as =”Delete”.  The action instructs the import process to delete the member.

Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

The next query creates the XML file.  It has three parts:  a SELECT clause, a FROM clause, and a FOR XML PATH clause.

The SELECT clause “SELECT [MemberName] AS [@name],’Delete’ AS [@action]” will order the results as attributes of the <member>  element.  The ampersand “@” indicates this is an attribute of an element instead of a row element.

The FROM clause is simply the same SQL statement from earlier that returns our list of accounts.

The FOR XML PATH clause specifies the format of the output. It creates a root element called <members> and each row in our list will become an <member> element.

The remaining required XML elements are displayed in the next image on lines 2-5 and 10-13.

Note: the <dimension> element has additional attributes which are not displayed. 

To make things easy, we just overwrite lines 6-9 in the first image with our XML results.  The second image displays the file after the copy and pastes operation.  Save this new file with extension .xml.

Before we import our file,  we check that our OneStream application has an Account dimension with 10,367 base members.  We are going to delete 190 of these with our file.

Now, log into the OneStream application.  Select and expand Application > Tools > Load/Extract.

Navigate to and select the XML file previously created.  Once this is done, select the Load icon.  The load will complete without error which is confirmed by the second image after this paragraph.

To confirm the upload did delete members, refresh the OneStream application, and then navigate to the Account dimension and Grid View.  The number of base members has updated from 10,367 to 10,177 which confirms 190 members were deleted.

I hope this solution is helpful the next time you have to delete numerous members.  If you have any questions, feel free to either post a comment to the blog or email me at terry.ledet@perficient.com.