Forum Discussion
Live connection/Direct Query mode makes reports very slow
- views should only be used for simple transformations like column selecting and some filtering to create different data sets for different countries for example. An exception is when the view is used in a step that would be used by another transformation in DWH, which will eventually lead to a table. If the data is used by any other process outside DWH (like PowerBI or other data consumers) I would suggest to always use a table.
- I would not suggest using many transformations in the copy data part. Try to split the data extraction from the data transformation. The transformation language in a copy data is limited and also obscure. You may google on a bronze silver gold structure.
- stored procedure is a good practice. Another tool I like to use is dbt (data build tool), but that is a bit more complex and not natively embedded in Fabric, but very good for transformations. You can run dbt in Fabric without additional costs, but it takes a bit more time to setup.
Your semantic model is falling back to Direct Query because the model is based on Views from your DWH.
"When a Direct Lake semantic model table connects to a view in the SQL analytics endpoint, queries that involve that model table will always fall back to DirectQuery mode. Query performance might be slower."
Direct Lake overview - Microsoft Fabric | Microsoft Learn
If you wish to move away from Import Mode, you will have to change your engineering practices to so that tables are created that match the requirements of your reports.
Thank you.
Would it help with the report performance if I use live connection to my DWH but the measures for the reports, I'd create directly in the DWH and not in the report (maybe it's a bad practice I have from the times I used import mode from the database for my reports). It is hard to imagine better engineering practises for this DWH and reports when there are different measures for different reports and one DWH as a data source.
I understand that live connection works slower because of the measures. But does it affect measure and report performance whether I create measure in DWH and use it on the report or create measure in PBI desktop and then use it for the report?
