Why Cube Partition "Auto Slice" Does Not Work As Desired Usually?

"During processing of MOLAP partitions, Analysis Services internally looks at the Source Query and identifies the range of data that is contained in each partition by using the Min and Max DataIDs of each attribute to calculate the range of data that is contained in the partition. The data range for each attribute is then combined to create the slice definition for the partition.
The Min and Max DataIDs can specify a either a single member or a range of members. For example, partitioning by year results in the same Min and Max DataID slice for the year attribute, and queries to a specific moment in time only result in partition queries to that year’s partition.
It is important to remember that the partition slice is maintained as a range of DataIDs that you have no explicit control over. DataIDs are assigned during dimension processing as new members are encountered. Because Analysis Services just looks at the minimum and maximum value of the DataID, you can end up reading partitions that don’t contain relevant data.
For example: if you have a partition, P2003_4, that contains both 2003 and 2004 data, you are not guaranteed that the minimum and maximum DataID in the slide contain values next to each other (even though the years are adjacent). In our example, let us say the DataID for 2003 is 42 and the DataID for 2004 is 45. Because you cannot control which DataID gets assigned to which members, you could be in a situation where the DataID for 2005 is 44. When a user requests data for 2005, Analysis Services looks at the slice for P2003_4, sees that it contains data in the interval 42 to 45 and therefore concludes that this partition has to be scanned to make sure it does not contain the values for DataID 44 (because 44 is between 42 and 45).
Because of this behavior, auto slice typically works best if the data contained in the partition maps to a single attribute value. When that is the case, the maximum and minimum DataID contained in the slice will be equal and the slice will work efficiently.
Note that the auto slice is not defined and indexes are not built for partitions with fewer rows than IndexBuildThreshold (which has a default value of 4096)."

Reference:  SQL Server 2008 R2 Analysis Services Performance Guide

"A duplicate attribute key has been found when processing: Table..." Error Causes & Fixes

"Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_TheTable', Column: 'TheColumn', Value: 'XYZ'. The attribute is 'The Attribute'."

There is a number of causes and ways to fix the above error:

• Could be a result of having both blanks and NULLs in the source table/view. SSAS does
  
   SELECT DISTINCT  COALESCE(attr,'') FROM SOURCE 
  which converts NULLs to blanks, resulting in duplicate value blanks in the resulting feed - hence the error.
  Solutions : Remove all nulls from the data source by either filtering out rows containing nulls, or update null values with another value before processing the cube. Another solution is to change the way SSAS process nulls, and to do so: go to the cube's Dimension Usage tab, open to edit the relation between the dimension contains the attribute with the error and the measure group, click Advance button, select the attribute in the Measure Group Bindings window, choose the appropriate "Null Processing" option, finally reprocess the cube. The following explains the options for Null Processing:
  
  • ZeroOrBlank: This tells the server to convert the NULL value to a zero (for numeric data items) or a blank string (for string data items).
  • Preserve: This tells the server to preserve the NULL value. The server has the ability to store NULL just like any other value.
  • Error: This tells the server that a NULL value is illegal in this data item. The server will generate a data integrity error and discard the record.
  • UnknownMember: This tells the server to interpret the NULL value as the unknown member. The server will also generate a data integrity error. This option is applicable only for attribute key columns.
  • Default: This is a conditional default. It implies ZeroOrBlank for dimensions and cubes, and UnknownMember for mining structures and models.
• Actually, when I was searching for the descriptions of the Null processing, I found a nice two posts by Hilmar Buchta where he list even more than the reasons I wanted to write about:
• Reason 1 to 3.
• Reason 4 to 6.

A loop involving the member with the key [no.], was detected in the parent-child relationship between the attribute [child_id] and the attribute [parent_id].

As the error implies there is a loop in the parent-child hierarchy in one of the dimensions you are trying to process. For example if you have an Employee dimension and there is a parent-child relationship between employee_id and supervisor_id attributes, having this error means that you have in your source table _for example_ Ali as a supervisor for John but in another record John is ALSO the supervisor of Ali!.

To find such loop run the following query on your source table:

select * from      tableName tl1

         left join tableName tl2

         on tl1.child_id = tl2.parent_id

where tl1.parent_id = tl2.child_id