Unable to open or download Power BI report, because link to Azure Analysis Services is gone. What now?

Recently, I encountered a situation at a client where the Power BI reports where previously bind to Azure Analysis Services, but now the reports became inaccessible. This was because the Analysis Services model was migrated to Power BI Premium, but the reports where not yet connected to the new data model.

Unfortunately, since the Analysis Services model was not accessible anymore, the users were unable to open the report. As they did not have the original PBIX files anymore, they wanted to open the report in order to rebind it to the Power BI dataset.

In this blog, I will further elaborate on the encountered situation and how we managed to fix this problem, and why decent monitoring on your Power BI ecosystem is the key to success here!

The encountered challenge

First, let’s elaborate on the situation a bit further to see if you recognize the situation. Recently, I encountered a typical situation at a client, with a central Business Intelligence team, which is responsible for building and maintaining multiple the data models used across multiple countries. A typical scenario of Enterprise grade business intelligence with managed content. All solutions build by the team exist of Azure Analysis Services and a Power BI report on top. The report was shared with the end users, but they also had the option to build their own reports on top, and so they did.

With increasing demands, the central BI team reached a point where Azure Analysis Services was not fulfilling their needs anymore and the way forward was Power BI Premium. They started this journey by migrating all models to Power BI one by one. Part of this migration was to inform all self-service users about this change, so they were aware of the upcoming changes.

Despite all communication from the central team, there were some users who missed the message and still had their reports in their own unmanaged environment connected to the old Analysis Services model. You might think, this could have been prevented by taking advantage of impact analysis and lineage capabilities in the Power BI Service, which is totally right! Though, we faced the unfortunate situation where we had to deal with reports that could not be opened anymore.

Reports showing warnings in the Power BI Service

Error message received

While opening the Power BI report (still connected to the old Analysis Services model), we received an error “There is no dataset associated with this report. Please open the settings pane of the report and pick a dataset to bind the report.” After this message, Power BI directly closes the report.

Error received in Power BI Service while opening the report

We got this message while the Azure Analysis Services was paused. Of course, we could have easily started the Analysis Services again, but imagine this happens when the model is already removed. As this might also happen in the future, I decided to explore a different approach for future use as well.

As the report did not open, we could not download the pbix file and rebind it. Also, the report settings in the Power BI Service weren’t helpful, as there is no option for rebinding there. So, what is next? I considered a few different approaches in a few steps;

  • As first step, I tried to download the PBIX files by calling the Power BI REST API to export reports using PowerShell, or using the Try It button in the documentation. To use this approach, I need two input parameters, being Workspace Id and Report Id. Getting the Report Id was the challenge here, as I could not get that from the URL, because the report did not open.
  • As I had to deal with the missing parameter, I changed my focus to see if I could get the Report Ids somewhere. Another API to the rescue? The Get Reports in Group API might return me all information about the reports in this workspace. Unfortunately, this was not the case, and only the working and valid datasets were returned. Still no Report Ids.

So, what now…?

Coming to the solution with the help of platform monitoring

Luckily, I am one of the Power BI Service Administrators admin on the client tenant. With these permissions, we once setup a mechanism that helps us to monitor the Power BI ecosystem. We call it the Power BI Monitor. This monitor collects information about all artifacts in the ecosystem every day, and saves this information in Azure Log Analytics. We do this by calling the Power BI REST API from an Azure Logic App, which then sends the results to Azure Log Analytics. The Power BI Admin APIs for groups are used in this solution to gather information about all workspaces and artifacts in it across the entire tenant.

Want to read more how to setup a solution like this? Read my blog about gateway monitoring which describes a similar approach.

In Azure Log Analytics, you can write Kusto Queries to get the data out and directly analyze it in the tool itself, and so I did. My goal was to see if the information about the broken reports was stored in Log Analytics and if I could get the Report Ids there. I used the following Kusto query to get the information out.

// Kusto query to get all information from 

PBI_Groups_CL // Name of the Custom Log in Log Analytics
| extend id_g = todynamic(id_g)
| extend report = todynamic(reports_s) // Unpacks the nested JSON including all Report information
| mvexpand id_g
| mvexpand report
| project
    workspaceId = id_g,
    reportId = report.id
    , reportType= report.reportType
    , reportName=report.name
    , datasetId=report.datasetId
| where workspaceId == "{Your Workspace Id here}" 
// Filter on Workspace Id, known from the PBI Service

This query returned a list of all reports in the filtered workspace, which also returned me the Ids of the broken reports, which brought me one step closer to a solution! Also, as I included the Dataset Id, it was directly visible which reports did not have a dataset connected.

Response from Log Analytics including all information about the reports

Once again, I tried to download the reports using the Power BI REST API, but that returned a error “401, Unauthorized“. So, I had to find another solution before I could download the reports.

The final part of the solution was rebinding the report to another dataset. Although, this was the initial goal of the entire approach, I decided to first rebind to a random dataset and download the reports. Basically, to directly put the pbix files in a versioning system to prevent for similar situations in the future.

To rebind the reports to another dataset, I did not spend much time on the dataset I wanted to use. I picked the very first dataset in the same workspace, as with that I was sure that I did not hit any authorization issues if the dataset resides in a different workspace. If you do not have a dataset in the same workspace, you could just publish a dummy dataset to the workspace, so you have something available. There is no need for actual data in the dataset. By rebinding the reports to another dataset using a PowerShell script, I managed to make the reports accessible again.

All reports could be opened again, but now all visual in the report returned the error “Something is wrong with one or more fields”. This is because the visual could not be rendered as the fields used in the visual did not exist in the dataset. However, this was not a blocker for me, as I could now easily download the pbix files directly from the service and rebind to the correct dataset that the central BI team put together in Power BI Premium.

In my GitHub repository for Power BI Automation you can find the PowerShell script I used to rebind all reports to the other dataset.

Wrap up

At first, I thought that I might had to raise a support ticket to hopefully get the reports back from the Power BI Service. But knowing from previous experience, support cannot always help you with getting your pbix files back. Maintaining and keeping track of your pbix files is up to yourself, which has gone wrong in the above-described situation. Personally, I was happy that I managed to fix this issue within my own knowledge.

The initial error returned by Power BI, stating that I should open the report settings to rebind, is still confusing to me. I still did not find any place in the Power BI Service to rebind the dataset. Maybe there is an option to do this, but I simply could not find it. That is because I ended up with this solution instead.

Also, the collection of data collected in the past, helped in this case to discover the Report Ids needed to rebind. Again, a case which proves that keeping track of your Power BI ecosystem useful and adds to the health of your environment.

In my solution, I used PowerShell in combination with the Power BI REST API, which is something I often do. I already wrote multiple blogs for different challenges and how you can solve them by taking advantage of the APIs and/or PowerShell. If you are not familiar with this, I encourage you to watch a video that was recently published by Adam Saxton, Guy in a Cube to introduce you to this topic.

Also, a few years ago Adam did a specific video on PowerShell and Power BI Admin REST APIs.

5 thoughts on “Unable to open or download Power BI report, because link to Azure Analysis Services is gone. What now?

  1. To get the report id, you could have right clicked the report link from the workspace and copied the link address. Also, you could have gone to the “Manage permissions” page for the report, and copy the report id from the URL.

    Like

  2. Mario Valdeavellano

    Sorry, I just realized that my last comment is wrong, because those options are not available. But I can’t see my comment to delete/correct it (pending moderation maybe?), so I’m posting a new one.

    Like

    1. Thanks for your comment Mario. Right-click the link might be an option though. However you still need to rebind the report to another dataset before you can access the report again šŸ™‚

      –Marc

      Like

  3. Pingback: Tech Lunch News – SQLServerCentral

  4. Pingback: Tech Lunch News – prep.joyfulcraftsmen.com

Leave a Reply to Mario Valdeavellano Cancel 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 )

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