Forum Discussion
Performance issue with Direct Query with SQL Azure
agree with greggyb live connect on Azure SQL or SQL Server On-Prem wil be slower than querying a powerpivot model fully compressed and using columnstore index. The only way to get closer to this kind of performance is using columnstore indexes on your SQL DB. But, even if you use CCSI on SQL DBs, Power BI traduce DAX query into SQL query which are not well optimized for SQL.
That's the same if you build an SSAS Taular Model, you will have better performance if you use In-Memory mode compared to Direct Query.
But if you use an APS (aka PDW) or an Azure SQL Db as source, you could have better performance, because you will benefits from CCSI and MPP.
these are some of the things to consider as you weigh in your options regarding direct query and import modes:
- Generally speaking, import models will be faster, regardless of how powerful your database/appliance is, unless all of the measures are materialized, but even then, there will be network latency to consider
- Direct query models cannot
yetcurrently be enhanced with calculations - Neither direct query nor import based models solve the security issue until RLS is implemented in the service
- Direct Query based models allow us to work with datasets that cannot be imported into a model that's less than 250MB which is the maximum size supported by the service right now
In short, as of right now, unless you are dealing with too much data to load in your Power BI model, I would always recommend to rule out the import option first.
andre, Direct Query against SSAS cubes respects security roles in that cube. Additionally, Direct Query against SQL should also respect row-level security in that database as well.
SSAS is a completely different conversation altogether... this conversation is about options to build a semantic model against a database from the self service perspective, so it is very important to understand the tradeoffs between the direct query and import model. If the SSAS cube had been developed, we would not be having this discussion in the first place.
Azure SQL DB and SQL Server 2016 have native row-level security, and this functionality can be mimicked in older SQL Server. Direct query against any source should respect the security defined in that source, since PBI Service will pass the effective username of the user logged into the service to the data source in a Direct Query environment.
