XDIM_NOSCAN

The instructions *XDIM_NOSCAN and *NOSCAN in *CALC_DUMMY_ORG allows you to load in memory information that is only needed in a *GET( ) statement and never used in a *IS statement. For example, in a Units * Price calculation the logic might read as follows:

*XDIM_MEMBERSET ACCOUNT=UNITS, PRICE *WHEN ACCOUNT

*IS UNITS

*REC(FACTOR=GET(ACCOUNT=”PRICE”),ACCOUNT=”REVENUE”) *ENDWHEN

In such situations, users can now instruct the logic to ignore the PRICE by simply saying: *XDIM_MEMBERSET ACCOUNT=UNITS, PRICE

*XDIM_NOSCAN ACCOUNT=PRICE *WHEN *

*IS *

*REC(FACTOR=GET(ACCOUNT=”PRICE”),ACCOUNT=”REVENUE”) *ENDWHEN

This makes the scanning of the record set loaded in memory somewhat faster, as all those with account PRICE will be skipped very efficiently.

Similarly, a *NOSCAN instruction can be added to a *CALC_DUMMY_ORG structure. See *CALC_ORG. *XDIM_MAXMEMBERS - more information

Allowed uses: By Commit, MDX, SQL

*XDIM_MAXMEMBERS {dimension}= {max number of members}

This instruction breaks the query into multiple queries if the designated maximum number of dimension members is reached. Using this instruction can help performance if there are too many members in the scope of the query.

In most cases, the fastest results are obtained running the logic as one single query. However, if the number of members in a dimension is too big, the performance can deteriorate significantly. In this case it may be preferable to break the execution in multiple queries. This can be accomplished using the

*XDIM_MAXMEMBERS instruction in the logic.

Example: *XDIM_MAXMEMBERS Entity = 50

This instruction breaks the query into multiple queries of no more than 50 entities each, in case the entities to process exceed the limit of 50 members.

The selected dimension can be any one, including a dimension explicitly mentioned in the passed region. The default for this option is, in MDX rules, one entity per query. In other words, if you do not specify any value for this option, the rules module automatically generates one separate MDX query for each entity being processed.

*XDIM_MAXMEMBERS Entity = 0 // unlimited number of entities

Important: the maximum number of members can be specified for up to TWO dimensions, like in these examples:

*XDIM_MAXMEMBERS Entity = 50 *XDIM_MAXMEMBERS Time = 1 Or..

*XDIM_MAXMEMBERS Entity = 50, PRODUCT=100 *XDIM_MEMBER - more information

Allowed uses: By Commit (Global in the Formula script of a DTS task), MDX, SQL *XDIM_MEMBER {dimension}={member} [TO {member}]

This instruction is similar to the *XDIM_MEMBERSET instruction, but, while it only supports ONE member to be passed for the specified dimension, it permits you to specify a different destination member into which the results of the rules execution must be written.

Example:

*XDIM_MEMBER CATEGORY=ACTUAL TO BUDGET

The above statement forces the logic to be executed reading the desired values from the ACTUAL category, but writes the results into the BUDGET category.

Multiple DIM_MEMBER instructions can be entered in the same rule, like in this example: *DIM_MEMBER DATASRC=INPUT TO ELIM

*DIM_MEMBER PARENTDIM=NONE TO GROUP1

This feature can be used to simplify some rules expressions. For example this rule... #ACC1 = ([ACCOUNT].[X],[CATEGORY].[BUDGET])

#ACC2 = ([ACCOUNT].[Y],[CATEGORY].[BUDGET]) #ACC3 = ([ACCOUNT].[Z],[CATEGORY].[BUDGET]) …could become:

*DIM_MEMBER CATEGORY=ACTUAL TO BUDGET #ACC1 = [ACCOUNT].[X]

#ACC2 = [ACCOUNT].[Y] #ACC3 = [ACCOUNT].[Z]

This instruction can also be useful when the QUERY_TYPE=2 is used. In such case, if multiple dimensions must be nested on rows, a NonEmptyCrossJoin query could result, and it may be importa nt to run the query directly from the data region containing the source values, in order not to miss some of them in the calculation (see the MDX language documentation for an explanation of the NonEmptyCrossJoin function). *XDIM_MEMBERSET - more information

Allowed uses: By Commit (Global in the Formula script of a DTS task), MDX, SQL *XDIM_MEMBERSET {Dimension name} = {Members Set}

While the rules module automatically builds the set of members that must be included in the logic query for each dimension, this set can be also controlled by the rules itself using the *XDIM_MEMBERSET instruction.

For example, in the exception translation rule we can enforce the query to generate results for all reporting currencies with the instruction:

*XDIM_MEMBERSET CURRENCY=USD,EURO

This operator is only supported for SQL logic (in MDX rules this feature is not needed, as the MDX syntax allows to build any sort of sets), and can be handy to pass to the SQL query smaller lists of valid

members, that are more efficiently parsed by Microsoft SQL engine. Example:

*XDIM_MEMBERSET INTCO<>NonInterco

This corresponds to passing the list of all intercompany members, excluding the NonInterco member. Forcing a dimension to read all members

Using the All keyword, you can force a dimension to read all members. Previously, when you wanted to make sure that all members of a given dimension would be loaded, irrespective of what specified in the passed region, you would have written something like:

// a workaround

*XDIM_MEMBERSET INTCO<>INVALID

Today the rules engine supports a cleaner definition, through the keyword <ALL>. The above example can be written as follows:

// a better syntax

*XDIM_MEMBERSET INTCO = <ALL>

This improves the readability of the logic and also generates faster SQL queries. Added recognition of NEXT(n) keyword

The instruction XDIM_MEMBERSET, when applied to the TIME dimension, can now also handle the keyword NEXT(n), which allows you to extend in the future the set of passed periods to process. For example:

*XDIM_MEMBERSET TIME=PRIOR,%SET%,NEXT // add 1 period before and 1 period after

*XDIM_MEMBERSET TIME= %SET%,NEXT(3) // add 3 periods after

*XDIM_REQUIRED - more information Allowed uses: Global, MDX, SQL

*XDIM_REQUIRED={dimname}[,{dimname}]

Sets dimensions that are required in order for the rules to run from a Data Manager package. If the specified dimension(s) are not passed to the rules from a package, then an error message is generated. Example:

*XDIM_REQUIRED=CATEGORY, TIME