*CLEAR_DESTINATION

Allowed uses: By Commit, SQL

*CLEAR_DESTINATION

*DESTINATION {DimensionName1}={MemberSet1}

*DESTINATION {DimensionName2}={MemberSet2}

Clears all records in the destination region, defined by one or more *DESTINATION instructions.

In most cases there is no need to perform a clear of the destination area when re-executing a modeling logic that uses SQL queries. SQL logics, in fact, base their computation on the existence of records in the fact table, regardless of the value assigned to these records. Since values that have been reset to zero maintain records in the fact table, the execution of the SQL logic ensures the correct handling of any value, including those that have been set to zero.

This may not be true in all cases. For example, if a value is set to zero and, before the SQL rules are executed, an administrator performs a compression of the fact table, the zeroed-out records are lost. This could lead to a situation where the rule does not clear the records it generated in a prior pass, until you reenter some value in the values that were set to zero. This situation is unlikely to happen when the rule is a default rule (which is executed at the same time data is entered). However, it could happen with rules that are executed in a batch mode (such as eliminations, allocations, or consolidations).

To control these situations, the administrator can make use of a couple of instructions that enforces a clear of all records existing in the destination region at the time the logic is executed.

Note Note

These instructions may lead to deletion of the input data, if used incorrectly. A good understanding of their behavior is required, to avoid the risk of serious losses of data in the database.

End of the note.

The instructions to use are:

*CLEAR_DESTINATION

*DESTINATION {DimensionName1}={MemberSet1}

*DESTINATION {DimensionName2}={MemberSet2} …

The instruction CLEAR_DESTINATION activates the clear mechanism. If not present, the rules do not try to perform any clear.

The second instruction, the DESTINATION instruction, is optional. However, when CLEAR_DESTINATION is used, the DESTINATION instruction MUST be used for ALL the dimensions for which you want to be SURE that the correct region is cleared. If not used, the program tries to automatically decide what to clear in each dimension, and this, in some cases, might be incorrect. Following is an explanation that clarifies what could happen:

Case 1: The source and destination regions are the same. For example, the category is ACTUAL for both the source and the destination regions. The category the program clears is definitely ACTUAL. For this dimension, there is no need to specify anything.

Case 2: The source and destination regions are different, as specified by a XDIM_MEMBER instruction.

Example Example

*XDIM_MEMBER DATASRC=LC TO ELIM

End of the example.

In this case the logic knows that the destination Datasrc member can only be ELIM. The Datasrc the program clears is definitely ELIM. In this case too, there is no need to specify anything.

Case 3: The source and destination members are different, as defined by one or more *REC( ) instructions.

This is the case where the rules might have an issue in deciding what to clear. The REC( ) instruction, in fact, has a great deal of power in deciding where to write its output, and could do it on multiple dimensions at the same time. For example, it could say:

*REC(CURRENCY=”EURO”)

*REC(ACCOUNT=ACCOUNT.PLUGACCOUNT)

*REC(ENTITY=”IC_%ENTITY%”,CURRENCY=ENTITY.CURR)

As a result, for all the dimensions where the destination region is defined by one or more *REC instructions, it is MANDATORY for you to explicitly restrict the region that are to be cleared.

Example Example

If a translation rule looks like this:

*WHEN ACCOUNT.RATETYPE

*IS "AVG","END"

*REC(FACTOR=LOOKUP(EURO)/LOOKUP(SOURCECURR),CURRENCY="EURO")

*ELSE

*REC(CURRENCY="EURO")

*ENDWHEN

The instructions to use are:

*CLEAR_DESTINATION

*DESTINATION CURRENCY=EURO

If the DESTINATION for the currency dimension is not specified, the destination region for the currency is:

LC,EURO

which would result in the loss of all input data.

End of the example.

The members specified in the DESTINATION region can be a list of comma-delimited members (example: *DESTINATION CURRENCY=EURO,USD) or a member set defined with an MDX expression,

Example Example

*DESTINATION

CURRENCY=filter([CURRENCY].members,[CURRENCY].properties("REPORTING"="Y")

End of the example.

A destination region is created by the rules even when no CLEAR_DESTINATION instruction is used. This is done when the CALCULATE_DIFFERENCE option is active, to calculate the difference between the newly calculated values and the values existing in the database. In this situation, having a destination region that could be broader than needed cannot do any harm, and you do not need to worry. However, the instruction DESTINATION could still be used, even if no clear_destination instruction is used, simply to optimize the size of the destination region to query, with some benefit to the performance and memory footprint of the logic execution.

The instruction *DESTINATION also supports the not equal to operator with the syntax: *DESTINATION<>{MemberSet}

This operator can be handy to pass to the SQL query smaller lists of valid members, which is more efficiently parsed by the Microsoft SQL engine.