cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Performance issues with DirectQuery, possibly due to gateway?

Hi all,

 

We are working on a new setup for our PowerBI users. We created a set of views on which we build datasets in PowerBI Desktop using DirectQuery. We publish these datasets to PowerBI Online to be used as the datasource for our users to build reports on. This way, we will not have multiple datasets which happened earlier when everyone published from a live connection to a multidimentional cube.

 

Unfortunately, we are running into performance issues we did not expect and do not understand. For example, it even takes a while to load a slicers even if there are only 5 options to select in this slicers, we're talking 5-10 seconds wait, which should be near instant. It seems that when we're trying to retrieve a lot of data the gateway can't handle the amount of data. We get an error, which I haven't found much about as of yet:

 

Gateway error.PNG

 

We currently have our gateway installed on the same machine as our SQL server installation, where also our ETL is running. We never had an issue with this when we were using the multidimension cube (SSAS) with everything on the same machine. Does anyone know if there is a difference when using DirectQuery which could impact the performance? And could it then be a solution to have a dedicated machine on which to install the gateway so that SQL server and the ETL are not taking up resources? Any help is appreciated as we cannot figure this out at the moment. Thanks

1 ACCEPTED SOLUTION
Burningsuit
Solution Sage
Solution Sage

Hi @Anonymous ,

I think the first qyestion to ask, is why are you using Direct Query ? Do you have multiple million rows to report on, or require real-time reporting (or both) ? Because I think DirectQuery is part of your problem. As you are already finding out, connecting Reports to a DirectQuery Dataset will generate an SQL query for every Visual on the Report and every Slicer, this will then generate another set of SQL queries whenever the Page is changed, a slicer is clicked, or an element on a Visual is clicked (cross filtering). Unless your Database is configured to expect this extra load, and the Gateway is able to keep up with the traffic, you will experience pauses and slowdowns. Moving the Gateway to a dedicated server and the SQL database to another dedicated server may help, but unless you really really need real-time data reporting you are probably better to go with an Import model not Direct Query.

 

see...

Import vs DirectQuery in Power BI - SQLBI

Power BI: Import Mode Vs DirectQuery Mode - TechNet Articles - United States (English) - TechNet Wiki (microsoft.com)

 

Hope this helps

Stuart

View solution in original post

5 REPLIES 5
v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

 

For information about using DirectQuery with SQL Server Analysis Services, see DirectQuery in SQL Server 2016 Analysis Services.

 

There are many facts can affect Power BI report render performance. I would suggest you go through this article and try to optimize reports then test again: https://docs.microsoft.com/power-bi/power-bi-reports-performance

 

 

Best Regards,
Winniz

Anonymous
Not applicable

Hi @v-kkf-msft,

 

Thanks for your reply and documentation. We are now choosing for import mode storage as for the most cases 8 times refresh per day is enough. This gives us a substantion performance increase compared to DirectQuery. I'll have a look at the optimization techniques.

Burningsuit
Solution Sage
Solution Sage

Hi @Anonymous ,

I think the first qyestion to ask, is why are you using Direct Query ? Do you have multiple million rows to report on, or require real-time reporting (or both) ? Because I think DirectQuery is part of your problem. As you are already finding out, connecting Reports to a DirectQuery Dataset will generate an SQL query for every Visual on the Report and every Slicer, this will then generate another set of SQL queries whenever the Page is changed, a slicer is clicked, or an element on a Visual is clicked (cross filtering). Unless your Database is configured to expect this extra load, and the Gateway is able to keep up with the traffic, you will experience pauses and slowdowns. Moving the Gateway to a dedicated server and the SQL database to another dedicated server may help, but unless you really really need real-time data reporting you are probably better to go with an Import model not Direct Query.

 

see...

Import vs DirectQuery in Power BI - SQLBI

Power BI: Import Mode Vs DirectQuery Mode - TechNet Articles - United States (English) - TechNet Wiki (microsoft.com)

 

Hope this helps

Stuart

View solution in original post

Anonymous
Not applicable

Hi @Burningsuit,

 

Thanks to your input we explored the import model and will now choose this as our main solution. The performance is a lot better than DirectQuery, and the data and refresh options are good in 99% of our cases.

 

Thanks for your input.

Anonymous
Not applicable

Hi @Burningsuit, Stuart,

 

Thanks for your reply. The reason we went with DirectQuery is we're coming from a live connection to an SSAS cube, and users are used to having realtime reports. Sometimes information is added to a datasource and needed in reports within the hour, so with current processes, near real time reporting is desired. The live connections seems to perform a lot better than direct query, even with the SSAS cube on the same machine. I must admit, I'm not familiar yet with performance aspects of live connection vs. DirectQuery and assumed they would perform about the same. 

 

On the lowest granularity, and unfortunately some users want to analyze on this level, to a few years back, we do have multiple million records. We've modelled our DWH so that regular SQL queries written by us perform well. Unfortunately, we can't influence how PowerBI queries the model, and now see that these queries do not run fast on our views.

 

Seems a possible solution might be to offer imported datasets for reports where realtime data is not neccesary and stick with directquery for near real time data. We might investigate what a dedicated machine could do in terms of performance.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!