Model filter syntax and examples (Analysis Services - data mining)

Applies to: SQL Server 2019 and earlier Analysis Services Azure Analysis Services Fabric/Power BI Premium

This article describes model filter syntax and provides sample expressions.

Filter syntax

Filters on case attributes

Filters on nested table attributes

Filters on multiple nested table attributes

Filters attributes missing in nested table

Filters on multiple nested table values

Filters on nested table attributes and EXISTS

Filter combinations

Filters on dates

Filter syntax

Filter expressions are generally equivalent to expressions in a WHERE clause. Combine multiple conditions with the logical operators AND, OR, and NOT.

In nested tables, use the EXISTS and NOT EXISTS operators. An EXISTS condition evaluates to true if the subquery returns at least one row. Use this condition to restrict the model to cases that contain a specific value in the nested table, such as customers who purchased an item at least once.

A NOT EXISTS condition evaluates to true if the subquery returns no rows. Use this condition to restrict the model to customers who never purchased a specific item.

Here is the general syntax:

<filter>::=<predicate list>  | ( <predicate list> )  
<predicate list>::= <predicate> | [<logical_operator> <predicate list>]   
<logical_operator::= AND| OR  
<predicate>::= NOT <predicate>|( <predicate> ) <avPredicate> | <nestedTablePredicate> | ( <predicate> )   
<avPredicate>::= <columnName> <operator> <scalar> | <columnName> IS [NOT] NULL  
<operator>::= = | != | <> | > | >= | < | <=  
<nestedTablePredicate>::= EXISTS (<subquery>)  
<subquery>::=SELECT * FROM <columnName>[ WHERE  <predicate list> ]  

filter
Contains one or more predicates, connected by logical operators.

predicate list
One or more valid filter expressions, separated by logical operators.

columnName
The name of a mining structure column.

logical operator
AND, OR, NOT

avPredicate
Filter expression that can be applied to scalar mining structure column only. An avPredicate expression can be used in both model filters or nested table filters.

An expression that uses any of the following operators can only be applied to a continuous column. :

• < (less than)

• > (greater than)

• >= (greater than or equal to)

• <= (less than or equal to)

An expression that uses any of the following operators can be applied to a continuous, discrete, discretized, or key column:

• = (equals)

• != (not equal to)

• IS NULL

When an avPredicate applies to a discretized column, use any value from the target bucket in the filter.

Don't define the condition as AgeDisc = '25-35'. Instead, use a value from that interval.

For example, AgeDisc = 27 represents any value in the same interval as 27, which is 25–35 in this example.

nestedTablePredicate
Filter expression that applies to a nested table. Can be used in model filters only.

The nestedTablePredicate accepts a subquery only for a table mining structure column.

subquery
A SELECT statement followed by a valid predicate or list of predicates.

Use only avPredicate predicates. Each predicate can refer only to columns in the current nested table identified by columnName.

Limitations on filter syntax

The following restrictions apply to filters:

• Use only simple predicates in a filter, including mathematical operators, scalars, and column names.

• Filter syntax doesn't support user-defined functions.

• Filter syntax doesn't support non-Boolean operators, such as plus and minus signs.

Examples of filters

The following examples show how to apply filters to a mining model. If you create the filter expression by using SQL Server Data Tools, the Property window and the Expression pane of the filter dialog box show only the string after the WITH FILTER keywords. Each example includes the mining structure definition to clarify the column type and usage.

Example 1: Typical case-level filtering

This example shows a simple filter that restricts the cases used in the model to customers whose occupation is architect and whose age is over 30.

ALTER MINING STRUCTURE MyStructure  ADD MINING MODEL MyModel_1  
(  
CustomerId,  
Age,  
Occupation,  
MaritalStatus PREDICT  
)  
WITH FILTER (Age > 30 AND Occupation='Architect')  

Example 2: Case-level filtering using nested table attributes

If your mining structure contains nested tables, you can either filter on the existence of a value in a nested table, or filter on nested table rows that contain a specific value. This example restricts the cases used for the model to customers over the age of 30 who made at least one purchase that included milk.

The filter can use columns that the model doesn't include. The nested table Products is part of the mining structure, but the mining model doesn't include it. You can still filter on values and attributes in the nested table. Enable drillthrough to view the case details.

ALTER MINING STRUCTURE MyStructure  ADD MINING MODEL MyModel_2  
(  
CustomerId,  
Age,  
Occupation,  
MaritalStatus PREDICT  
)  
WITH DRILLTHROUGH,   
FILTER (Age > 30 AND EXISTS (SELECT * FROM Products WHERE ProductName='Milk')  
)  

Example 3: Case-level filtering on multiple nested table attributes

This example shows a three-part filter: a condition applies to the case table, another condition to an attribute in the nested table, and another condition on a specific value in one of the nested table columns.

The first condition in the filter, Age > 30, applies to a column in the case table. The remaining conditions apply to the nested table.

The second condition, EXISTS (SELECT * FROM Products WHERE ProductName='Milk'), checks whether the nested table contains at least one purchase that includes milk. The third condition, Quantity >= 2, requires the customer to purchase at least two units of milk in one transaction.

ALTER MINING STRUCTURE MyStructure  ADD MINING MODEL MyModel_3  
(  
CustomerId,  
Age,  
Occupation,  
MaritalStatus PREDICT,  
Products PREDICT  
(  
ProductName KEY,  
Quantity        
)  
)  
FILTER (Age > 30 AND EXISTS (SELECT * FROM Products WHERE ProductName='Milk'  AND Quantity >= 2)   
)  

Example 4: Case-level filtering on the absence of nested table attributes

This example limits cases to customers who didn't purchase a specific item by filtering on the absence of an attribute in the nested table. The model uses customers over age 30 who never bought milk.

ALTER MINING STRUCTURE MyStructure  ADD MINING MODEL MyModel_4  
(  
CustomerId,  
Age,  
Occupation,  
MaritalStatus PREDICT,  
Products PREDICT  
(  
ProductName  
)  
)  
FILTER (Age > 30 AND NOT EXISTS (SELECT * FROM Products WHERE ProductName='Milk') )  

Example 5: Filtering on multiple nested table values

This example shows nested table filtering. The nested table filter runs after the case filter and restricts only nested table rows.

This model could contain multiple cases with empty nested tables because EXISTS isn't specified.

ALTER MINING STRUCTURE MyStructure  ADD MINING MODEL MyModel_5  
(  
CustomerId,  
Age,  
Occupation,  
MaritalStatus PREDICT,  
Products PREDICT  
(  
ProductName KEY,  
Quantity        
) WITH FILTER(ProductName='Milk' OR ProductName='bottled water')  
)  
WITH DRILLTHROUGH  

Example 6: Filtering on nested table attributes and EXISTS

In this example, the nested table filter restricts rows to those that contain milk or bottled water. An EXISTS statement then restricts the model to cases with a nonempty nested table.

ALTER MINING STRUCTURE MyStructure  ADD MINING MODEL MyModel_6  
(  
CustomerId,  
Age,  
Occupation,  
MaritalStatus PREDICT,  
Products PREDICT  
(  
ProductName KEY,  
Quantity        
) WITH FILTER(ProductName='Milk' OR ProductName='bottled water')  
)  
FILTER (EXISTS (Products))  

Example 7: Complex filter combinations

This scenario resembles the scenario in Example 4, but it's far more complex. The nested table, ProductsOnSale, has the filter condition (OnSale), which means the value of OnSale must be true for the product listed in ProductName. In this case, OnSale is a structure column.

The second part of the filter, for ProductsNotOnSale, uses the same syntax but filters products for which OnSale is false (!OnSale).

Finally, you combine the conditions and add one more restriction to the case table. The result is to predict purchases of products in the ProductsNotOnSale list, based on the cases that are included in the ProductsOnSale list, for all customers over the age of 25.

ALTER MINING STRUCTURE MyStructure ADD MINING MODEL MyModel_7

(

CustomerId,

Age,

Occupation,

MaritalStatus,

ProductsOnSale

(

ProductName KEY

) WITH FILTER(OnSale),

ProductsNotOnSale PREDICT ONLY

(

ProductName KEY

) WITH FILTER(!OnSale)

)

WITH DRILLTHROUGH,

FILTER (EXISTS (ProductsOnSale) AND EXISTS(ProductsNotOnSale) AND Age > 25)

Example 8: Filtering on dates

You can filter input columns on dates, as you would any other data. Dates contained in a column of type date/time are continuous values; therefore, you can specify a date range by using operators such as greater than (>) or less than (<). If your data source doesn't represent dates by a Continuous data type, but as discrete or text values, you can't filter on a date range, but must specify individual discrete values.

However, you can't create a filter on the date column in a time series model if the date column used for the filter is also the key column for the model. That restriction exists because, in time series models and sequence clustering models, the date column might be handled as type KeyTime or KeySequence.

If you need to filter on continuous dates in a time series model, you can create a copy of the column in the mining structure, and filter the model on the new column.

For example, the following expression filters a Continuous date column added to the Forecasting model.

=[DateCopy] > '12:31:2003:00:00:00'

For other model types, you can use dates as input criteria or filter criteria just like you would in any other column. However, if you need to use a specific level of granularity that a Continuous data type doesn't support, create a derived value in the data source by using expressions to extract the unit to use in filtering and analysis.

For example, if you want to filter your call center results to show only weekends, you can create an expression in the data source view that extracts the weekday name for each date, and then use that weekday name value for input or as a discrete value in filtering. Remember that repeating values can affect the model, so use only one of the columns, not the date column plus the derived value.

See Also

Filters for Mining Models (Analysis Services - Data Mining)
Testing and Validation (Data Mining)