Analysis Services server properties in Power BI Premium are now in public preview

At Power BI, we’ve committed to providing powerful enterprise modeling capabilities to Power BI Premium. As part of our product journey over the past five years, this commitment has brought numerous Azure Analysis Services (AAS) semantic modeling features for performance and management to Power BI Premium.

Today, we are excited to announce the additional Analysis Services server properties that are now publicly available in Power BI Premium and Power BI Embedded as configurable workspace settings. For customers that are considering migrating from Azure Analysis Services to Power BI Premium, it’s another example of how we ensure you not only have better performance in Power BI Premium, but also achieve similar settings behaviors after your dataset migration. In addition to the existing capacity settings, administrators can now modify workspace settings of the most commonly configured server properties. This capability allows customers to implement granular control of their workspaces to optimize and alter query behavior. You may notice that several AS server properties share similar behaviors to some capacity settings found in the service. For those settings, we’ve added a toggle that will allow admins to decide which setting is applied, the capacity setting or the workspace setting.

The table below provides a detailed description of the additional AS server properties and real-world applications of the setting. Check out a full list here.

 

**A Power BI capacity setting equivalent exists.

Server Properties	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 This setting will affect all transactions (e.g., metadata transactions).
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 value 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 do I enable or disable server properties for a 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 do I modify a server property for a workspace?

1. Follow these steps to get your workspace connection URL.
2. Open SQL Server Management Studio (SSMS)
3. Connect to your Premium workspace in SSMS
4. In Object Explorer, right click on the Premium workspace and select Properties                                                                                                                                                                     
   
   
   
5. In the Analysis Services Properties panel select General                                                                     
   
   
   
6. Check the Show Advanced (All) Properties box                                                                               
   
   
   
7. Modify a property in the Value column
8. 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 AdminTimeout or DAX\DQ\MaxIntermediateRowsetSize.

1. Follow steps 1-5 above
2. Change the value of any property (do not select OK)
3. Select the script button
4. In the query window, change the name of the modified server property to AdminTimeout.                                                                                                         
5. Modify value to a value within range
6. Select Execute