Reply
Regular Visitor
Posts: 24
Registered: ‎09-22-2016

Dataset refresh not working from Azure SQL

We have created a report in Power BI desktop that loads data from Azure SQL (Not direct query). We published the report under the Power BI service and the refresh data set worked for a while.

 

Recently it stopped working for some reason and this is what shows up on the Dataset settings page:

 

https://www.dropbox.com/s/vwju552dymtqkc4/Power%20BI%20Refresh.JPG?dl=0

 

There is no option to change the database credentials or anything similar. It used to be there... 

 

We have many reports based on that data set, so renaming the dataset and republishing it is not an option.

 

Thanks!

Super Contributor
Posts: 2,935
Registered: ‎06-25-2015

Re: Dataset refresh not working from Azure SQL

@gorgiRankovski Are you connecting to any other data source? Do you see the most recent data when you look at the report from with the Desktop? If there are data changes, have you republished the PBIX to see if that would re-initiate a refresh?

Regular Visitor
Posts: 24
Registered: ‎09-22-2016

Re: Dataset refresh not working from Azure SQL

@Eno1978 there are no other data sources, everything comes from a single database. From the desktop tool I am able to refresh the data and when I publish it to the service, the new data is available in the service. That's the current workflow, but I would like to be able to refresh the dataset from the service.

 

I tried republishing with different credentials of the data source, but nothing helped. 

 

In the refresh history, I see entries for Scheduled and all rows status is set to completed. The start and end time have the same values. 

Moderator
Posts: 4,314
Registered: ‎03-06-2016

Re: Dataset refresh not working from Azure SQL

Hi @gorgiRankovski,

 

Though you said you didn't use DirectQuery to the Azure SQL database, from the image you shared indicates it's DriectQuery mode rather than Import. The reason is Databases in the cloud.

 

Generally, if the dataset only contain Azure SQL database without any other data sources, we needn't to configure gateway for the dataset. But from the image, it said the "data sources under the data gateway are offline". I guess you have configured Azure SQL data source under data gateway. Please go to Manage Gateways, find the Azure SQL data source, make sure Connection Successful and the gateway host this data source is Online.

 

Best Regards,
Qiuyun Yu

Regular Visitor
Posts: 24
Registered: ‎09-22-2016

Re: Dataset refresh not working from Azure SQL

Hi @v-qiuyu-MSFT

 

This has been a problem for a while, so I've tried all obvious solutions. I never set this up as a gateway, but actually used SQL Server database as a source. Now, I see they added Azure SQL, but back then, they did not had that option. Could that be the reason?

 

When I go to Manage Gateways, it says "You don't have any data gateways."

 

At the beginning (6 months ago), the desktop file was initially pointing to the Azure SQL database (with the SQL Server database source option) and the refreshing was working under the PBI service.

 

Thanks for your help.

Moderator
Posts: 4,314
Registered: ‎03-06-2016

Re: Dataset refresh not working from Azure SQL

Hi @gorgiRankovski,

 

I notice that you hide the information that who configured this dataset. Please let this author check his data gateway and data source, make sure all are online.

 

q2.PNG

 

If the author is yourself, as you don't have any gateway, I guess if some one share the data source under his data gateway with you, you can ask your workmates to check this. See: Manage users.

 

Best Regards,
Qiuyun Yu

Regular Visitor
Posts: 24
Registered: ‎09-22-2016

Re: Dataset refresh not working from Azure SQL

@v-qiuyu-MSFT

 

I set up the dataset. We are only 2 persons in the workgroup. 

 

We tried swithcing over the ownership, but without result. The other person also doesn't have the gateway.

 

Not sure what else to try... Is there anyway I can contact Power BI team directly for this issue? 

Regular Visitor
Posts: 24
Registered: ‎09-22-2016

Re: Dataset refresh not working from Azure SQL

@v-qiuyu-MSFT

 

Also, I think once I have published the dataset from the Power BI desktop with a local database connection. Now, in the Developer Console, I'm looking at the network traffic and this URL: https://wabi-north-europe-redirect.analysis.windows.net/metadata/datamovement/models/1452244/dataSou...

 

And I see collection called "monikers" and each item has a property called "monikerDataSources" which is a collection of objects. All of the objects are pointing to my local database called ".\sqlexpress2016". I suspect that this is the problem.

 

How can I clear these data sources and make the data set point our production DB?

Regular Visitor
Posts: 24
Registered: ‎09-22-2016

Re: Dataset refresh not working from Azure SQL

@v-qiuyu-MSFT @Eno1978

 

Any ideas?

Super Contributor
Posts: 2,935
Registered: ‎06-25-2015

Re: Dataset refresh not working from Azure SQL

@gorgiRankovski 

Check the Azure SQL side... Did the firewall rules change, access permissions on that side, did the password of the user change?

 

 

I could throw down a bunch of hyjinks that you could try in the Desktop, but I don't necessarily know what the cause of this could be...