Forum Discussion

ChristianW's avatar
ChristianW
Valued Contributor
4 years ago

Copy data from another cube (with different dimensionality)

Is there an easy way, to copy data from one cube to another (with different dimensionality)
Finance
  • ChristianW's avatar
    ChristianW
    4 years ago

    Yes, there are some function in the financial business rules api to support this:

    api.Data.ConvertDataBuffer
api.Data.ConvertDataBufferExtendedMembers

    With the first function, you define the mapping between the cubes on your own, the second one uses the extended dimension definition for the mapping.

  • ChristianW's avatar
    ChristianW
    4 years ago

    Here is a sample for ConvertDataBufferExtendedMembers

    Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("cb#CubeName:A#All")
targetDataBuffer = api.Data.ConvertDataBufferExtendedMembers("CubeName", "ScenarioName", sourceDataBuffer)
Dim expDestInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo($"C#{api.Pov.Cons.Name}")
	
api.Data.SetDataBuffer(targetDataBuffer , expDestInfo)

    Here is a sample for ConvertDataBuffer

    Dim dataBufferConvInfo As New DataBufferConversionInfo
	
Dim dataBufferConvDimInfo As New DataBufferConvDimInfo
Dim dataBufferMapItem As New DataBufferConvMapItem(DataBufferConvType.MapSourceMembersToFirstDestMember, "UD1#Top.Base",  "UD1#None", 1)
dataBufferConvDimInfo.MapItems.add(dataBufferMapItem)
dataBufferConvInfo.UD1Info = dataBufferConvDimInfo

dataBufferConvDimInfo = New DataBufferConvDimInfo()
dataBufferMapItem = New DataBufferConvMapItem(DataBufferConvType.MapSourceMembersToFirstDestMember, "UD2#Top.Base",  "UD2#None", 1)
dataBufferConvInfo.UD2Info = dataBufferConvDimInfo
	
Dim sourceDb As DataBuffer = api.Data.GetDataBufferUsingFormula("cb#CubeName:A#All")
Dim targetDb As databuffer = api.Data.ConvertDataBuffer("CubeName", "ScenarioName", dataBufferConvInfo, sourceDb)
	
Dim expDestInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo($"C#{api.Pov.Cons.Name}")
	
api.Data.SetDataBuffer(targetdb, expDestInfo)

     

ChristianW's avatar
ChristianW
Valued Contributor

Here is a sample for ConvertDataBufferExtendedMembers

Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("cb#CubeName:A#All")
targetDataBuffer = api.Data.ConvertDataBufferExtendedMembers("CubeName", "ScenarioName", sourceDataBuffer)
Dim expDestInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo($"C#{api.Pov.Cons.Name}")
	
api.Data.SetDataBuffer(targetDataBuffer , expDestInfo)

Here is a sample for ConvertDataBuffer

Dim dataBufferConvInfo As New DataBufferConversionInfo
	
Dim dataBufferConvDimInfo As New DataBufferConvDimInfo
Dim dataBufferMapItem As New DataBufferConvMapItem(DataBufferConvType.MapSourceMembersToFirstDestMember, "UD1#Top.Base",  "UD1#None", 1)
dataBufferConvDimInfo.MapItems.add(dataBufferMapItem)
dataBufferConvInfo.UD1Info = dataBufferConvDimInfo

dataBufferConvDimInfo = New DataBufferConvDimInfo()
dataBufferMapItem = New DataBufferConvMapItem(DataBufferConvType.MapSourceMembersToFirstDestMember, "UD2#Top.Base",  "UD2#None", 1)
dataBufferConvInfo.UD2Info = dataBufferConvDimInfo
	
Dim sourceDb As DataBuffer = api.Data.GetDataBufferUsingFormula("cb#CubeName:A#All")
Dim targetDb As databuffer = api.Data.ConvertDataBuffer("CubeName", "ScenarioName", dataBufferConvInfo, sourceDb)
	
Dim expDestInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo($"C#{api.Pov.Cons.Name}")
	
api.Data.SetDataBuffer(targetdb, expDestInfo)

 

LeeB's avatar
LeeB
Contributor II
3 years ago

Hi Christian,
Can this also be used to map one dimension to another?

For example, I have Legal Entities in the Entity dimension in one Cube but in another they are in UD1.   The base members in each dimension have identical Names. 

I want to copy data between the two Cubes. 

 

 

  • RafMarques's avatar
    RafMarques
    New Contributor III
    2 years ago

    Hi LeeB / FrankDK,

     

    Did you manage to find a solution for the cross dimension mapping? I also have a similar requirement but I am struggling with documentation,

     

    Thanks

    Raf

  • wesley_waldrop's avatar
    wesley_waldrop
    New Contributor
    2 years ago

    Hi Lee,

    Did you ever find a solution to your issue?  I am in need of copying data from one cube to another with different dimensionality similar to what you outlined.

    Any help would be greatly appreciated.

    Thanks.

    Wes

  • ChristianW's avatar
    ChristianW
    Valued Contributor
    2 years ago

    No, that's not how it works.

    Did you try using an Eval DataBuffer calculation for it?

    I couldn't test it, because I don't have the cubes for it, but something like this might work:

    api.data.Calculate("cb#Cube1:A#All = Eval(cb#Cube2:A#All)", AddressOf OnEvalDataBuffer)

    with an eval function like this:

    Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
	Try

		eventArgs.DataBufferResult.DataBufferCells.Clear()

		For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values	'Loop over the source cells.
				
			Dim resultCell As New DataBufferCell(sourceCell)
			Dim resultNameUd1 As String = api.Members.GetMemberName(DimType.Ud2.Id, sourceCell.DataBufferCellPk.UD2Id)
			Dim resultNameUd2 As String = api.Members.GetMemberName(DimType.Ud1.Id, sourceCell.DataBufferCellPk.UD1Id)
			
			resultCell.DataBufferCellPk.UD1Id = api.Members.GetMemberId(DimType.Ud1.Id, resultNameUd1)
			resultCell.DataBufferCellPk.UD2Id = api.Members.GetMemberId(DimType.Ud2.Id, resultNameUd2)
			
			resultCell.CellAmount = sourceCell.CellAmount
			
			eventArgs.DataBufferResult.SetCell(api.SI, resultCell, True)
						
		Next

	Catch ex As Exception
		Throw ErrorHandler.LogWrite(api.SI, New XFException(api.SI, ex))
	End Try
End Sub

    Obviously mapping using the member names repeatedly is suboptimal, but it illustrates the concept.

    Prebuilding a mapping table using a dictionary(of integer, integer) might give you a better performance.

    • wesley_waldrop's avatar
      wesley_waldrop
      New Contributor
      2 years ago

      Thanks Christian, that did the trick.

       

      Wes