An excerpt from Chapter 4 of OneStream Planning: The Why, How and When by Cameron Lackpour and Celvin Kattookaran (due December 20 2021)
Relational Blending is just what it says: it is a way to blend relational data with Cube data. OneStream provides functions to support that blend of data Types.
Relational Blending API Methods
The following Methods query/calculate blend data:
What information is being blended – and where that data is located – drives the function selection.
Our use case is blending external table-based text information with Cube data, so the function to use is GetCustomBlendDataTableUsingCurrentPOV…
Public Function GetCustomBlendDataTableUsingCurrentPOV(ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal sourceDBLocation As String, ByVal sourceSQL) As DataTable
After choosing the correct function, we need to find out what cache level is needed for the formula.
The following cache levels are available for Relational blending API.
- Custom – only use with the custom blend API because, in that context, the custom SQL drives the cache
Why is cache level important? Let me explain through a daily chore that anyone with young children can relate to. (For those of you who do not have children or who have mercifully forgotten the following because of the passage of time, you have missed out on some not-entirely-minor physical pain, but the idea is easy to grasp.)
A Childhood Analogy
Those Little Plastic Blocks From Denmark are a wonderful educational toy. Children love them because they are a blank slate upon which they can build; parents look on approvingly because they are not a video game or television. After a while, however, said parents might start thinking those snap-together building blocks are evil because of their ability to camouflage their location and their little sharp (and strong) corners when stepped upon with bare feet. Those little buggers hide and hurt in places where it hurts the most, clog vacuum cleaners, and then add insult to not-inconsiderable injury by making the victims dig them out from the dust pile within the vacuum bag. Ugh.
A way to sort and segregate and select these blocks from Hell would be nice. Sadistic (and hopelessly unrealistic) parents, of course, turn immediately to the idea of having their kids clean them up and sort them by color. Yellow bricks go and stay in the yellow box, red ones in the red box, and so on. Once sorted, it is easier to find a brick instead of dumping them all onto the floor, although there will, of course, remain the usual moderate level of natural anarchy and entropy 😊.
If the plastic building block gods smile upon you, those wee little rascals could sort the bricks by size/shape (getting them to colored boxes itself is a big deal). It is now easier to find a square yellow brick from the color-size/shape box compared to dumping the whole colored box.
Relational blend caches are similar to those boxes except for the plastic, the physical pain, and the unhappy children who have strangely satisfied parents.
Think of WFProfileScenarioTime, WFProfileScenarioEntity, and WFProfileScenarioAccount as the colored boxes. Supplemental information needed from Stage based on Profile, Scenario, and Time/Entity/Account can be stored in separate boxes and queried there instead of searching the whole staging area.
Similarly, WFProfileScenarioTimeEntityAccount is our color-size/shape sorted box. It is a more granular level where you can easily get to the supplemental items.
It is important to pick the right cache level, as it is directly related to the performance of the query (and directly related to the pain it can cause, as with the case of those damnable little plastic bricks). When dealing with the relational world, the less you query the source tables, the better the performance, because of the time OneStream spends creating and closing connections, on a cell-by-cell basis, when it queries relational data in a gird.
In our example, we are looking for the details of a State, Account, and Product from the relational store, then the choice of cache is only one. You can only use Custom as your cache if you are using a custom blend. When using the CustomBlendDataTable Method, the SQL used to get the data defines the cache for you.
* * * * *
OneStream Planning: The Why, How and When by Cameron Lackpour and Celvin Kattookaran