Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Power BI dataset refresh fails in Service when using merge between Azure SQL DB and On-prem SQL DB

Hi all,

When I am using merge query between Azure SQL DB and On-premises SQL DB then power bi dataset refresh fails in service for on-demand as well as scheduled. 
2 data sources are used - Azure SQL DB and On-premises SQL DB 

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":"Timeout expired. The timeout period elapsed prior to completion of the operation."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.HostingError.Reason","detail":{"type":1,"value":"Timeout"}}],"exceptionCulprit":1}}} Table: Case.


NOTE : When I remove merge on-prem DB from azure SQL DB then refresh wokrs fine including refrreshing on-prem tables .

I would like to highlight the below which might give some backgroud:

- Gateway has been setup already and works fine for other dataset (gateway server is up to date) except merge 

- Dataset refresh works fine in desktop and get refreshed in 2 mins 
- Tried changing the privacy settings and sometime it worked using Ignore privacy level but again failing (no changes in dataset)
- Both data source has privacy setting set as Organizational
- Tried increasing command timeout to 90 minutes in both data sources but no luck 
- Dataset size is very less of 2 MB . Usually, it get refreshed in 2-3 mins max
- Parameter is used for Azure SQL DB to ease deployment
- Also tried disconnecting the VPN while refeshing the dataset

I have opened MS ticket as well but didn't get solution yet and also shared the gateway logs while refrshing the dataset . The ticket is still open and its been more than 15 days now. Support team have engaged porduct team but they will not join call unless its prod issue and we need to just share logs and wait . 

I strongly feel that there is no issue with gateway as it works fine when merge is not being used.

Please let me know if anyone has come across such scenario and what was the fix applied to the problem

Thanks in advance!

1 REPLY 1
lbendlin
Super User
Super User

yeah, that error message is suspicious. I think it masks the actual issue.  Keep working with Microsoft's SI on the Pro ticket, they will resolve it eventually.  Please keep us in the loop on what you find.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.