Copy data from another cube (with different dimensionality)

ChristianW
Valued Contributor

Is there an easy way, to copy data from one cube to another (with different dimensionality)

2 ACCEPTED SOLUTIONS

ChristianW
Valued Contributor

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.

View solution in original post

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)

 

View solution in original post

8 REPLIES 8

ChristianW
Valued Contributor

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

 

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?

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

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

 

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
New Contributor II

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

FrankDK
New Contributor III

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