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.
(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.
(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.
(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:
- Contact us using the form here, or call us at 952.947.9300.
- Drowning in data but parched for predictive insights? Arrange a free half-day analytics health check.
- Talk to our experts face to face at one of our upcoming industry events.
