PowerBI DirectQuery Performance with Measures

[mmitch2k]

Hello, we are in the middle of creating a web app and powerBI embedded for our marketing cliends to create a custom reports dashboards. We started simply by creating reports with imported data, then swiched over to DirectQuery in order to prove RLS. This is when things went down hill. The performance hit with took using DirectQuery made the PowerBI report unusable. After doign some debugging we think the problem is with our PowerBI measures. The measures are not very complicated (take value from one table, divide it by valie of another table). When we remove these meaures fro the visuals the performace became acceptable. 

My question is, is it best practice to not use measures when connecting with DirectQuery or could be be something related to our database.

Databse is very small 8-10 tables. 50k rows of data in the biggest table.

Any insight would be greatly apprecated. Thanks

[Anonymous · Accepted solution]

Hello,

We had the exact same issue, sometimes the report took 60sec or more to load.

The best trick to reduce time was to split the report in to two or three tabs reducing the number of concurrent queries to the server.

Another bug(?) we have found is the Top N filter (in one report I had it for 6 different metrics). Removing that and displaying the information in an other way increased the performance significantly.

And ofcourse there is always the solution of upgrading your SQL.

As this is still an issue I would love to hear anyone else's ideas also.

ty,

C.K.

p.s. 

having the measures calculated in the database and not in the model would also help but that was not an option for us. allowing unrestricted measures in the Direct Query hurts also the performance. (check File > Options > Direct Query). Not allowing them though, would significantly limit the funtions you can have in the model.  

[Eric_Zhang · Accepted solution]

@mmitch2k

I've also experienced the performance issue in DQ mode. What I've found is the slow execution plan in database side. As the sent underlying query has some where clause like "CAST(yourIndexColumn as int) = 1", the CAST would not allow the index to be used, so creating index won't work in many cases. 

Also the Direct Query limitation has stated measure performance shortage. So, to have a acceptable performance, either use less measures or high tier Azure SQL database.

[sasshaikh]

I had similar issue, Below link helped me to understand the actual process,

https://link.medium.com/cZYywatC3xb

[Hazenm]

I know this is a very old thread, but I ran into this issue myself over the weekend. 
I had integrated a Dataverse table as Direct Query to allow my users to input variables to immediately update report. I was getting 15-20 second load times on any change because of the large amount of measures required to run the report. 

After reading this thread and reading more about DQ I realized that each one of my measures was querying the database many, many times (instead of an in-memory cache). So I faked it and started out each measure with a virtual table of SELECTCOLUMNS. The following variables and results used data from this virtual table instead of continuing to send new queries to the server. 
In this way, I was able to increase performance drastically. Seeing load times on the Power BI web application down from 15+ seconds to around 3 seconds per change. Still clunky, but it is now much more useable.

I should also note that my Direct Query table is very small (~300 rows), so that allows this method probably to work even better. 

[nanojau]

Hi guys, 

Use calculated measures in the SQL query, or inside of the native query is better. The performance is faster if you going to use Direct Query, the problem is that possible use only database to do it. I found the link below to differentiate Import and Direct Query to clarify both of them. 

https://www.cittabase.com/blog/power-bi-tips-import-vs-direct-query/

[tringuyenminh92]

Hi @mmitch2k,

What kind of source are you using? Is it tabular model or cube in SSAS or simple table in db engine? Have you tried to use profiler to get the query execution and analyze it. Maybe there are missing in indexing. Cause when using DQ, response time depends on how long server database return result. So please optimize the query execution in your server side(database).

[mmitch2k]

We are using a simple table in AzureSql.  We will look at indexing but our databse gets new data frequently, that may also affect performance as index is rebuilt.

[Eric_Zhang · Accepted solution]

@mmitch2k

I've also experienced the performance issue in DQ mode. What I've found is the slow execution plan in database side. As the sent underlying query has some where clause like "CAST(yourIndexColumn as int) = 1", the CAST would not allow the index to be used, so creating index won't work in many cases. 

Also the Direct Query limitation has stated measure performance shortage. So, to have a acceptable performance, either use less measures or high tier Azure SQL database.

[mmitch2k]

@Eric_Zhang

Thanks that for the response, that was a helpful link. It seems like in its current state PowerBi may not be the right solution for us. Too many lmitations for what we need. Its unfortunate, hopefully they implement some type of import or data caching system that can be used with PowerBi Embedded.

[tringuyenminh92 · Accepted solution]

Hi @mmitch2k,

Yup, There are some limitation of DQ and PBI team are trying enhance it. Hope you share your opinion in this survey to help them clarify our need: https://powerbi.microsoft.com/en-us/blog/help-improve-directquery/

[mmitch2k]

@tringuyenminh92

Thank you, just took the survey. 

[Anonymous]

Hi everyone,

Thanks for information currently I am facing same problem, one more thing I have noticed in DQ performance being affected if we have Created Bookmarks. I have tried without book marks, performance have improved.

Thanks 

Mazhar

[Anonymous]

@tringuyenminh92 as far as I know the cube is not supported in the power bi embedded

[Anonymous · Accepted solution]

Hello,

We had the exact same issue, sometimes the report took 60sec or more to load.

The best trick to reduce time was to split the report in to two or three tabs reducing the number of concurrent queries to the server.

Another bug(?) we have found is the Top N filter (in one report I had it for 6 different metrics). Removing that and displaying the information in an other way increased the performance significantly.

And ofcourse there is always the solution of upgrading your SQL.

As this is still an issue I would love to hear anyone else's ideas also.

ty,

C.K.

p.s. 

having the measures calculated in the database and not in the model would also help but that was not an option for us. allowing unrestricted measures in the Direct Query hurts also the performance. (check File > Options > Direct Query). Not allowing them though, would significantly limit the funtions you can have in the model.  

[mmitch2k]

Hi, thanks for the reply. We are currently not using any Top N filters. Ive tried putting one simple table visual on the page with 3 columns and it takes over 2 minutes to render. I will trying upgrading the DB