MSAS - Introduction and Managing Partitions Page - 4

Using a filter when editing an existing partition

In the partitions folder right click the Sales 97 partition and click Edit.

The Partition Wizard opens. Navigate to the last screen of the partition wizard by clicking next in the screens of the Wizard. On the Final screen click the Advanced button The Advanced settings dialog box allows the user enter a filter statement. In the Filter box type “State”.”Country”<> ‘USA’ and click Ok

Note that the Aggregation prefix value in the Advanced settings dialog box determines what prefix the Analysis Server uses when creating ROLAP aggregation tables in the relational data source. If ROLAP storage is not being used the aggregation prefix has no impact. The Drill through options button is useful only when drill through has been enabled for the cube.

On click ok to the Advanced settings the user is brought back to the Finish screen of the Partition Wizard. Now click Copy The Aggregation Design From An Existing partition option, select the check box for processing the cube and click Finish.

Select the Sales97 partition and browse the data. One of the greatest advantages of using filters is the flexibility it provides. Unlike a slice, the user can reference any field in any table used in the SQL query, not just the members of the dimension. Since the filter uses SQL the user can use any expression supported by the relational database system.