Filter a Fact table for Multiple Partitions

To create multiple partitions, you begin by modifying the Source property of the default partition. By default, a measure group is created using a single partition that is bound to a single table in the DSV. Before you can add more partitions, you must first modify the original partition to contain just a portion of the fact data.

 You can then proceed to create additional partitions for storing the remainder of the data.

Construct your filters such 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. The same fact data might be double-counted if it appears in multiple partitions.

• In SQL Server Data Tools, in Solution Explorer, double-click the cube to open it in Cube Designer, and then click the Partitions tab.
• Expand the measure group for which are adding partitions. By default, each measure group has one partition, bound to a fact table in the DSV.
• In the Source column, click the browse (. .) button to open the Partition Source dialog box.

• In Binding Type, select Query Binding. The SQL query that selects the data appears automatically.
• In the WHERE clause at the bottom, add a filter that segments data for this partition.
• Examples of WHERE clause syntax include WHERE OrderDateKey >= '20060101' or WHERE OrderDateKey BETWEEN '20051001' AND '20051201'. For other examples
• Notice that the following filters are mutually exclusive within each set:

et 1:	"SaleYear" = 2012 "SaleYear" = 2013
Set 2:	"Continent" = 'NorthAmerica' "Continent" = 'Europe' "Continent" = 'SouthAmerica'
Set 3:	"Country" = 'USA' "Country" = 'Mexico' ("Country" <> 'USA' AND

• Click Check to check for syntax errors, and then click OK.
• Repeat the previous steps to create the remaining partitions, modifying the WHERE clause each time to select the next data slice.
• Deploy the solution or process the partition to load the data. Be sure to process all partitions.
• Browse the cube to verify the correct data is returned.
• After you have a measure group that uses multiple measure groups, you can create additional partitions in SQL Server Management Studio. Under a measure group, right-click the Partitions folder and select New Partitions to start the wizard.

 Instead of filtering data in a partition, you can use the same query to create a name query in the DSV, and then base the partition on the named query