Measure Search Expressions

This section describes how to create and use measure search expressions, which enable you to access rows from the fact table based on the value of a measure for the facts themselves (that is, at the lowest level rather than at an aggregate level). These expressions are an InterSystems extension to MDX.

Details

A measure search expression has the following syntax, which refers to a special dimension in DeepSee called %SEARCH:

%SEARCH.&[comparison expression]

Where comparison expression is a logical expression like the following example:

[MEASURES].[test score]>60

Note:

Both sets of square brackets are required: the square brackets around the comparison expression and the square brackets of the measure identifier in the comparison expression. Hence a valid search expression always starts with %SEARCH.&[[

For example, the following query selects all patients with a test score higher than 60:

SELECT FROM patients WHERE %SEARCH.&[[MEASURES].[Test Score]>60]
 
Result:               6,191

More generally, comparison expression can be a combination of logical expressions. This expression can include:

Logical comparison operators: > (greater than), >= (greater than or equal to), = (equal to), < (less than), and <= (less than or equal to).
If the searchable measure contains string values, you can also use the SQL LIKE operator.
The AND operator, the OR operator, and parentheses to control precedence.
Numeric literals.
String literals enclosed in single quotes.

The SQL expressions IS NULL and IS NOT NULL. For example:

SELECT FROM HOLEFOODS WHERE [%Search].&[[Measures].[Units Sold] IS NULL]

Uses

You can use measure search expressions in all the following contexts:

As the argument for the %FILTER clause
As the argument for the WHERE clause
As an argument for the FILTER function.

Additional Notes

DeepSee parses a measure search expression as follows:

%Search is treated as a dimension.
Because the comparison expression is enclosed inside &[], DeepSee treats it as a KEY value, which permits it to contain arbitrary syntax.
The comparison expression is converted to an SQL statement against the fact table.

The preceding means that comparison expression can include SQL syntax.

Also, it may be possible to use a measure in a measure search expression even if it is not marked as searchable="true" in the cube definition. This attribute value causes DeepSee to do two things:

Display this measure as an option in advanced filters.
Add additional index, if needed, to enable the measure to be searchable.