Filter strings in bedrock

Understanding how the pFilter parameter encodes filter strings is central to understanding the bedrock library. Bedrock converts the contents of the pFilter string into views by splitting the string into dimension names and element names and creating the view on the fly. Let’s examine the components of the filter string using the process }bedrock.cube.view.create
  – pCube = “Employee”
  – pFilter = “Year ¦ 2018 & Version ¦ Actual + Budget & Employee Measure ¦ FTE”

The process will create a view in the Employee cube:
– the Year dimension will be restricted to the element 2018
  – the Version dimension will be restricted to the elements Actual and Budget
  – the Employee Measure dimension will be restricted to the element FTE

The string is split into the component parts by virtue of the 3 delimiters which each also have a parameter to define them:
  – pDelimEleStart : (default = “¦”) separates the dimension name from the first element of the dimension
  – pDelimEle : (default = “+”) separates element names (when more than one element from a given dimension)
  – pDelimDim : (default = “&”) separates the last element name of one dimension from the next dimension name

As a minimum a valid filter must contain:
1. a dimension name from a dimension belonging to the cube
2. the element start delimiter
3. a valid element name (or alias) from the dimension

For example: pFilter = “Year ¦ 2018”

The element delimiter is only required in cases where multiple elements are included from a single dimension. Dimension delimiter is only required in cases where the view is filtered on multiple dimensions.

Another key feature of how bedrock interprets filter strings is the interaction between the pSuppressConsol parameter and the elements provided as part of the pFilter filter string. If pSuppressConsol = 1 and consolidated elements are provided in the filter string then this is interpreted as a shortcut for all leaf descendants of the consolidation and the element will be automatically expanded and the leaves included in the view.

In our example the Version dimension contains a consolidation Act vs Bud, which contains elements Actual & Budget as children. If our filter string was the following:
pFilter = “Year ¦ 2018 & Version ¦ Act vs Bud & Employee Measure ¦ FTE”
… then we would achieve an identical result in the view.

If pSuppressConsol = 1 (the default value) then by default any dimension not referenced in the filter string will be unfiltered and will be set in the view to the implicit subset “All”. For normal processing when pSuppressZero = 1 and pSuppressConsol = 1 this is recommended as all non-leaf elements as well as all leaf elements not containing values will be automatically skipped.

Conversely when pSuppressConsol = 0 and consolidated elements are provided in the filter string the consolidated element(s) will be added directly to the view and not expanded to the leaves. In this situation it is good practice to assign to the view a subset of all leaf elements for all dimensions not already explicitly included in the filter. The parameter pSubN serves this purpose. It is recommended to set pSubN = 1 whenever pSuppressConsol = 0.

If we repeat the first example but with pSubN = 1 then the resulting view will look like this:
(The “Example 1” subsets contain all leaf elements for all dimensions except for Version which contains only Actual & Budget elements).

1. Spaces in the filter string are not necessary, they are included in the examples above only for clarity.
2. If any delimiters are passed as a blank string then the default delimiters will be used.
3. Only same named hierarchies can be used in pFilter. This is because “traditional views” are created and these do not support alternate hierarchies.
4. Views created by bedrock processes are for data processing purposes and so all dimensions are implicitly on row. Title, row & column dimensions and stacking order are not assigned.
5. The broken pipe character “¦” has been used for the default element start delimiter. A colon “:” was used for this purpose in past versions of bedrock but is now deprecated as colon is now the delimiter between dimension name and hierarchy name (e.g. dimension:hierarchy) making colon ambiguous for this purpose. Consequently, it is necessary to use a different delimiter in this case to avoid the filter being tokenized in wrong positions.