Global Subsets in OLAP Rules

Based on the scenario, Subsets can be referenced in either the rule target definition or in DCR (not in static cell reference).

'DimensionName'::'SubsetName'
'DimensionName'::'SubsetName'(<optional_Subset_parameters>)

If the Subset uses variables, the variables must be named "_1", "_2"..., and so forth. In rules, these variables can be set using parameters, e.g. ['Months'::'Subset'(<value-of_1>,<value-of_2>,…)]

The following global Subset definitions will be used in rule examples below:

=PALO.Subset("<server>/<database>","<dimension>",1,,,,,PALO.AFILTER(_1,0,{true,true,true,true}),,PALO.SORT(1,0,,0,,0,1,,))

=PALO.Subset("<server>/<database>","<dimension>",1,,PALO.HFILTER(_1,0,false,,,,,1,1,true),,,,,PALO.SORT(1,0,,0,,0,1,,))

=PALO.Subset("<server>/<database>","<dimension>",1,,PALO.HFILTER(,0,false,1,,,,,,),,,,,PALO.SORT(0,2,,0,,0,0,,))

Rule syntax examples

Setting all consolidated elements of dimension Months to null

['Months'::'Consolidated']=null

Creating a Picklist with the element names delimited by comma

No additional white spaces should be between the element names.

['Months'::'PicklistOf'("Jan,Feb,Mar")]=1

Here element "Product A" is in escaped double quotes (doubled) because it contains space in the name.

['Products'::'PicklistOf'("""Product A"",All")]=null 

If parameters for the Attribute query are passed and multiple attributes are filtered, they have to be specified in pairs <attribute_name>,<attribute_value> delimited by colon. In the example below, products with "Blue" color AND "Brand A" have been selected.

['Products'::'AttributeOf'("Product Color,Blue:Brand,Brand A")]

Using Subsets in rule target definition

Subsets can be used in the rule target definition (the "left side" of the rule). In this scenario, the rule target definition is dynamically set to cover all elements that are returned by the Subset. In the following example, the rule target is covering all elements in the "Months" dimension that are returned by the stored Subset "Children", given the string "Q1" as value for the Subset variable:

['Month'::'Children'("Q1")]

In this context, a current member expression like !'dimension_name' cannot be used in Subset parameter, because it is undefined in the target definition.

Subsets in rule formulas

Subsets can be used in rule formulas (the "right side" of the rule) as a source of the aggregation functions SUM, AVERAGE, COUNT, MAX, MIN, MEDIAN.

In this case, a DCR expression referencing a Subset must be the only parameter of the aggregation function. Such a DCR expression, which uses a Subset, cannot be used with additional parameters (such as static numbers).

SUM is the implicit default aggregation function if the global Subset is referenced in dynamic cell reference (DCR). Therefore

['Month':'Q1'] = SUM(''['Month'::'Children'("Q1")])

is identical to

['Month':'Q1'] = ''['Month'::'Children'("Q1")]

You can also use a current member expression in the rule formula (right side) of the rule. For example, to calculate the value of the consolidated elements in the Months dimension as an average of the children of the current element:

[] = C: AVERAGE(''['Month'::'ChildrenOf'(!'Month')])

In another example, the rule below averages sales of a product with specific color:

['Measure':'Average Red Revenue'] = AVERAGE(['Products'::'AttributeOf'("Color,Red"),'Measure':'Revenue'])

Using Subsets in rules

It is also possible to use stored Subsets in rules. For example, you can use a stored Subset with an attribute filter to apply a rule to products with a specific color. Or you could use a data filter for retrieving the top 10 products and use these for any KPI calculation via rule. Stored Subsets can be used in the source area of the rule too. This enables model builders to use a dynamic number of values in rule aggregation functions like SUM or AVERAGE.

In general, stored Subsets in rules reduce the need for template rules and enable new possibilities.

Updated November 4, 2024