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
tobyb
Frequent Visitor

Using Azure SQL DB, cannot schedule refresh

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.

1 ACCEPTED SOLUTION
tobyb
Frequent Visitor

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. 

 

NuQ8APE

 

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 🙂

View solution in original post

5 REPLIES 5
tobyb
Frequent Visitor

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. 

 

NuQ8APE

 

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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.

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.

On-prem-data-gateway-UI-logs

Installer logs

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

Configuration logs

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

On-premises data gateway service logs

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.

On-prem-data-gateway-event-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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
bajee
Resolver III
Resolver III

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.

 

 

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.