cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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

aformenti
Contributor

2 weeks ago

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.   

Following it is an overview of the Solution:

 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  Tot Group:

aformenti_0-1717762637348.png

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:

aformenti_1-1717762637359.png

  • 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:

aformenti_2-1717762637360.jpeg

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:

aformenti_3-1717762637364.png

 

Labels:
1 REPLY 1

aformenti
Contributor

2 weeks ago

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 > 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=<>)
					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 <> 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 <> 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

 

 

Please sign in! aformenti
Community Resources
Training and Documentation
Additional Resources