10-20-2021
07:21 AM
- last edited on
05-02-2023
10:18 AM
by
JackLacava
Is there an easy way, to copy data from one cube to another (with different dimensionality)
Solved! Go to Solution.
10-20-2021 07:26 AM
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.
10-20-2021 08:13 AM
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)
10-20-2021 07:26 AM
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.
10-20-2021 08:13 AM
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)
05-12-2022 12:26 PM
Hi Christian,
I am trying to run the ConvertDataBuffer but receive an error when executing the Data Management job. The error says "Error processing script 'C#USD'." I have tried updating $"C#{api.Pov.Cons.Name}" to reference C#Local, but that still errors out. Did you run into this error as well?
05-13-2022 11:08 AM
Hi
Consolidation isn't a databuffer dimension but a dataunit dimension. So you only can set it to the api.pov.cons.name member of a financial business rule. So if api.pov.cons.name is 'elimination' and you set the destination info to 'local', or 'USD', you will get an error message.
So if you embedded the code in a member formula or cube business rule, you're not only calculating it on local, but also during translation, elimination, or share calculation and it will trigger an error.
I hope this helps
Cheers
05-18-2022 07:38 AM - edited 05-18-2022 07:39 AM
As an alternative to
api.Data.SetDataBuffer(targetdb, expDestInfo)
you can use these lines, it make the pov situation obvious
Dim povString As String = $"E#[{api.Pov.Entity.Name}]:C#{api.Pov.Cons.Name}:S#[{api.Pov.Scenario.Name}]:T#{api.Pov.Time.Name}:V#{api.Pov.View.Name}"
'Save the data buffer
api.Data.FormulaVariables.SetDataBufferVariable("resultBuffer", resultBuffer, True)
api.Data.Calculate(povString & " = $resultBuffer", True)
Cheers
10-12-2022 07:30 AM
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.
02-06-2023 08:18 AM
07-03-2023 02:57 PM
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
07-04-2023 08:47 AM
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.
07-06-2023 04:03 PM
Thanks Christian, that did the trick.
Wes
10-12-2022 07:57 AM
Good stuff - but where do we find more documentation on these functions? As @LeeB also ask's, I need to map from one Ud to another UD in the target cube. Seems there's an option to use MapSourceMembersToDestMembers, but how this works is not clear. /Cheers