A while ago, I wrote a blog about migrating Analysis Services dataset to Power BI Premium. Back then there were not many options to do so, today there are many more like ALM toolkit. Though, the read is still useful to get the concept behind it and understand the similarities between Azure Analysis Services meta data and Power BI meta data.

Having the context of an Azure Analysis Services dataset that is migrated to Power BI Premium, you might have to rebind many reports. Especially if this dataset is positioned as being a managed dataset that is also used for self-service purposes and has many related reports.

In this blog I will elaborate on how you can easily rebind all these reports to the new Power BI dataset, without downloading all reports and manual rebinding.

Directly go to the script on GitHub without reading the explanation in the blog.

Azure Analysis Services dataset in Power BI

First, let’s have a look at how an Azure Analysis Services dataset looks like in the Power BI service. Below I took the example of a composite model, where an Analysis Services dataset is used on Direct Query mode, but also combined with a dataflow that is imported in the dataset.

{
   "value":[
      {
         "datasourceType":"AnalysisServices",
         "datasourceId":"f8c56590-43cb-43bf-8daa-233ba2520f55",
         "gatewayId":"1f69e798-5852-4fdd-ab01-33bb14b6e934",
         "connectionDetails":{
            "server":"My-As-Server",
            "database":"My-As-Database"
         }
      }
   ]
}

Each dataset connection is specified in a JSON response from the API, which helps us to understand the next step to take.

Update data source connection

As Analysis Services and Power BI share (almost everything) the same meta data structure, it was already easy to migrate Analysis Services datasets to Power BI as explained in previous posts. Also check ALM Toolkit if you want to explore other options.

Now, let’s have a look at how we can update the data source connection of our Power BI dataset, to point to the new Power BI dataset instead of Analysis Services. An external tool in the Power BI desktop ribbon we could consider here, is Hot Swap Connections. This tool helps you to remove existing data source connections from live-connected reports, so the report can be bind to another dataset or source. A great option if you ask me but be aware that installing this external tool requires administrator rights. Also, you must download all reports to swap the connection to the new model. As you might know, there are limitations that might block you from downloading reports from the service.

Luckily there is another Power BI REST API that allows us to update data sources in a data model in the Power BI Service. With the API to Update data sources in group, we can specify the existing connection that we want to replace for the new connection. We have to pass this input in the request body of the API. An example of this request body below.

{
   "updateDetails":[
      {
         "datasourceSelector":{
            "datasourceType":"AnalysisServices",
            "connectionDetails":{
               "server":"Previous-Analysis-Services-Server",
               "database":"Previous-Analysis-Services-Database"
            }
         },
         "connectionDetails":{
            "server":"New-Analysis-Services-Server",
            "database":"New-Analysis-Services-Database"
         }
      }
   ]
}

As you can see in the example request body, we must specify the existing connection that we want to replace, as there might be multiple connections in the same Power BI dataset. The information about the new connection only includes the variables server and database, as we cannot swap the data source type, but only the connection. This is a limitation that we have to keep in mind!

Also know that in general, this API is not supporting all types of data sources. The most common sources are supported, which are listed in the restrictions section of the API documentation.

Again, if you want to give it a try, take advantage of above example request body and click the Try-it button on the documentation page.

Script to swap the connection

Recently, at a project we had a situation where we had many self-service reports taking advantage of our centrally managed Analysis Services model. But as we migrated this Analysis Services model to a Power BI Premium dataset, all connections had to be swapped. This may have resulted in a lot of manual labor. Personally, I rather work smart, not hard. Therefore, I setup a script that helps you to swap the connection from your old Analysis Services connection to the newly published Power BI dataset.

You might think, but previously he mentioned that you can only swap the connection, not the type of data source. And that’s right! But luckily Analysis Services and Power BI share the same meta data. By leveraging the XMLA endpoint of the dataset that is backed by a Premium capacity, you will be able to easily swap this connection.

The script used to swap the connection, is shared in my GitHub repository for Power BI Automation.

Executing the script

First of all, make sure you download the script from GitHub using the link above. When you open the script for the first time, please specify the variables listed, being;

• Workspace ID
• Dataset ID
• SourceServer
• SourceDatabase
• TargetServer
• TargetDatabase

In this example I assume you will swap from Analysis Services to Power BI. Because of that, DataSourceType variable is not listed above as a variable that you must change.. If you use the script for other sources to swap, please change this variable as well.

After defining variables, you can start executing the script. This will first define the API request body and setup a few functions that list all actions required. You will be prompted to authenticate to the Power BI Service as a first step during the script execution. After authenticating, the connection will be swapped according to the defined variables.

When successfully executed, you will see something like below, informing you that the dataset with specified Dataset ID, is now connected to the specified data source.

After swapping the connection, you can already see that the connection is changed in the Lineage view in the Power BI Service. Please be aware that you must reauthenticate to the data source, as the script returns on the screen in yellow text. The changed data source has to be reauthenticated, like every other new data source added to a model.

Remarks

In order to successfully execute the script, you must be dataset owner in the Power BI Service. If you’re not, consider to take-over the dataset before executing the script. You can easily take-over the dataset in the dataset settings in the Power BI Service. But as I rather work smart, not hard, you can also script to take-over the dataset and leveraging another API to take over in Group.

I did not include take-over dataset in the shared script intentionally. I believe people should be warned by this limitation before they take-over all datasets without informing the original dataset owner. If you prefer to also automate dataset take-over, consider executing below script separately or including the bottom three rows to the existing script.

<#
Additional task that can be added to take-over dataset before swapping the connection.
Variables and connecting to the Power BI Service might be obsolete if the script is merged with another script where those are already defined. 
#>

# Specify variables 
$WorkspaceId = "{Your workspace ID}" 
$DatasetId = "{Your dataset ID}" 
$BasePowerBIRestApi = "https://api.powerbi.com/v1.0/myorg/"

# Connect with user account that has access to the dataset you want to bind to
Connect-PowerBIServiceAccount

# Take over dataset
$SpecifyTakeOverAction = $BasePowerBIRestApi + "groups/" + $WorkspaceId + "/datasets/" + $DatasetId + "/Default.TakeOver" 
Invoke-PowerBIRestMethod -Method POST -Url $SpecifyTakeOverAction -ErrorAction Stop

# API call used for this execution can be found here: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/take-over-in-group?WT.mc_id=DP-MVP-5003435&

I hope this script helps you to automate swapping the connection from Analysis Services to a Power BI dataset and save a lot of time downloading and manually updating the connections!