FIX, REMOVE and CLEARBLOCK, You may end up with No data ;)

December 09, 2014

The FIX…ENDFIX command block restricts database calculations to a subset of the database. All commands nested between the FIX and ENDFIX statements are restricted to the specified database subset.

What I know about FIX is, it takes the union of the members which are in different FIX or (Hypothetically) are repeated in the same fix, so even if you mention FIX("E1","E1","E1") it will work on "E1" and just once, not thrice!.

Say you have fully populated data cube and you need to delete the data for one Entity(say E1), the fastest thing you will probably write is:

FIX(@REMOVE(@RELATIVE("Entity",0), "E1")

CLEARBLOCK ALL;

ENDFIX

Yes, its done! This will remove the data for all the level 0 entities but not for E1. This will happen assuming there is no alternate hierarchy.

------------------------------------------------------------------------------------------------------------

Lets go in detail: the hierarchy I am going to use for this is following:

Here I need to delete the data for E1. I wrote below script (I should have taken @relative of "Main" instead of "Entity").

FIX(@REMOVE(@RELATIVE("Entity",0),"E1"))

CLEARBLOCK ALL;

ENDFIX

Executing above script has deleted all data, including E1 as well. Now, lets find the reason why. Here are the essbase logs after executing the above:

Clearing all data blocks from [(ALL)] partition with fixed members [Entity(E2, E3, E4, E5, E6, E1, E2, E3, E1, E2)]

Even if we have removed E1 from the first list (info: Remove function takes two arguments, two lists, and removes the members from the first list which are present in the second list), its still considering E1 in the fix and will clear the blocks of that! What Essbase has done here is:

First list (@RELATIVE("Entity",0)) gave: E1,E2, E3, E4, E5, E6, E1, E2, E3, E1, E2

Second one gave: E1

Now, because of Remove function it has removed E1 from the fist list(but just one occurrence not all!) and here is the result which you can see in the logs as well:

E2, E3, E4, E5, E6, E1, E2, E3, E1, E2

While writing the script I thought that it will remove all the occurrences of E1 from the first list. However, Base Members and Shared Members are treated differently by essbase, it has removed only one occurance and deleted the data for E1 as well and I was doomed!

Now, I wrote the members directly in the List1 (same set of member which I got earlier using @relative zero of entity):

FIX(@REMOVE(@LIST("E1","E2","E3","E4","E5","E6","E1","E2","E1","E2","E3"),@LIST("E1")))

CLEARBLOCK ALL;

ENDFIX

What you will find the logs is:

Clearing all data blocks from [(ALL)] partition with fixed members [Entity(E2, E3, E4, E5,E6)]

So this time Essbase has removed all the occurrences of E1 from the first list!!!

Now Lets do this:

FIX(@REMOVE(@RELATIVE("Entity",0),@LIST(@RELATIVE("Shared1",0), @RELATIVE("Shared2",0), "E1")))

CLEARBLOCK ALL;

ENDFIX

List 1: E1,E2,E3,E4,E5,E6,E1(Shared),E2(Shared),E3(Shared),E1(Shared),E2(Shared)

List 2: E1(Shared),E2(Shared),E3(Shared),E1(Shared),E2(Shared),E1

Here is a part of the logs:

Clearing all data blocks from [(ALL)] partition with fixed members [Entity(E2, E3, E4, E5, E6)]

Here is the conclusion: Base Members and Shared Members are treated differently by essbase. Even though when we look in the logs we cant distinguish them by name, Essbase shows them with the same name.

Little more to add:

Even if Essbase shows the member name many times in the Fix but when Essbase actually performs operations it doesn't visit the same block twice. To prove this:

FIX(@REMOVE(@RELATIVE("Entity",0),"E1"))

"A1"=1;

ENDFIX

Here are the logs after the execution of this:

[Tue Dec 09 05:49:02 2014]Local/R_Test/Rdb/admin@Native Directory/10348/Info(1012668)

Calculating [ Account(A1)] with fixed members [Entity(E2, E3, E4, E5, E6, E1, E2, E3, E1, E2)]