Which is the best place to perform calculations?

[ZLJ]

Hi

Can someone explain to me the best place to perform calculations / transformations?

i.e. If I want to concat 2 columns should I

1 - do it in the original SQL query

2 - do it in the Edit Query applied Steps

3 - do it in the Report Data view

I'm used to writing SQL, so feel more comfortable doing most of the calculations in the SQL Query, and just using the Report data view calculations for merging data from multiple sources.  Just not sure if there is an advantage to using the Edit Query Steps instead, and what benefit it has over leaving the calculations till the Report Data view stage.

Thanks

[ZLJ]

Thanks for the feedback everyone.  (Sorry for the slow reply, i've been on holiday / offline).  I think i understand a bit more about the difference between them now.

So sounds like there is no right answer, and i should just go for what works for the situation. 

[Eric_Zhang · Accepted solution]

@ZLJ

I'd partially agree with @Greg_Deckler that the 3 approaches affect the load distribution.

Regarding "Report Data view"， from what I observed, when using SQL Server, in Import mode, the behavior is different from in the mode DirectQuery. In DirectQuery mode a measure would query the database directly (as below snapshot) while in Import mode the PBI desktop itself handles the measure.


Regarding "Edit Query", powerbi desktop just re-forms the query(regardless of the mode) and the load is still mainly on SQL Server. A merge queries option in Edit Query sent the re-formed query to SQL Server as I observed.


So in one word, the DAX would distribute load according to Import or DirectQuery mode. "Edit Query" always put load on SQL Server.

That's what I observed.

[ryans]

Just a heads up,

Edit Query won't always put the all of the load on the server. It depends on the steps applied and whether they can be folded back. If they cannot all be folded back then the query engine (M) will handle the rest. So there is a bit of trial and error/art to desiging optimal queries if you are trying to have the query folded back to the server.

[Greg_Deckler]

I think this really boils down to where you are putting the load. In a SQL query, you are putting the load on the SQL Server, with doing it in the query, you are putting the load on the Desktop and the gateway and in the report data view, that would be the Desktop and the Service. Is there an advantage one way or the other? Eh, perhaps. You could make an argument that if you are doing a bunch of heavy calculations that it is better to offload that from your SQL Server to basically cloud-based resources, once you upload the Desktop file to the Service. There are also some really powerful functions that you can leverage in "M" (query) and DAX like a lot of the time intelligence stuff. I tend to follow the mantra of using what you are comfortable with as well as trying to do as much in one place versus having calculations spread out between SQL, "M" and DAX.