If you’re familiar with the topic of query folding in Power Query, you’ll know that the View Native Query right-click option in the Applied Steps pane of the Power Query Editor can be used to show the native query that is run against the data source. You may also know that there are some data sources where query folding does take place but where View Native Query remains greyed out.
In the April release of Power BI Desktop the Power Query Query Diagnostics feature was enhanced so that you can now return performance counter data. As the blog post says:
When you run performance counters, every half second Power Query will take a snapshot of resource utilization. This isn’t useful for very fast queries but can be helpful for queries that use up a lot more resources.
Currently there’s a lot of talk about Business Central performance. Microsoft has released and is releasing guidelines, tools about mechanisms to program for performance, analyse and monitor performance. At the same moment there’s also discussions going on on public and private fora about database size and limitations. And last but not least Artificial Intelligence is also a hot topic, making Business Central smarter, so it can help you in your daily business.
In my last post I showed how to connect SQL Server Profiler up to a Power BI Premium dataset but I didn’t give you any examples of why this might be useful. In this post I’ll show you how you can use a Profiler trace to visualise all the queries run by a Power BI report, see when they start to run, see which ones run in parallel with each other and see what the overall time taken to run all the queries is.
Performance wise it can be very interesting to use query objects as a data-source in a report (or any other object).
Imagine you want to create a report that shows the top X records from a table, let’s say for example Customers, then creating a report dataset with a top-x filtering can be very cumbersome.
Query objects can also perform calculations on data, such as finding the sum or average of all values in a column of the dataset.
Business Central query objects enable you to retrieve records from one or more tables and then combine the data into rows and columns in a single dataset.