Business Rules Overview

image_pdfimage_print

Jedox Business Rules enable you to execute complex calculations (division/multiplication, logical and various functions) for Jedox databases. These calculations can be executed for data cubes and for the following system cubes:

  • #_GROUP_CUBE_DATA
  • #_USER_GROUP
  • #_ROLE_RIGHT_OBJECT
  • #_USER_USER_PROPERTIES
  • #_GROUP_GROUP_PROPERTIES.

The general syntax of rules is [Target] = f[Source], i.e., [Target] is a function of [Source].

[Target] is the area in the cube that is to be calculated using rules. This target area is defined by dimension elements. Elements are not to be listed, if a rule is to apply to all dimension elements. Rules are mapped out per cube.

Note: if a target area is defined in a rule, subsequent rules that describe the same or an overlapping target area are no longer effective. This means that specific rules have to be stored before general rules. The only exception is the rule function CONTINUE, which can switch from one rule into another rule defined on overlapping target areas. 

You can access the Rule Editor by right-clicking on the cube in the Navigation pane of the Modeler, which opens a context menu. You can also select a cube in the Navigation pane of the Modeler and click on the Rules tab. See screenshots below:

Context menu

Rules tab

 

Terminology
Area An area is a set (or sub-cube) of cells created as a combination (or cross-product) of the elements from all dimension subsets defining the area.
(GP)GPU General-purpose computing on Graphics Processing Units. Used in Jedox OLAP Accelerator to speed up rules and consolidation calculations, and  for accelerated write-back, including splashing and copy/like operations.
Markers Part of Jedox OLAP Rules syntax, restricting rule calculation to a reduced set of calculated cells to speed up the process. Marker computation has two phases: the first one is pre-processing made directly after rule modification or value writeback. The second calculation, time phase, benefits from the results prepared in first phase.
Sparsity Ratio of the number of cells with non-null results to the total number of cells in the cube (or area). In real-life databases, this ratio is typically very small, e.g. one value per million cells or even less.
Subset Collection of elements from a single dimension. Contains at least one element. Can be up to the full dimension.
Operators of the Rule Editor

The following operators can be used in rules:  +   –   *   /   ==   !=   <   <=   >   >=   @

These operator differ from Excel operators in the following ways:

== means equal to (in Excel = is used); only used in the IF function
!= means not equal to (in Excel <> is used); only used in the IF function
@ is used only for marker

Jedox can store 0 in cubes, as well as treat 0 and null differently in comparison with Excel. For more information, see Storing Zero Values. This feature has impact on arithmetic operations or on the rules using the +, , * or / operators. For example, for the division by 0, excel returns #error, while Jedox returns the following:

x/0 or 0/x = 0

x/null or null/x = null

for other numeric value of x:

x/x = 1

Comments and formatting

You can add inline comments to your rules to help other users understand their structure and logic. Inline comments can be added with // and block comments can be added with /* … */ (see green text in screenshot below). All comments and formatting persist after the rule is stored in the In-Memory DB.

Cell Type Restriction

Cell type restrictions can be either C: or B: (N: in older versions). If not specified, the rule is enabled for all cells of the target area. C: enables the rule only for cells with a pure consolidated element in at least one dimension, those with no string element in any dimension or under any of the consolidated elements. B: enables the rule for base cells only, i.e., cells with a base element in every dimension. Consolidated values will be calculated as the aggregation of the rule-calculated base cells.

Examples:

If a rule is to apply to base elements only (consolidated elements will be aggregated as usual), the target area may be defined as follows:

[Target]=B: f[Source]

Vice versa, it is also possible to restrict the target area to consolidated elements.

[Target]=C: f[Source]

To build on the examples we used earlier, change RULE 1 and RULE 2 as follows:

['2015'] = B:123
['2015','Desktop L'] = B:['2013'] * 1.1

List of target dimensions

Optional occurrence of current member expression !’DimensionName’. Current member means that the element used in dimension ‘DimensionName’ to fetch an input value for the rule is the same as that of the target cell.

Example 1:

[] = PALO.DATA("","#_CONFIGURATION",PALO.EFIRST("","#_CONFIGURATION_"))

This rule, although it looks complicated, is actually very simple. It assigns the same value for all cells of the target cube, and it is read from the first cell of the system cube #_CONFIGURATION. The rule does not contain !‘Dimension’, and therefore it is constant for the whole cube.

Example 2: (based on the Sales cube from the Demo database)

['2014'] = ['2013']

As you can see, this rule also does not contain any construction !’Dimension’. However, because it is written in the short (implicit) form, we have to translate the cell reference to the explicit PALO.DATA(…) format to be able to count them all:

['2014'] = PALO.DATA("", "", !'Products', !'Regions', !'Months', !'Years', !'Datatypes', !'Measures')

This rule does the same thing, but here we can count occurrences of !’..’. In this case, 30*24*17*1*9*7=771.120 potential unique results have to be evaluated.

Note: the number of potential values that the rule can have is one indicator of calculation time. In the example above, it will take a few microseconds to several seconds, based on the calculation engine that is used.

Defining multiple elements as rule targets

Multiple elements from one dimension can be defined as the target of a rule. The elements are listed inside of curly brackets on the left side of a rule.

Example:

['Months':{'Jan','Feb','Mar'}] = ...

Rules with references to removed elements

Rules with element entries that were removed will only be disabled and are still available on the server for manual correction.

Cell references

Static cell reference

Pros: short syntax, name-independent

Cons: works only with its cube; source elements must be precisely identified

PALO.DATA function

Pros: can access any cube in any database; element names can be complex expressions

Cons: complex syntax; elements are not name-independent–when element name changes, the rule stops working; doesn’t react on flexible cube layout changes

Dynamic cell reference (DCR)

DCR shortens and simplifies the use of PALO.DATA and provides support for flexible cube layout. 

DCR requires a cube name to be specified in single quotes before square brackets. It can be empty to reference the “self” cube. This name specification is important to distinguish between old intracube cell references, which have many limitations and special STET representation ( STET() can be written as []). Each item MUST specify the source dimension name for the source cube in single quotes, followed by a colon and either the source element in single quotes or general string expression that evaluates an element name.  

Benefits of DCR are: 

  • The dimension can be found automatically. It does not have to be specified. 
  • The default read element is used if a dimension is added to the source cube. 
  • The order of parameters is not important  

For example, the rule below shows the syntax before Jedox 2018.3: 

PALO.DATA("","Sales",!'Month',!'Year',!"Product",!'Customer',!'Region',"Units") 

The same rule with 2018.3 syntax is much more efficient: 

'Sales'['Measure':'Units'] 

Related links: Advanced Rule Editor, Rule Engines and Algorithms, Markers


Learn more about rules in this short video from Jedox Academy! It’s absolutely free. Just enter your email address when prompted, and the video will begin playing.

image_pdfimage_print