Migrate SSAS to Power BI Premium

Migrate SSAS to Power BI Premium

In this post, we will cover how to migrate SSAS to Power BI Premium using Tabular Editor.
While there may be some more advanced techniques using scripting to Migrate SSAS to Power BI Premium this post focuses on a simple solution which is probably just fine for most small to medium-sized companies.

Why migrate SSAS to PowerBI Premium instead of Fabric?

Why Migrate SSAS to Power BI Premium instead of opting for Fabric?
Well, surely migrating an on-premise architecture to Fabric and fully leveraging the direct lake would likely be the most optimal solution. However, it involves moving all the data sources to Fabric first which would be a significantly larger project migration.
Also, since Fabric is still fairly new you may prefer waiting a bit before fully utilizing all the Fabric new features in Production.

Instead, starting with a more mature tool that has been around for a while and widely used in prod in many companies could be a good transition until Fabirc becomes more mature.
So moving all the SSAS models to PowerBI premium first could be a good transition and it is also much easier to move back to SSAS if for whatever reason the migration would not be successful in your scenario.
Choosing to first migrate SSAS to Power BI Premium as a transition is not only simpler but also provides the flexibility to revert back to SSAS at any time if the migration does not yield the desired results in your specific scenario.

Prerequisite

Power BI Premium offers numerous advantages over an on-premise architecture using SSAS and encompasses most of the features of SSAS if not all. And of course, it has tons of features that SSAS does not have. So unless you have very large models that cannot fit into the largest Premium capacity or that you are not comfortable using SasS solutions, I see very little reason not to make the move to Power BI Premium.

However, there are some prerequisites to consider before migrating from SSAS to PBI Premium. First, you need to be aware of the licensing cost and also identify which capacity is right for you.
Additionally, there are also some small technical issues that if you are not aware of are going to complicate the migration process but don’t worry we’re going to review them in this post.

Power BI Premium capacity or PPU or PBI as a SKU

Premium Per USER (PPU):
If you are a small company with a limited number of PBI developers and users, and your models are relatively small, PPU is likely sufficient.

Premium Capacity:
If your company has several hundred Power BI consumers and multiple models, you are most likely going to fall under this category. However, it’s important to determine which tier to buy.

PowerBI as SKU:
This option is generally the most expensive, especially if you keep PBI active at all times. However, I recommend starting with PBI as a SKU since it’s straightforward to set up in Azure, and you can scale up or down to find the capacity size that best suits your company. Once you’ve identified the right size, you can contact the Microsoft sales team to purchase a premium capacity that suits your needs. If PowerBI is only used during working hours, it may remain the most cost-effective solution.

Enable XMLA Read/Write

To migrate SSAS to Power BI Premium we first need to enable the XMLA Read Write to your Capacity as shown below. Once done we will be able to modify or deploy the model directly to Power BI using external tools such as SSMS, Visual Studio with SSDT, Powershell and of course Tabular Editor which we’re going to use in this post.

Gateway Configuration

Once the SSAS model is deployed to Power BI it also needs to be refreshed with the latest data, so unless it is already done you will also need to configure all the data sources used by your SSAS models.

Since you were using Power BI with SSAS you are likely to have a machine with a gateway installed on it so you can simply reuse this same gateway. Note that having different gateways for your different environments is a good practice.

To create a new data source you need to open the settings menu and select “Manage connections and gateways”

Then create a new data source, if you do not see any Gateway cluster name available you may need to install and configure a gateway or check with your PowerBI admin if you don’t have the necessary permission on the gateway.

This step is probably the most complicated since you need to ensure that all the following are properly configured:

  • Identify all the data sources used by your SSAS models
  • Recreate all the data sources in Power BI
  • Grant all the necessary permissions on the source to the account used to configure the Power BI connections

Note that for online or cloud-based data sources gateway are not necessarily required, I assume in this post that most of your source are also on premise or on a VM following IasS architecture.

Migrate SSAS to Power BI Premium

As mentioned earlier we will use Tabular Editor to migrate SSAS to Power BI Premium, there’s many other ways to do it such as using Powershell or even GitHub with a CI/CD pipeline but this would require a dedicated post.

If you are not already using Tabular Editor you can get it from here, there’s also a paid version of Tabular Editor which is definitely worth it but not needed for this scenario.

Open an existing SSAS model with Tabular Editor

Select the model that you want to migrate to Power BI.

And now we’re almost ready to deploy our model, we can click on the model and hit deploy.

Now we need to get the get destination server where to deploy our model.

Get the XMLA Endpoint

As we saw above to migrate SSAS to Power BI premium we had to enable the XMLA read/write, this is now the time to retrieve the XMLA endpoint of where we’re the SSAS model will be deployed.

Migrate SSAS to Power BI Premium

We can now pass the destination server to Tabular Editor parameter:

migrate to power bi premium

If you try immediately to deploy your model you will encounter the following error:

To fix it we need to make some changes in the SSAS model first.

Update your SSAS model

Change the default Power BI data source version

By clicking on the model we can change the default Power BI data source version:

There are still a few other things to consider before deploying our model if we don’t want to get another error message.

Other roles than reading

If you have any roles in your model such as Administrator or Read Refresh you won’t be able to deploy it as Power BI only accepts Reader roles when you deploy a model. The more permissive accesses are directly configured inside the Power BI workspace.

The error message mentioned RLS but even without any RLS defined in your model, you will get the same error message.

Local domain users not available in your Azure tenant

You may face another issue if you try to migrate SSAS to Power BI premium that has local members in their roles. The quickest to fix this issue is simply to not deploy the role members but only the roles and add the new members into their respective roles later.

There are some techniques to map local domain users to Azure Entra using some scripts or your company may already have something in place, you can also use GitHub CI/CD to replace local domain users with their UPNs. This part only would need a dedicated post so I cannot go deeper into it.

Data Source not compatible with Power BI Premium

Finally, another very common issue is when you have a Power Query data source defined in your SSAS model,

If you encounter this issue I see three options for you:

Recreate everything by hand:

This is an easy option only if you have a few models to edit with only a few tables to update.
It implies recreating a Legacy Data source such as the below image.

Then enter all information to connect to your server, once the connection is configured you need to delete the tables still referencing the Power Query Data Source and then reimport the tables from the Legacy Data Source, for larger models with many tables this solution will be too much time-consuming.

Modify the BIM file

Since Tabular Editor does not support editing the Partition type, you can alternatively modify the BIM file and replace the JSON part that references the M partition by the new Legacy Data Source connection + rewrite the M code and convert it to an SQL query.
This is an option that I wouldn’t use since you can easily break the JSON structure and your model so make sure to save your model and test it before running it if for whatever reason you need to use this method.

Use C# script

This is my favourite method and I used it for all my migration projects, this solution is by far the best especially if you have multiple partition tables which is a good practice for better performance. (You can read more about it in my post Performance benefits of partitioning in Analysis Services Tabular.
This solution is presented by Michael Kovalsky in his blog post Convert Data Sources for Premium Migration

So no need to repeat the explanation provided in his great post you can just follow the instructions step by step.

Deploy and Refresh your model

Once we have fixed all the issues that prevented us from deploying our model we can deploy the model.

Finally, we managed to migrate SSAS to Power BI Premium and refresh it the next step would be to schedule the next refresh which can be done directly from the Power BI portal or using other tools by leveraging the XMLA endpoint or the REST API which allows better control on when to trigger the refresh.

Conclusion

This post covered how to Migrate SSAS to Power BI Premium for most simple scenarios. Unfortunately as of today, there’s no automatic way to migrate SSAS to Power BI Premium supported by Microsoft such as for Migrating Azure AS to Power BI Premium.
Most tasks described in this post could be done programmatically using Powershell or C# script combined with Tabular Editor. However, if you have only a few, or perhaps up to a dozen models to migrate, this approach should suffice, as it’s only a one-time task.

I tend to think that a simple manual solution is preferable to an overly complex automated one, especially for one-time tasks.

2 thoughts on “Migrate SSAS to Power BI Premium

  1. So helpful! Thanks a lot!
    Did more limitations occur during the deployment after you postet this article? Like things that won’t be deployed and need to be reconfigured manually after the deployment, like connections to the old ssas model in excel that has now been shifted to PBI.. How can one deal with that?

    For AAS there is a automated redirection available, but what about SSAS?

    1. hi Toni,
      Glad you found this post helpful, as of now I’m not aware of any redirection from SSAS to PBI dataset and trust I need it too! I will definitely post about it if I find something

Leave a Reply

Your email address will not be published. Required fields are marked *