Using Azure SQL DB, cannot schedule refresh

[tobyb]

Hi,

I'm using an Azure SQL DB (import, not direct query) as the sole data source for a pbix. When it's uploaded to the power bi web app, I can refresh manually but I can't schedule refresh. "You can't schedule refresh for this dataset because one or more sources currently don't support refresh."

I can't find anything wrong with the queries that would stop this, e.g. all queries have "Include in report refresh" ticked. I only have one query that I've customised in the query editor:

let
    Source = Sql.Database("tcp:{Database Server}.database.windows.net,1433", "{Database Name}"),
    dbo_AssetAges = Source{[Schema="dbo",Item="Ages"]}[Data],
    #"Sorted Rows" = Table.Sort(dbo_AssetAges,{{"Age", Order.Ascending}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "Age", "Age - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Age - Copy", "AgeCat"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"AgeCat", type text}})
in
    #"Changed Type"

In fact. I've got another pbix, that is a direct copy (as in, I copied and pasted the first pbix) with slightly different tables/queries but same data source, and that schedules fine. 

Any help would be appreciated.

Thanks,

Toby.

[tobyb · Accepted solution]

Hi guys,

I was able to fix this problem! 

I simply deleted the dataset in the Power BI service by clicking on the workspace name, then selecting the context menu on the dataset and hitting delete. 

I then published the .pbix again from Power BI desktop, and the refresh schedule controls were available to me. I'm not sure what the underlying cause was, but I imagine this is essentially the "turning it off and on" solution in Power BI 🙂

[v-shex-msft]

Hi @tobyb,

>>In fact. I've got another pbix, that is a direct copy (as in, I copied and pasted the first pbix) with slightly different tables/queries but same data source, and that schedules fine. 

Based on error message, it seems like schedule refresh not support your query result.
Since I can't reproduce your issue, can you share some detail about these changes?

Regards,

Xiaoxin Sheng

[tobyb]

@bajee I can't configure gateway settings for this, like you would an excel file, as those settings don't show up:

 

@v-shex-msft The difference between the dataset that schedules and that one that doesn't is the one that doesn't has 3 extra tables from the database. Also some of the tables have columns removed in query editor. That's all I can see.

Cheers.

[v-shex-msft]

Hi @tobyb,

Are there any append/merge operations or custom functions used in your query?

BTW, you can refer to below link document to share some detailed information.

Troubleshooting the on-premises data gateway

Tools for troubleshooting

Collecting logs from the gateway configurator

There are several logs you can collect for the gateway, and you should always start with the logs. The simplest way to collect logs after installing the gateway is through the user interface. In the On-premises data gateway user interface, select Diagnostics and then select the Export logslink near the bottom of the page, as shown in the following image.

Installer logs

Copy

%localappdata%\Temp\On-premises_data_gateway_*.log

Configuration logs

Copy

%localappdata%\Microsoft\On-premises Data Gateway\GatewayConfigurator*.log

On-premises data gateway service logs

Copy

C:\Users\PBIEgwService\AppData\Local\Microsoft\On-premises Data Gateway\Gateway*.log

Event Logs

The On-premises data gateway service event logs are present under Application and Services Logs.

Fiddler Trace

Fiddler is a free tool from Telerik that monitors HTTP traffic. You can see the back and forth with the Power BI service from the client machine. This may show errors and other related information.

Refresh History

When using the gateway for scheduled refresh, Refresh History can help you see what errors have occurred, as well as provide useful data if you should need to create a support request. You can view both scheduled, as well as on demand, refreshes. Here is how you can get to the Refresh History.

1. In the Power BI navigation pane, in Datasets, select a dataset > Open Menu > Schedule Refresh.

   

2. In Settings for... > Schedule Refresh, select Refresh History.

   

   

For additional information about troubleshooting refresh scenarios, take a look at the Troubleshooting Refresh Scenarios article.

Regards,

Xiaoxin Sheng

[bajee]

Hi Toby,

You have to configure the datasource for refresh. If your using Azure sql no need to install and configure the date way.

Since your using Azure sql server then go settting option in power bi service>select data souce option>click on gateway connection there you have to select  connect directly so that your data source will configure with your Pro user id.

Then you can able to schedule the refresh.

Regards,

Bajee.