Partition Filters and Incremental Update Filters

Partition Filters and Incremental Update Filters

You can use filters to specify the data that you want to incorporate into the partitions of a cube. You can specify a filter for your partition data or incremental update by entering the criteria expression of an SQL WHERE clause.

Note  When entering a filter clause, the WHERE keyword is not required.

The filter expression you enter is used in a pass-through statement to be executed by the source database. The filter is not verified for syntax until the partition or incremental update is processed. Regardless of the complexity of your filter, data is retrieved only from the fact table for use in the partition. The default value for a partition filter is derived from the Source Table Filter property for the parent cube. This property is accessed in the properties pane of Cube Editor.

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'

Note  Because the 1992 ISO and ANSI standards for SQL specify that double quotation marks (") should be used as delimiters for table and column names, the preceding example uses this convention. Microsoft® SQL Server™ 2000 and Microsoft Access standards also support brackets ([ ]).

More complicated filters are also possible. 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. If the underlying database supports nested SELECT statements, you can use tables that are not referenced by the cube definition in your filter, but you must specify the join to the fact table in your filter.

Important  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.

The following examples of filters can be used in a partition for a cube that is based on the sample FoodMart 2000 database, where the cube includes the sales_fact_1997 fact table and the time_by_day dimension table (and possibly other dimension tables):

• This filter uses 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
  

• The following equivalent filter uses the fact table and the time_by_day dimension table in a nested query:

  "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')
  

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.

Using Filters with Overlapping Partition Data

Incorrect results can be returned from cubes whose partitions contain overlapping data as a result of filter statements that are not mutually exclusive. For more information, see Managing Partitions.

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 are mutually exclusive within each set.

Examples

A. Making the Years 1997 and 1998 Exclusive of Each Other

"SaleYear" = 1997
"SaleYear" = 1998

"Continent" = 'NorthAmerica'
"Continent" = 'Europe'
"Continent" = 'SouthAmerica'

"Country" = 'USA'
"Country" = 'Mexico'
("Country" <> 'USA' AND "Country" <> 'Mexico')

"Continent" = 'NorthAmerica'
"Country" = 'USA'