Freigeben über

SQL Server Analysis Services Multidimensional Model – Merging Partitions with Slice

  • Artikel

According to SQL Server Books Online: Partitions can be merged only if you meet all of the following criteria: https://technet.microsoft.com/en-us/library/ms175318.aspx. They are in the same cube.

· They have the same structure.

· 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).

The BOL also talks about a known behavior with “Merging Partitions That Have Different Data Slices” https://technet.microsoft.com/en-us/library/ms175410.aspx

“When you merge partitions that have data slices specified in the Partition Wizard the resulting partition can contain unexpected, incorrect data after it is processed. To prevent this, you can create a filter that specifies the data in the resulting partition.”

However, you may find that even you meet all the requirements above and you have no plan to reprocess the data after merge. You still run into some unexpected situation.

  1. If the partition slices are not explicitly set, there are no problems in merging the partitions. This problem only occurs if the partition slices are explicitly set. If we remove the slices now, it will unprocess the data but that defeats the main reason we choose to use the “merge partition” feature.
  1. Let say we have Partition A with slice “[Date].[Calendar Year].[CY 2003]” and Partition B with slice “[Date].[Calendar Year].[CY 2004]”. After merging Partition A with Partition B, the resulting partition A still has the old slice “[Date].[Calendar Year].[CY 2003]”. Merging Partition B into Partition A would violate that [CY 2003] constraint. The following error will be shown:

Errors in the OLAP storage engine: The slice specified for the Calendar Year attribute is incorrect.

clip_image002

  1. The same behavior is observed in SSAS 2005, 2008, 2008 R2 and SSAS 2012 Multidimensional model

Look like we are stuck - If we merge two existing partitions with different slices, we have a constraint issue. If we update the slices and reprocess the partitions, it defeats the purpose of “merging partitions”.

 

If you run into this situation, my suggestion for you is to

  • Create an empty partition with an update slice and a correct new blinding SQL statement + “and 1=0” at the end.
  • Process the new empty partition and then do the merge. Remove the “and 1=0” portion from the blinding query.

C S John Lam | SQL Business Intelligence | Premier Field Engineering