FilterMembers using Cb#Cube

[GParreiras]

Hi All, 

Is there a way to the use cb#cube in the data buffer FilterMembers? 

I'm trying the following, but it does not work. 

Dim sourceBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(Cb#Cube:V#Periodic, [A#123.Base], [U7#Top.Base]))",)

Thanks.

[Henning · Accepted solution]

Hi, yes, that should work. Try including the data unit dimensions such as this:

Dim sourceBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(Cb#myCube:E#myEntity:C#Local:S#myScenario:T#2021M1, [A#Top.Base]))")

When I run this formula from a different cube and different scenario etc. this logs the correct data to the error log when I log the content.

(You can log the data buffer content to the error log like this:)

sourceBuffer.LogDataBuffer(api, "My data buffer content (see details below)", 100)

[Henning · Accepted solution]

Hi, yes, that should work. Try including the data unit dimensions such as this:

Dim sourceBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(Cb#myCube:E#myEntity:C#Local:S#myScenario:T#2021M1, [A#Top.Base]))")

When I run this formula from a different cube and different scenario etc. this logs the correct data to the error log when I log the content.

(You can log the data buffer content to the error log like this:)

sourceBuffer.LogDataBuffer(api, "My data buffer content (see details below)", 100)

[GParreiras]

Hi @Henning, Thanks!

In the end, I was able to filter the data buffer using the cube, but I had to remove the account filter to make it work.

Here is the scenario: Cube1 has the Acc1 dimension, and Cube2 has the Acc2 dimension. When initiating the DM job from Cube2 and filtering the data buffer with FilterMembers(Cb#Cube1, [A#Acc1.Base], [U7#Top.Base]), it did not work. However, when I removed the account filter, it worked.

That I'm still confused about and trying to understand why.

[TheJonG · Accepted solution]

Hi - the situation you described is actually expected behavior - let me explain why:

When you run the DM Step with Cube2 specified in the Data Unit - the Finance Engine will cache (bring into memory) the dimensions which are relevant to that Cube which would be Acc2. When you retrieve a Data Buffer from a Data Unit outside of the one specified in the DM step and a different dimension is assigned (Acc1), the filter will not work because the Finance Engine does not have those dimensions cached. The only way to apply the filter in this case would be to remove the account filter and loop through the Data Buffer and add only the relevant account IDs to a new Data Buffer. 

Again, this is expected behavior based on my experience but could be enhanced in the future.

[GParreiras]

Hi @TheJonG,

Awesome, than you!