FIX: MDX query returns errors if the value of MaxRolapOrConditions is greater than 256 in SQL Server Analysis Services

Symptoms

Assume that you use relational OLAP (ROLAP) storage mode for a measure group in an instance of SQL Server 2014 or 2016 Analysis Services. After you set the value of the MaxRolapOrConditions advanced property to more than 256, you execute MDX queries on that measure group. In this case, you receive errors that resemble the following:

#Error The DOM parser failed to load and parse the stream. URL:'' Reason: 'Operation aborted: max-element-depth constraint violated.' Source:''. File position: 0. Line: 6.

Cause

Analysis Services generates a pseudo-SQL XML document, which is then parsed through a XML Style sheet to generate the final SQL statement that is sent to the relational source. When generating the initial internal XML document, OR conditions for a column are generated as nested XML elements as in the following example:

<OR>
<Condition>column1 = value1</Condition>
<OR>
<Condition>column1 = value2</Condition>
<OR>
<Condition>column1 = value3</Condition>
</OR>
</OR>
</OR>

The default MaxElementDepth Property for an MSXML DOM object that is used by Analysis Services to build the XML document is 256 in MSXML 6.0.

Therefore, when the MaxROLAPOrConditions configuration is increased, the generated XML document can exceed this maximum depth.

Resolution

This issue is fixed in the following cumulative update for SQL Server:
    Cumulative Update 5 for SQL Server 2016 RTM

    Cumulative Update 2 for SQL Server 2016 SP1 


About cumulative updates for SQL Server
About cumulative updates for SQL Server

Status

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

More Information

This fix changes how the intermediate XML document is generated, so that each additional OR condition is not nested in the prior OR XML element. Therefore, you could increase the MaxROLAPOrConditions beyond 256. However, there are still practical limitations to the number of OR conditions that can be used.

As the number of OR conditions increases, the complexity of the SQL statement that is generated may exceed the capacity of the relational database server, causing new error conditions.

The following are examples of conditions that may cause new errors:
  • Exceeding the maximum number of parameters in a statement
  • Exceeding the maximum number of conditions in a where clause
  • Errors in parsing the SQL query string
  • Exceeding the maximum size of an SQL query string

Therefore, we recommend that any adjustments to the MaxROLAPOrConditions configuration value be made with caution.

References

Learn about the terminology Microsoft uses to describe software updates.
Properties

Article ID: 3208179 - Last Review: 2017 ots. 21 - Revision: 3

Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2016 Service Pack 1, Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core, Microsoft SQL Server 2016 Standard

Oharrak