I'm wondering what is the best practice to model data with the use of dataflows.
My data source is Power BI dataflows created by the company, they contain a big amount of data - the fact table is huge. Also, dimension tables are quite big. To transform and filter data I used dataflows that I made.
For every report, I just need a part of the data - the problem is that the attributes that I need to filter by are mostly in dimension tables so then I need to filter using merges.
Due to how the connections between tables are constructed to filter by attributes present in Dimension 1 I need to go through Dimension 2 and then I can filter the Fact. This kind of transformations make the performance horrible, dataflows can take 2 hours to transform all the data and consume a lot of CPU. These kind of issues are not present when using SQL sources - usually joins are not that costly, transformations with Power Query are much less efficient.
I also have a question about dimensions - should I load them as a whole or filter to rows only relevant to my filtered fact table?
I have dataflows where there are 10+ merges. For now I have separate dataflows for staging and then usually one layer for transformations. Will making it more layered solve my problems? I read that incremental refresh when dataflow is a source is not recommended - why? Won't it improve performance of my dataflows?
