"Query optimizer generated too many subcubes" error and MDX query fails in SSAS multidimensional instance

When you run a Multidimensional Expressions (MDX) query on a Microsoft SQL Server Analysis Services (SSAS) multidimensional instance, the MDX query fails and returns the following error message: 

This error occurs if the following conditions are true:

• Too many calculated members are defined on a single hierarchy level or attribute.
• Many fields or attribute members are put onto each axis. Or, many fields are put together on the rows or columns of a PivotTable in Microsoft Excel.
• All members of selected hierarchies are included in the axis.
• Grand totals and subtotals are turned on in the Excel PivotTable.

The SSAS Formula Engine (FE) has to generate all relevant MDX sets for the Storage Engine (SE) Query subcube or Sonar subcube. There is a limit on the number of SE Query subcubes per query that can be generated. This is by design. Currently in the query plan, an error occurs if the FE generates too many query subcubes for the query.

To avoid this error, follow these "best practices" guidelines:

• In the Excel PivotTable, turn off both grand totals and subtotals.
• Remove the hierarchy from the Rows or Columns axis of the PivotTable in the Excel UI.
• Do not define too many calculated members (for example, more than 500) on the dimension hierarchy. Instead, have regular members in the dimension hierarchy, and use MDX scope assignment expressions (also known as calculated cells) to replace the expressions of those calculated members.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.