Debugging a slow Power BI report with Phil Seamark
Вставка
- Опубліковано 10 лип 2024
- Phil sits down with us to walk through his process in debugging a slow Power BI report. This uses some new features in Power BI Desktop as well as using DAX Studio to get further insights.
Connect with Phil
Twitter: / philseamark
Blog: dax.tips/
Guy in a Cube courses: guyinacu.be/courses
********
LET'S CONNECT!
********
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
Check out my Tools page - guyinacube.com/tools/
#PowerBI #DAX #Performance - Наука та технологія
Saw a bunch of comments on this. Try not to get caught up in the DISTINCTCOUNT vs. SUMX. The point of the video was the debugging technique. As Phil called out in the video, you may not see it have better performance on your end. Always test and verify. We chatted with Marco Russo as well. He commented that he would start but understanding what DISTINCTCOUNT was performing badly first. Could be due to relationships (maybe bi-direction), etc... the SUMX approach isn't exactly doing the same thing. Which is why mileage will vary.
How about Countrows( Values (Column name) ) ,why Sumx @Guy in a Cube
Phil Seamark is my all time FAV... Glad to see you sir with guyinacube ... I was lucky enough to meet you in person... THANKS A LOT! You are real DAX GURU..
Phil is a great person and super smart! We were happy to get him onto the video. 👊
Whoa, that is a sneaky way of fooling a DISTINCTCOUNT, love it! Also, kudos for the SE analogy.
Yeah it was pretty neat. Just be careful as it won't perform better every time. Test and verify ;) 👊
This is great, more and more techniques on troubleshooting is always helpful, nice to see Phil and have a great trip to NL guys
Appreciate that! We love the troubleshooting stuff. Thanks for watching. 👊
Great video, will help me with some challenges I'm working on now - perfect timing guys. Please keep up the great work!
Love it! Thanks for watching 👊
Awesome. Thanks Adam and Phil 👍👍
This was very helpful! Didn't even know PBI has a Performance Analyzer!
Bro, this was the real magic ...Please keep up the great work!
Guys, this was such a useful tuning 101, huge thanks. I'm slowly but surely getting to grips with Power BI/DAX coming from a SQL Server background. Had no idea you could copy the query from report elements. Thanks again, a really great nugget.
YES!!! It's the little things that help you move the needle 👊
Brilliant !! Thanks for making this available. Eagerly looking forward to more such techniques for better Reporting!! Loved It!! Many Thanks!!
Awesome! We definitely want to get more content out like this. Thanks for watching! 👊
very handy lads, thanks
So glad to hear that off-hand comment about filtering on fact tables over dimensions, that's a pet peeve of mine that I had a gut feeling was a bad idea, I'd just never gone to research it.
Great video 👌💯
Great video. Very helpful for a problem at hand :)
Love it! It has helped us numerous times 👊
Amazing Video guys. very very informative. Could you guys show how you take that enhanced query from DAX studio and use it in PowerBI.
Thank you for all of your work
Thanks
Pretty awesome, going to definitely try this out. Thanks guys!
Awesome! Glad to hear this may help you track some things down. Thanks for watching. 👊
Awesome! Love this. Thank you team.
Most welcome! Thanks for watching. 👊
thats very useful feature.. thankyou guys...
This is pure gold! The distinctcount trick was brilliant and helped my reports a good deal!
Please remember, this won't always be faster. Just try it with your dataset and see.
Agreed with Phil. Your mileage may vary. Always test and verify based on your dataset and query patterns. The distinct count part wasn't the point of the video and was just a quick example Phil threw together to talk about the debug technique. Talking to Marco Russo, he suggested, for DISTINCTCOUNT specifically, to try to understand why DISTINCTCOUNT is performing badly and try to fix that first before replacing it.
@@GuyInACube Right right right, that makes sense. Thanks for the clarification guys!
Perfect, thank you!
Thanks for this super video. Trying it out now
Most welcome! Thanks for watching. 👊
I love the process, can you please explain how you return the query back to powerbi desktop, you guys are amazing.
Useful technique, thx a lot
Most welcome. Thanks for watching! 👊
Really nice job Phil (and as always, Adam). I loved the analogy about getting cake ingredients one by one. Definitely going to steal that one with pride! Really helpful for providing an overall methodology that can be applied no matter the scenario. As you say below, Adam (or is it above?)--actual results may vary.
The grocery store analogy was great! Steal away 😀 Yeah results will always vary. Everyone's data is different.
You're perfect guys, sumx trick made my visual 4x faster!
Kudos.We need more performance videos. You showed only matrix visual what if we have many visuals in a page and using different functionalities. Please make a video on this
This is fantastic, more performance videos please! My query went from 12,460 to 121 ms!
Woah! Nice, was that just from using this technique?
WOW!!! That's amazing. Did you achieve that by using this technique?
@@GuyInACube Yeah! Phil and your enthusiasm made me start doubting myself, so I replicated the whole experiment, here are the two DAX studio results: (not sure if youtube allows links)
abload.de/img/daxstudio0wjgp.png
@@vog5197 Worked for me. Very impressive and well done! :)
Big thanks for a great video! I am trying to debug a reeeeally slow Power BI report but got stuck when trying to copy the DAX Query from the performance analyzer. We're using Direct Query in our reports and in my script there's a line for "// SQL Query" and the output says that I can't run the select statement. Is there a way around this?
Good Stuff! Wish i was at work right now to delve into some of users models. :)
Thanks Chip! This was a really fun one to do. Love digging in like this as it pushes us to really learn how things work.
Great vid!
For some reason though, the measure redefinition in DAX Studio does not work for me. Even when I comment out all of Measure's lines and just make it [Measure] = 1, for some reason when I press Run, it still uses old definition.
What am I doing wrong?
what are the tools other than DAX Studio we can perform this DAX analysis???
Life saver
Love the Bifocal shirt as well :D
Love the BIFocal guys (Jason and John). Listen to the podcast if you can. 👊
Sumx trick for distinctcount is interesting. I assume replacing All(Calendar) with All(Calender[Date]) inside filter also improves performance in this example?
Not by much. Clearing filters is fast. However if I was adding filters, I would target specific columns rather than an entire table.
Does CALCULATE and FILTER combination has any impact on measure's speed and efficiency? Im struggling to find a solution to slow down my PBI report. Have tried everything on the internet.
Thanks in advance, to you guys!
How do I get the new query from dax studio and replace it ln my visual ?
Excellent article! What tools apart from Dax Studio you all recommend to troubleshooting performance issues? Thanks and have a great week ahead!
VertiPaq Analyzer from sqlbi.com is a great tool that we use quite frequently to inspect the model. SQL Profiler can also be used for tracing, but DAX Studio does that as well and I can do more with that. Those are the main tools we use.
Guy in a Cube Thank you all! Have a great week!
Great video from @GuyInACube feat @PhilSeamark on how to bake fast DAX.
If you're in Wellington, NZ then meet Phil in person on the last Tues of EVERY MONTH at the Wellington Excel & PowerBI User Group. Know PowerBI users in Wellington? Share this so they can find us and geek out!
Appreciate that Jeffrey. Definitely a treat to sit down with Phil. Phil is amazingly smart.
its good thankyou
Glad you liked it Ali! 👊
What if the majority of the time spent by a query is not in the Storage Engine but in the Formula Engine? While my SE queries are at 76, 97% of the query is spent in the FE and not the SE. Any recommendations for troubleshooting FE?
Great video!
Since I found the dax studio plus performance analyzer/VertiPaq, it really spice things up
But THE question. What about the “OTHERS” measure listed by the performance analyzer? Hahaha
Often that reflects how much time visuals are spent queuing behind one another to render. Might be a good topic for another video.
@@MrRJolly Hi Phil, great video , very interesting.
But I have a report with a lot of measures, each one only takes few ms for DAX query but more than 1000 ms in 'other' !!
Where does it come from this 'other' time ?
How can we debug this ?
A video on that topic would be very helpful !!
Thanks in advance,
Pierrick
Can you share the file that you're working with for our best practices
RIP!
I have a query that takes 14.55 minutes, (data from1997) is it possible to exclude a query from a data refresh, so that i can create a new truncated (faster) query and than merge the tables to have whole dataset?
What the resources for learning about which functions are time-eaters, and what we can use as alternatives?
As with all things, it depends. a given function may perform well in one situation, but then not with another. I would highly recommend getting the 2nd edition of the Definitive Guide to DAX. It is really helpful in understanding what is happening, especially under the hoods. Be sure to test and validate your items to also understand more.
Hello, question about date tables. How to use dates with my country name mounths? Is it posibile?
You could also try COUNTROWS ( VALUES ( Sales[Customerkey] ) ) in stead of SUMX ( VALUES ( Sales[Customerkey] ) ,1) Don't know if it's faster though....
I have like 1.8 seconds total only, but the SE Queries are about 400+ ... Does that matter as long as total is not taking too long?
Nice tricks, guys. But , what should I do when option Other is the major impact on the visual using performance analyzer ?
How many visuals do you have on your page? Might be some queuing going on
More than 30, including cards, tables and background images.
yeah, that is probably one of the major contributors in your case. You should look at reducing the number of visuals. You mention tables as well. I've seen where tables and matrix visuals can contribute to it especially if you have a lot of stuff going into them.
@@GuyInACube Thanks. I'll try. Congratulations for the channel. It's helping a lot to create amazing dashboards here. Come visit Brazil.👍
Awesome, I am facing with my Power Bi dataset where we have the Import Data instead of Live connection.
Will this help approach help me debug the issue? as I just have the dataset and it takes a lot time approximately ~2 hours for refreshing data.
This technique will help you work on slow running DAX queries (import or DQ). It will not help you diagnose refreshing.
@@MrRJolly Thanks for confirmation!! If you can just suggest what method can be used to see where exactly it take long for refreshing?
It depends on the data sources. You do have some logging you can enable in Power BI Desktop for the mashup engine. This may help you but is hard to read. If it is SQL Server, i'd recommend getting tracing information from the SQL side as part of your data collection. It is not really straight forward though unfortunately.
Love the tips, SE analogy, and seeing these variables, I’ll def try TREATAS as filter variables. That SUMX was interesting for sure.. I’m just getting into having to optimize my measures in matrix.. would love more tips and options for measures like this.
Hello,
I am I’m experiencing a performance issue with one of my reports and when I run the Performance Analyzer it says that for the time for all actions is slowed down by the “Other” Category. Analyzer report listed below:
DAX Query 28
Visual Display 43
Other 16820
I went through the steps that you show in this video and I only have about 1 or two SE Queries according to DAX Studio.
Can you point toward where the issue is and how I can further troubleshoot it?
Thanks!!
If the "Other" bucket is the only large item you see, chances are you have too many visuals on your report. Check out this video for an example: ua-cam.com/video/kkIXtvU1AiM/v-deo.html
Guy in a Cube Thank you for your timely response!
It turned out that it was the OKVIz Smart Filter that was making the report very slow. I removed it and added the regular slicers and now the report is flaying!! Has this happened to anyone else?
Distinctcount - the scourge..)
My DAX studio is showing 39 ms while my performance analyzer is showing 305. Why is that?
hmmm ... probably more SUMX than DISTINCTCOUNT in my reports this time :)
Hi.... Please if you can make a vedio..in your report add buttons to each visual with and give command to print, or download the visual... Please
Thanks for the video Sahar. If you'd like to provide product feedback to the Power BI Product team, you can give that at ideas.powerbi.com.
I think this approach has some issues. When you use VALUES you also have to consider the fact that one blank row value may be returned, thus the sumx function would have a +1 extra row count.
In this case, the DAX was taking a lot of time, but what happens when we have visuals where DAX is not the major time consumer? How can you make it faster?
There is only so much you can do. Typically, the only major thing you could do is either limit the number of visuals, or if it is a table/matrix, try to make it no so complex so it doesn't have to spend as much time from a rendering perspective. Custom visuals also tend to have a higher render overhead than the base visuals.
I've seen so few things more horrid than the "yooo" at the start of the video.
Power BI sucks. I would ove an analytical platform where I could build a data model and use pandas to do data manipulation and aggregation.
and that's exactly what it is not. it's not an analytical tool.