We’ve always wondered how to aggregate text in the EPM world. I’ve always thought that it is nonsensical to aggregate text, till I saw the way OneStream handles this.
Text aggregation is handled using Relational Blending. It is a nice trick to keep the elements that don’t belong in a cube elsewhere and use them when it is needed.
Text and Value attributes
Before we jump into relational blending and text aggregation, we should first talk about attributes and how it is different yet similar to the EPM world.
Up until 5.1 version of OneStream attributes were something that was associated with data. I thought about this as supporting detail of Hyperion Planning on steroids. It is much easier to load the information as it must come along with the data. Now the drawback is, updating the detail needs reloading the data set.
5.1 version introduced (we’ll look at this in a different blog post) Essbase like attributes (the ones which are associated with metadata) into OneStream. You can add attributes to any user-defined (UD) dimensions. Just like how Essbase cannot add attributes to Dense dimensions, OS attributes cannot be attached to System specific dimensions (like Entity, Account,..). You can still use Text1-8 (like UDAs) for calculation purposes.
Let’s look at the file that we used for Matrix load.
The data is probably familiar to Essbase folks, yes it a very basic sample database.
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.
The requirement is to show all the attributes associated with the Market and Products in a Webform.
Webform equivalent in OneStream is a cube view, and it has more features than an EPM webform like you can custom label columns! (that alone got me sold on cube views).
To show the attributes along with data, we must be using on the reporting blend functions explained here.
I’m going to use a dimension that is not present in the cube. I created a few members that help in getting the attribute text and attribute value fields from the stage view.
As you can see from the above screenshot, these are DynamicCalc members with a member formula.
For text members, I used two different text operations to show the difference in return.
Below are the formulae in text.
' Workflow name which had the attributes
Dim wkNAme As String = "Sample_Input_Lower.Import"
If Not api.Entity.HasChildren Then ' run only for base entities
' Build the sql criteria for StageBlend
Dim criteria As New Text.StringBuilder
' UD member (transformed) from StageTargetData view
criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
' Account member (transformed) from StageTargetData view
criteria.Append("And acT = '" & api.Pov.Account.Name & "' ")
Return api.Functions.GetStageBlendTextUsingCurrentPov(BlendCacheLevelTypes.WfProfileScenarioTimeEntityAccount, "cacheNameText", wkNAme, "U1T,acT,A1,ConvertedAmount", criteria.ToString, "A1", BlendTextOperationTypes.Concatenate)
End If
In the code mentioned above, I’m using a specific Workflow name to get the attributes.
BlendTextOperation type here is Concatenate.
' Get workflow name from the one you are on
Dim wkNAme As String = brapi.Workflow.Metadata.GetProfile(si, si.WorkflowClusterPk.ProfileKey).Name
If Not api.Entity.HasChildren Then ' run only for base entities
' Build the sql criteria for StageBlend
Dim criteria As New Text.StringBuilder
' UD member (transformed) from StageTargetData view
criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
' Account member (transformed) from StageTargetData view
criteria.Append("And acT = '" & api.Pov.Account.Name & "' ")
Return api.Functions.GetStageBlendTextUsingCurrentPov(BlendCacheLevelTypes.WfProfileScenarioTimeEntityAccount, "cacheNameText2", wkNAme, "U1T,acT,A2,ConvertedAmount", criteria.ToString, "A2", BlendNumericOperationTypes.FirstValue)
End If
In the code mentioned above, I’m using the current workflow (this is just for a demonstration purpose) to get the attributes.
BlendTextOperation type used here is FirstValue.
Cube view to show the information
A cube view can be created to show the data and textual information using the members mentioned above. The interesting part is that the dimension is not part of the cube!!
Here is what I’ve selected in rows.
My first column is going to show the amount field that was loaded into the cube.
The second column is showing the package type information that is loaded to attribute column 1 (A1). I can then label the column to a different name using the Name tag.
Yes, that is a cross-dimensional operation in member selection (UD8#Member:V#Member).
If you look at the explorer window I don’t have two rows for UD8 and View dimension, it is one row with the custom label!!
There you go, we got all the attributes from the Stage table into the cube view. Notice the difference between Concatenate BlendTextOperation and FirstValue. Concatenate appends the number of occurrences of the text.
I guess there is a need for a function which out the count for both Concatenate and ConcatenateDistinct BlendTextOperations.
Now that we got the level 0 working what about the parents?
For parent entity members, we are going to re-write the formula and make use of another Blend function called GetStageBlendDataTableUsingCurrentPOV.
' Workflow name which had the attributes
Dim wkNAme As String = "Sample_Input_Lower.Import"
' Get delimiter from a parameter
Dim delimiter As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "TextBlendDelimiter")
' Get the data table
Dim stageBlendTable As DataTable = api.Functions.GetStageBlendDataTableUsingCurrentPov(BlendCacheLevelTypes.WfProfileScenarioTimeAccount, "cacheNameText1", wkNAme,"U1T,acT,A1,EtT")
If Not api.Entity.HasChildren Then ' run only for base entities
' Build the sql criteria for StageBlend
Dim criteria As New Text.StringBuilder
' UD member (transformed) from StageTargetData view
criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
' Account member (transformed) from StageTargetData view
criteria.Append("And acT = '" & api.Pov.Account.Name & "' ")
' Entity member (transformed) from StageTargetData view
criteria.Append("And EtT = '" & api.Pov.Entity.Name & "' ")
Dim stageRows As DataRow() = stageBlendTable.Select(criteria.ToString)
Dim blendText As New List(Of String)
If stageRows.Count > 0
For Each stageRow As DataRow In stageRows
blendText.Add(stageRow("A1"))
Next
Return String.Join(delimiter,blendText)
End If
Else ' Entity is a parent member
Dim blendText As New List(Of String)
' Get all base members of the entity (this is where data is loaded
For Each mbr As Member In api.Members.GetBaseMembers(api.Dimensions.GetBaseDim(DimTypeId.Entity).DimPk, api.Pov.Entity.MemberId, Nothing)
' Build the sql criteria for StageBlend
Dim criteria As New Text.StringBuilder
' UD member (transformed) from StageTargetData view
criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
' Account member (transformed) from StageTargetData view
criteria.Append("And acT = '" & api.Pov.Account.Name & "' ")
' Entity member (transformed) from StageTargetData view
criteria.Append("And EtT = '" & mbr.Name & "' ")
Dim stageRows As DataRow() = stageBlendTable.Select(criteria.ToString)
If stageRows.Count > 0
For Each stageRow As DataRow In stageRows
blendText.Add(stageRow("A1"))
Next
End If
Next
Return String.Join(delimiter,blendText)
End If
I’m also using a Literal Parameter to get the delimiter for the parent members and level 0 multiple rows for concatenation.
Line 6 is using GetStageBlendDataTableUsingCurrentPov and caching that as a data table.
If the entity is a level 0, a select statement is executed to retrieve all rows associated with the Entity, UD1, and Account combination. (line 18)
For all the rows that are present, an accumulation is performed (line 22). This behavior is mimicking the BlendTextOperations.Concatenate.
When an upper-level entity is detected, Line 30 is helping in getting all the base (level 0) members. With all the level 0 members of the entity, loop through them and perform the same operation we did for a level 0 entity member.
' Workflow name which had the attributes
Dim wkNAme As String = "Sample_Input_Lower.Import"
' Get the data table
Dim stageBlendTable As DataTable = api.Functions.GetStageBlendDataTableUsingCurrentPov(BlendCacheLevelTypes.WfProfileScenarioTimeAccount, "cacheNameValue1", wkNAme,"U1T,acT,V1,EtT")
If Not api.Entity.HasChildren Then ' run only for base entities
' Build the sql criteria for StageBlend
Dim criteria As New Text.StringBuilder
' UD member (transformed) from StageTargetData view
criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
' Account member (transformed) from StageTargetData view
criteria.Append("And acT = '" & api.Pov.Account.Name & "' ")
' Entity member (transformed) from StageTargetData view
criteria.Append("And EtT = '" & api.Pov.Entity.Name & "' ")
Dim stageRows As DataRow() = stageBlendTable.Select(criteria.ToString)
Dim blendValue As Decimal
If stageRows.Count > 0
For Each stageRow As DataRow In stageRows
blendValue += stageRow("V1")
Next
Return blendValue
End If
Else ' Entity is a parent member
Dim blendValue As Decimal
' Get all base members of the entity (this is where data is loaded)
For Each mbr As Member In api.Members.GetBaseMembers(api.Dimensions.GetBaseDim(DimTypeId.Entity).DimPk, api.Pov.Entity.MemberId, Nothing)
' Build the sql criteria for StageBlend
Dim criteria As New Text.StringBuilder
' UD member (transformed) from StageTargetData view
criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
' Account member (transformed) from StageTargetData view
criteria.Append("And acT = '" & api.Pov.Account.Name & "' ")
' Entity member (transformed) from StageTargetData view
criteria.Append("And EtT = '" & mbr.Name & "' ")
Dim stageRows As DataRow() = stageBlendTable.Select(criteria.ToString)
If stageRows.Count > 0
For Each stageRow As DataRow In stageRows
blendValue += stageRow("V1")
Next
End If
Next
Return blendValue
End If
Number accumulation is done by adding up all the rows that are found for the criteria.
Here is the result.
Keep in mind that an entity with a large number of base (level 0) members can cause some performance degradation. Also, text accumulation might get out of hand if the parent member got many base members.
With these in mind, we’ll look at a different solution using dashboards in the next blog post.
thanks for sharing nice post. This article is very helpful and interesting.
Thank you for your research and for sharing. I learned OneStream, and continue to learn so much more, from your blogs!