Migrating Azure Analysis Services to Power BI
In today’s fast-paced world, businesses are constantly seeking ways to make sense of their data to make smarter decisions. One tool that has revolutionized data analysis and visualization is Microsoft Power BI. With its user-friendly interface and strong integration with Microsoft, Power BI has become a popular choice for businesses.
If you’ve been using Azure Analysis Services (AAS) for data modeling and analysis, migrating to Power BI can be a game-changer. With the Microsoft Azure Analysis Services to Microsoft Power BI Premium migration feature in Power BI Service, you can seamlessly migrate your AAS database to a dataset in Power BI Premium, Power BI Premium Per User, or Power BI Embedded workspace. This blog aims to provide you with a comprehensive understanding of the use cases for this migration feature and guide you through the step-by-step process, ensuring a smooth transition from AAS to Power BI.
2 Use Cases
Use cases for Migration of AAS to Power BI are several, of which the following are the most beneficial in contrast with AAS:
- Platform scalability and resiliency
- Power BI Premium architecture, which supports increased scale and performance.
- Optimized dataset memory management.
- Scale limits per data model instead of per server.
- CPU smoothing for refresh resiliency.
- Autoscaling, which automatically adds compute capacity to avoid slowdowns under heavy use.
- Business continuity and disaster recovery (BCDR) with Azure regions and availability zones
Power BI outperforms AAS in Autoscaling by offering automatic management without the need for complex setup or additional costs.
Scheduling refresh in AAS has been a challenge as it can only be triggered with a runbook job whereas scheduling refresh in Power BI is very simplified, and it keeps cached dataset data current.
To proceed with the Migration of AAS model to Power BI, the first step is to get all the following prerequisites in place.
Ensure each environment meets the following prerequisites:
In Azure Analysis Services
- The Azure Analysis Services server that you’re migrating from and the Power BI workspace that you’re migrating to must be in the same tenant.
- You must have Server administrator permissions and be assigned to the Owner and/or Contributor roles for the subscription.
To add server administrators by using Azure portal
- In the portal, for your server, click Analysis Services Admins.
- In <servername> – Analysis Services Admins, click Add.
- In Add Server Administrators, select user accounts from your Azure AD or invite external users by email address.
- Azure Analysis Services must have an Azure Storage account with a container configured and backup enabled for the server as described in Azure Analysis Services database backup and restore.
Before backing up, you need to configure storage settings for your server.
To configure storage settings
- In Azure portal > Settings, click Backup.
- Click Enabled, then click Storage Settings.
- Select your storage account or create a new one.
- Select a container or create a new one.
- Save your backup settings.
- If Firewall is enabled for your server, ensure Allow access from the Power BI Service is set to On, or disable Firewall during migration.
- Your server must be started during migration. You can pause your server after migration is complete.
Note : You must have Owner permission at the storage account layer. Permissions at the resource group or subscription level won’t work.
In Power BI
- To migrate to Power BI, you must have a Power BI Premium per Capacity, Power BI Premium per User, or Power BI Embedded license.
- You must have Workspace administrator permission. Power BI admins can view migrations for their tenant; however, they can’t perform migrations unless they also have Workspace administrator permission.
- You must have an Azure Data Lake Storage Gen 2 (ADLS Gen 2) storage account in the same tenant and the workspace you’re migrating to must be connected to that storage account.
To connect to an Azure Data Lake Gen 2 at a workspace level:
- Navigate to a workspace that has no dataflows. Select Workspace settings. Choose the Azure Connections tab and then select the Storage section.
- Select Connect to Azure to connect to the storage account.
- Fill in the dropdowns to choose a valid Azure subscription, resource group and storage account that has hierarchical namespace option enabled, which is the ADLS Gen2 flag.
- Select save.
Note: By default, workspace admin can’t connect their own storage account. The global admin or Power BI admin must turn on a setting that allows workspace admins to connect their own storage account. To enable this, go to Admin portal > Azure connections > Connect to Azure resources > Workspace-level storage permissions, and check the Allow workspace admins to connect their own storage account checkbox.
- For the best performance, your ADLS Gen 2 storage should be in the same region as the workspace capacity.
- Large dataset storage format must be enabled for the workspace.
To set this up:
- Goto the workspace, select Settings > Premium.
- In Default storage format, select Large dataset storage format, and then select Save.
- The XMLA endpoint must be Enabled for read-write for the capacity.
To enable read-write for Premium Per User
- Select Settings > Admin portal.
- In the Admin portal, select Premium Per User.
- Expand Dataset workload settings. In the XMLA Endpoint setting, select Read Write.
- If a Microsoft on-premises data gateway is configured for the Azure Analysis Services server to connect to on-premises data sources, you must also install and configure a gateway in Power BI.
Once the prerequisites are in place, the next step towards migration is to Pair both environments.
- Go to Power BI service, click on Settings > Azure Analysis Services migrations.
- Open Azure Analysis Services to Power BI Premium page, click on + New Migration.
- Open the Create Azure Analysis Services migration flyout to select Connect to Azure.
- Now select the Azure Subscription, Resource group, and Server name with one or more model databases you want to migrate to.
- Create a new Premium workspace and give it a workspace name and select license mode. You can also select an existing workspace to migrate to from the list of workspaces.
- Once you have confirmed the settings click on create at the bottom.
- On the Azure Analysis Services to Power BI Premium page, select All migrations to refresh and show migration connection pairs created for your tenant.
- Under Azure Analysis Services Server, select the server containing one or more model databases you want to migrate to the paired Power BI workspace.
- In Migration Details, verify your Azure Analysis Services server and Power BI Premium workspace settings. If any of the prerequisites are not met are shown. Model databases on the server that can be migrated are shown in Datasets.
Once the migration is successful, you will be able to see the models in the workspace and be able to connect to them from Power BI Desktop and create measures and calculated columns.
In conclusion, this blog provides a comprehensive guide to successfully migrate from Azure Analysis Services (AAS) to Power BI, covering all the necessary steps and required permissions based on official Microsoft documentation and practical steps based on our own experience. By following this guide, you can seamlessly transition your analytics workflows to Power BI and unlock its powerful features and benefits for your organization’s data analysis needs.