How to reference intersections of hierarchies in rules

This is a follow on from the previous article on working with hierarchies in rules. It will deal with how to address the intersection of two (or more) hierarchies in the right-hand side of a rule statement and the left-hand side of a feeder statement.

Note this is accurate as at version 2.0.6.71

It may first be prudent to clear up what we mean by intersections of hierarchies. For the below example we are working with a business that engages in sales of different models are cars. In the example below we have a ‘Product’ dimension which includes a number of hierarchies, of which one is the eponymous ‘Product’ and one is ‘Color’.The ‘Product’ hierarchy contains a consolidation by type and series while the ‘Color’ hierarchy contains the same leaf level elements rolled up into the vehicle color and was created based on an attribute. Below you can see the hierarchies as viewed in Planning Analytics Workspace.

When viewed in a cube view it is now possible to use each hierarchy as you would a dimension and create an intersection of different consolidations.

In this example we can see that there were sales of 1,043 Gray vehicles of which 44 were in the S Series and 999 in the L Series.

To create the same analysis in a traditional dimension would have necessitated multiple consolidations and unique consolidation element names or a rebuild of the cube with additional dimensions.

From the previous article, should you want to reference all Red car sales, you would use the following syntax using the DB() notation on the right hand side of a rule.

= DB ( ‘Sales’ , ‘Actual’, ‘Color’ : ‘Red’ , ‘Units’ ) ;

From the above view the result of this formula would be 101,433

Should you want to refine this to only Red car sales in the T Series you would need to encase the references in brackets to avoid them being mistakes for separate dimensions in a reference.

= DB ( ‘Sales’ , ‘Actual’, (  ‘Color’ : ‘Red’, ‘Product’ : ’T Series’ ) , ‘Units’ ) ;

From the above view the result of this formula would be 100,000

Further hierarchies can be included within the brackets with the syntax ‘Hierarchy’:’C Level Element’ in a comma separated list.

= DB ( ‘Sales’ , ‘Actual’, (  ‘Color’ : ‘Red’, ‘Product’ : ’T Series’, ‘Engine Type’ : ’Diesel’ ) , ‘Units’ ) ;

In the above examples we can see that by adding further hierarchies into a rule statement the number of leaf level cells that are children of both (or all) consolidations would be smaller than the super set of children of each.

In the above rule, for example, there are 12 Red cars and 8 cars in the T Series (mental note 12 + 8 = 20) . However there are only two Red cars in the T Series (the 2.8L Sedan and the 2.8L Coupe). Meaning that there are 10 Red cars that are not in the T Series and 6 T Series cars that are not red (mental note 10 + 6 = 16).

To feed the rule below efficiently we would only want the result to be fed from the 2 cars that are in both consolidations rather than the 16 cars that are in one consolidation but not also in the other.

= DB ( ‘Sales’ , ‘Actual’, (  ‘Color’ : ‘Red’, ‘Product’ : ’T Series’ ) , ‘Units’ ) ;

The following feeder statement would feed from ONLY the 2 elements contained in both consolidations.

[ ‘Actual’, ‘Color : Red’,  ‘Product : T Series’ , ‘Units’ ]  =>

Note that the hierarchies are not enclosed in braces.

This is different to the feeder statement below where the result would be fed from all elements that are children Red and all elements that are children of ‘T Series’ – the super-set of 20 elements in total. Resulting in the target being potentially fed from the 18 cars that are not Red and T Series.

[ ‘Actual’, {‘Color : Red’,  ‘Product : T Series’} , ‘Units’ ]  =>

This could also be written as to separate statements as per below:

[ ‘Actual’, ‘Color : Red’ , ‘Units’ ]  =>

[ ‘Actual’,  ‘Product : T Series’ , ‘Units’ ]  =>

In other words, specifying different elements from the same dimension and different hierarchies within curly braces in a feeder area statement specifies a UNION, whereas no braces specify an INTERSECT.