Many of us here at Perficient have been busy lately exploring OneStream XF, the new planning and financial consolidation solution. For consultants coming from the Hyperion Financial Management (HFM) background like me, it’s impossible not to appreciate new amazing capabilities that come with .NET platform, the elegance of and omnipresence of help articles on VB.NET syntax. OneStream internal methods and functions too make writing business rules swift, expressive and fun. Here for example is a neat custom function that tackles selective data clearings in OneStream cubes.
SetDataCell vs HS.Clear
Very often the need arises to delete a broadly-defined subset of data in the cube – for example, wipe out some particular account, or department. Back in HFM, I could write something like this in the rules file:
The above would take care of all data captured in statistical account “Headcount”. For OneStream, the suggestion is to use the SetDataCell method to clear accounts:
Simply pass over zero for the amount and True for isNoData, and it clears the intersection defined in memberFilter – like this:
The only downside here is that the method would only accept fully resolved POVs, meaning you have to specify all custom dimension members when you delete data. To clarify, you cannot simply say something like api.Data.SetDataCell(“A#Headcount”,0,True), run consolidation and expect headcount data to disappear.
Employing data buffer to clear data
So how to delete data in the application if you really want to delete everything there is for a specific member filter, i.e. the whole slice of data? Looking up existing data cells in a data buffer while applying the said member filter to the buffer seems to be the most natural solution:
- I spent a couple hours trying to figure out what’s going on with this parameter, so I thought it’s worth sharing here: this is a Boolean parameter called changeIdsToCommonIfNotUsingAll. GetDataBuffer is an overloaded function – meaning, it allows for passing over different sets of arguments while shooting for somewhat the same result. I initially used the call that omits the changeIdsToCommonIfNotUsingAll parameter. As I learned, the default value for the parameter is True. What happened was all the member IDs for dimensions included in the filter (sourcePOV) were set to -1, while the name property for these members always yielded “XFCommon”. I assume, if the ALL keyword is not used in the filter (as in “A#ALL”, i.e. when filtered members apply to all data cells in the buffer), this trick saves memory when working with large data buffers intended for writing data. The bottom line is, in order to be able to build the member script you need to retrieve data buffer with this parameter set to False.
- This is the showcase of OneStream robust internal methods: instead of calling out and concatenating member names for each dimension, you can simply call the data cell member script builder to yield the whole target POV (dimension tags included!) in just one line of code.
Once the above procedure has been pasted into your financial business rule on the same level as the Main function, you can simply call it whenever you need to clear data by filter (line 396):
- ScenarioType is one of OneStream internal enumerations – search “enumeration” in the API chm doc to see the full list of enumerations and look up values .
- This is a custom function that returns True if Time1 > Time2.
- This method yields the list of MemberInfo objects that match the criteria – here same filters can be used as in the member selector (as opposed to filters passed over to the GetDataBufferUsingFormula method, which won’t accept WHERE clauses). The last parameter has to be set to True to filter out duplicate members from different hierarchies.
