Power BI Dataset Scale-Out

1 - Introduction

Power BI has evolved into the leading platform for both self-service and IT-managed enterprise business intelligence. The decision to migrate to Power BI Premium depends on the requirements of each customer. Customers should carefully evaluate additional benefits to make an informed decision. And to be clear currently there aren't any plans to deprecate AAS.?Having said you would have noticed that there is a focus on investment in Power BI Premium for enterprise data modeling, and so additional values provided by Power BI Premium should increase over time.

In August 2022 I wrote an article on features released on Power BI Service reducing parities with Azure Analysis Services for performance and management of tabular Model : Power BI - Granular control with Analysis Services server properties .

Since this article new features have been released on the topic:

-?????????Accelerate your migration experience from Azure Analysis Services to Power BI Premium with the automated migration tool

-?????????New cache refresh settings available in Power BI Premium

In November I wrote another article on how to use the automated migration tool to Migrate Azure Analysis Services to Power BI .

And since this last article new features have been released on the topic:

·??????Announcing the Public Preview of Power BI Dataset Scale-Out

·??????Announcing backup and restore improvements for large datasets near the size limit

So last AAS parity features to make Power BI a superset of AAS has been tackled?with the Query Scale Out (QSO) feature enabling?high user concurrency for semantic models consumed at global scale?by spreading the query load across multiple read replicas and reducing global footprint of memory for refreshes activity.

Here is the current state of functionality supported in both:

Microsoft Documentation: Feature comparison

?? In this article I will demo the Scale Out feature currently in Public Preview.

2 - Scale Out

Power BI Dataset Scale-Out offers the following key benefits: Cost-efficient dataset scalability, Reliable performance for both refresh and query operations?and maximized dataset memory utilization.

Some prerequisites:

• The Power BI Administrator should have enabled the feature “Scale out queries for large datasets” (enabled by default) and in the Tenant Portal Setting and XMLA read/write are enabled on the Premium Capacity:?

• Workspace on top of the Premium Capacity should enable large datasets format (not by default):

• The dataset should also have the Large Dataset storage format enabled:?

• During the public preview phase, workspaces must be enabled manually for Premium Scale-Out by using the following XMLA request : https://powerbiblogs.azurewebsites.net/wp-content/uploads/2023/01/XMLA-command.txt

You can connect to a specific type of dataset copy by appending one of the following strings to the dataset's URL.

• Read-only: "powerbi://api.powerbi.com/v1.0/myorg/RCA_ScaleOut?readonly;initial catalog=ScaleOut_Test"
• Read/write : “powerbi://api.powerbi.com/v1.0/myorg/RCA_ScaleOut?readwrite;initial catalog=ScaleOut_Test”

Having created a simple Power BI report with a DAX calculated column with actual time:?

After a Process of the Dataset we can now execute on both ReadOnly and ReadWrite Endpoint the same query on the Calculated Column to compare both results:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")

$PowerBILogin = "###"
$PowerBIPassword = "###"
$Query = "Evaluate Query1"

$PowerBIEndpoint = "powerbi://api.powerbi.com/v1.0/myorg/RCA_ScaleOut?readonly;initial catalog=ScaleOut_Test"
$Connection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$Results = New-Object System.Data.DataTable
$Connection.ConnectionString = "Datasource="+ $PowerBIEndpoint +";UID="+ $PowerBILogin +";PWD="+ $PowerBIPassword??
$Connection.Open()
$Adapter = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter $Query ,$Connection
$Adapter.Fill($Results)
$Results
$Connection.Dispose()
$Connection.Close()

$PowerBIEndpoint = "powerbi://api.powerbi.com/v1.0/myorg/RCA_ScaleOut?readwrite;initial catalog=ScaleOut_Test"
$Connection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$Results = New-Object System.Data.DataTable
$Connection.ConnectionString = "Datasource="+ $PowerBIEndpoint +";UID="+ $PowerBILogin +";PWD="+ $PowerBIPassword??
$Connection.Open()
$Adapter = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter $Query ,$Connection
$Adapter.Fill($Results)
$Results
$Connection.Dispose()
$Connection.Close()?        

The PowerShell Script is available here: https://raw.githubusercontent.com/Pulsweb/MyScripts/master/Power BI/Query PBI Dataset in Scale-Out.ps1

The Read-Only Replica is unsynchronized since I have refreshed the dataset using XMLA Endpoint. It would not have been the case with an OnDemand UI and Scheduled Refresh.

So now, how can I sync my two datasets ?? ?

New Power BI Rest API support the monitoring and synchronization, for example to check the sync status of your dataset copies, you can use the?SyncStatus?REST API as explained in the following documentation: Sync a read-only dataset scale-out replica

You can also use the great script developed by Rui Romano to monitor the synchronization: pbiscripts/Dataset-QSO.ps1

Running previous PowerShell script showcase that in fact both datasets are now synchronized:

3 - Conclusion

The Power BI Dataset Query scale-out feature is currently in Preview (Query scale-out ) and new features on top is already planned like the Balance user queries with query scale-out .

Why not have then a way to have Read Only replicas in different Azure regions using Multi-Geo support for Power BI Premium bringing the dataset closest to end users ? ??

Feel free to suggest your own Ideas to the Product Group.

??Kay Unkroth Power BI Program Manager, will discuss and showcase Dataset Scale Out for government customers who have invested in our Premium capacity technology on February 9th at 10am PST/12pm CST/1pm EST:?https://aka.ms/SLGPowerBICommunityCall

Wishing you happy Power BI Dataset Scale-Out Tests ??