DirectQuery Vs. Live Connection for PBI Datasets

[rajulshah]

Hello everyone,

What should be preferred when PBI Datasets are used? Live Connection or DirectQuery?
And how DirectQuery will be better than Live Connection for PBI Datasets? One reason I can think of is we can create new relationships and create calculated columns. Please let me know if there are any other reasons.

Will there be any discrepancies in the performance of the two? Please guide.
Discussion on these will be great.

Thanks.

[ibarrau · Accepted solution]

Hi. It looks like a question to give an opinion so here is mine. In my opinion Live connections is the best option. That connection is faster than the other one and will help a company maintain data integrity. The direct query option I can only see it worthy for a very special situation that need a composite model to incorporate one table for an specific requirement. Otherwise if it's a needed table for more reports it should be added in the first dataset to keep all connections lives and avoid data silos that will be generate with people adding tables in their direct queries.

Regards,

[v-luwang-msft · Accepted solution]

Hi   @rajulshah 

DirectQuery

Advantages

• Large Scale data sources supported. No size limitation.
• Pre-Built models in some data sources can be used instantly

Disadvantages

• Very Limited Power Query functionality
• DAX very limited
• Cannot combine data from multiple sources
• Slower Connection type: Performance Tuning in the data source is MUST DO

Live Connection

Advantages

• Large Scale data sources supported. No size limitation as far as SSAS Supports.
• Many organizations already have SSAS models built. So they can use it as a Live Connection without the need to replicate that into Power BI.
• Report Level Measures
• MDX or DAX analytical engines in the data source of SSAS can be great asset for modeling compared to DirectQuery

Disadvantages

• No Power Query
• Cannot combine data from multiple sources
• Slower Connection type: Performance Tuning in the data source is MUST DO

To learn more details,you could read the following article!

DirectQuery, Live Connection or Import Data? Tough Decision!

Wish it is helpful for you!

Best Regards

Lucien

[ibarrau · Accepted solution]

Hi. It looks like a question to give an opinion so here is mine. In my opinion Live connections is the best option. That connection is faster than the other one and will help a company maintain data integrity. The direct query option I can only see it worthy for a very special situation that need a composite model to incorporate one table for an specific requirement. Otherwise if it's a needed table for more reports it should be added in the first dataset to keep all connections lives and avoid data silos that will be generate with people adding tables in their direct queries.

Regards,