I think the differences in usage are laid out pretty clearly in the Technical Reference entries for CLEARBLOCK and CLEARDATA. Although there is one notable error in the documentation - CLEARDATA on a purely sparse combination does actually remove blocks from the database. See Cameron's Blog For Essbase Hackers: Stupid Programming Tricks #6
I'm not totally clear what you want to know, though. If you're asking how a pair of snippets like the following differ (functionally)...
FIX ("New York")
CLEARDATA "New York";
...the answer (per Cameron's blog post) is that they don't. And that's true if you choose a dense member too.
My question is....Is better use clearblock?? why??
>>My question is....Is better use clearblock?? why??
^^^Do you want to remove blocks or not? Despite what I blogged about, CLEARDATA is not (and doesn't, when the dimension is dense) remove blocks. You might (quite likely) do want to keep those blocks around for allocation/calculation purposes.
If you use CLEARBLOCK you will remove the blocks as per the Tech Ref:
"Sets cell values to #MISSING, and if all the cells are empty or #MISSING, removes the block."
Just remember that despite the documentation, CLEARDATA can remove blocks if pointed at a sparse dimension member.
As for whether one is better than the other given the above guidance on removing blocks, it's my favorite (and most constant) Essbase answer:
My experience the only time I used CLEARBLOCK was when there was a block error in the cube.
I was able to narrow it down to the blocks that were causing the issue. Using the CLEARBLOCK managed to resolve it, CLEARDATA didn't.
However for all of my calculations where I need to clear data I still use CLEARDATA.
We should start a poll....
Just curious to know what do you mean by "My experience the only time I used CLEARBLOCK was when there was a block error in the cube.". What kind of Block error you are talking about?
I think the error was something to do with the Invalid Block Headers (IBH)
Again I am not stating that the CLEARBLOCK function will always resolve the above error.
But it did when I had the issue with 2 of my clients. I had to keep redefining my FIX statement in my calculation to trace the blocks that were potenially causing the error.