Azure Analysis Services Migration to Power BI Premium

Around November 2022, Microsoft announced a new feature within Power BI which allows you to migrate Tabular models from Azure Analysis Services into Power BI Premium. This interested me as more and more it seems Microsoft want people to migrate away from Analysis Services and into Power BI.

Migrate Azure Analysis Services to Power BI Premium

Blog Contents

Migration Overview

Here is the link to the official blog post announcement. Links to all the Microsoft documentation is available in the References section.

Accelerate your migration experience from Azure Analysis Services to Power BI Premium with the automated migration tool | Microsoft Power BI Blog | Microsoft Power BI

Technically, migrating is simple but understanding the size of the model at rest and also when being refreshed and when in use is the trickier part

So whilst the actual technical process of migrating is fairly straight-forward (which we’ll go through later in this blog), understanding the size of the model at rest and also when being refreshed and when in use is the trickier part. E.G. those pesky DAX calculations that explode the RAM usage…

Now my thoughts around migrating from Azure Analysis Services have tended to be “well, if I have a 100GB Azure Analysis Services server with several models on…am I expected to buy a P4 with 100GB RAM?” Well, no. And that’s because how Power BI Premium Gen2 allocates memory to Power BI Datasets.

Why Migrate?

Good question…and one that I keep asking myself. After all, Azure Analysis Services is a standalone product and can be scaled up and out. If we migrate to Power BI Premium are we not then just putting more pressure on the Power BI tenant we’re migrating to? And also depending on the size of the migration, it might require increasing the premium tier…more £££/$$$! But that’s probably going to be offset by the cost of the Azure Analysis Services server being migrated away from.

I’m coming around to the idea of migrating into Power BI Premium after learning more about the internal architecture of Premium Gen2 and how it handles dataset sizes in memory….more on that in the next part.

Looking at Memory Usage

Shout out to Johnny Winter who pointed out the Max memory per dataset resource limit concept. The official Microsoft documentation states:

“The Max memory per dataset (GB) column represents an upper bound for the dataset size. However, an amount of memory must be reserved for operations such as refreshes and queries on the dataset. The maximum dataset size permitted on a capacity may be smaller than the numbers in this column.”

So we don’t need to worry about the total amount of memory used on the AAS server but rather think of it as separate models? It appears so. Although we need to take care about vCore usage…it’s no good putting a lot of large models that fit within the Max Memory per dataset limit and then expecting high performance.

I like to visualise, so I created a simple image to show what it may look like to migrate several models from an Azure Analysis Services server to a Power BI Premium P1 tenant.

What’s also interesting is that as of January 2023, Microsoft has announced scale-out capability for Power BI Datasets in Premium capacities, a similar feature exists in Azure Analysis Services for scaling out read-replicas to improve concurrent performance. More details on that new feature here.

How to Migrate from Azure Analysis Services to Power BI Premium

So how does migrating via the Power BI service work? Let’s walkthrough an example below, which includes backing up the Azure Analysis Services server and configuring Azure Data Lake Gen2 connection in Power BI.

Pre-requisites

The Microsoft documentation has a list of pre-requisites here, in summary:

Azure Analysis Services server and the Power BI workspace must be in the same tenant.
You must have Server administrator permissions and belong to the Owner and/or Contributor roles for the Azure subscription.
Backup must be configured in AAS (we have a step for this in this blog)
If Firewall is enabled for your server, ensure Allow access from the Power BI Service is set to On, or disable Firewall during migration.
You need Power BI Premium per Capacity, Power BI Premium per User, or Power BI Embedded.
You must 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. This blog has a guide for that.
Large dataset storage format must be enabled for the workspace.
The XMLA endpoint must be Enabled for read-write for the capacity.

Start Migration Process

Login to the Power BI service and click the settings icon in the top-right
Click the Azure Analysis Services migrations option

Click New Migration in the top-right

Now you can enter the information required to connect to the Azure Analysis Services server
- Azure Subscription
- Resource Group
- Server name (AAS server)
- Workspace (to migrate datasets to)
Click Create

Once the migration has been created (no migration is actually taking place yet, this is just creating the definition) you should see it listed in the migration page.

You can now click on the server URL which displays the migration details, what’s important about this is that it will show any issues detected that need to be resolved. E.G. in the image below it states that we need to backup the AAS server and also configure Azure Data Lake Gen2 on the Power BI capacity.

As you can see, the migrate button is greyed out as pre-requisites have not been met. If you click on a model, this will retrieve its details including the size.

Configuring Azure Analysis Services Backups

To configure an Azure Analysis Services (AAS) backup, you can do the following:

Login to Azure and go to the relevant Analysis Services page
Click on Backups under the Settings area
Ensure backups are Enabled
Under Backup Storage Settings configure the Azure storage account
Save the changes

As soon as this is configured and you click Save, if you refresh the Power BI migration page then the message to enable AAS backups should disappear.

Configuring Azure Data Lake Gen2

We now need to assign an Azure Data Lake Gen2 storage account to the Power BI workspace we are migrating the AAS models into.

Note that for a storage account the following setting needs to be applied in the Azure storage account (please see following image).
The user that is configuring the Data Lake connection in Power BI needs to be added as Owner to the Azure storage account.

In the Power BI workspace, click Settings, click the Azure Connections tab then expand Storage. You can now enter the relevant Azure Data Lake Gen2 account details.

Refresh the migration page, click on the server URL and the backup and Azure Data Lake Gen2 messages should have disappeared. We are now ready to migrate.

Start the Migration

Now we can enable each model we would like to migrate in the migration screen and click Migrate in the top-right corner.

You should see the status Migration in progress.

You should then see the status is Successfully migrated. The message Model will be overwritten will now show in the Exists in Workspace column as the model has been migrated into the workspace.

We should then see the migrated model in the workspace.

We can perform the migration repeatedly so if any changes are made to the AAS model then it can be migrated again into Power BI.

Conclusion

After the migration has been performed, there is no ability to download the model to author in Power BI Desktop.

It’s common to use Visual Studio with Analysis Services extension to author models deployed to AAS. So what are the options to carry on developing the model? We could still use VS and deploy the model to the XMLA endpoint. We could also use Tabular Editor, E.G. to convert the model.bim file in the VS project and to JSON objects.

Also, we could just deploy the model definition to Power BI using the XMLA endpoints and refresh the data. The migration tool seems useful if you have models that contain data that you do not want to refresh, as the migration includes the model definition and the data itself.

What would be good is a migration assessment in terms of the dataset size and some recommendations. Perhaps using logs to see the usage patterns of the AAS model could help here.

References

Tags: Azure Analysis Services, Power BI

Azure Analysis Services Migration to Power BI Premium

Migration Overview

Why Migrate?

Looking at Memory Usage