Table Data Manager:
OneStream has a Marketplace Solution called Table Data Manager that allows users to create custom tables where data can be loaded and updated. See also this Perficient blog post on how to get started with Table Data Manager:
Table Data Manager has a function/ button to import data into custom tables. However, the import function in Table Data Manager only accepts files in XML format. In this blog post I will show you how to load data to a custom table using a flat file such as a comma delimited (.csv) file instead of having to use XML format.
Create a Table:
Using the Table Data Manager instructions from the blog linked above I created this XFC_Test table:
Flat File loaded to OneStream:
I created this flat file and saved as a comma delimited file:
I then loaded the file to File Explorer under File Share/Applications/NHLBI_Blog_copy/Batch/Harvest:
Note: NHLBI_Blog_copy was the name of my application. Everything else would stay the same. The file was loaded here since we won’t have to be concerned about OneStream having access to this folder.
Business rule to upload data:
I created this business rule to upload the comma delimited file:
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.
Then I made it an Extensibility rule and made it an unknown type so it could be run from the Business rules page:
I developed this code using the Custom Table Load (Delim) Snippet in OneStream:
To get the file path (line 34) I used the GetFileShareFolder snippet:
From the business rule click on Run:
The business rule uploads the data and you will get a pop-up message that the business rule ran successfully:
Go back to Table Data Manager and View the data:
Tips and Tricks:
The column names in the business rule must match the column names in the custom table exactly. Lines 39, 40 and 41 had the exact same column names in brackets [ ] as the column names in the custom table:
The order of the columns in the business rule must match the order of the columns in the flat file. For example, the first column referred to in the business rule was Department and the 1st column in the flat file was also Department:
The business rule will replace all the data since the load method is set to replace: