Scheduled Refresh of Gateway's Denodo connection is failing, however, manual refresh works.

[dbullivant]

Hello has there been any progress on the issue with scheduling refreshes of Denodo-sourced data.  Similar to Tom, I am trying to refresh a dataset using our Gateway's Denodo connection.  It fails frequently.  The same dataset can be refreshed successfully in seconds.

Attached is the script from the advanced editor for 4 queries in the report:

1.

let
Source = Sql.Database("PLKFSQL10V\PROD2012", "EDM", [Query="SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;#(lf)SELECT GETDATE() AS Last_Refresh;", CommandTimeout=#duration(0, 0, 5, 0)])
in
Source

2.

let
Source = Odbc.DataSource("dsn=Denodo64AProd1", [HierarchicalNavigation=true]),
admin_Database = Source{[Name="admin",Kind="Database"]}[Data],
ana_workflow_Schema = admin_Database{[Name="ana_workflow",Kind="Schema"]}[Data],
open_case_timeliness_View = ana_workflow_Schema{[Name="open_case_timeliness",Kind="View"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(open_case_timeliness_View,{{"create_datetime", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"branch_level", "Branch Level"}, {"branch_name", "Branch Name"}, {"case_id", "Case ID"}, {"case_status", "Case Status"}, {"create_date", "Create Date"}, {"create_datetime", "Create Date/Time"}, {"current_stage", "Current Stage"}, {"general_manager", "GM"}, {"workbasket_id", "Workbasket ID"}, {"aged", "Aged"}, {"division", "Division"}, {"market", "Market"}})
in
#"Renamed Columns"

3.

let
Source = Odbc.DataSource("dsn=Denodo64AProd1", [HierarchicalNavigation=true]),
admin_Database = Source{[Name="admin",Kind="Database"]}[Data],
ana_workflow_Schema = admin_Database{[Name="ana_workflow",Kind="Schema"]}[Data],
open_case_timeliness_View = ana_workflow_Schema{[Name="open_case_timeliness",Kind="View"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(open_case_timeliness_View,{{"create_datetime", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"create_date", "market", "division", "aged", "case_id", "create_datetime", "current_stage", "case_status"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"workbasket_id", "BU"}, {"branch_level", "branch level"}, {"branch_name", "branch name"}, {"general_manager", "GM"}})
in
#"Renamed Columns"

4.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVXbbhMxEP2VUSQkkMql3OGtbaSCRAG1RTxUPLi7k6xVx17G3qzCU38DCX6uX8IZO9kmoAhaXrK7jufMOWfG47Oz0e5oZ7Q35RqPN6Gn4GpKDVNlIpONdI5nTcFT37C//seI2Dn+sJ4M9UEusO+CE33tuOMHRKcNi+5ihAgz1TZWwokptMkGH8nMjXXm3PFrOjEzprFZ7NBHsUHK64c5Cz3W9/hg9GXnbPQYBPfF+Kqhdzxnh08kUYpKKtpvwJ7k9/OyawKovrF408U1jggxzoXKJK4LVZob13EkBE6ZJhJm9I0lUAo0gcwNPcbFMKjIq5roIEgbBIh0zHPLPRlf00mDADxY5rYCvClc11UoGR8SmbZ1tlI/NOmGpakxadgWo516YOZNRWix58m1Pe/hZ3YHQRPLKGgVfDLWl/ST0Mn9CtRMleBx5y2KtjLN1uyTRZRAaPBT6m0qBlY2LbKqmFTmhtfZoCFbdmhIKTyBSV71g3QFeDEO1appKqFrsytZmnNrqmPxeqvcpxB4oI34doy3T0XDGnmtiVFtSZkqLhjNLARZ/5vBGe/ZCu8E6rqIrw/eLagBOZMVd1GBvFZWS6JtZ9G2d0/3xzlZ19awJd7LaM8VTVh9GuNn2aq6oVRTnVuypx4pqry3LtJebAY/PLWzDQStQc79d6iXCtUJ7E8qbKo4x9wKR6wsD86grS8tPqABDO61EqbYH3P5eiN1VCNbx3oASpZXQB3buY1Y0SkC/41UzaKcquxOKR0wtJ0wM/ZasaZE7z5CzOERfrRvV31lfQ3EujOOGowkLZwuH7IHuKMj46FGqA3RKhEtiqHWSLJV54xs9MqujrgjI1rrm9PTubM2jJRnNzvHN5gurbq6/I7eRwMP/aVcry5/rMVdXf4kHbOkY2caZLGEf7IJT3eWpW5Z9KxA5f8m0kJqyfIsWoakkOBiaDHSdZwvueihGkbwjYQOUdtkPluHvqXI7Un+XaKezNV9cyOFq6BtAl+sAd9S39YU/y5PD/zn68mGm73cyH8cbr0DV8NVT8CkS52UWxJQX34B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sort Order" = _t, Metric = _t, Definition = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sort Order", Int64.Type}, {"Metric", type text}, {"Definition", type text}})
in
#"Changed Type"

[v-qiuyu-msft]

Hi @dbullivant, 

Please go to Dataset Settings, check detail error in Refresh History. 

Please check if the Power BI is busy during the schedule refresh time, eg: refresh other datasets, view reports, etc which could consume capacity resource. If it is, please set a different refresh time to see if the same issue occurs. 

Best Regards,
Qiuyun Yu

[dbullivant]

Hi Qiuyun ,

I have scheduled the dataset refresh at many different intervals throughout the day and experience the same results each time.  The refresh will hang for 10-15 minutes.  Whenever I refresh manually, it takes about 5 seconds.  Also, when I refresh manually, I can see the query executing in the Denodo query monitor whereas with the scheduled refresh, nothing is getting through.  Both are using the Power BI Gateway.

David

[v-qiuyu-msft]

Hi @dbullivant, 

Please see below note in this document: 

"Note also that the configured refresh time might not be the exact time when Power BI starts the next scheduled process. Power BI starts scheduled refreshes on a best effort basis. The target is to initiate the refresh within 15 minutes of the scheduled time slot, but a delay of up to one hour can occur if the service can't allocate the required resources sooner."

For detail reason, you can go through this document: https://docs.microsoft.com/en-us/power-bi/service-premium-capacity-optimize#why-are-refreshes-not-st...

Best Regards,
Qiuyun Yu