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.
Solution SageHi, what logic do you have in your views? Would it be possible to convert those views into tables and see if that improves the performance?
Our main logic for the DWH was to take raw data from the database -> calculate/join/change raw data -> transform it to views as a clean, informative DWH for the company.
- FabianSchut
If you have all those transformations in the views (calculate/join/change raw data), then that would absolutely impact the performance. Those transformations are always executed everytime you use it in PowerBI. Please run those transformations once and store it in a table. You can achieve that with a stored procedure for example.
FabianSchut,
thank you very much for this point, yesterday I searched a lot about it and thought about this 'slow perfomance because of using views' too, just wanted to get a comment like this from an expert.
I tried connecting raw tables which are copied from the database first -> go to lakehouse -> go to dwh and from those raw tables then are made modified views. This is how external company which created one of our dwh made it and "taught" me this as the best practice which is clearly not.
When I connected raw dwh tables to the PBI desktop it works as it should be...without lagging. Even created measures in PBI on top of these raw tables work as expected.
I will definitely contact external vendor about their "best solution" for our company, but maybe you will answer a couple of my following questions:
- Views usage: am I right that these views are only a good practice for other analysts to use for an easier data execution (without writing complex queries) with one code and without modification but only to use in DWH enviroment without connecting those views to any source as they are really slow?
- What could be the solution for my case - excluding using those views as a data source in PBI? Recreate copy data pipeline step where I extract data from the database by modifying code to make simple transformations in that 'copy data' part and using those modified raw tables as a data source in PBI with adding measures in the semantic model rather than calculating measures in the PBI?
- Are those stored procedures is a good practice? I think I could then rewrite everything using stored procedures instead of views and handling these raw tables modifications but maybe my first given option ('copy data' pipeline modification) is a better way?
Trying to become and understand data engineering in a really fast and hurtful way as you see.. 😄 😄 😄
Thank you for your help!
- FabianSchut
- 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.