is max freq for Power BI Service refresh q 30minutes? I need q15.

[hxkresl]

Is there a way to set more frequent refreshes than q30 minutes in power bi service?  (interested in q15 min).

If more freq refreshes not possible from Power Bi Service what are options for hosting tabular model with real time refreshes?

[GilbertQ]

Hi @hxkresl

Currently if you are using the On-Premise Gateway then the max refresh frequency is indeed 30 minutes.

As you hinted another option is to use DirectQuery which will then send the query as the user interacts with your SSAS Tabular model.

Very soon there will be the Power BI Premium, which will have up to 48 refreshes available, which over 8 hours will work out to every 10 minutes.

https://powerbi.microsoft.com/en-us/power-bi-premium/

[hxkresl]

@GilbertQ  Please elaborate. I am not familiar with DirectQuery.  

Currently I have all data needed for reporting hosted and refreshed in SQL server.  I refresh from SQL in power bi service and refresh in service takes only 5 minutes, and refresh in backend also 5 minutes for combined 10 minutes.  I would be able to provide 15 min latency if I could set it to q15.  Since I can't and am after real time I very much want to take this recommendation of DirectQuery.     

Is DirectQuery same as hosting data in Tabular Model and consuming in power bi service?  I really want to enable real time or as you say 'to use DirectQuery which will then send the query as the user interacts with SSAS Tabular model"  but I don't know where to start.  Can you pls recommend tutorials and some language I can do internet searches with'?

[GilbertQ]

Hi @hxkresl

Here are some more details

On-Premise Gateway

DirectQuery in Power BI Desktop

If you are looking for search terms you can use the following:

• Power BI DirectQuery
• Power BI On-Premise connecting to SQL Server

[GilbertQ]

Hi @hxkresl

If you have a SQL Server Source, what you can do is when you connect to your data using Get Data, you can change it from Import Mode to DirectQuery as shown below.

/t5/image/serverpage/image-id/27095i99BEA7AACE78F264/image-size/large?v=1.0

Then you can connect your On-Premise gateway to your dataset, and when you click on the data it will send the query to your SQL Server source.

[hxkresl]

@GilbertQ  Sounds like I am closer than I thought!  pls share link again, it doesn't return anything.   

[GilbertQ · Accepted solution]

Hopefully this will show the picture

[hxkresl]

@GilbertQ  Do i need to rebuild the data model from within power bi service in order to use DirectQuery?  I have already built the report in Desktop and uploaded to service. So, can I activate direct query for this existing published pbix? 

If I need to rebuild model, i do not see plain SQL Server as option.  Given only Azure SQL DB, or Azure SQL DW, or SQL Server Analysis Services, or Spark.

[GilbertQ]

Hi @hxkresl

Unfortunately you will have to rebuild the model in the Power BI Desktop.

Once you have re-built it in the Power BI Desktop you will then have to upload it to the Power BI Service, and then make sure you have got your On-Premise gateway configured and set up. After which you will then be able to use the DirectQuery.

Just note that to use DirectQuery will require a Pro license.

[hxkresl]

@GilbertQ   so to make sure I got you:

1. i rebuild model from power bi desktop and upload to Service and then activate DirectQuery? or did you mean i have to rebuilding from power bi service?

2. only options available are Azure SQL DB, Azure SQL DW, SQL Server Analysis Services, or Spark, right?  no vanilla SQL Server db?

[GilbertQ]

Hi @hxkresl

Yes that is correct in that you will have to rebuild.

And once you have uploaded you will then have to connect it to the On-Premise gateway to get it working with your SQL Server database, not via the connected services.

[hxkresl]

@GilbertQ OK, i got it.  I will rebuild from desktop using Get Data + SQL Server.   Thank you.