Increasing On-Premise SQL Server Data refresh rate

[Anonymous]

Need a way to refresh on-premise sql server db data to Power BI reports less than every 5-10 mins. problem is currently using Gateway i can refresh only 30 mins once.(i have used Currently Direct query mode for the reports).

I saw about Power BI API Push mechanism but how it affects existing data present in the data set already published? do we need to make logic in a Sql server stored procedure or program to maintain which data is sent to Power BI APi and which is not and call this API every 5 mins or so? is this right approach or can some one suggest me a better way to do this.

Note: i cant use other services like Azure or SQL server Analytics services in the cloud. 

[v-shex-msft]

Hi @Anonymous,

I don't think it is possible to achieve this through gateway scheduled. Gateway has limit on daily refresh frequency and refresh interval.

Data refresh in Power BI

Configuring scheduled refresh

For your scenario, I'd like to suggest you to create a streaming dataset to push data from sql database every 5-10 minutes.

Real-time streaming in Power BI

Notice:

1. By default streaming dataset can only use on specific live tiles and it not store pushed data to power bi service.

2. If you want to create other visuals, you need to turn on history data option to store pushed data to power bi service dataset then you can create visual based on it.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin

Thanks for your feed back. i have below two questions

Question 1

For other visuals i will turn on history data as you mentioned but when i do some changes in my local for the report and publish again will my data set gets refreshed with the new one? how my existing push data set gets affected with this?

Question 2

For other visuals if i start using Pushed data set to Power bi with help of Power BI Rest APIs each time i need to push only the difference in data correct? so i need to maintain each time what data is pushed to Power BI data set and what is not ? in case of any failure i need to send them again? can you give example/ tutorial for this if you have any ?

Thanks

Aravind

[v-shex-msft]

Hi @Anonymous,

I think you are misunderstanding with streaming dataset. When you turn on history data option, it will store each records which you pushed without any modify operations on stored records.  If you haven't turned on that option, these pushed records will not save and only display last pushed records on live tiles.

According to your description, I'd like to suggest you use directquery connection mode to design your report, it support more shortly refresh frequency.

When you interaction with it, it will try to load last data from database; if not contains any interaction, it will refresh every 15 minutes.

Regards,

Xiaoxin Sheng