Working with Business Rules

image_pdfimage_print

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

General information about rules

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 as of Jedox 6.0, 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.

Important 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 as of Jedox 7.0, 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 preprocessing made directly after rule modification or value writeback. The second calculation, time phase, benefits from the results prepared in first phase.
Sparsity Ratio of amount of cells with non-null results to total amount 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.

Creating a simple rule

The following example is a simple rule (RULE 1) using the Sales cube of the Demo database:

RULE 1: [‘2018’] = 123

The target area is the year 2018 of the Years dimension. This rule populates all cells in the cube for the year 2018 with the value 123. The other years are not affected by this rule. The rule applies to all elements in the other dimensions, since the elements were omitted for all other dimensions.

For a new rule, click on Add, then enter the rule and confirm the entry.

Now create a view to check the result. Organize the view in such a way that the years 2017 up to 2018 become column headers. Place “Products” in the Row titles and select all products.

The values in column D are retrieved from the corresponding cube cell by the PALO.DATAC() function. For 2018, this value was zero throughout. The values currently displayed in this view are computed using the rule, but the values stored in the database remain zero, i.e., the displayed values are not being written back to the database (which is the case with entries made in cells!).

The current computation of this view, using the rule, also shows that consolidated values are not computed: The rule simply overwrites the values.

If you delete the rule again, the values according to their consolidation will be displayed again.

Operators of the Rule Editor

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

These operator differ from Exel 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 markers

Restricting the target area

Building on the last example, the following example (RULE 2) creates a rule for Desktop L that shows a value for the year 2015 that is 10% higher than in 2013. The target areas are 2015 and Desktop L. The function for the source is the value of 2013 and Desktop L multiplied by 1.1.

Note: if the target area contains a part of the source area, as in the example Desktop L, it does not have to be repeated in the source area.

The rule should appear as follows:

RULE 2:  [‘2015′,’Desktop L’] = [‘2013’]*1.1

The target area is the year 2015 of the dimension “Years” for the product  “Desktop L”. The result of this rule is that all “Desktop L” cells of this cube for 2015 show the 2013 value multiplied by 1.1. The other years and products are not affected by this rule.

Important note: since you already defined the target area for the entire year of 2015 in RULE 1, RULE 2 will only have an impact if you place it in front of the more general rule for 2015. You can change the order of execution using the arrows in the rule editor.

For optimal performance, it is advised to restrict the target in as many dimensions as possible, which reduces the total amount of rule-calculated cells. The target area reduction should be considered, even if it means defining more rules. Multiple defined rules usually have much less impact on performance than a single rule with a large target area.

Besides a short notation, element names can be submitted along with dimension name.

Short form: [‘2015′,’Desktop L’] = [‘2013’]*1.1

Long form: [‘Years’:’2015′,’Products’:’Desktop L’] = [‘Years’:’2013′]*1.1

This makes sense only when the same element name occurs under several dimensions. To get the correct ordering, the new rule must be placed before the general rule [‘2015’] = 123. If the general rule [‘2015’] = 123 were to occur before the new rule, then the general rule would be applied to all cells in [‘2015’] and the more specific rule standing further down in the list would be ignored in the calculation.

[‘2015′,’Desktop L’] = [‘2013’]*1.1

 

Important note: prior consolidations will be consistently overwritten by the rules without any error message.

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

As of Jedox 6.0, multiple elements from one dimension can now 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.

image_pdfimage_print
Was this post helpful?
NoYes (+5 rating, 9 votes)
Loading...