Preprocessor commands for native code rules

Switch View :
Classic
Lightweight
ScriptFree
Preprocessor commands for native code rules

Typically, when you write a rule in SQL or MDX, you want to refer to elements of the underlying system, such as tables, databases, columns, and so on. To enable this, Planning Business Modeler supports preprocessor commands for rules that have NativeSql and NativeMDXScript implementation.

The preprocessor commands can also help you re-use a rule between different models and applications, and can help insulate the rules from minor structural changes. You can also use parameters in native rules that contain preprocessor commands. For more information, see Using parameters in rules that have NativeSql implementation.

NoteNote:

Native MdxQuery rules do not support preprocessor commands.

To avoid processing difficulties in the implementing system, Planning Business Modeler replaces these preprocessor commands before the rule is inserted into the cube or saved as a stored procedure.

Preprocessor commands for NativeSql implementations

The following table shows the preprocessor commands that Planning Business Modeler supports for rules that use NativeSql implementation.

NoteNote:

Except where indicated in the table, these commands must be used as literal values.

Command$argument Description

$ruleId

The integer ID of the rule

alldim$except(dimensionList)

Indicates that the procedure should create enumerations of all dimensions except those that are explicitly listed in dimensionList.

dimensionList must be a comma-delimited list in the dim$DimLabel form.

The following list shows valid examples of this command.

  • alldim$except()

  • alldim$except(dim$Time)

  • alldim$except(dim$Time, dim$Flow)

bmo$ChangeDataTime

The column name for the Change Date Time column of the fact table

bmo$CreateDateTime

The column name for the Create Date Time column of the fact table

column$ruleId

The Rule ID column of the fact table, if applicable

column$timePeriod

The Time column of the fact table, if applicable

column$year

The Year column of the fact table, if applicable

db$dimensionDatabase

The database that contains the dimension tables.

db$factDatabase

The database that contains the fact tables.

definedim$except(dimensionList)

Indicates that the procedure should create enumerations of dimensions that include declared data types except those that are explicitly listed in dimensionList.

dimensionList must be a comma-delimited list in the dim$DimLabel form.

The following list shows valid examples of this command.

  • definedim$except()

  • definedim$except(dim$Time)

  • definedim$except(dim$Time, dim$Flow)

dim$DimLabel

The column that represents the DimLabel dimension, where DimLabel is the Planning Server label of the dimension

dimtype$SqlType

The column name of the column of SqlType in the fact table. SqlType must be one of the following

  • Time

  • Currency

  • Account

  • Entity

key$DimLabel

The key column name of DimLabel dimension table, where DimLabel is the Planning Server label of the dimension

matchdim$except(dimensionList)

Indicates that the procedure should match dimensions from two instances of the same table in the enumerations of dimensions, except those that are explicitly listed in dimensionList.

dimensionList must be a comma-delimited list in the dim$DimLabel form.

The following list shows valid examples of this command.

  • matchdim$except()

  • matchdim$except(dim$Time)

  • matchdim$except(dim$Time, dim$Flow)

sp$procedure

The name of the stored procedure that is created in the rule that uses this command

sp$validation

The stored procedure that is used to validate any data that is being written into this model

tbl$DimLabel

The table that represents the DimLabel dimension, where DimLabel is the Planning Server label of the dimension

tbl$factTable

The fact table

tbl$tempTable

The submission temp table, which exists only during the session of client data submission

Preprocessor commands for NativeMDXScript implementations

The following table shows the preprocessor commands that Planning Business Modeler supports for rules that use NativeMDXScript implementation.

Command Description

default$Measure

The default measure of this model

dim$Time

The Time dimension in this model, if applicable

dim$Account

The Account dimension in this model, if applicable

dim$Currency

The Currency dimension in this model, if applicable

dim$Entity

The Entity dimension in this model, if applicable.

hier$DimLabel

The default hierarchy of the DimLabel dimension, where DimLabel is the Planning Server label of the dimension

gran$DimLabel

The granularity of the DimLabel dimension in this model, where DimLabel is the Planning Server label of the dimension

time$HalfYear

The half-year granularity in this model, if applicable

time$Trimester

The trimester granularity in this model, if applicable

time$Year

The year granularity in this model, if applicable

time$Day

The day granularity in this model, if applicable

time$Week

The week granularity in this model, if applicable

time$Month

The month granularity in this model, if applicable

time$Quarter

The quarter granularity in this model, if applicable

Example

The following example shows a snippet of the system-defined Aggregation rule. This rule has NativeMDXScript implementation, and makes extensive use of preprocessor commands.

SCOPE (
      [Measures].[default$Measure],
      [dim$Account].[hier$dim$Account].Members,
      [TimeDataView].[All Members].[TimeDataView].[Periodic],
      Descendants(
                  [dim$Time].[hier$dim$Time].[All],
                  [dim$Time].[hier$dim$Time].[gran$dim$Time],
                  BEFORE)
         );

See Also