What is Relational Blending? You can read a higher-level overview of Relational Blending here. I think of relational blending as “Supporting detail on steroids,” that is the only relatable feature in EPM world.
In this blog post, we are going more in-depth for a fuller understanding of what happens behind the scene.
Since blending needs a data source and by virtue of it a file/table we are going to look at the same file we used for Matrix load.
The data is probably familiar to Essbase folks, yes it the very basic sample database.
If you look at this from a Supporting detail standpoint, blend data is easy to load. You are loading that just like a data load. Now if you’ve to update an individual detail, then you’ll have to load a data again.
As you can see from the file above, Products and Market attributes are present in the data file, and one could argue that there is repetition. Each data point needs representation from an attribute value. Row eight and nine are repeating the attribute values for 20_010.
Data is loaded to the cube using a data source, and below is the final result of the load.
Stage Tables and Views
Every single cell of data that gets imported into OneStream goes through a set of Tables called Stage tables.
- StageArchivesInformation
- StageAttributeData
- StageIntegrationMaps
- StageRuleGroups
- StageRuleGroupsHistory
- StageRuleProfileMembers
- StageRuleProfileMembersHistory
- StageRuleProfiles
- StageRuleProfilesHistory
- StageRules
- StageRulesHistory
- StageSourceData
- StageSummaryTargetData
- StageTargetData
- StageToFinanceLoadResult
- StageToFinanceValidationError
The ones that we are interested in are the following.
- StageAttributeData
- StageSourceData
- StageTargetData
These tables hold Attribute, Source, and Target data.
The following Stage views are also present.
- vStageRuleGroupsForProfile
- vStageRulesForGroup
- vStageSourceAndTargetData
- vStageSourceAndTargetDataWithAttributes
- vStageSummaryTargetData
The one that we are interested in is vStageSourceAndTargetDataWithAttributes. It combines data from the above mentioned three tables.
Reporting Blend API uses some functions to get the data from the view, as mentioned earlier (at least that is what I think it is doing).
Relational Blending API functions
The functions that we can use to query/calculate the blend (supporting detail) data are the following.
- GetStageBlendTextUsingCurrentPOV
- GetStageBlendText
- GetStageBlendNumberUsingCurrentPOV
- GetStageBlendNumber
- GetStageBlendDataTableUsingCurrentPOV
- GetStageBlendDataTable
- GetCustomBlendDataTableUsingCurrentPOV
- GetCustomBlendDataTable
All blend functions mentioned above use a caching mechanism for efficiency, get the data from Stage view/Custom table, and read from the cache for later operations. It is essential to get the cache level correct, or else you’ll be making multiple queries on the table.
Cache Levels
The following cache levels are available for Relational blending API.
- WfProfileScenarioTime
- WfProfileScenarioTimeEntity
- WfProfileScenarioTimeAccount
- WfProfileScenarioTimeEntityAccount
- Custom – only to use with the custom blend API, the custom SQL drives the cache
Let’s look at an example of the wrong choice of the cache level. If you have a cube view with Account in POV, we are going to use a cache level other than WfProfileScenarioTimeAccount. For instance, you cannot use Entity based cache levels as there are multiple entities involved. If the cache level used is WfProfileScenarioTime, then all the rows for the selected Workflow profile, Scenario, and Time is going to be cached (bigger cache).
For example, let’s run a SQL query to demonstrate this.
The below query mimics WfProfileScenarioTime cache. (join to WorkFlowProfileHierarchy is done so that I can use the name of the workflow instead of the GUID)
The cache generates 782-row Datatable for blend processing.
The below query mimics WfProfileScenarioTimeAccount cache.
The cache generates 176-row Datatable for blend processing, which is a much better one.
In the case above you should be using WfProfileScenarioTimeAccount cache level for the blend function.
Now that we have a full understanding of how caching works, let’s look at the rest of the pieces in blending.
All four functions have a flavor which uses the current POV and other four where you must specify the POV.
As the name implies, GetStageBlendText is used to return one of the 20 Attribute text values from the Stage View. In other words, from the Stage tables.
GetStageBlendTextUsingCurrentPOV
Public Function GetStageBlendTextUsingCurrentPOV (ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String, ByVal criteria As String, ByVal fieldToReturn As String, ByVal textOperation As BlendTextOperationTypes) As String
It needs the following
- CacheLevel – We discussed this earlier in the cache levels
- CacheName – A unique name to identify the cache. If you are using two blend functions in the formula, they cannot have the same name.
- WfProfileName – If you leave this as an Empty String, then function uses current profile. You can even use wild cards to get a profile. For instance, *.Import PLP data.
- FieldList – Comma separated list of fields used in the criteria.
- Criteria – Criteria to filter the rows from the Data Cache. I believe this acts like DataTable.Select(criteria)
- FieldToReturn – A1~20
- TextOperation – A BlendTextOperation type must be specified
Blend Text Operation Types
The following are the different BlendTextOperationTypes you can specify while using GetBlendStageText API function.
- Concatenate – Concatenation of all fieldToReturn occurrences
- ConcatenateDistinct – Concatenation of all distinct fieldToReturn occurrences
- FirstValue – Return the first found value
GetStageBlendText
Public Function GetStageBlendText (ByVal cubeName As String, ByVal entityName As String, ByVal scenarioName As String, ByVal timeName As String, ByVal accountName As String, ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String, ByVal criteria As String, ByVal fieldToReturn As String, ByVal textOperation As BlendTextOperationTypes) As String
You need to specify the POV to use in addition to the parameters for GetStageBlendTextUsingCurrentPov. The extra parameters are the following.
- CubeName
- EntityName
- ScenarioName
- TimeName
- AccountName
GetStageBlendNumberUsingCurrentPOV
Public Function GetStageBlendNumberUsingCurrentPOV(ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String, ByVal criteria As String, ByVal fieldToReturn As String, ByVal mathOperation As BlendNumericOperationTypes) As Decimal
BlendNumber is similar to BlendText function except for OperationTypes.
Blend Number Operation Types
The following are the different BlendNumberOperationTypes you can specify while using GetBlendStageNumber API function.
- Average – An average of all fieldToReturn occurrences
- AverageSkipZero – An average of all fieldToReturn occurrences without considering zeros
- Count
- CountSkipZero
- FirstValue – Return the first found value
- Max – Maximum value from all fieldToReturn occurrences
- Min – Minimum value from all fieldToReturn occurrences
- Sum – A sum of all fieldToReturn occurrences
If you want to perform other operations by going through the cachedTable you can use GetStageBlendDataTableUsingCurrentPOV and GetStageBlendDataTable functions.
They both return a data table.
GetStageBlendDataTableUsingCurrentPOV
Public Function GetStageBlendDataTableUsingCurrentPOV(ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String) As DataTable
I’m not sure why you need a fieldList for these functions since fieldList is used to apply criteria. To use GetStageBlendDataTable, you must specify the POV.
If you want to perform a blend using a custom table, then you can use GetCustomBlendDataTableUsingCurrentPOV and GetCustomBlendDataTable functions. In other words, you don’t want to load the data into Stage table. All the above mentioned six functions use Stage tables.
GetCustomBlendDataTableUsingCurrentPOV
Public Function GetCustomBlendDataTableUsingCurrentPOV(ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal sourceDBLocation As String, ByVal sourceSQL) As DataTable
- SourceDBLocation – Source Database location, this can be Application, Framework or a Named external connection
- SourceSQL – SQL statement for extracting the data.
GetCustomBlendDataTable
Public Function GetCustomBlendDataTable( (ByVal cacheLevel As BlendCacheLevelTypes, ByVal fullCacheKey as String, ByVal sourceDBLocation As String, ByVal sourceSQL) As DataTable
I’m not sure why we have two flavors of this function. They are querying from custom tables, and there may not be a POV in the custom table. Top of that the function GetCustomBlendDataTableUsingCurrentPOV is not allowing you to mention a workflow name as that is the case with all other similar functions. There could be something going behind the scenes that I’m not aware of.
That concludes our detailed look at Relational blending. I’ll cover some of these features with an example in another blog post.