The Challenge
We have all seen it… a OneStream user who wants a report that expands rows on several dimensions and wants all the detail. While it is imperative to understand what is really needed before solutioning, I have seen legitimate requests that would benefit from the cube view capability.
While the first inclination would be to start a cubeview and put the first dimension in expansion 1, the second in expansion 2 and so on, once you include 3 large dimensions things often slow down and the results paginate. Sparse row suppression can be a tool in the toolbox but that is not for every case, nor will it solve cases where the number of rows being called for with sparse data can be massive.
This blog assumes that due diligence has been done and it is determined that you do want to be able to report using a cube view vs. the myriads of alternatives that OneStream provides.
The Requirement
So let’s start with the requirement. For instance, a user wants a report that expands all entities, all sales and cogs accounts, and all cost centers. If these dimensions have a lot of members there will be many cells for the cube view to process. For a situation with 100 entities, 50 accounts, and 2,000 cost centers the cube view would need to evaluate 10,000,000 potential intersections. I have seen users new to OneStream build cube views this way and consume 95% of memory for long periods of time. There is an alternative that can fit many cases -especially when it is the base data that is requested.
A Solution Alternative
The less common but often successful approach is to evaluate the records that need to be rendered in OneStream when the cube view is asked to run and lighten the load of evaluating 10MM possible rows. This can be accomplished with a business rule approach, and the levels it can be taken to is impressive.
To get into a little more detail, let’s start with the member filter in the cube view row. In this alternative approach we are only going to use the first expansion. Instead of using Entity, E#ExampleEntity.Base, we will use an XFBR. For those new to the use of an XFBR, it is a business rule that can substitute for text in MANY places in OneStream. It is one of my favorite capabilities in OneStream and underscore the mentality of it being a platform as opposed to a point solution.
To do this where I would have put the “E#ExampleEntity.Base” in, I enter XFBR(NameOfTheBusinessRule, NameOfTheFunction, Paramater1 = example1, Parameter 2 = example2…). This calls a dashboard xfbr string created for this purpose (and other purposes if more functions exist).
Now that the cube view is calling the XFBR, the rule needs to send back the point of view combinations that the cube view will need to render. We are going to pull data into memory where data exists, and only where data exists. Within the rule we will assemble a return string that tells the cube view the series of points of view (there is a common misconception that the rule sends numbers back to the cube view which is not the case).
The magic starts with a feature called FDX, which stands for fast data extract. There are multiple version of FDX for different purposes, but the one for this need is the ExecuteDataUnit.
In this example we are setting a data table with base records that need to be shown in the cubeview. Getting the data as above is an early step, after setting up variables to represent the parameters that have been passed into the XFBR itself.
Now that we have the data in our data table (dt), we need to turn that into a comma delimited string that will be sent back to the cubeview. This is accomplished by declaring a loop.
Within that loop we are going to assemble the string, record by record. For each record we will get the pertinent info into variables, then add it to a list type variable.
The loop repeats until it has cycled through all records in the table.
The steps next sorts the list and ensures that it is a unique list. Code is omitted here as we use BDA libraries that are not publicly available.
The last two steps are to 1) combine the list into one big huge string that is comma delimited and 2) return it to the cubeview.
The Result
This is the high-level easy example to send many lines back to the cube view that has been pre-evaluated for data. I have seen cube views return over 30k records in an acceptable amount of time, where out of the box cube view approaches rendered times and paging that made the report unusable.
It is important to note that FDX is going to retrieve base level data unit detail. It does not return parent level accounts and other UD dimensions on its own.
The Platform Capabilities of So Much More
This technique can be expanded with some additions to the code to handle many challenges:
- Multiple cubes
- Subtotals and grand totals by the “expanded” dimensions.
- Sending back descriptions for the rows that trigger conditional formatting
- Sending back descriptions for the rows that warn if a report will not subtotal naturally (for example a series of entities with numbers that are in mixed local currencies and would not sum up to a USD parent).
- Resolving parents of data up the hierarchy from the base data.
Getting into the specifics of handling every case is beyond the scope of this blog, but the possibilities are endless.
Engage
Feel free to engage here, on LinkedIn, or book a demo. I am happy to delve deeper into the technical side, or tips and tricks that I learned when applying this technique.