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!
