SQL processing vs Query Folding

[Camill88]

Hi, could you please share your experience on this topic? Which one is better performance-wise in real world projects?

What I mean is: do we have to make all transormations on server side using SQL and then load transformed tables (cleaned, grouped, calculated) to Power BI or rather import tables to PQ and transform there?

I know Query Folding but I wonder if 'query flow' isn't something what is less eficient than writing SQL on server on import ready to go tables. In Query folding there's an SQL generated undernetah, then it's send to DB server where query is executed, next the result of that query gets back to Power BI. Here are several steps, that's why I write about 'query flow' (besides I know that sometimes QF can't be generated, due to complex transformations etc.)

In my opinion it would be better to always do all transformations on DB server and then import ready to go tables into Power BI, especially for large datasets which is most common scenario in real projects I guess. Don't touch Power Query then. I think it would be more efficient. However, it's my point of view.

What are your thoughts on this and what approach is usually applied in real PBI projects?

[Anonymous · Accepted solution]

Hi @Camill88 ,

As you and lbendlin  said, it is better to do all the conversions on the database side, which reduces the load in power bi. In real projects, a mixed approach is usually used. Initial heavy transformations (cleaning, grouping, calculations) are first performed on the server side using SQL to ensure that the data is in a manageable state before it reaches Power BI. Final adjustments and report-specific transformations are then performed using Power Query, which allows for flexibility and speed to make changes without having to go back to the database.

Best regards,
Albert He

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

[lbendlin]

Yes, in general the SQL code generated by query folding is very verbose,  and will nearly always be inferior to carefully crafted SQL code (including the knowledge about indexes and statistics).