Gateway data refresh fails when data is merged from web and on-prem sources

[Sokon]

Datasets that include a query that merges data coming from a web data source on one side and another set of data coming from an on-Premises data source can't be refreshed using the On-Premises data gateway.

Error messages depend on the kind of the web data source. For example:

• Sharepoint Online List: "The credentials provided for the SharePoint source are invalid"
• Excel in Sharepoint Online (Web Connector): "SharePoint: Request failed: https://[...]"

I tried to merge data

• from On-Prem Oracle with SPO List
• from On-Prem Excel with SPO List
• from On-Prem Excel with Excel in Sharepoint Online (Web Connector)

Every attempt failed. Tested with On-Prem Data Gateway 12-2018. I followed this documentation. According to this announcement this should work.

However, refreshes succeed when I access the identical data sources with reports that do NOT merge the data in a query:

[Anonymous]

Hey @Anonymous,

I suggest creating a support ticket of your own, to avoid confusion for the support team, as each of our scenarios are probably slightly different. But feel free to reference my suppor ticket no. so that support can see that several customers are having similar issues.

Cheers,

Carlton.

[Sokon]

@Anonymous, @Anonymous, @v-qiuyu-msft: I got the following response to my support ticket:

"After looking in to the verbatim we found that you are trying to combine an On premise datsource with cloud datsource (WEB). This is not possible by using the On premise data gateway in enterprise mode.

In order to combine the data you should be using the On premise data gateway (personal Mode)."

Using the gateway in personal mode ist no option for us. We need this feature in enterprise mode! Not quite sure how to proceed...

[Anonymous]

Hi @Sokon,

That was their initial response to my support ticket as well. But after I showed them the ideas page (which is incorrectly marked as completed), and the official Microsoft help documentation page about this feature working on Enterprise Gateway, they are now doing further investigations. Hopefully they'll either recognise it as a bug, or acknowledge that the idea/enhancement hasn't actually been completed properly, and re-open it.

Regards,

Carlton.

[Anonymous]

I had the same issue. Once i figured out privacy levels and got the two environments squared away with each other, the problem disappeared.

[Anonymous]

Hi @Anonymous,

We have both data sources set to privacy levels set to "Organisational", and it didn't work.

Just wondering what Privacy Levels settings you used?

Cheers,

Carl

[Sokon]

@Anonymous, @Anonymous: I might be able to clear that up. They sent me additional information on the issue: 

"The Combining of on premise with cloud is available for only personal Mode gateway for now . by design Personal Mode is only support for now to these kind of scenario.

But the availability of this feature for enterprise Mode is on baseline…. But we don’t have any ETA for this. This month we tried from our end with an Proto type for this enterprise mode which was provided by Product group for testing purpose , We found that the Privacy levels of gateway are getting turned in to Private in the service when the data is combined and that is not supporting the refresh.

Whereas Personal gateway overrides the Privacy level and the refresh will be successful.. Soon We can expect this feature in to the production for Enterprise mode, But depending up on the possibilities and other factors we don’t have any ETA. I will be posting your case as an reference to the product team, If we get some update on the progress, you will receive an email from our side.

Regarding the Documentation, We already Submitted the test cases and issues submitted by the users to the respective team they are working on the scenarios and possible workarounds to update the document soon that will be updated."

So, the privacy levels are the reason and the issue appears only in Enterprise mode.

I then argued that because it is nowhere mentioned otherwise users could rightly expect it to work in both enterprise and personal mode so i consider that behaviour being a bug. I then got a call from their manager who promised to take this into their regular meeting with Microsoft. Let's hope that helps...

[Anonymous]

@Sokon

Thanks a lot for this information.

[Anonymous]

@Sokon have you received any further update on this issue?

I am also facing the similar issue and unable to use On-premise gateway to refresh my report.

Currently, using personal gateway to refresh but want to switch to enterprise gateway.

[Sokon]

@Anonymous: Sadly, I haven't got any feedback after that mentioned phone call. 😞

[Anonymous]

Hi @Sokon , 

one year passed - were there any updates related to your ticket? Or maybe you came up with other solutions for this problem?

Thanks and best regards!