Using Tables Views and Named Queries

If the DSV already organizes facts into individual tables (for example, by year or quarter), you can create partitions based on an individual table, where each partition has its own data source table. 

This is essentially how measure groups are partitioned by default but in the case of multiple partitions, you break the original partition into multiple partitions, and map each new partition to the data source table providing the data.

Views and named queries are functional equivalent to tables, in that all three objects are defined in the DSV and bound to a partition using the Table Binding option in the Partition Source dialog box. You can create a view or named query to generate the data segment needed for each partition.

Note:

When you create mutually exclusive named queries for partitions in a DSV, ensure that the combined data for the partitions includes all data from a measure group that you want to include in the cube. Make sure that you do not leave a default partition based on the entire table for the measure group, or else the query based partitions will overlap the query based on the complete table. 

• Create one or more named queries to use as the partition source.
• The named query must be based on the fact table associated with the measure group. For example, if you are partitioning the FactInternetSales measure group, the named queries in the DSV must specify the FactInternetSales table in the FROM statement.
• 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.
• Click New Partition to start the Partition Wizard. If you created the named queries using the fact table bound to the measure group, you should see each of the named queries you created in the previous step.
• In Specify Source Information, choose one of the named queries you created in a previous step. If you do not see any named queries, go back to the DSV and check the FROM statement.
• Click Next to accept the default values for each subsequent page.
• On the last page, Completing the Wizard, give the partition a descriptive name.
• Click Finish.
• Repeat the previous steps to create the remaining partitions, choosing a different named query 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.