You can use filters to specify the data you want to be incorporated into a cube’s partitions. You can set filters in either the Advanced Settings dialog box or in the Incremental Update wizard. The Advanced Settings dialog box appears when you click Advanced in the last step of the Partition wizard or in the Convert to Partition dialog box. You set a filter for your partition data or incremental update by entering the criteria expression of a WHERE clause. Do not enter the keyword WHERE. The filter expression you enter will be used in a pass-through statement to be executed by the source database. The filter will not be verified for syntax until the partition or incremental update is processed.
The simplest filters are based on one or more columns in the fact table. For example, to select rows for only the West region from the Location fact table, use the following filter:
"location"."region"='West'
Because dimension tables are inner-joined to the fact table, a filter can include criteria applied against the fact table or any dimension table used by dimensions in the partition. However, to filter on dimension tables, the column names from the dimension tables must be contained in a nested SELECT statement, and the underlying database must support nested SELECT statements. The following are examples of filters that can be used in a partition for a cube that is based on the sample FoodMart database, where the cube includes the sales_fact_1997 fact table and the time_by_day dimension table (and possibly other dimension tables):
Filter using the fact table (518 is the time_id for June 1, 1997, and 547 is the time_id for June 30, 1997):
"sales_fact_1997"."time_id" BETWEEN 518 AND 547
Equivalent filter using the fact table and the time_by_day dimension table:
"sales_fact_1997"."time_id" IN
(SELECT "time_id" FROM "time_by_day"
WHERE "time_by_day"."the_year" = 1997
AND "time_by_day"."the_month" = 'June')
If the underlying database supports nested SELECT statements, you can use tables not referenced by the cube definition in your filter, but you must specify the join to the fact table in your filter.
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.
Regardless of the complexity of your filter, data is only retrieved from the fact table for use in the partition.
Per 1992 ISO and ANSI standards for SQL, the examples in this topic use double quotation marks (") as delimiters for table names and column names. Microsoft SQL Server and Microsoft Access permit brackets ([]) in place of double quotation marks.
Incorrect results can be returned from cubes whose partitions contain overlapping data as a result of filter statements that are not mutually exclusive. You must ensure that no data is duplicated among multiple partitions, and that no data is duplicated within a partition.
For example, these sets of filters would be mutually exclusive within each set:
Set 1:
"SaleYear" = 1997
"SaleYear" = 1998
Set 2:
"Continent" = 'NorthAmerica'
"Continent" = 'Europe'
"Continent" = 'SouthAmerica'
Set 3:
"Country" = 'USA'
"Country" = 'Mexico'
("Country" <> 'USA' AND "Country" <> 'Mexico')
The following filter expressions, the first in one of a cube’s two partitions and the second in the other, will yield incorrect query results from the cube data because the USA data will be included in both partitions.
"Continent" = 'NorthAmerica'
"Country" = 'USA'
When you create mutually exclusive filters for partitions, ensure that the combined partition data includes all data you want for the cube.
For more information, see Partitions in Advanced Topics.