Power BI query reduction when using DirectQuery

which of the following is a measure of how quickly an item can be converted to cash? This is a topic that many people are looking for. star-trek-voyager.net is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, star-trek-voyager.net would like to introduce to you Power BI query reduction when using DirectQuery. Following along are instructions in the video below:

“What s up this is patrick from guy in a cube and in this video. Video. We re gonna talk about query reduction for direct query stay tuned. Okay imagine imagine this you ve got this huge database and you re trying to minimize how many queries go to this database or or the database not only is it big.

But it s slow and there s nothing you can do to make it faster right you can send you ve tuned it out and there s nothing you can do to make the query response time effect response times. Faster. So you re looking for a way to reduce the chattiness between power bi and that database or you re looking for a way to control when queries go to the database in power bi. You just want to minimize the number of queries that go to the database.

Because you re using direct query. These people want real time queries real time reporting from that database. And so what do you do well back in november. I think it was november if i if i if it s wrong.

Adam corrected on the screen november of last year. They really microsoft the product team released a feature called query reduction. And that s what i m going to talk about today especially specifically for direct query. Okay so instead of all this talking you guys know how i like to do.

What is head over to my laptop alright. So imagine right use your imagination imagine this is connected to a very large a very large slow database okay and every time and i m gonna use dax to do an i ll bring it onto the screen just a little bit to show you what happens so let s imagine filters and slices. So right i ve set up multi select so you can select multiple items here so watch what happens. I choose 2012 i m going to show you the queries in a little bit so i choose 2012 alright and then.

I choose 2011 right and so what happens is let s bring that studio down over here just so you can see the queries..

That s coming through that s being sent over to you know that s being run on the model that sent down to the the underlying data source. So you can see the first queries that it sends down it s just looking at it double click here is just 2012 right. But when i select another option in that multi select list. It sends the query back down as 2012 and 2011.

Two round trips for the same set of data all right we want to reduce that we don t want that to happen in fact. Let me clear this out in fact take a look at this if i use my filter here. So i have a page level filter on quarter. If i choose one right and then i choose all right and then you ll see if we go back over to dax to do all right.

It s running so many queries behind the scenes here so it s got one and then it s got one two for that given year. I want to minimize you see what i m saying so if i double click. This one right. It s you know this for this just for these two years with this particular quarter and then if i get rid of that and open this one up you could see right it s for this year.

These two years and these quarters right same query just ran more times than i wanted to run. I want to minimize what happens there i don t want this to happen over and over and over again. I want to control when these queries are sick. So.

That s when i can use query reduction. So let s go ahead and clear this out minimize this and then we re gonna go to file options and then we re gonna go right down. Here. You re gonna see an option here called query reduction c.

Query reduction click..

It and the first part of query reduction. I m going to talk about is show and apply button right show and apply button and only send queries. Once for slices and filters so instead of saying. If i have a multi select.

If i say 2012 send the query then i say 2012 7. 2011. Send the query instead of doing that only sin 2011 2012. So i m gonna check this box and uncheck filter both of these right i m gonna click ok then what i m gonna do is uncheck these two and i m gonna say sin this one this one right if we go over to that studio nothing s happening right but when i click apply i m gonna go ahead and click apply all right wait for the filter to happen and then we ll look over at dax to do and then only one query came through right multiple queries one for each element.

But only one query for each element instead of two queries for each element. So ever. I ve reduced the round trips to my underlying source right i ve cut it in half. This is great and it also works for filters man.

If you guys are using directquery and you want to control things you know how wind queries are going to your underlying data source you should take a look at query reduction. But wait but wait i m not finished there s one more thing that i want to show you guys all right so check this out all right so we re gonna clear everything out here in dax to do i m gonna go back here. Let s clear everything out here and you guys know there s this cross filtering that happens so if i click here if i click here if i click here right everything is being updated every time i click something right every time. I click.

These elements are being updated every time i click something every time. And if i go back over to dax do you just see all these queries coming through coming through and it s just kind of you know it s clogging up my underlying data source because we re constantly sending all these queries back to the underlying system and i want to control this right and so instead of let me clear this instead of all these interactions happening maybe i only need interactions to happen between the table and the map and the bar chart. I don t need to happen between the card oh maybe i only wanted to happen between the table and the card now. I know what you think and patrick.

I can go i can click on the element..

I can go to format. And i can choose edit interactions. I can turn it off for each one yeah of course you can. But what if you want to off by default for all of the elements and then you just want to turn it on as you need it well same option right file go to file go to options.

Options and settings go to options. Alright and we ll go to query reduction. Check. Unless uncheck these and check.

The box for disable cross. Highlighting you can give them all check. If you want it s completely up to you and click ok. So.

If i go to edit interactions. You ll see that all of them are turned off. And now i have to selectively turn the ones. I want on so instead of all those queries coming through every time i interact with some elements.

I m visual on the report. Only the ones that i turn on so i turn on this checkbox turn them by the fall and then i have to explicitly go turn them on so. I m gonna go say. So.

What i need is for this particular element..

I want this one to be on and this one to be on so now if i just pick one. So. Let s make sure our decks studio is clear. Let s clear it out so instead of seeing three queries come through right.

I m only gonna see the two queries. If it hadn t cashed something up you guys know part b. I will do some some caching on this side. But perfect.

I only see the two queries coming through just for the particular. One the particular you know row in that table that i ve selected. It s filtering out just the bar chart and the map. It just works like that it s great.

If you haven t tried this out if you re using direct query and you re struggling with so much chattiness going between power beyond your underlying data source or if you want to control when queries are going back to that underlying data source. You should take a look at query reduction. Okay so if you guys have any questions comments about this if you re doing this other way let me know post it in the comments below. If this is your first time visiting the guy in the cube channel.

You know what you got to do hit that subscribe button. If you like my video. Me a big thumbs up as always from adam and myself thanks for watching we ll see you ” ..


Thank you for watching all the articles on the topic Power BI query reduction when using DirectQuery. All shares of star-trek-voyager.net are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.


Leave a Comment