Leveraging directquery for performance. Does this make sense?

[Gherty0011]

Does this make sense to complete all my ETL with M in Excel, load my data into a table with all completed transformations, have Access connect to that table, then use PBI/Excel to connect (9 times out of 10 PBI) to leverage directquery performance and speed?

Effectively using Access as a warehouse and PBI/Excel to build a data mart for my specific reports needed? 

[v-rzhou-msft · Accepted solution]

Hi @Gherty0011 ,

As far as I know, Power BI Desktop will highly compressed the data which is loaded. Then there will be 1GB limitation to your dataset, when you publish your report to Power BI Service. So if your data model is too large, import mode report may show error due to the storage limitation.

I think you may choose your connection mode based on the size of your data model. Then you may try Direct Query or live connection. Direct Query will only load some metadata into Power BI, this will reduce the size of your dataset. Then you can publish your report successully to Power BI Service.

I think import connection mode should have the best performance in Power BI, due to you will load all data into Power BI instead of sending query to data source like Direct Query.

Best Regards,
Rico Zhou

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

[Greg_Deckler]

@Gherty0011 Generally speaking, having an import mode dataset in Power BI will provide faster performance than DirectQuery.