Earlier this year, we announced Power BI as a superset of Azure Analysis Services. It presents our investment and vision for the Power BI ecosystem to meet the needs of customers requiring BI solutions that scale to petabytes, are secure, easy to manage, and accessible to all users across their entire enterprise. At the core of this vision is that models support blazing fast performance for interactive analysis and business-critical decisions even over vast data volumes. We’re excited to announce on-demand load capabilities to further ensure highly performant and reliable large datasets. With on-demand load, Large dataset mode enabled models are hydrated into memory faster, which can provide significantly improved report performance.
What is on-demand load?
Due to user inactivity, datasets can be evicted from memory. With the new capability, relevant data pages are loaded on-demand (paged-in to memory) during subsequent query or refresh. Evicted datasets are then made available for queries more quickly and reliably.
Based on a sample of datasets our team used for internal benchmarking, we found on-demand load can significantly improve the downstream user experience — observing up to a 38% reduction in report load times!
This new functionality is most significant with larger models. For small datasets, load times typically aren’t a problem. However, with large models exceeding 5GB, the large data volumes can begin to affect performance. With on-demand load in Power BI Premium, the query performance of your largest datasets, even when previously evicted due to user inactivity can be blazing fast!
Monitor with Dynamic Management Views
On-demand loading surfaces additional information that can be used to understand the state of your models. To access this information, use a Dynamic Management View (DMV) in SQL Server Management Studio (SSMS).
- From SQL Server Management Studio, connect to the server and model object you want to query.
- Right-click the server or database object > New Query > MDX.
- In the query, type Select * from SYSTEMRESTRICTSCHEMA ($System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS, [DATABASE_NAME] = ‘<Dataset Name>’), and then click Execute.
The following new columns are now included in the DMV: IsPageable, IsResident, Temperature, Last Accessed.
To learn more about the new column segment states, see Discover Storage Table Column Segments.
Enabled by default
There are no changes required to enable on-demand load for your capacity. By default, it’s automatically enabled for all Power BI Premium datasets that have Large dataset mode enabled. To learn more about enabling large datasets for your capacity, see Large datasets in Power BI Premium. Also be sure to check out the new Power BI Premium Metrics App to see your memory utilization!