How to migrate Azure Analysis Services models to Power BI Premium

Do you want to migrate your existing models from Azure Analysis Services (AAS) to Power BI Premium? Migrating your AAS models to Power BI Premium can be done just in few clicks. The entire process is much easier than you think!

This article will provide you step by step instructions (just 5 steps) on the migration process. I will use the XMLA endpoint read/ write capability of Power BI Premium to migrate one of my AAS models to a Power BI workspace running on dedicated capacity.

Prerequisites:

• Advanced level of working knowledge in Power BI
• Access to Azure portal and the AAS model to be migrated
• Access to a Premium workspace in Power BI Service
• Login credentials for your backend data source
• Visual Studio (VS) 2019 or any other compatible platform

Make sure that your workspace is running on dedicated capacity and the XMLA Endpoint feature is turned on with Read Write option enabled.

If your workspace is running on Premium, you should see a Diamond symbol attached to the name of the workspace. For example, the name of the workspace which I am using is “AAS Deployment” and you can see that there’s a diamond symbol attached to it.

To check if your XMLA Endpoint is turned on, you will need to go to Admin Portal > Capacity settings > Power BI Premium. Click on the name of the capacity. Under Management tab > More Options, expand Workloads section. Find the XMLA Endpoint dropdown and select the Read Write option.

5 Steps to follow:

Step 1: Login to your Azure portal and go to your Azure Analysis Services resource where you have your existing models. In my case, I will be working with Adventure Works Internet Sales model with 1500 compatibility level. Click on “Open in Visual Studio” as shown in the image below.

Note: If you have a model with lower compatibility level, it’s recommended to change it to 1500 (or higher).

Once you click on the Open in Visual Studio option as shown above, it will download a zipped folder for you. Unzip it.

Step 2: Open your VS 2019 and click on “Open a project or solution”. Browse to your unzipped folder and select the References object (.smproj file).

It will open your Tabular model designer. Select “Integrated workspace” if not selected already and click on “OK”. Next, VS 2019 will redirect you to the Solution Explorer window (image attached below).

In the Solution Explorer, double click on the Model.bim file and it will open the Tabular Model Explorer. Right click on your model name in the Tabular Model Explorer and select Process > Process All as shown in the image below.

This step tries to validate your data source credentials. A dialog box will pop up asking for your data source credentials. Once you provide your database credentials and click on Connect, it will start processing your data.

Depending on your data volume, few seconds later you will see a Success message for each of your tables.

Close the Success message and go to your Solution Explorer. Right click on your model name and click on Rebuild. Ideally, you should get an output as “Rebuild All: 1 succeeded, 0 failed, 0 skipped”.

In the Solution Explorer, right click again on your model name and select Properties. This will open a dialog box. Keep that dialog box open.

Step 3: Login to your Power BI Service and go to the Premium workspace where you want to migrate your AAS model. Select workspace Settings and under the Premium tab, copy the Workspace Connection string.

Step 4: Go back to the Properties dialog box which you have kept open in VS 2019. Under the Deployment Server section, delete your AAS details and paste your Premium Workspace Connection string in the Server field. Click on OK.

Again in your Solution Explorer, right click on your model name and select Deploy.

A Deploy dialog box will open up and you will see the status of your deployment for all your tables.

Now when you deploy for the first time, you will see the metadata deployment is successful but you will see errors for the tables. If you click on one of these errors, it says that the data source information is not found in Power BI Service.

When you deploy your model for the first time, you will see a Power BI dataset is created by using metadata from the Model.bim in your workspace. However, processing to load data from the data source into the Power BI dataset fails.

This failure happens because unlike deploying in AAS — when you deploy your model to a Premium workspace, you can not specify the data source credentials as part of the deployment operation. Instead, you need to provide data source credentials in the Power BI Service in dataset settings.

Step 5: Now go to your Premium workspace and there you will be able to see that a Power BI dataset with the same name as your AAS model is created. This is just the metadata. You will need to provide the data source credentials for this dataset to bring the data in Power BI. To do that, go to your dataset settings.

After data source credentials are specified, you can then click on “Refresh now” option available for the dataset in your workspace. You can also configure scheduled refresh depending on your requirement.

Note: This is just a one-time task. Once you set up a data connection, you will not be prompted to enter credentials anymore for your subsequent deployments.

Your AAS model now has been migrated to Power BI Premium. You can now open your Power BI Desktop application and live connect to this newly created Power BI dataset to create your reports.

Explore and enjoy!