How to Dynamically Report on Extended Members from the Top Consolidation Cube?

Question

Depending on the circumstances, you could find yourself into a situation where you need to report numbers on Extended dimension Members from the Top Consolidation Cube. In a Vertical Extensibility Cube set up this gets tricky since the data is only available at the lower cube levels.
To work-around this challenge (considering the limitations exposed below), I have created a dynamic calculation Business Rule that allows you to achieve that by leveraging a specific UD8 Dynamic calculation Member.&nbsp; &nbsp;
Following it is an overview of the Solution:
&nbsp;Problem Statement:

When using Cube Vertical Extensibility, reporting from Tot Group cube is not possible for those dimensions that have been extended.

In the example, UD1 is only available at Summary Level for &nbsp;Tot Group:

Solution Overview:

UD8 Dynamic Calculation to derive the Extended members from the Base Entities consolidating to the Parent Entity Level (E#Tot Group). Data is dynamically retrieved from the base entities and translated on the fly based on the Parent/Child Entity relationships:

Works with any extended Account Type Dimension (A#, F#, U1#-U7#) without having to modify the Business Rule.
Dynamic Calculation driven from the UD8 Text1 field:

Considerations:

Solution aggregates and translates on the fly the base data without:

Eliminations
% of Consolidation (It could potentially be incorporated into the solution)

Suitable for reporting/analysis being done at O#BeforeElim.
When used in large Cube View/Quick Views queries could create slow retrieval performance.

Technical Set Up:

Create the UD8 Member and Define the Text 1 property referencing the Dimension Tag you’d like to report on (Ie: DimTag=[U1#]).
Add the following Member Formula within the UD8 Member:

Dim referenceBR As New OneStream.BusinessRule.Finance.XF_DynamicExtensibilityReporting.MainClass
'Return referenceBR.DynamicProductReporting(si, globals, api) 
Dim Value As Decimal = referenceBR.DynamicExtensibilityReporting(si, globals, api) 
If Not Value.Equals(0)
	Return Value
Else
	Return Nothing
End If

Create a new Finance Business Rule, make sure that the Business Rule is set to Global:

&nbsp;

aformenti · Answer

Here is the main Finance Business Rule for the Dynamic Calc which is referenced from the Member Formula:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports System.Diagnostics
Imports System.Text.RegularExpressions
Imports System.Threading
Imports System.Threading.Tasks
Imports System.Collections.Concurrent

Namespace OneStream.BusinessRule.Finance.XF_DynamicExtensibilityReporting
	Public Class MainClass
		
		#Region "Not in use"
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try
				
				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
		#End Region 
		
		Public Function DynamicExtensibilityReporting (ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi) As Decimal
		
			Try

If api.Entity.HasChildren AndAlso api.Pov.Cube.CubeReferences.Values.Count &gt; 0 'Dynamic calc is run at Parent Entity Level for the TOP Consolidation Cube
					
					Dim ParentEntity As String = api.Pov.Entity.Name
					Dim PovTime As String = api.Pov.Time.Name
					
					'Retrieve Current UD8 Text Properties:
					Dim U8Text1 As String = api.UD8.Text(api.Pov.UD8.MemberId,1,api.Pov.ScenarioTypeId,api.Pov.Time.MemberId)
	
					Dim DimTag As String = ""
					Dim FixedSourcePOV As String = ""
					
					If U8Text1.StartsWith("DimTag=",stringcomparison.CurrentCultureIgnoreCase) Then 
						DimTag = StringHelper.SplitString(U8Text1,"=")(1)
						DimTag = StringHelper.RemoveEnclosingSquareBrackets(DimTag)
					End If
				
					Dim DynamicPOV As New Dictionary (Of String, String) From {{"A#",$"{api.pov.Account.Name}"}, {"F#",$"{api.pov.Flow.Name}"}, {"O#",$"{api.pov.Origin.Name}"}, {"I#",$"{api.pov.IC.Name}"}, {"U1#",$"{api.pov.UD1.Name}"}, {"U2#",$"{api.pov.UD2.Name}"}, {"U3#",$"{api.pov.UD3.Name}"}, {"U4#",$"{api.pov.UD4.Name}"}, {"U5#",$"{api.pov.UD5.Name}"}, {"U6#",$"{api.pov.UD6.Name}"}, {"U7#",$"{api.pov.UD7.Name}"}, {"U8#","None"}}

'Gets the Source Data Buffer POV
					Dim DimSourcePov As String = Me.GetSourceDataBufferPOV(si,globals,api,DimTag,DynamicPOV) 
					
					'Creates data buffer where we store the Aggregated data for all base Entities from the given Parent Entity:
					Dim AggEntityData As New DataBUffer()

'Data is pulled from all base Entities by Extended Dim as defined in U8 Text1 (DimTag=&lt;&gt;)
					If Globals.GetObject($"{ParentEntity}{PovTime}{DimSourcePov}") Is Nothing
						Dim BaseEntityList As List(Of Member) = api.Members.GetBaseMembers(api.Pov.EntityDim.DimPk,api.Pov.Entity.MemberId)
						AggEntityData = Me.AggregateEntityDataBuffer(si,api,BaseEntityList,DimSourcePov)
						Globals.SetObject($"{ParentEntity}{PovTime}{DimSourcePov}",AggEntityData)
					Else	
						AggEntityData = globals.GetObject($"{ParentEntity}{PovTime}{DimSourcePov}")
					End If

'Defines Target CellPK to pull from the Aggregated Data Buffer - Pull cell by Extended Dim Member:
					Dim GetTargetCellPK As DataBufferCellPk = Me.GetTargetDataBufferCellPk(si,globals,api,DimTag)
					
					If Not AggEntityData.GetCell(si,GetTargetCellPK) Is Nothing	'If Target Cell Exists within AggDataBuffer we pick it and return the Cell Amount:

Return AggEntityData.GetCell(si,GetTargetCellPK).CellAmount
						
					Else 'If Cell Does not exist within the Data Buffer, aggregate values through Extended Dim:
						Dim DoesBufferExist As Boolean = False
						api.Data.FormulaVariables.GetDataBuffer($"{ParentEntity}{PovTime}AggEntityData",DoesBufferExist)
						If Not DoesBufferExist Then 
							api.Data.FormulaVariables.SetDataBufferVariable($"{ParentEntity}{PovTime}AggEntityData",AggEntityData,True)
						End If 	
						Dim FilteredAggEntityData As DataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(${ParentEntity}{PovTime}AggEntityData, [{DimTag}{DynamicPOV(DimTag)}.base])") 'ie: DataBufferFilter = [U1#api.pov.UD1.name.base]
						
						Dim TotalCellAmount As Decimal = Decimal.Zero
						TotalCellAmount += Aggregate DriverCell In FilteredAggEntityData.DataBufferCells.Values Into Sum(DriverCell.CellAmount)

Return TotalCellAmount
					End If

Else 'If its base Entity, return base product cell: 
					
					Return api.Data.GetDataCell("U8#None").CellAmount	
					
				End If

Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
			
		End Function
		
		Public Function AggregateEntityDataBuffer(ByVal si As SessionInfo, ByVal api As FinanceRulesApi, ByVal DataUnitList As List(Of Member), ByVal SourceFormulaString As String, ByVal Optional DataBufferFilters As String = "" ) As DataBUffer

'  Aggregates All Data Units (Entities) based in a DataUnitList and provided Filters
		'  
		'  Version		Date		Author							Comment
		'  -------------------------------------------------------------------------------------------------------------
		'  V1.0			25-04-2024	A.Formenti 						Initial Version
		'  
		'  Changes:
		'  
		'______________________________________________________________________________________________________________

Try
				
				Dim ResultBuffer As New DataBuffer()

Dim TargetCurrency As Currency = api.Entity.GetLocalCurrency(api.Pov.Entity.MemberId)
				
				For Each CalcEntity As Member In DataUnitList

'Get the FX Rate based on the Entity and Account POV:
					Dim SourceCurrency As Currency = api.Entity.GetLocalCurrency(CalcEntity.MemberId)

Dim rateFx As Decimal = 1
					
					Dim rateType As New FxRateType
					Dim ruleType As New FxRuleType

If sourceCurrency.Id &lt;&gt; targetCurrency.Id Then
						Dim accountType As String = api.Account.GetAccountType(api.Pov.Account.MemberId).Name
						If accountType.XFEqualsIgnoreCase("Asset") OrElse accountType.XFEqualsIgnoreCase("Liability")
							rateType = api.FxRates.GetFxRateTypeForAssetLiability(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
							ruleType = api.FxRates.GetFxRuleTypeForAssetLiability(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
						Else If accountType.XFEqualsIgnoreCase("Revenue") OrElse accountType.XFEqualsIgnoreCase("Expense")
							rateType = api.FxRates.GetFxRateTypeForRevenueExp(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
							ruleType = api.FxRates.GetFxRuleTypeForRevenueExp(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
						End If 
						rateFx = api.FxRates.GetCalculatedFxRate(rateType, DimConstants.DefaultPOV, DimConstants.DefaultPOV, sourceCurrency.Id, targetCurrency.Id)	
					End If
					api.Data.FormulaVariables.SetDecimalVariable("FXRate",rateFx)
					
					Dim SourceDataBuffer As New DataBuffer
					Dim AggDataBuffer As New DataBuffer

If api.Pov.View.Name.XFEqualsIgnoreCase("YTD") AndAlso ruleType.Name.XFEqualsIgnoreCase("Periodic") 'If View is YTD and Rule Type Periodic - Apply Periodic Translation
						
						If sourceCurrency.Id &lt;&gt; targetCurrency.Id Then 'If needs translation, translate on the fly with Periodic Method:
							Dim TimeList As List (Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Pov.TimeDim.DimPk, $"T#{api.pov.time.name}.AllPriorInYearInclusive")
							For Each sTime As MemberInfo In TimeList
								rateFx = api.FxRates.GetCalculatedFxRate(rateType, DimConstants.DefaultPOV, sTime.Member.MemberId, sourceCurrency.Id, targetCurrency.Id)
								api.Data.FormulaVariables.SetDecimalVariable("FXRate",rateFx)
								Dim SourceDataBufferByTime As DataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(RemoveZeros(V#Periodic:T#{sTime.Member.Name}:E#{CalcEntity.Name}:P#[{api.Pov.Parent.name}]:C#Local:{SourceFormulaString}) * $FXRate, {DataBufferFilters})",,True)	
								'Converts Data Buffer to get Aggregated Extended Members:
								Dim AggDataBufferByTime As DataBuffer = api.Data.ConvertDataBufferExtendedMembers(api.Pov.Cube.name,CalcEntity.Name,api.Pov.Scenario.Name,SourceDataBufferByTime)
								
								'Accumulates both Base and Extended Members Data Buffers
								For Each AggCell As DataBufferCell In AggDataBufferByTime.DataBufferCells.Values
									 If Not SourceDataBufferByTime.DataBufferCells.ContainsKey(AggCell.DataBufferCellPk)
										 SourceDataBufferByTime.SetCell(si,AggCell)
									 End If 
								 Next AggCell
								
								'Loop through the Buffer and accumulates data by Time:
								For Each sCell As DataBufferCell In SourceDataBufferByTime.DataBufferCells.Values
									SourceDataBuffer.SetCell(si,sCell,True)
								Next sCell
								
							Next sTime
						End If 
						
					Else 'If View Is Periodic or Direct Translation:
						
						 SourceDataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(RemoveZeros(E#{CalcEntity.Name}:P#[{api.Pov.Parent.name}]:C#Local:{SourceFormulaString}) * $FXRate, {DataBufferFilters})",,True)					
						 'Converts Data Buffer to get Aggregated Extended Members:
						 AggDataBuffer = api.Data.ConvertDataBufferExtendedMembers(api.Pov.Cube.name,CalcEntity.Name,api.Pov.Scenario.Name,SourceDataBuffer)	
						 'Accumulates both Base and Extended Members Data Buffers
						 For Each AggCell As DataBufferCell In AggDataBuffer.DataBufferCells.Values
							 If Not SourceDataBuffer.DataBufferCells.ContainsKey(AggCell.DataBufferCellPk)
								 SourceDataBuffer.SetCell(si,AggCell)
							 End If 
						 Next AggCell

End If 	
					
					'Loop through the Buffer and accumulates data by Entity:
					For Each sCell As DataBufferCell In SourceDataBuffer.DataBufferCells.Values
						ResultBuffer.SetCell(si,sCell,True)
					Next sCell	
					
				Next CalcEntity

Return ResultBuffer
			
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
			
		End Function

Private Function GetSourceDataBufferPOV(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal DimTag As String, ByVal DynamicPOV As Dictionary (Of String, String)) As String  
				
			Try
				
				'  Gets the Source Data Buffer POV
				'  
				'  Version		Date		Author							Comment
				'  -------------------------------------------------------------------------------------------------------------
				'  V1.0			05-04-2024	A.Formenti 						Initial Version
				'  
				'  Changes:
				'  
				'______________________________________________________________________________________________________________

Dim DimSourcePov As String = ""
			
				'Builds Up the Source POV:
				Dim sPovBuilder As New System.Text.StringBuilder
				
				For Each sDimTag As String In DynamicPOV.keys
					If  Not sDimTag.StartsWith(DimTag, StringComparison.CurrentCultureIgnoreCase) Then 'Add all Dynamic Dims POV except for Extended Dim
						sPovBuilder.Append($":{sDimTag}{DynamicPOV(sDimTag)}")
					End If 	
				Next sDimTag
								
				'Source POV:
				DimSourcePov = sPovBuilder.Remove(0,1).ToString
				globals.SetObject($"GetSourcePOV_{api.pov.UD8.Name}",DimSourcePov)

Return DimSourcePov

Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
		End Try
		
	End Function

Private Function GetTargetDataBufferCellPk(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal DimTag As String) As DataBufferCellPk  
				
			Try
				
				'  Gets the Target DataBufferCellPK
				'  
				'  Version		Date		Author							Comment
				'  -------------------------------------------------------------------------------------------------------------
				'  V1.0			05-04-2024	A.Formenti 						Initial Version
				'  
				'  Changes:
				'  
				'______________________________________________________________________________________________________________

Dim TargetBufferCellPk As New DataBufferCellPk

'By Default all members pulled from XFCommon:
				TargetBufferCellPk.SetMembers(api,"XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon")
					
				'Extended dim to be pulled from the api.pov:
				If DimTag.XFEqualsIgnoreCase("A#") Then 
					TargetBufferCellPk.AccountId = api.Pov.Account.MemberId
				Else If DimTag.XFEqualsIgnoreCase("F#") Then
					TargetBufferCellPk.AccountId = api.Pov.Flow.MemberId
				Else If DimTag.XFEqualsIgnoreCase("O#") Then
					TargetBufferCellPk.AccountId = api.Pov.Origin.MemberId
				Else If DimTag.XFEqualsIgnoreCase("I#") Then
					TargetBufferCellPk.AccountId = api.Pov.IC.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U1#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD1.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U2#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD2.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U3#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD3.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U4#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD4.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U5#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD5.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U6#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD6.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U7#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD7.MemberId
				End If

Return TargetBufferCellPk

Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
		End Try
		
	End Function

End Class
End Namespace

&nbsp;
&nbsp;

jwagner · Answer

This is awesome @aforementi, I was playing around with this today and noticed that base members seem to show data but intermediary parents between cubes show no aggregation or data for them. I'm curious if you know a work around for this.
The concept I am describing is outlined below. I basically would love a way to be able to fill in the red row.

Forum Discussion

How to Dynamically Report on Extended Members from the Top Consolidation Cube?

2 Replies

Related Content

The Dynamic Grid Dashboard Component

Tech Talks After Hours: Dynamic Cube Services - Dynamic Dimensions

Consolidation Vs Aggregation

The Consolidation Dimension

Tech Talks: Dynamic Cube Services