Merging Partitions Cube

Partitions can be merged only if they meet all of the following criteria:

• They have the same measure group.
• They have the same structure.
• They must be in a processed state.
• They have the same storage modes.
• They contain identical aggregation designs.
• They share the same string store compatibility level (applies to partitioned distinct count measure groups only).

If the target partition is empty (that is, it has an aggregation design but no aggregations), merge will drop the aggregations for the source partitions. You must run Process Index, Process Full, or Process Default on the partition to build the aggregations.

Remote partitions can be merged only with other remote partitions that are defined with the same remote instance of Analysis Services.

If you are using a combination of local and remote partitions, an alternative approach is to create new partitions that include the combined data, deleting the partitions you no longer use.

To create a partition that is a candidate for future merging, when you create the partition in the Partition Wizard, you can choose to copy the aggregation design from another of the cube's partitions. This ensures that these partitions have the same aggregation design. When they are merged, the aggregations of the source partition are combined with the aggregations in the target partition.

Updating the Source after Merging Partitions:

Partitions are segmented by query, such as the WHERE clause of a SQL query used to process the data, or by a table or named query that provides data to the partition. The Source property on the partition indicates whether the partition is bound to a query or a table.

When you merge partitions, the contents of the partitions are consolidated, but the Source property is not updated to reflect the additional scope of the partition. This means if you subsequently reprocess a partition that retains its original Source, you will get incorrect data from that partition. The partition will erroneously aggregate data at the parent level

Special Consideration for partitions segmented by partition:

In addition to queries, partitions can also be segmented by table or named query. If the source partition and target partition use the same fact table in a data source or data source view, the Source property is valid after merging partitions. It specifies the fact table data that is appropriate to the resulting partition. 

Because the facts that are required for the resulting partition are present in the fact table, no modification to the Source property is necessary.

Partitions using data from multiple fact tables or named queries require additional work. You must manually merge the facts from the fact table of the source partition into the fact table of the target partition.

Alternatively, you can change the source for the merged partition to a named query that returns the contents of two separate fact tables. If this manual step is not performed, the fact table does not contain complete information.

For the same reason, partitions obtaining segmented data from named queries also require updating. The combined partition must now have a named query that returns the combined result set that was previously obtained from the separate named queries.

Merging Using SSMS

Merging Using XMLA