Server properties in Analysis Services

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Administrators can modify many default configuration properties of an Azure Analysis Services (Azure AS) server resource, SQL Server Analysis Services (SSAS) server instance, or a Power BI workspace assigned to a Premium capacity. Modifying default configuration properties is not supported for Power BI Premium per user.

Properties pages in SQL Server Management Studio (SSMS) show a subset of those properties most likely to be modified. For Azure AS and Power BI, all applicable properties can be modified by using XMLA script in SSMS. For SQL Server Analysis Services, all applicable properties can be modified in the msmdsrv.ini file.

Note

In Power BI, a workspace is effectively an Analysis Services server. In context of Analysis Services, the terms workspace, server, and instance are synonymous.

Permissions

For Azure AS and SSAS, server administrator permissions are required to modify server properties.

For Power BI, workspace admin permissions are required to modify workspace properties.

Power BI XMLA-based workspace properties

Power BI workspaces support modifying a limited subset of XMLA-based Analysis Services properties in the General, DAX, Filestore, Memory, and OLAP categories by using SSMS. When modified, the change applies only to that workspace.

Some XMLA-based workspace properties have equivalent capacity settings that can be set in the Admin portal, in Capacity settings/Workloads/SEMANTIC MODELS. These settings apply to all workspaces assigned to the capacity, however, workspace admins can override settings for a specific workspace by using SSMS.

Analysis Services property	Category	Power BI Capacity setting
AdminTimeOut	General	NA
ClientCacheRefreshPolicy	General	NA
CommitTimeout	General	NA
DefaultSegmentRowCount	General	NA
ExternalCommandTimeout	General	NA
ExternalConnectionTimeout	General	NA
ForceCommitTimeout	General	NA
MaxIntermediateRowsetSize	DAX	Max Intermediate Row Set Count
MaxOfflineDatasetSizeGB	Filestore	Max Offline Dataset Size
RowsetSerializationLimit	OLAP	Max Result Row Count
QueryMemoryLimit	Memory	Query Memory Limit
ServerTimeout	General	Query Timeout

Capacity admins can enable or disable the ability for workspace admins to modify XMLA-based workspace property settings. By default, this setting is enabled, meaning workspace admins can modify workspace property settings by using SSMS. Capacity admins can disable this setting in the Admin portal, in Capacity settings > Workloads > SEMANTIC MODELS > Observe XMLA-based settings.

When disabled, workspace admins can't modify any XMLA-based property setting.

Configure by using SQL Server Management Studio

1. In SQL Server Management Studio, connect to an SSAS, Azure AS, or Power BI instance.

2. In Object Explorer, right-click the instance, and then click Properties. The General page appears, displaying the more commonly used properties.

3. To show more properties, click the Show Advanced (All) Properties checkbox at the bottom of the page.

Configure by using XMLA script

Those properties that can't be set in the Properties page in SSMS or in the msmdrsrv.ini file (SSAS only) can be set by using the XMLA Alter Element in an XMLA script in SSMS.

Configure in msmdsrv.ini

For SSAS, server properties are in the msmdsrv.ini file. If the property you want to set isn't visible even after you show advanced properties in SSMS, you might need to edit the msmdsrv.ini file directly. For a default installation, msmdsrv.ini can be found in the \Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Config folder.

1. Check the DataDir property in the General properties page in Management Studio to verify the location of the Analysis Services program files, including the msmdsrv.ini file.

   On a server that has multiple instances, checking the program file location ensures you're modifying the correct file.

2. Navigate to the config folder of the program files folder location.

3. Create a backup of the file in case you need to revert to the original file.

4. Use a text editor to view or edit the msmdsrv.ini file.

5. Save the file and restart the service.

Server property categories

The following articles describe the various configuration properties:

Topic	Applies to	Description
General Properties	Azure AS, SSAS, Power BI	General properties are both basic and advanced properties, and include properties that define the data directory, backup directory, and other server behaviors.
Data Mining Properties	SSAS	Data mining properties control which data mining algorithms are enabled and which are disabled. By default, all of the algorithms are enabled.
DAX Properties	Azure AS, SSAS, Power BI	Defines properties related to DAX queries.
Feature Properties	Azure AS, SSAS	Feature properties pertain to product features, most of them advanced, including properties that control links between server instances.
Filestore Properties	Azure AS, SSAS, Power BI	File store properties are for advanced use only. They include advanced memory management settings.
Lock Manager Properties	Azure AS, SSAS	Lock manager properties define server behaviors pertaining to locking and timeouts. Most of these properties are for advanced use only.
Log Properties	Azure AS, SSAS	Log properties controls if, where, and how events are logged on the server. Includes error logging, exception logging, flight recorder, query logging, and traces.
Memory Properties	Azure AS, SSAS, Power BI	Memory properties control how the server uses memory. They're primarily for advanced use.
Network Properties	Azure AS, SSAS	Network properties control server behavior pertaining to networking, including properties that control compression and binary XML. Most of these properties are for advanced use only.
OLAP Properties	Azure AS, SSAS, Power BI	OLAP properties control cube and dimension processing, lazy processing, data caching, and query behavior. Includes both basic and advanced properties.
Security Properties	Azure AS, SSAS	Security properties contain both basic and advanced properties that define access permissions. Includes settings pertaining to administrators and users.
Thread Pool Properties	Azure AS, SSAS	Thread pool properties control how many threads the server creates. These properties are for advanced use only.

See also

Create Analysis Services scripts in SSMS
Analysis Services instance management