• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

New memory options for Analysis Services

June 9, 2018   Self-Service BI

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 New memory options for Analysis Services

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.

Let’s block ads! (Why?)

Analysis Services Team Blog

Analysis, Memory, options, Services
  • Recent Posts

    • Pearl with a girl earring
    • Dynamics 365 Monthly Update-January 2021
    • Researchers propose Porcupine, a compiler for homomorphic encryption
    • What mean should I use for this exemple?
    • Search SQL Server error log files
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited