DM_GWPipeline_Gateway_MashupDataAccessError On-Premises Data Gateway Error

[Anonymous]

Hello,

So I have a dataset published in the Service that has the following data sources:

• https://tenders.nsw.gov.au/
• https://www.tenders.act.gov.au/tender/search
• https://www.tenders.wa.gov.au/watenders/tender/search/tender-search.do
• a Microsoft Exchange inbox

In the desktop file, all privacy levels are set to Organizational. Everything refreshes fine.

But when published to the Service and connected to the On-Premises Gateway (Standard, March 2020 version), the refresh returns the following error:

Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"An unknown error occurred when navigating to the web page."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"Web"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"https://www.tenders.act.gov.au/tender/search"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","d...}}} Table: ACT_UNSPSCReference.
Cluster URI: WABI-AUSTRALIA-EAST-A-PRIMARY-redirect.analysis.windows.net
Activity ID: 2b36d3b7-0e3b-409b-99a8-e8608e63df72
Request ID: 0ef64159-25bc-b3d8-2f28-3d990db753f4
Time: 2020-03-31 06:24:02Z

The "Table" that shows as the culprit changes everytime, so I'm pretty sure it's not specific to one table only.

The data sources are added in the gateway with an 'Anonymous' authentication and 'Organizational' privacy setting (except for the Exchange mailbox which does not need to be added to the gateway).

Another weird thing that has been boggling me is that I have a gateway installed in one laptop, which refreshes the dataset with no problems, but when the same gateway version is installed in another machine, it returns the error stated above. Unfortunately I cannot use the laptop in the production environment as it is a personal laptop, so I need to install a gateway in the production environment machine.

I have been working on this for weeks and I could not seem to make anything work. Would appreciate any insight anyone might have!

[v-yiruan-msft]

Hi @Anonymous ,

Whether your problem has been resolved? Is there anything else that needs assistance regarding this thread?

Best Regards

Rena

[OB]

This is to let everyone know that our issue stopped being an issue on September 1, 2020 when refreshes started working again without errors (almost a month after they first failed).  However, if it helps anyone to troubleshoot in the future, we identified another workaround along the way. 

• Our sources were SharePoint on-prem lists that were appended as part of the M query in Power BI. 
• They were all in sub-sites of one parent site and there was one gateway connection to the parent site. 
• After the report failed to refresh with Append, we tested that lists could still be refreshed individually (without appending) using the above gateway connection. 
• The workaround, though, was to create a gateway connection per each sub-site/source (using the same account and password, so no change in SharePoint, only several new gateway connections created instead of one).
• Then each list was mapped to its own gateway connection and with that, the refresh of the report with appended lists worked - that was a more acceptable temp solution for us as it didn't require any changes inside the report.

[Anonymous]

How did you manage to create a a sub source for eacn connection without going through the root URL? Im trying to follow this temp solution, but can only create a SharePoint source using the root URL. Am I missinf something here?

[OB]

@Anonymous

To clarify, I am going through the root connection. What I meant by sub-sites is, for example

http://abc/sites/def/AAA

http://abc/sites/def/BBB

The Desktop file has a separate connection to each site, then the two sources are appended by applying steps in Transform Data and the refresh works, but it didn't work in PBI Service using one gateway connection to the http://abc/sites/def/

So we created two gateway connections in PBI Service, one for http://abc/sites/def/AAA and one for http://abc/sites/def/BBB

Then we mapped each source to its own connection and the refresh worked in PBI Service.

This is how gateway connections would look like for the above sources:

Data Source Name AAA Data Source Type SharePoint Data Source URL http://abc/sites/def/AAA Authentication Windows

Data Source Name BBB Data Source Type SharePoint Data Source URL http://abc/sites/def/BBB Authentication Windows

[ashc_cool10]

Hi @OB ,

Hope you are doing well.

I am facing the same issues while refereshing my data in PBI Servie . Below is the screenshot for the same.

If you have the solution Could you be please help what needs to be done?

Thanks,

Ashish

[OB]

Hi Ashish,

Your case is different from what I had.  You have two different sources, one SAP HANA and one SharePoint Online.  My issue was with two Shareoint on prem sources.  

I would recommend troubleshooting the following: connect from PBI Desktop to only one source at a time, then publish that report, and then see if the refresh in PBI Service works.  So check each source separately.  It could be that the password changed or that the firewall has been updated in your company and blocked the PBI Service connection.  Many options here, so further testing is required.

Sorry, I can't help more without additional info.

OB

[v-yiruan-msft]

Hi @OB ,

It's glad to hear that the refresh start working. Thanks for your reply and sharing your solution. There are some threads about this type of problem which schedule refresh can't work properly due to the append query. There is no good solution yet. Your solution may help people who have similar problem. Could you please just mark your last post as Answered? It will help the others find your solution easily and fast. Thank you.

Best Regards

Rena

[OB]

@v-yiruan-msft , please send me a snapshot how to mark the post as Answered 😂  The very first post in the thread wasn't mine, though.  In addition, there were two workarounds, one Union in DAX and one gateway connections, so both should be marked as solutions, even though I would still call them workarounds.

Thank you!

[v-yiruan-msft]

Hi @OB ,

Please check if there are two buttons ("Reply" and "Accept as Solution" button) which are same with the ones in below screenshot under each reply in your thread? You can click the "Accept as Solution" button to mark your two workarounds as solutions.

Best Regards

Rena

[v-yiruan-msft]

Hi @Anonymous ，

Could you please complete the following steps first and check if it can solve the problem?

1. Uninstall the gateway which installed in production environment machine

2. Reinstall the latest version of the gateway and update the setting of Mashup engine by this link

In addition, as checked the posts from users who have encountered similar problems before, if both personal gateway and enterprise gateway running on the same machine at the same time, there seems to be a conflict. It is recommended to uninstall the one which is not currently using.

Best Regards

Rena

[Anonymous]

Thanks for this! However I have already uninstalled/reinstalled the latest version of both the gateway and PBI desktop multiple times, to no avail. I do not have both personal and standard gateways in the same machine either 😢

[v-yiruan-msft]

Hi @Anonymous ,

Could you please check if the production environment machine which installed gateway hit any following consideration?

Best Regards

Rena

[Anonymous]

Hello! Thanks again for the response. However the machine does not fall under any of these conditions. I'm running out of options here ☹️

I have another info that might help - my client is in Australia and his PBI's default environment is in the East Australia region. I, on the other hand, am remotely working from the Philippines. I tried installing the gateway in another laptop here, using different internet connections, and it's working effortlessly. However, when I use the gateways installed in my client's environment and in an Azure virtual machine located in Australia, it does not work. Could it be a geographic issue? What issue could it be if that's the case?

I have a copy of the logs from the non-working gateway by the way, if that would help.

Thanks!!!

[GilbertQ]

Hi there

If you are getting an error from the clients VM it is very possible that it does not have access to the Power BI Service.

Ask them to open HTTPS from the VM to the internet?

[Anonymous]

Hello! The client's VM and production machine can access all the data source websites, as well as app.powerbi.com

[v-yiruan-msft]

Hi @Anonymous ,

Can I know whether the account for logging in to Power BI Service and the gateway of the production environment machine are same as those of the laptop? In addition, what are the relevant gateway and power bi service regions? You can refer the following documentations about how to get the region of gateway and Power BI Service.

Get gateway datacenter region

Get Power BI Service region

Best Regards

Rena

[OB]

Hi Is there a Permament solution to this issue?  It's been an issue for our team since last week - nothing has changed on our end, but reports stopped refreshing in PBI Service giving errors about credentials and mashup.  Someone else posted a separate issue today and I described our issues in detail there https://community.powerbi.com/t5/Service/Credential-Missing/m-p/1289813#M105497. 

As Elwiraa mentioned in her reply to this post , we also used DAX for Union and it worked, but there were other transformations applied to the Append table in Power Query, so it would be desirable to see this work again instead of recreating the whole report with DAX.

[Anonymous]

Hi I had the same problem.

I got message: UnderlyingErrorMessage","detail":{"type":1,"value":"Information is needed in order to combine data"}}
And it was connected to how I combine the data. In power query I couldnt merge data form table (data from computer disc C) and form different table (form jira or from sharepoint) even privacy level was the same.
I just didnt merge this data in power query, I did it in DAX by "related", and then refresh is working ok.

[GilbertQ]

Hi there

If it was working fine on the laptop that might indicate to me that it could be a connectivity issue from where the gateway is now installed?

If you could see if it can access the same website where the gateway is installed?

[Anonymous]

Hello thank you for the response! I'll double check but yes the websites are accessible from the other machines too.