New memory options for Analysis Services

  • Article

We’re happy to introduce some new memory settings for Azure Analysis Services and SQL Server Analysis Services tabular models. These new settings are primarily for resource governance, and in some cases can speed up data refresh.

IsAvailableInMdx

The IsAvailableInMdx column property is available in Azure Analysis Services and SQL Server Analysis Services 2017 CU7.

It prevents building attribute hierarchies, reducing memory consumption. This means the column is not available for group-by queries to MDX clients like Excel. Fact (transactional) table columns often don’t need to be grouped and are often the ones that use up the most memory.

This property can also improve performance of refresh operations; particularly for tables with lots of partitions. Building attribute hierarchies can be very expensive because they’re built over all the data in the table.

Currently, this property is not yet exposed in SSDT. It must be set in the JSON-based metadata by using Tabular Model Scripting Language (TMSL) or the Tabular Object Model (TOM). This property is specified as a Boolean.

The following snippet of JSON-based metadata from the Model.bim file disables attribute hierarchies for the Sales Amount column:

   {
    "name": "Sales Amount",
    "dataType": "decimal",
    "sourceColumn": "SalesAmount",
     "isAvailableInMdx": false
  }

QueryMemoryLimit

The Memory\QueryMemoryLimit property can be used to limit memory spools built by DAX queries submitted to the model. Currently, this property is only available in Azure Analysis Services.

Changing this property can be useful in controlling expensive queries that result in significant materialization. If the spooled memory for a query hits the limit, the query is cancelled and an error is returned, reducing the impact on other concurrent users of the system. Currently, MDX queries are not affected. It does not account for other general memory allocations used by the query.

The settable value of 1 to 100 is a percentage. Above that, it’s in bytes. The default value of 0 means not specified and no limit is applied.

You can set this property by using the latest version of SQL Server Management Studio (SSMS), in the Server Properties dialog box. See the Server Memory Properties article for more information.

QueryMemoryLimit

DbpropMsmdRequestMemoryLimit

The DbpropMsmdRequestMemoryLimit XMLA property can be used to override the Memory\QueryMemoryLimit server property value for a connection. Currently, this property is only available in Azure Analysis Services.  The unit of measure is in kilobytes. See the Connection String Properties article for more information.

RowsetSerializationLimit

The OLAP\Query\RowsetSerializationLimit server property limits the number of rows returned in a rowset to clients. Currently, this property is only available in Azure Analysis Services.

This property, set in the Server Properties dialog box in the latest version of SSMS, applies to both DAX and MDX. It can be used to protect server resources from extensive data export usage. Queries submitted to the server that would exceed the limit are cancelled and an error is returned. The default value is -1, meaning no limit is applied.