Refresh Every 5-10 minutes

[ElliotP]

Evening,

I was wondering if it was possible with a pro license (or even a free) to refresh a dashboard/report every 5-10minutes?

I've seen the option to use streaming analytics but I'm not sure how I would be able to use this as well as apply my transformations in powerbi desktop as so the end user sees my work with the data.

I've seen the option for DirectQuery, but from what I've read I encounter the same issue as immedaitly above.

[Anonymous]

Hello,

I understood that we can set incremental refresh for every 30 minutes only in dataflows?

Is that right?

[Mohit_Chhabra]

Now, you can refresh data set ervey second. Check below link. 

https://thetrendsettershub.blogspot.com/2023/08/power-bi-tips-api-refresh.html

[GilbertQ]

Hi there, the same applies for dataflows as far as I am aware

[Seth_C_Bauer]

@ElliotP

I was wondering if it was possible with a pro license (or even a free) to refresh a dashboard/report every 5-10minutes? No, not with those license types. Premium offers much more refresh times but that is because it is in an isolated capacity.

I've seen the option to use streaming analytics but I'm not sure how I would be able to use this as well as apply my transformations in powerbi desktop as so the end user sees my work with the data.

I don't understand the last part of this. You can use API's to push data into Power BI. 

I've seen the option for DirectQuery, but from what I've read I encounter the same issue as immedaitly above.

What issue? If the data source supports DQ then this option is optimal as the report would be a live look at what is in the data source. As the user interacts with the report they would see the latest information.

[ElliotP]

@Seth_C_BauerThanks for the quick response.

Right, that's interesting about premium.

In regards to Streaming Analytics, I can see where you can add the Real Time Streaming of data option (videos, personal experience, etc); but what I don't understand is how I would be able to apply the transformations, measures, etc that I do in powerbi desktop for example. Without being able to apply these transformations, use my measures, etc then it is very much just showing streaming data, that's it.

In regards to DQ, I'm using an Azure SQL Database so I think it supports DQ. From what I've read I'll have the same issue as above where I won't be able to apply transformations and measures upon the data before it is published to the web to be consumed.

[GilbertQ · Accepted solution]

Hi @ElliotP

If you are connecting using Azure SQL Database, it is by default then connecting using DirectQuery. So what that means is that everytime you click on something in your report, it is sending a query back to your Azure SQL Database in realtime and returning the results.

Your dashboards are automatically refreshed every 15 minutes, and there is the possibility to change this in the service.

You can find more details with this here: https://powerbi.microsoft.com/en-us/documentation/powerbi-azure-sql-database-with-direct-connect/

[Nithyakala]

Hi,

how can we schedule dataset in every 30 minutes? here we are able to schedule 8 times. 

Please suggest is there any other possible ?

[Seth_C_Bauer]

@Nithyakala When you use the import connection type, you can only schedule up to 8x per day.

[Nithyakala]

Ok thanks

[ElliotP]

This is interesting.

I'm attempting to change the type of a column from an sql table in powerbi desktop to datetime and it isn't allowing me as it says it isn't supported in Direct Query.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/

I see that special treatment of dates is a limitation. In my sql database my DateTime Column is held as a String data type, would converting it to DateTime solve this issue?

Otherwise, how often am I able to refresh my reports using imports? Once an hour? 8 times a day?

[GilbertQ]

Hi @ElliotP

I would always suggest making sure that your data types on your columns reflect what the data is in each column. This can increase the performance of the database, as well as in this case, then mean that Power BI will recognize the DateTime column.

As mentioned before when using DirectQuery there is no need to configure an On-Premise Gateway as it is all managed by the Power BI Service.

[ElliotP]

My data is held in an Azure SQL so I wasn't worried about using an on premises gateway.

So if I change the column types in my sql table it will recognise the datetime data type and I'll be able to work with it using DirectQuery?

[GilbertQ]

I cannot say 100%, but I am fairly certain it should work yes.

[ElliotP]

Ideal.

I noticed my DTUs spiked dramatically and maxed out when Direct Query was pulling. It took a solid 20 seconds for the visuals to appear when i clicked on certain visuals. Do I simply need more DTU's?

[GilbertQ]

Hi there.

What I would suggest doing if it is taking so long is to look at ensuring that you have the correct indexes and joins in place so that the query can run as efficiently as possible.

Also look at column store indexes which can help improve query performance also

[ElliotP]

@GilbertQThanks for the responses; I'll try it out and see how it goes.

[GilbertQ]

Glad to have help