cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevhav
Continued Contributor
Continued Contributor

Change dataset for "Power BI service" live connection?

I have a dataset, in "Import" mode, that I've published to two app workspaces. So: WorkspaceA and WorkspaceB, each having an identical copy of the dataset.

 

I had created a report in Power BI Desktop, with a "Power BI service" live connection to the dataset in WorkspaceA.

 

Now, I want to change the connection to point to the same dataset in WorkspaceB. I go to Edit Queries --> Data Source Settings; click the "Refresh" button; and select the same dataset in WorkspaceB.

 

I believe I had tested this in the last 1-2 weeks, and it worked fine. But now, it's not working. After making the change, all my visualizations say "Can't display this visual. See details." And in the details, it says a bunch of stuff, including:

Client Error Code:
ServiceErrorToClientError

Error Details:
An exception of type System.InvalidOperationException occured.

Stack Trace:
System.InvalidOperationException: No session exists for database with ID:7f...43

"Database ID" is the same as the "dataset ID" that I can see in the Power BI Service URLs, right? I think I confirmed this is true.

 

But...I don't seem to have a dataset with ID = 7f...43! So, I wondered, where did it get 7f...43? I tried repeating these exact steps again, and got the same error, except now it said: "No session exists for database with ID:6f...78"

 

I tried a third time, and it said: "No session exists for database with ID:89...54"

 

Huh?! Where is it getting these random database IDs?

 

I can look in the Power BI service, and see that the dataset ID of the dataset I want—the one in WorkspaceB—is: f7...ed

 

I should note that after creating this .pbix report file with the "Power BI service" live connection, I have since modified and re-published the dataset. I re-published it to both WorkspaceA and WorkspaceB, naturally. With the same dataset name. (But the modifications don't break the visualizations.) I wonder if this is causing the issue? (But it shouldn't be!)

 

It seems that re-publishing a new version of the dataset results in a new dataset ID. Right?

 

But like I said, in the "Data Source Settings" screen, I'm clicking the little refresh button. That should ensure that Power BI Desktop is getting the current dataset IDs from the Power BI Service, shouldn't it?!

 

But it's weird that every time I try it, I get an error message with a different dataset ID.

 

Is this a bug? Please help! Like I said, I had tested this and thought it would be easy to re-point a "Power BI service" live connection to a different copy of the dataset.

 

And/or, is there any other way for me to change the connection? I read other threads about changing the .pbix file extension to .zip, then modifying the "Connections" file. I tried this, but (a) it won't let me modify and save over the "Connections" file, when it is within the .zip file. It says "You can't save to...Please choose another location." And (b) when I try extracting all the files from the .zip file, then modifying the "Connections" file, then sending the files to a zipped folder again, then changing the .zip to .pbix...Power BI Desktop won't open the file. It complains about it being "corrupted," or something like that.

1 ACCEPTED SOLUTION
kevhav
Continued Contributor
Continued Contributor

I found a solution, of sorts!

 

After changing the data source and having all my visualizations say "Can't display this visual. See details"...then I tried clicking the "Refresh" button, to "Get the latest data." This did not change anything.

 

But then I tried saving the .pbix report file, despite all my visualizations saying "Can't display this visual. See details." Then I closed the .pbix file and re-opened it. This did the trick -- now everything works, and in the bottom-right corner I can see it says "Connected live to the Power BI dataset: ..." and it shows the correct name of the dataset -- the one in WorkspaceB.

 

(I gave the dataset different names, in WorkspaceA and WorkspaceB.)

 

Something still seems buggy, but at least I can work with this for now.

View solution in original post

12 REPLIES 12
kevhav
Continued Contributor
Continued Contributor

@Anonymous and @1tcook , I believe this was "resolved" some time ago. Power BI Desktop now has a feature to let you change the dataset to which your report is pointing...

 

If you have a .pbix file that is a report with a live connection to a dataset in the Power BI Service, then just click "Transform data" > "Data source settings" (using the new ribbon), and it will prompt you to select a new dataset to use.

 

Also note that with the new workspace experience and shared datasets, you now are able to share a dataset across workspaces. So in my original post, where I talked about having WorkspaceA and WorkspaceB, each with its own copy of a dataset...we no longer have that. We now only have one copy of the dataset in the Power BI Service, and it is shared, so that we can create reports that use a live connection to that dataset, and publish those reports to other datasets.

1tcook
Frequent Visitor

Well, not for me. I have a file with a direct connection to another Power BI file. When I download the file and try to open it I get the error "No session exists for database". There is no way to change the connection in the Service and since I cannot open the file in Desktop I cannot change it there either. I have spent several hours trying to find a solution to no avail. This includes trying to replace the connection file when changing the file format to a zip file. 

 

I am rebuilding the dependent report now.

kevhav
Continued Contributor
Continued Contributor

I think I understand. When you get that error, does it give you an "Edit" button? 

 

In my experience, if I have a .pbix with a live connection to a dataset in the Power BI Service ... and if I try to open that file, and it cannot establish a connection to that dataset in the cloud, then an error message pops up. But it includes an "Edit" button which, when clicked, opens the dialog to select a (different) published dataset to connect to.

 

If you don't have get an "Edit" button like this, then I would wonder (a) do you have the latest version of Power BI Desktop ... and (b) does the .pbix file truly have a live connection to a dataset in the Power BI Service?

1tcook
Frequent Visitor

@kevhav, first of all, I appreciate the help. 

 

I can edit the file in the service. In doing so I have be able to create another verison of the file but it includes the entire data model...

 

When I download it and try to open it I get the dialog box below and I cannot do anythign else.

2020-03-16 14_58_18-Something went wrong.png

 
 

I found a solution. (Link below.). The solution is to open Power Query, remove all tables and folders. Then connect to a PBI Data Source.

https://community.powerbi.com/t5/Issues/No-session-exists-for-database-with-ID/idc-p/978283#M54879

Anonymous
Not applicable

Hi,

 

I know a solution I marked, but let me comment how I fixed it for other people that are looking for this in the future.

 

In your original post you've mentioned a .pbix to .zip approach (I also got the 'corrupted' message). Then I got this working for me by:

- Create a .pbix file with a connection the dataset that I want to use

- Save the .pbix file and rename it to .zip

- Open de .zip and extract the file 'Connection'

 

- Then I created a .zip file from the report were I want to change the dataset.

- Open the .zip file 

- Drag the 'Connection' file into the .zip explorer  (it had overwritten the original 'Connection' file in my .zip)

- Close .zip explorer and rename the file to .pbix

 

*I used the tool 7Zip to update the 'connection' file, I have not tested it with other .zip extraction tools

 

Still it would be nice if Microsoft comes with a OOTB solution, so we don't have to trick PowerBI whitch I would imagine is not supported ofcourse.

kevhav
Continued Contributor
Continued Contributor

Done. But @v-huizhn-msft, would it be possible for you note this as an issue/bug, to be addressed by Microsoft?

kevhav
Continued Contributor
Continued Contributor

Also, after getting it to "work" in this way, I changed the file extensions to .zip, and looked at the contents of the "Connections" files.

 

The original version, before changing the data source, was:

 

{
  "Version": 1,
  "Connections": [
    {
      "Name": "EntityDataSource",
      "ConnectionString": "
         Data Source=pbiazure://api.powerbi.com;
         Initial Catalog=bd...c5;
         Identity Provider=\"https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, 7f...cf\"
      ",
      "ConnectionType": "pbiServiceLive",
      "PbiServiceModelId": 3...5,
      "PbiServiceGroupId": "5a...ed",
      "PbiModelVirtualServerName": "sobe_wowvirtualserver",
      "PbiModelDatabaseName": "bd...c5"
    }
  ]
}

After having changed the data source, it is now:

 

 

{
  "Version": 1,
  "Connections": [
    {
      "Name": "EntityDataSource",
      "ConnectionString": "
         Data Source=pbiazure://api.powerbi.com;
         Initial Catalog=f7...ed;
         Identity Provider=\"https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, 7f...cf\";
         Integrated Security=ClaimsToken
      ",
      "ConnectionType": "pbiServiceLive",
      "PbiServiceModelId": 3...9,
      "PbiServiceGroupId": "ab...8e",
      "PbiModelVirtualServerName": "sobe_wowvirtualserver",
      "PbiModelDatabaseName": "f7...ed"
    }
  ]
}

So I confirmed it switched to the f7...ed dataset ID. Good.

 

Curiously, it added a new element to the ConnectionString: the 'Integrated Security=ClaimsToken' part. I can't guess what that is. But I tested it, and that new element seems to appear in any new .pbix file I create with a "Power BI service" live connection. I guess maybe this new element is included in files that were created with the November release of Power BI, whereas my "original version" was created weeks ago, when I was using an earlier release.

 

Hi @kevhav,

Congratulations, you have resolved your issue by yourself. Please mark your solution as answer, so more members here will find workaround easily and clearly.

Best Regards,
Angelia 

kevhav
Continued Contributor
Continued Contributor

I found a solution, of sorts!

 

After changing the data source and having all my visualizations say "Can't display this visual. See details"...then I tried clicking the "Refresh" button, to "Get the latest data." This did not change anything.

 

But then I tried saving the .pbix report file, despite all my visualizations saying "Can't display this visual. See details." Then I closed the .pbix file and re-opened it. This did the trick -- now everything works, and in the bottom-right corner I can see it says "Connected live to the Power BI dataset: ..." and it shows the correct name of the dataset -- the one in WorkspaceB.

 

(I gave the dataset different names, in WorkspaceA and WorkspaceB.)

 

Something still seems buggy, but at least I can work with this for now.

1tcook
Frequent Visitor

Tried it using built in windows zip utility and 7zip. No luck. I was able to download a version from the service but the orginal was connected to another data model. I had to create a copy in the service but now I have the entire model.....

Anonymous
Not applicable

Thank you for that. We had the similar issue

kevhav
Continued Contributor
Continued Contributor

Oh, I just tested it, and re-publishing a new version of the dataset, by overwriting the existing one on the Power BI Services, does not actually change the dataset ID.

 

So, I don't get it.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors