Migrate Analysis Services models to Power BI using Tabular Editor

Over the last two weeks, I had the pleasure to work on an innovative case where we wanted to migrate on premises Analysis Services models to a Power BI Premium data model. Definitely an interesting case! Because of this project I finally found some decent time to investigate XMLA endpoints for Power BI Premium!

In this blog I will describe the journey we took to migrate one of the existing Analysis Services models to Power BI Premium and provide a step-by-step guide how to do this yourself.

Spoiler! You do not need a Power BI Premium P-SKU to get this working!

Considerations, Power BI Premium or Azure Analysis Services?

Before we jump into all the details, you might have a few questions at this moment. Below I describe a few of them.

  • There is an Analysis Service model already, why migrate it?
    The current model is an on-premises analysis services tabular model. Together with the client, we took this approach to explore their way to the cloud in a proof-of-concept.

  • Why do you want to move to Power BI Premium and not simply “lift & shift” to Azure Analysis Services?
    In the announcements done by Microsoft over the last year, they state the Power BI Premium will offer a superset of what Azure Analysis Services has to offer today. With all the recent improvements in Power BI Premium and the main focus of the Microsoft team to have new developments in Power BI Premium before they are released in Azure Analysis Services, we decided to first look at Power BI Premium. Recent improvements such as XMLA endpoints are making a huge difference in this case.

    If you want to learn more about the way forward according to Microsoft, I advise you to check the links to external content at the end of this blogpost.

  • Why using Tabular Editor and not deploy the model from Visual Studio to the Power BI XMLA endpoint?
    We have tried that and bumped into a few challenges. It seems to be that Visual Studio and I are not friends and we have never been. At the same time, why use a heavy tool with expensive licensing if a lightweight, community driven free tool as Tabular Editor can do the job as well.

  • Why using Tabular Editor if there are tools like BISM Normalizer or ALM Toolkit that can do the job as well?
    This is a legit one! During the proof-of-concept we have used both approaches. In our case we got everything working with Tabular Editor, but I cannot say that you have to use one or the other. I believe both approaches are possible.

  • Why should I migrate to the Cloud?
    Scalable, innovation, future-proof and many more reasons. Still not convinced? I think we should have a different talk… 😉

Probably you can come up with many more considerations but let us move on and just give it a try!

The case

Since I do not want to use the customer case to show you the steps to take, I will use my own Azure Analysis Services model to migrate to a Power BI Premium data model. The approach for an on-premises model is identical to the approach with an Azure Analysis Services model.

My Azure Analysis Services Model connects to an Azure SQL Database that is hosted in Azure as well. Our goal is to migrate the model to Power BI Premium, including measures and all other model objects. In order to do this, we use Tabular Editor. If you are not familiar with Tabular Editor yet, please check the below webinar as an intro to the tool.

Webinar recording: Introduction to Tabular Editor

As a starting point we took the following assumptions:

  • You have Tabular Editor installed on your machine
  • You have a running model in (Azure) Analysis Services
  • You have availability of a Power BI Workspace hosted on Power BI Premium Capacity.
  • Your Power BI Premium Capacity has XMLA Read/Write enabled.

If one of the above assumptions is not applicable, please make sure you match these criteria before you move on. Enough side talk, let’s get things done! As said before, you might not have Power BI Premium. Please check the link at the bottom of this blogpost how you can use XMLA endpoints without Power BI Premium.

Let’s migrate!

Below I describe the process of migrating the model from Analysis Services to Power BI step by step. Please know that some of the below steps are unsupported! Use it at your own risk. I advise to try it with a copy of your model first.

1. Open Tabular Editor
As soon as we have opened the application, we click File > Open > From DB (CTRL + SHIFT + O)

Open a model in Tabular Editor

2. Connect to the Tabular Server
In the pop-up window, we connect to our existing (Azure) Analysis Services tabular model and click OK. In my case I use OAuth authentication to read my Analysis Services meta data. I am also listed as Analysis Services Administrator in the Azure Portal.

Connect to you Analysis Services Instance in Tabular Editor

3. Choose database
After authenticating to the Analysis Services, we must select the Database we want to migrate. In my case I only have one. Please also notice the compatibility level here!

Connect to the Database in your Analysis Service

4. Model properties
After we successfully connected, we see all our metadata in tabular editor. We do not really have to look at this right now. But notice that the compatibility level is also listed in the database properties.

Notice the compatibility level in the database settings in Tabular Editor

5. Save Model.bim file
Please click File > Save As and save the Model.bim file on an easy to find location. You do not have to rename the file or anything like that.

6. Update compatibility level
Now, we must do one (unsupported) manual change to our Model.bim file. Please go to the file location and open the Model.bim file with a text editor. I chose to use Notepad++ for this.

In the text editor, we will see all the meta data of our Tabular model nicely formatted in JSON. Please change the compatibility level to 1500 or higher. In my case I use 1520. This is the compatibility level that is used by Power BI Premium. This change is required to move our model to Power BI Premium in a bit. More about compatibility levels in the links at the bottom of this blog.

Manually update the compatibility level in a text editor

7. Re-open the Model.bim
After saving the manual changes to the Model.bim file, please go back to Tabular Editor. In the top bar, select File > Open > From File and select the modified Model.bim file.

Connect to Model.bim file

8. Ready to deploy to Power BI?
Check if your model is ready to be deployed to Power BI. Make sure you are not using unsupported features in your model that do not work (yet?) in Power BI Premium. Also confirm that the compatibility level is updated to 1500 or higher in the database settings (see step 4). Notice that the Compatibility Mode is now also changed to PowerBI.

Compatibility level is set to 1520 and mode to PowerBI

9. Deploy to Power BI
After checking all the prerequisites, click Model > Deploy (F6) in the top header of Tabular Editor.

Ready to deploy model to Power BI Premium

10. Tabular Editor Deployment Wizard
Please enter the XMLA endpoint for the Power BI Premium workspace in the Tabular Editor Deployment Wizard. The XMLA endpoint exists of a general part and the name of your workspace: powerbi://api.powerbi.com/v1.0/myorg/[workspacename]
After you have entered the endpoint, click next and authenticate with your Power BI username and password.

It might happen that you run in to an error because of duplicate workspace names (v1 and v2 workspaces). Please check the links at the bottom of this blog to see how you can get an appropriate XMLA connection string.

Tabular Editor Deployment Wizard with Power BI XMLA Endpoint

11. Choose destination
Now, we have authenticated to Power BI, we have to select the destination where we will deploy our model. You can either select an existing model that are in the workspace or create a new one. In my case I decide to create a new one and enter the Database Name at the bottom of the screen and click Next.

Choose destination to deploy model in Power BI

12. Decide what to deploy
Almost done, we only must decide what exactly we want to deploy. In my case I decide to deploy everything except Role Members (currently not supported) and again click Next.

Select objects to deploy to Power BI

13. Time to deploy!
Everything is configured and ready, time to deploy! In the final screen we get an overview of our configuration before we hit the Deploy button.

Tabular Editor Deployment Summary

14. Deployment progress
After clicking the deployment button, we see a wheel spinning for a second. As soon as it disappears, the deployment is finished.

Deployment Progress

15. The final step!
There is one thing left we have to do after the deployment. We have only deployed the metadata but no data or connection credentials. We have to configure the credentials in the Power BI Service and hit the refresh button, similar to what we do with any other Power BI dataset.

Configure the connection in the Power BI Service

That is all! Our dataset is now deployed to the Power BI Service and good to go! Now, we can start leveraging the full capability of Power BI Premium!

Wrap up and remarks

Well, in only 15 (baby) steps we were able to deploy our existing (Azure) Analysis Services model to Power BI Premium. As noticed before, Tabular Editor is only one of many tools that can do the job. Personally, I prefer Tabular Editor for many other reasons as well, like the best practices analyzer and because it is all community based!

Though, there are some things we have to take into account. XMLA Read/Write on Power BI Premium is currently (at the moment of writing in June 2020) in public preview. So, there might occur some issues. We are also doing some unsupported steps, especially the manual edit in the Model.bim file is not how you usually should do these kind of edits, it is a tricky step and mistakes are made easy. So please only do this in a copy of your model!

Another thing we should be aware of, is that our result will always be a split between data model and Power BI report, where this is normally packaged together in one single PBIX file. I think this is not a big deal at all and has a lot of advantages. More about that in an earlier blog about a multi-file strategy.

You might think, there is one step missing! What about my Power BI report? The report that previously connected to my (Azure) Analysis Services model, now needs to connect to my Power BI dataset. Since Power BI Desktop does not let you change this connection, how can I get this done? I can totally relate that you want to avoid rebuilding your entire Power BI report or even multiple reports. Luckily, the guys from PowerBI.tips came up with the solution for you. Read their blogpost about how to hot swap Power BI report connections.

Last but not least, you might think this only applies to Power Premium, so this is not for me? You are wrong! Please check out Just Blindbæk his blogpost about using XMLA endpoints without the need for a P-SKU!

Links

One thought on “Migrate Analysis Services models to Power BI using Tabular Editor

  1. Pingback: Bring existing tables to Power BI dataflows with Tabular Editor – Data – Marc

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s