Live connection vs Direct query performance

[Fcaill]

Hi,

I have a SSAS cube that is slow. We need to optimize it but it requires a big effort. 

We migrated it to Power BI Premium but the performance was the same. Then I tried to connect to the dataset in Direct Query mode and surprisingly the performance was much better. It's the opposite of what I expected. Do you have any idea why? Maybe some cache? Is there any drawback of direct query vs live connection?

Thanks for your help!!

Edit to make it more clear:

I'm talking about report usage performance

A) Initially, report connected in live connection to on prem SSAS cube loads in 15 seconds

B) After migration to Power BI Premium, report connected in live connection to Power BI Premium dataset loads in 15 seconds

C) Finally, report connected in Direct Query to Power BI Premium dataset (B) loads in 5 seconds

[Anonymous]

Hi @Fcaill ,

Both connects to your on-prem data source and keeps connected so when you interact with your visuals queries are sent to on-prem and results returned back are updatd in visual. You can do further reading here to understand difference between Live connection / DirectQuery and Import.

Another thing to note in difference. Live Connection refers to connecting directly to a Model (SSAS Multidimensional or Tabular) in this regard, all your modeling work is done on those. With Direct Query - you are connecting to a database, and you have the ability to create calculated columns and measures in Power BI. So, Power BI serves as your model layer, while still keeping your data in your databases.

Using DirectQuery in Power BI - Power BI | Microsoft Learn

Power BI Deployment. Import v Direct Query v Live Connection — Clearly Solutions

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Fcaill]

Hi @Anonymous and @Jayee ,

Thanks for your replies ! 

I'll try to give a more accurate description. First, I'm talking about report usage performance.

Initially, I have a report connected to a SSAS cube (A). It takes approximately 15 seconds to load the report page.

Then I migrated the cube to a dataset in Power BI Premium (B) and connected my report to this dataset in Live Connect. Again, it takes approximately 15 seconds to load the report page.

Finally, I tried to connect my report in Direct Query to the dataset B. So I have my report connected to a dataset C that is connected in Direct Query to the dataset B. Now my report page loads in 5 seconds.

Summary:

A) report connected in live connection to on prem SSAS cube loads in 15 seconds

B) report connected in live connection to Power BI Premium dataset loads in 15 seconds

C) report connected in Direct Query to Power BI Premium dataset (B) loads in 5 seconds

I refreshed my browser cache every time. 

Hope it is more clear. Let me know if you have any idea,

Thanks!

[Jayee]

Hi @Fcaill 

Thanks for detail description and the cache will be in Dataset not in your browser which is ssas tabular model.

I strongly recommend you to have a look in report performance by using performance analyzer and Michael kovalsky's report analyzer please go through below links

Report analyzer:-

https://youtu.be/MttvudnaBD0

https://youtu.be/Z-J4n9uymQ0

Troubleshoort your entire report and data model

https://youtu.be/91D1WrNrHtY

[Jayee]

Hi @Fcaill 

You need to more clear while creating a post and description is vauge.

Not sure performance means you are taking about refresh performance or report usage performance....

Any way please got throught the below website from guy in cube they are explained clearly difference between import vs direct query vs live connection 

https://guyinacube.com/2021/01/28/power-bi-get-data-import-vs-directquery-vs-live-2021/

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!