We’re pleased to provide an update to the following announcement – Analysis Services server properties in Power BI Premium are now in public preview. At this time, we’ve transitioned this capability to general availability with full support in Power BI Premium and Power BI Embedded. As mentioned in the public preview announcement, the Analysis Services (AS) server properties gives administrators granular control to optimize and alter query behavior in their workspaces. Although several AS server properties share similar behaviors to a few capacity settings found in the Power BI service, administrators coming from Azure Analysis Services will recognize these AS server properties as some of their most commonly configured settings. To enable, admins should navigate to the settings page and turn on the toggle that determines which setting is applied— the capacity or the workspace setting. Refer here to review the full list of available settings. Continue reading below to learn more about the scenarios where these AS server properties may be helpful in your use case.
**A Power BI capacity setting equivalent exists.
| Server Property | Description | Scenario |
| AdminTimeout | A signed 32-bit integer property that defines the administrator timeout in seconds. The default value for this property is zero (0), which indicates there is no timeout. | AdminTimeout can be used to set a refresh timeout on Power BI Premium Gen 2 for runaway refreshes. This setting will affect all transactions (e.g., metadata transactions). |
| ClientCacheRefreshPolicy | Overrides the scheduled cache refresh setting for all Power BI datasets. | Data caches (dashboard tile data and report data) are stored to enhance report interactivity for users. Sometimes an excessive number of cache queries are submitted to Analysis Services and overload the server. This setting allows the user to disable automatic cache refresh. All Power BI Live Connect reports and dashboards will observe the setting irrespective of the dataset-level settings, or which Power BI workspace they reside on. |
| CommitTimeout | An integer property that specifies how long (in milliseconds) the server will wait to acquire a write lock for the purpose of committing a transaction. | If a transaction is unable to acquire the commit lock after the specified timeout, the transaction will fail and be rolled back. The default value is infinite. |
| VertiPaq\DefaultSegmentRowCount | Defines the number of data rows per segment. Every table partition has at least one segment of data. The default is 8,388,608 rows. | By default, tables are processed into segments that contain 8,388,608 rows each. The larger the segment, the better the compression. On very large tables, it is important to test different segment sizes and measure the memory usage, so to achieve optimal compression. Keep in mind that increasing the segment size can negatively affect processing time: the larger the segment, the slower the processing. |
| ExternalCommandTimeout | An integer property that defines the timeout, in seconds, for commands issued to data sources. | When executing commands against external data sources, the operations can sometimes be complex and expensive and take a long time. This setting allows a user to adjust the timeout for queries issued to external servers. |
| ExternalConnectionTimeout | An integer property that defines the timeout, in seconds, for creating connections to external data sources. | When the user encounters connection time out errors, this setting allows a user to increase the timeout for the connection to an external server. |
| ForceCommitTimeout | An integer property that specifies how long, in milliseconds, a write commit operation should wait before canceling other commands that preceded the current command, including queries in progress. | This setting is used to control how long a transaction waits when it is trying to acquire the final commit locks at the end of the transaction. If there are other operations that prevent the commit lock from being acquired and the timeout expires, the transaction will request those operations to be canceled. When the timeout is set to zero, it implies that it is infinite. |
| DAX\DQ\MaxIntermediateRowsetSize** | Maximum number of rows returned in a DAX query. | When a query to a DirectQuery dataset results in a very large result from the source database, it can cause a spike in memory as well as a lot of expensive processing of data. This can lead to other users and reports running low on resources. This setting allows the capacity administrator to adjust how many rows can be fetched by an individual query to the data source in a dataset. |
| FileStore\MaxOfflineDatasetSizeGB** | Maximum size of the offline dataset in memory. This is the compressed size on disk. The default value is 0, which is the highest limit defined by SKU. The allowable range is between 0 and the capacity size limit. | When users are experiencing slowness due to a large dataset taking up memory resources, admins would very often end up in the similar cycle of first identifying the culprit datasets, contacting the owner or migrating to a different capacity. With this setting, admins can now configure the dataset size and prevent report creators from publishing a large dataset that could potentially take down the capacity and secondly save the admin from the painful cycle of identifying and mitigating. |
| OLAP\Query\RowsetSerializationLimit** | The maximum number of rows returned in a DAX query. The default value is -1 (no limit), and the allowable range is between 100000 and 2147483647. | Sometimes, a user can execute an expensive DAX query that returns a very large number of rows. This can cause a lot of resource usage and affect other users and reports executing on the capacity. This setting allows the capacity administrator to limit how many rows should be returned for any individual DAX query. |
| Memory\QueryMemoryLimit** | Specified in % and limits how much memory can be used by a query. | Some queries can be expensive and may consume large amounts of memory on the capacity. This can negatively impact other queries and operations on the capacity, causing slow performance, out of memory errors and dataset evictions. Identifying such queries can be challenging for administrators, and this setting allows them to have some control over the impact of these expensive queries. |
| ServerTimeout** | An integer that defines the timeout, in seconds, for client queries. The default is 3600 seconds (or 60 minutes). Note that Power BI reports will already override this default with a much smaller timeout for each of its queries to the capacity. Typically, it is approximately 3 minutes. | Long running queries can consume CPU and memory resources and have a negative impact on other operations that may be executing on the capacity. This setting allows the administrator to adjust how long queries should be allowed to run on the system, so that slow and expensive queries could be controlled. |
How to enable or disable server properties for my workspace?
You enable these properties to be enforced in your workspace by turning on the toggle to Observe XMLA-based workspace settings. To enable navigate to datasets workload in the Capacity Settings page under Admin Portal.
How to modify a server property for my workspace?
- Follow these steps to get your workspace connection URL.
- Open SQL Server Management Studio (SSMS)
- Connect to your Premium workspace in SSMS
- In Object Explorer, right click on the Premium workspace and select Properties
- In the Analysis Services Properties panel select General
- Check the Show Advanced (All) Properties box
- Modify a property in the Value column
- Select OK
How do I modify properties that are private settings by default?
Some server properties are private and will not appear in the SQL Server Management Studio (SSMS) Analysis Services Properties panel. Follow the instructions below to modify these private properties such as Admin Timeout or MaxIntermediateRowsetSize.
- Follow steps 1-5 above
- Change the value of any property (do not select OK)
- Select the script button
- In the query window, change the name of the modified server property to Admin Timeout
- Modify value to a value within range
- Select Execute
Note: When modifying private properties, the following error may be displayed: The object ID cannot be changed from ‘autopremiumhost’ To resolve, remove the line <ID>autopremiumhost</ID> and rerun the query.
Full path for Server Properties
If you choose to modify server properties using the script above, be aware that certain server properties have a full path for XMLA. Use the full path name when modifying using SSMS.
| Full Path |
| VertiPaq\DefaultSegmentRowCount |
| FileStore\MaxOfflineDatasetSizeGB |
| OLAP\Query\RowsetSerializationLimit |
| Memory\QueryMemoryLimit |