Before going into detail about why it is important to clear your target data before running an @XWRITE, I recommend reviewing the initial eCapital blog on how to properly write an @XWRITE calc script.

Missing block movement with @XWRITE

In the previous blog on this topic, a dense member was used in the calculation block.  This helps to keep runtime at a minimum for this function.  However, when a dense member is used, missing blocks will not be written from the source to the target.

CLEARDATA Before XWRITE - dense member

(click to enlarge)

If for some reason data was removed from the source and a restructure had occurred, the data set in the target will not get removed on @XWRITE.  The reason is that, similar to the DATACOPY function, the XWRITE function moves blocks from source to target.  If a restructure has occurred since the value was cleared, it may be the case that a block no longer exists at that intersection.  The function would require a blank block to be created again in the source or the data be manually cleared in the target to produce the correct results.

Options that can be used to ensure data integrity

Option 1: Use a Sparse member in the calculation block.

The example script below shows how we rotated the dense member [Sales] into the FIX and moved the sparse member [Budget] into the calc block.

CLEARDATA Before XWRITE - example script

(click to enlarge)

This will clear the data in the target even when the block is missing the in source since we’re explicitly solving for a sparse member.

Option 2: Clear the data in the target before @XWRITE

This option is as simple as running a script to clear the data in the target before moving it from the source.  If the FIX is setup properly in the source application on the clear script, the runtime of the clear should be much shorter than the @XWRITE itself.  Below is how the clear would be created in the example we have been using.

CLEARDATA Before XWRITE - running a clear script

(click to enlarge)

Since the target zone for the @XWRITE is base level of Year, Market, and Product, those are in the fix.  Since the @XWRITE is also moving data to Budget, Sales and No Entity are the other members in the zone so they are also included in the FIX.  In that FIX we will just clear all blocks.  This script will complete very quickly and will help ensure the source and the target stay in sync.

Of the two options, I recommend clearing prior to running XWRITE calcs rather than solving for sparse members.  As stated previously, the script can take much longer to complete if solving for sparse.  Depending on the shape of your outline, calcs that take seconds to complete when using a dense member in the block while the same script with sparse can take hours.  In smaller outlines, this might not be noticeable, but future changes in metadata could cause the runtimes to increase drastically.  Because of this, I recommend using CLEARDATA as opposed to solving for sparse.

Have more questions? We can help:

Jon Harvey

Jon has been a part of the Oracle team at eCapital Advisors since 2009 and is passionate about educating and teaching clients and team members how to apply the technology to drive business value within organizations. An Oracle Certified Expert in both Hyperion Planning and Essbase, Jon oversees the technical aspects of EPM project delivery and managed services. In addition, he’s responsible for ensuring the continued growth of the practice’s technical capabilities and serves as the primary interface between eCapital and the Oracle user community.

Comments are closed.