大网数据广告活动
数据库与应用安全

Error and MDX query fails in SSAS multidimensional instance

· 52CE 我爱测

This article provides a resolution for the problem that occurs when you run a Multidimensional Expressions (MDX) query on a Microsoft SQL Server Analysis Services (SSAS) multidimensional instance.

This article helps you resolve the problem that occurs when you run a Multidimensional Expressions (MDX) query on a Microsoft SQL Server Analysis Services (SSAS) multidimensional instance.

Applies to:   SQL Server 2012 Analysis Services, SQL Server 2014 Analysis Services, SQL Server 2016 Analysis Services, SQL Server 2017 Analysis Services Windows, SQL Server 2019 Analysis Services Windows
Original KB number:   4533057

Symptoms

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:

Query optimizer generated too many subcubes in the query plan

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.

Cause

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.

Resolution

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.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 01/14/2021
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。