the #1 tip on copying and clearing data

In “tips and tricks” by Arthur van den Berg

The DATACOPY command not only copies existing data, it also copies non-existing data, effectively clearing the target.

Arthur van den Berg

This is the set-up:

  • fix on a set of members in a sparse dimension;
  • DATACOPY from one sparse dimension to another;
  • All data for the set of members will be copied to the target, including the #MISSING blocks!


Consider the following starting position (a cube with two sparse dimensions and a dense period dimension). Note that the data blocks for sparse members 1, 4, 5, 6 and 7 do not exist (#MISSING):


data copy example, start position. Note that the blocks for sparse mbr 1, 4 ,5, 6, 7 do not exist!

Now I want to copy the first 6 sparse mbrs from the source to the target using this business rule:

data copy rule

And this is the result. Note that the #MISSING blocks in the source have been “copied” over the target, effectively removing the data blocks in the target for sparse mbr 1, 4, 5 and 6:

result of the data copy, note that data has been cleared!

Amazing stuff, huh? Very useful in standard house-keeping rules.

about the author

Arthur van den Berg


I love the calculation power of Essbase and the financial problems you can solve with it. I have developed, implemented and improved dozens of EPM solutions with Essbase at its core and I love sharing what I learned with professionals that need to build and/or support these solutions.

share this post