CODEWITHSUNDEEP
performancepowerbi
Erechtheus
Erechtheus

Reputation: 765

How can I Improve Direct Query performance between Microsoft's Power BI Service and our SQL Virtual Machine?

We have a B4ms VM running a SQL server (as well as web server). We have installed Power BI Gateway on it to make reports with on-prem data.

Basically the user can sign to the server and view power bi reports in the browser.

I find it a bit dumb that the user has to query Power BI for the data, that in turn gets it from the machine, but perhaps there is no other way.

The issue we are running into is that some visuals take a huge performance hit when loading. Some even seem to exceed the resources.

I know it's somewhat of a broad question to ask, but maybe specifically - is there a way to improve the connection between the VM and the PBI server?

Upvotes: 0

Views: 4301

Answers (2)

Jon
Jon

Reputation: 4967

It will depend on the type of query that you are doing/sending down to the SQL Server, for a number of projects that I have deployed, I have used Direct Query to sit over data sources that have been at least 50-100GB, however these have been mostly standard Star Schema data warehouses, or a defined reporting table, both will have the relevant indexes, covering indexes, or Column Store Indexes to allow more efficient retrieval of data. These have been on Azure SQL and On-Prem SQL Instances.

Direct Query Mode will slow down due to the number of query's that it has the do on the data source based on the measure, relationships and the connection overhead. Another can be the number of visuals on page, as each visual is a query and each one has to run on the data source.

One other method to increase the speed of Direct Query would be to use Aggregations in Power BI, to store an imported subset of data in Power BI. If the query can be answered by the aggregation layer then it will be answered quicker. Microsoft demonstrated this with the 'Trillion Row Demo'

In terms of the Power BI Direct Query Issues, from the range of clients that I interact with, those that do have issues with Direct Query, have a mash up of tables in an inefficient schema, running sub optimal query's on the data source, with a number of data transformations in DAX, and DAX measures that have been badly written, for example lots of DISTINCT COUNTS & SWITCH.

For the connection make sure you have the latest Data Gateway Installed/Update as optimizations to the Mash Up engine can make it faster. Another option would be to shift the DB to Azure SQL Database and remove the need for the gateway.

Upvotes: 2

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

For DirectQuery reports you need to examine the generated SQL and evaluate the execution at SQL Server. You can use the Performance Analyzer in Power BI Desktop to capture the DAX and SQL generated as your DirectQuery model interacts with SQL Server, and then use SQL Server Management Studio and the Query Store to examine the Execution Plans and indexing options.

Upvotes: 1

Related Questions