Microsoft recently unveiled its July round-up of updates to Power BI. Additionally, users of the business analytics service are also currently testing some new features in early access as part of the Dynamics 365 2019 release wave 2.
Now, the tech giant has announced the introduction of five new capacity settings for its premium offering of Power BI. These are aimed toward providing administrators better control over capacities, thereby avoiding related issues before they become a problem. Moreover, the adjusting of dataset sizes will enable better performances to be leveraged from different capacities.
The following is a detailed description of each of the settings, and how they might turn out to be useful in real-world scenarios:
| Setting Name | Description | Scenario |
| Max Offline Dataset Size (GB) | Maximum size of the offline dataset in memory. This is the compressed size on disk. Default value is set by SKU and the allowable range is from 0.1 – 10 GB | 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 new 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. |
| Query Memory Limit (%) | Applies only to DAX measures and queries. Specified in % and limits how much memory can be used by temporary results during a query. | Some queries/calculations can result in intermediate results that use a lot of memory on the capacity. This can cause other queries to execute very slowly, cause eviction of other datasets from the capacity, and lead to out of memory errors for other users of the capacity. Without this setting, capacity administrators would find it challenging to identify which report/query is causing the problem, so that they could work with the report author to improve the performance. With this new setting, admins can better control impact of bad or expensive report on others using the capacity. |
| Query Timeout (seconds) | An integer that defines the timeout, in seconds, for 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. | When users are experiencing spinning on the report primarily due to another expensive report, admins would sometimes have to move the workspace to a different capacity. With the new settings, admins can better control expensive queries so that they have less impact on other users of the capacity. |
| Max Intermediate Row Set Count
| The max number of intermediate rows returned by DirectQuery. Default value is set to 1000000 and allowable range is between 100000 and 2147483647 | 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. |
| Max Result Row Set Count | Defines the maximum number of rows returned in a DAX query. Default value is set to -1(no limit) and 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. |
The aforementioned settings can be accessed through the Capacity settings page in the Admin Portal. Expanding the "Workload" section under the "Management" tab will display them. Although they will already be preloaded with default values, admins can change these depending upon their own requirements and preferences.
You can learn more about creating and managing capacities in Power BI Premium over here, while ideas regarding future improvements for the service can be submitted here.