Filtering a Fact Table for Multiple Partitions

When the same fact table is used for more than one partition in a cube, it is important that the same rows are not used in more than one partition. It is possible for a row that is used in more than one partition to be included multiple times when the cube is processed or queried; this can cause queries to return incorrect data.

You can use filters in partitions to ensure that data is not duplicated among the partitions. A partition's filter specifies which data in the fact table is used in the partition. It is important that the filters for all partitions in a cube extract mutually exclusive datasets from the fact table. For example, these filters are mutually exclusive within each set:

Set 1:

"SaleYear" = 2004

"SaleYear" = 2005

Set 2:

"Continent" = 'NorthAmerica'

"Continent" = 'Europe'

"Continent" = 'SouthAmerica'

Set 3:

"Country" = 'USA'

"Country" = 'Mexico'

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

You specify a query to restrict rows in a table on the Enter a Query to Restrict Rows page of the Partition Wizard. On this page, select the Specify a query to restrict rows check box, and then construct the query that limits the rows.

When you create mutually exclusive queries for partitions, ensure that the combined partition data includes all data you want to include in the cube. You normally want to remove the default partition that was based on the table itself or else the query based partitions will overlap the query based on the complete table.