Migrate Azure Analysis Services to Power BI ??
Romain Casteres
Cloud Solution Architect @ Microsoft | Unlocking Customer Data Value | Learn & Share
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 that 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 new features have been released on the topic:
Last AAS parity feature to make Power BI a superset of AAS will be handled 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.
Microsoft Documentation: Feature comparison
2 - AAS Model Migration
?? Following steps as of November 23, 2022.
I will use the new Automated Migration tool to migrate an AAS Model to a Dataset within a Premium Workspace, I will then bind Power BI Service reports to the newly migrated Dataset and enable the Server Redirection to automatically redirect client applications, tools, and automation processes.
The Power BI Report "MyReport" is connected to the AAS Model in Live Connection mode:
Generating a Query from the Power BI Report on top of the AAS Model after having configured the Diagnostic setting to Log Analytics Workspace: Diagnostic logging for Azure Analysis Services
The first step for the migration is to review all prerequisites in Azure Analysis Services as well as in Power BI. Both services are in the same Tenant, I have configured the Azure Connections to an Azure Data Lake Storage Gen 2 and enabled the read-write XMLA endpoint…
The AAS Model "AdventureWorks" has a Compatibility Level 1200 in North Europe whereas the Power BI PPU is in West-Central US (default location of my Power BI Tenant).
领英推荐
Note that all prerequisites are met and that having the services in different regions will not block the migration.
The compatibility Level has been raised to 1500.
During preview, Live connect reports in the Power BI service connected to an Azure Analysis Services model database being migrated aren't automatically bind to the new dataset in Power BI. In order to bind the report to the migrated Dataset I run the following PowerShell Script:?Reports - Rebind Report - REST API
Login-PowerBI
$token = (Get-PowerBIAccessToken)["Authorization"]
$body = @{"datasetId" = "b01fb2dc-e502-492e-a81f-3776e28156b0"}
Invoke-WebRequest `
?-Method 'Post' `
?-Uri "https://api.powerbi.com/v1.0/myorg/groups/cebb9bc4-5593-4fa2-95b0-b6a58a96ebd1/reports/f99a8035-fdb2-4909-b479-b055604897d2/Rebind" `
?-Headers @{ "Authorization"=$token } `
?-Body ($body|ConvertTo-Json) `
?-ContentType "application/json"
From the Lineage View, "MyReport" is now connected in Live Connection to the newly migrated Dataset and Report is working fine:
Looking at query made on top of the Power BI Dataset (Azure Log Analytics has been configured on top of the Premium Workspace): Using Azure Log Analytics in Power BI (Preview)
Power BI Reports within the service are now connected to the migrated Dataset but what about all other client applications, tools, and automation processes? Well for those we can activate the Server Redirection?to continue to work without having to change the server’s name reference in the connection string. Client applications, tools, and automation processes are automatically redirected to the migrated dataset in Power BI before updating them at your pace:
Azure Analysis Services server?can be paused to reduce cost but if it remains started after you’ve enabled server redirection, existing Azure Analysis Services models can still be queried by setting the?AsAzureRedirection?connection string property to?Disabled.
??During preview, do not delete Azure Analysis Services server, doing so will cause redirection to fail and there is no way to recover redirection. When you enable server redirection the Azure Analysis Services server must exist and can't be paused >>> This is only true during the enablement of the redirection that can take few minutes.
When trying to connect to the AAS Server with SSMS to the AAS Service, I am now connected to the Power BI Workspace (redirection happening transparently):
From Excel all connections are well redirected to the Dataset without any change:?
But from Power BI Desktop you will receive the following error message and it will be the same in the Power BI Service for not already rebind Reports:?
The solution for those Power BI Desktop reports is to update the Datasource.
?? Prefer to bind Power BI Service Reports before activating the redirection.
All supported tools for the Redirection are presented here: Client Library and applications
3 – Conclusion
Within this article we have seen the migration steps simplified with the newly Migrate Azure Analysis Services to Power BI (in preview).
Not all AAS cubes are meant to be migrated to Power BI Premium. It will depend on the requirements of each customer and each Data Model, taking into account Model size, features used, cost ... or business needs (Pause features). But for customers having Power BI reports that connect to AAS, the migration can represent an opportunity to consolidate artifacts. Consolidation makes Premium capacity more economically viable and can help to provide higher levels of throughput, scalability and advanced features support (Composite Model, Aggregations…).