cancel
Showing results for 
Search instead for 
Did you mean: 

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

 

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"

 

 

 

Status: New
Comments
Community Support

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

New Member

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

Community Support

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