Performance issue with Direct Query with SQL Azure

[dilkushpatel]

Hi,

I'm trying to use direct query feature with data from SQL Azure DB.

Now I do have quite a good amount of data in there . (one of the fact has 7 mil records)

I understand that with qirect query it will try to do query for each and every click and importing this amount of data also does not make sense.

So is there any way I can improve performance so that i can create viable report?

This could well be more of a Azure question but wanted to see if there is anything in power bi that can be helpfull.

just to give perspective simple select count from fact table with 7 mil records took 35 min.

Any tips for improving perfomance?

Thanks,

Dilkush

[fbrossard]

If your Azure SQL DB is at Premium Level, you can use Clustered Culomn Store indexes.

But @andre is right 7M rows is not large.

Do you need live connection on your Azure SQL DB ? 

If not, try to use PBI Desktop to import your data. I've worked with a 4 milions rows fact table, and my pbix size was less than 50 Mb.

[Bjoern]

@fbrossard

Live-Connectt should be faster. Live-Connect will always be more favorable than an import, as security-settings, scalability ... blabla. Lot of reasons.

I would be interested whether the ccsi have solved the issue. 

[greggyb]

@Bjoern, it seems to me that the expectation should be that any DirectQuery will be slower than a hosted model. You get an extra round-trip of latency from Power BI Service to SQL Server, along with potential bandwidth issues if the returned dataset is large (shouldn't be the case).

Additionally, you've got an additional level of boilerplate query generation. DAX must be translated to SQL. Being two layers removed from writing optimized queries against the source DB leaves me expecting inefficient idioms in the queries, or at least sub-optimal.

The potential upside is parallelism from the SQL query engine, and custom indexing.

[fbrossard]

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.

[andre]

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 yet currently 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.

[greggyb]

@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.

[andre]

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.

[greggyb]

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.

[andre]

security in a direct query mode is only respected for the person who develops the model, not for the users with whom the model is shared.  The shared model will operate in the security context of the person who created it, or, effectively, will have no security.

[greggyb]

@andre, I apologize, I was mistaken. The effective user name is only passed to SSAS, as you said, and not to SQL data sources.

With the Enterprise Gateway, credentials for each data source other than SSAS can be defined by the gateway admin. You could easily use this to set up high level security (e.g. set up a data source with credentials that only have select permissions for limited views in a database), but cannot effectively enforce granular security, as you mentioned - everyone with access to the dataset will have access to the same data.

I must admit that the majority of my time is spent in SSAS, with Power BI only utilized as the consumption layer for the models. I can't think of any circumstances with our clients where we wouldn't argue against Direct Query, entirely due to reasons other than security.

[andre]

there are three things you can do:

1. create an index and see if that helps
2. bump up the performance characteristics of your Azure SQL Database
3. explore in-memory option for your database

7M rows is not a large dataset and if your tables are not very wide, I would also explore importing the entire data set into your model, my guess you will see good compression with that and it might be below the 250mb limit

[dilkushpatel]

Hi,

By In-memory option for database you mean importing in pbix or SQL Azure has some setting for this?

Actiually there are 3 tables with approx 7 mil each and then there are 5 dimension tables.

I will try importing in pbix and see how it goes.

Thanks,

Dilkush

[andre]

yes, in Azure SQL you can create in memory indexes which can potentially improve performance.