Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have created a pbix in the desktop. It has data sources from 2 spreadsheets and an on-premises SQL database.
When I click publish I receive the following error message:
An error occurred while attempting to publish <pbix file name>. The underlying connection was closed. A connection that was expected to be kept alive was closed by the server.
As an alternative I know I can read the pbix from the PBI portal but I also have a problem with that. This is why I am trying to 'publish' from the desktop. I can import the pbix from the portal and it works ok. The problem is that the portal does not allow me to configure the SQL database to use the data gateway.
Does anyone have any suggestions?
Solved! Go to Solution.
Hi @bpsearle,
According to your description, when you publish the report from desktop to Power BI Service, the error throws out. While you are able to use Get Data-> Files to upload .PBIX file in service, right?
In your scenario, please try to update the Power BI Desktop to the latest one, then publish it again. Also enable the trace log to see detail error messages.
Besides, as you connect to Excel file and SQL Server database in the report, to set the dataset to use data gateway, you need to configure two data sources: File and SQL Server, which use the same connection information as in Power BI Desktop.
Best Regards,
Qiuyun Yu
Hi @bpsearle,
According to your description, when you publish the report from desktop to Power BI Service, the error throws out. While you are able to use Get Data-> Files to upload .PBIX file in service, right?
In your scenario, please try to update the Power BI Desktop to the latest one, then publish it again. Also enable the trace log to see detail error messages.
Besides, as you connect to Excel file and SQL Server database in the report, to set the dataset to use data gateway, you need to configure two data sources: File and SQL Server, which use the same connection information as in Power BI Desktop.
Best Regards,
Qiuyun Yu
Hi Qiuyun
Thank you very much for your reply.
Your first question is correct.
I'm on the latest version.
I haven't seen the trace option and will have a look at this.
In terms of setting up the data source on the portal it is more than likely the problem.
For the data source, I would like to use One Drive For Business for the location of the spreadsheets. How would you configure the data source on the portal, would that still be exactly the same path as on the desktop - just wondering how it resolves a local path to One Drive. For example in the desktop I select the the spreadsheet and the path is c:\users\<my user name>\SharePoint\<project name folder in One Drive for business>. I must admit I haven't tried it and will do so, just thought I'd ask to see what you think.
Thanks very much
Brian
Hi @bpsearle,
1. In terms of setting up the data source on the portal it is more than likely the problem.
Please follow the suggestion in my original post then test again to use SQL Server and local Excel data sources in one report.
2. For the data source, I would like to use One Drive For Business for the location of the spreadsheets. How would you configure the data source on the portal, would that still be exactly the same path as on the desktop - just wondering how it resolves a local path to One Drive. For example in the desktop I select the the spreadsheet and the path is c:\users\<my user name>\SharePoint\<project name folder in One Drive for business>. I must admit I haven't tried it and will do so, just thought I'd ask to see what you think.
In Power BI Desktop, to connect to the Excel file exists in OneDrive for Business, we can use web data source. Please refer to this article: Use OneDrive for Business links in Power BI Desktop.
By the way, if you connect to Excel use web data source and publish to service, there will have a refresh issue. And product team already realized this issue and are working on it to find the fix.
Best Regards,
Qiuyun Yu
Hi Qiuyun
Thank you again and for following up on my further questions.
The link to "Use OneDrive for Business links in Power BI Desktop" provides most of the answer. The bit I'm stuck with is probably the issue you refer to that MS are working on. The problem seems to be that with the "web" data source there is no option for "Organizational Account" and therefore there is no way to configure the data source.
Do you know if what I describe above is the issue MS are working on? Or if it's not how I configure the data source? The reason I ask is there seems to be very little information about it, or any that I can find.
On a separate and related point, have you done anything with AD data source? This seems to be similar to OneDrive. It works on the desktop but there is no matching data source on the PBI Service. I have previously assumed that the data sources are the same between the desktop and the service, but now I’m not so sure.
Many thanks
Brian
Hi @bpsearle,
The link to "Use OneDrive for Business links in Power BI Desktop" provides most of the answer. The bit I'm stuck with is probably the issue you refer to that MS are working on. The problem seems to be that with the "web" data source there is no option for "Organizational Account" and therefore there is no way to configure the data source.
Do you know if what I describe above is the issue MS are working on? Or if it's not how I configure the data source? The reason I ask is there seems to be very little information about it, or any that I can find.
Yes, it's the issue product team is working on. I test for web data source again today, it's able to check OAuth2 authentication to enter organization account and refresh the dataset successfully. Please try it again in your environment.
On a separate and related point, have you done anything with AD data source? This seems to be similar to OneDrive. It works on the desktop but there is no matching data source on the PBI Service. I have previously assumed that the data sources are the same between the desktop and the service, but now I’m not so sure.
As the on-premise data gateway doesn't support Active Directory data source, I use the personal gateway for the Active Directory dataset. But the error throws out like below whether I use Windows or Basic authentication. I will report this issue internally and update here once get any information.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |