Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Solutionz
Helper I
Helper I

Azure SQL Data Refresh

Hi There

 

We have a Azure SQL database that is kept refreshed by Azyre Sync Services. Then we have Power BI datasource linked to this Azure SQL database.

 

There should be no need to refresh the Power BI reports but when I look at them they are never refreshed. When I click on Refresh it says I need a Gateway installed, but that does not sound right?

 

When you seutp an Azure SQL database it says there is no refresh required. This surely means the data should always be as what is in the Database?

 

Am I missing something? Does one Gateway need to be running anyway?

1 ACCEPTED SOLUTION

15 REPLIES 15
Greg_Deckler
Super User
Super User

Well, it says that Azure SQL Database doesn't require a gateway and supports refresh:

https://support.powerbi.com/knowledgebase/articles/474669-refresh-data-in-power-bi

 

 

But it also has three asterisks that point you to this article:

https://support.powerbi.com/knowledgebase/articles/527228-troublehooting-scheduled-refresh-for-azure...

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Yup, I found that too and it's a strange one..

 

When you login to Power BI Desktop the data is not fresh. You have to Refresh to load the data and then it all updates (graphs etc)..

 

But at that point the web is still out of date. I have to then Publish the reports before the web updates. I must be missing something? I'm now installing the Power BI gateway just ot see if it makes any difference. I'm not setting any refresh there as it won't allow it on an Azure SQL db..

 

 

Further to this...I've not installed the Power BI gateway and the Azure SQL server is now visible under my Manage Datasources section..It required me to edit the credentials and seems to be up now. 

 

My data refreshes at 12:00 and I'll see if the data is now live...It does seem you need at least one gateway running.

If you connect to Azure SQL from PBI Desktop the connection is not live ( the data are stored in PBI desktop data model) ..You need to schedule refresh..No need for Personal DMG..In order for the Azure SQL to be "live" ( refresh every 15 min & with quering ) needs to be connected through the "Get Data" Databases options..

Up to now there is no "live connection" to Azure SQL as for SSAS in PBI Desktop..

 

**edited .. for live Azure SQL you need Pro lisence 

Konstantinos Ioannou

Thanks for the reply...

 

I now realise that if the dataset is in Power BI desktop then it needs to be Published to take affect. But if I create the Data Link in the cloud through "Get Data", I assume this is different....But it is not...The data cannot be refreshed nor is it refreshed automatically. So with Azure it seems right now either way nothing can be done but delete and start again? Surly this cannot be right?

 

When trying to refresh the Azure linked DB this appears...

 

"You cannot schedule refresh for this dataset because it does not contain data model connections. You cannot schedule refresh on worksheet connections or linked tables. To schedule refresh the data must be loaded into the data model."

 

Basically my steps are:

- Get Data directly from Azure SQL Database - (It gets the latest)

- Data changes but Power BI does not update

- Start Again

 

 

Cool, I'll be interested to know if it works with the gateway.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Did nothing...

Yeah, you are correct, as of now, if you create the data set directly in Power BI Service, you cannot refresh it, you have to put it in the a data model. Actually, if you go to Schedule Refresh, it tells you that:

 

"You cannot schedule refresh for this dataset because it does not contain data model connections. You cannot schedule refresh on worksheet connections or linked tables. To schedule refresh the data must be loaded into the data model. Learn More about data sources that currently support refresh. "

 

I get what you are saying though, certainly kind of a weird limitation...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I'm jumping around here...My data is now refreshing from the Azure SQL..It think the only difference is that I created the data connection direcly in the Power BI interface on the Cloud, not the desktop.

 

If I log out and back in again online I see little wheels spinning and it refreshes...

My test: 

The tiles are not refreshing.( 3 hours ) .Although when you click a tile the report queries the DB and the graphs are refreshing..Strange but the report keeps cached the old data..so when I go back to dashboard & to report it queries again..doesn't keep the latest query values

Konstantinos Ioannou

Me again....

 

I can now confirm that the Azure SQL data refreshes when I close Power BI and log back in. I suspect it also refreshes after a while (I've not sat waiting to see). The mobile app also refreshes when closed and re-opened..

 

To get this to work I had to create the "Get Data" connection on the powerbi website...

 

However, I now have another issue. A new table has been added to the Azure SQL database, it has data and I can query it from MS Management Studio..However, the new table is not showing up on the Power BI Dataset..The data in the existing dataset has refreshed but the new table is not visible.

 

Any ideas? I hope I don't have to delete the dataset and start again..

Hopefully not..you will have to wait..(again no tried )

https://support.powerbi.com/knowledgebase/articles/581421-azure-sql-database-with-direct-connect

 

Untitled.png

Konstantinos Ioannou

Hi, 

Is there a way to manually trigger schema update? Or will I need to create a new connection, and redo all the reports?

Hi Sparso

 

At the moment I am assuming that I have to reconnect the dataset for the schemer to refresh. It allows you to connect the same database twice but I suspect that will become messy. So until Microsoft add an auto-refresh or manual (either way is good), I'm rebuilding my reports each time.

 

😞

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors