Filter Expression Dialog Box
Use this dialog box to specify a filter to limit the fact table records used in the incremental update of the cube or partition you selected in the Analysis Services Processing Task dialog box.
This dialog box is displayed when, in the Analysis Services Processing Task dialog box, you select a cube, click Incremental update, and then click the edit (...) button beside the Filter box.
Caution If in the Analysis Services Processing Task dialog box you select the default fact table (that is, the same fact table already used by the cube or partition), you must create a filter to ensure that only data not already in the cube or partition will be added. Otherwise, the cube or partition will contain duplicate and therefore inaccurate data.
For example, a cube contains data for years 1995 through 1997. You are adding data for 1998 from the same table that supplies the 1995 through 1997 data. The name of the column that contains years is the_year. You must use the following filter expression:
"the_year"=1998Another example (alphanumeric data):
"the_month"='January'
Options
Filter expression
Type a WHERE clause expression of an SQL SELECT statement. Do not include the WHERE keyword.
Whenever necessary to avoid ambiguity, use a qualified expression. For example, if a column name appears in multiple tables, include the table name in the expression:
"time"."the_month"='January'
The SELECT statement used to retrieve records for the incremental update is generated automatically. The filter expression is connected with AND to the automatically generated part of the WHERE clause. Therefore, if you specify multiple filter expressions, enclose them all in a pair of parentheses. For example, to retrieve records for years 1998 and 1999, use the following filter:
("the_year"=1998 OR "the_year"=1999)
Filters consist of one or more expressions using columns in the fact table. A filter can also contain columns in dimension tables if they are included in a nested SELECT statement and the underlying database supports nested SELECT statements.
The filter expression acts as a pass-through statement, and its syntax is not checked until the package is executed. If the syntax is incorrect, the incremental update and the task fail.