SQL VS DAX mental Set

[Marounnsader]

Hi all

ive been hired to lead a team in BI & Analytics, the current team use SQL to create all measures and use PowerBI as visualization tool, and rarely use DAX for that, saying that its "better" but im not convinced

also, the DW is on ORACLE and we use ODI for transformation, so no powerquery nor dataflows nor shared datasets

so can you please adivse if this is the best practice to use SQL rather than DAX, and whats the best practice here

[MattAllington]

This is a huge topic. Power BI (import mode) is a model based tool. You build a model (typically star schema) and write measures that work together with the model to be an extensible reporting tool. The objective is to minimise the need for new queries each time there is a new need. It is not best practice to do the work in SQL and use PBI for visualisation only. In addition, some things cannot be done in queries in SQL. Eg, calculating the % margin for any user selection of products can only be done in a measure. 

in my experience, people that take this approach are comfortable with what they know and are yet to embrace the unknown. 

I think it is fine (Even preferred) to do any transformations possible in SQL. PQ is there for when that is not possible. 

[Marounnsader]

Hi Matt

thanks alot for the reply

you got me confused, in the 1st paragraph you said exactly the steps i do in every project, or even when i do training, and the same feelings i had that they work with what they are comfortable of, and its the culture im trying to change.

But you said that its not best practice to do all the work in SQL then, in the end of your message, you said it's preferred to do it in SQL, did you mean transformation to be done in SQL and ODI, but model building and measures to be done in PBI?

appreciate your help

P.S im a fan of your website and your channel 🙂

[MattAllington · Accepted solution]

My view is you do all *necessary* transformations in SQL, and then write measures in Power BI to do the calculations. You said your team does everything in SQL and avoids measures - that is the difference between the two.  My view is you are better to shape the tables in SQL if you have pros that can do this, rather than do it in Power Query. You should generally load the lowest level of detail needed into PBI and do not preaggregate the data (something SQL pros love to do). Hope that is clearer. 

[Marounnsader]

this is crystal clear sir, seems we need to build confidence in DAX for those pros

thanks alot

[Anonymous]

Hi @Marounnsader

Build confidence in DAX... This is hard and can only be achieved if you put people through rigorous courses.

This is because DAX is difficult to master and one has to be very pedantic, indeed, and know the underlying theory of the language to use it with confidence. It took me about 1-2 years to get to speed and be on par with the best in the field. People think DAX is easy but it's not really.

As Alberto Ferrari, the DAX Maestro, has put it:

DAX is simple but it's not easy.

This summarizes everything in one sentence. In the hands of a guru DAX can calculate EVERYTHING and will do it correctly(!). In the hands of a newbie... or a person who thinks they can get away without learning the underlying theory it can lead to disasters, of which they will not even be aware (since testing is rarely, if ever, performed on huge datasets). Because the language must be taught together with the golden rules of the dimensional design. If you forget to convey this aspect of the DAX knowledge, no amount of DAX will save you from producing wrong numbers and slow calculations.

As easy as that. If you want to get people to know DAX well, the best way I've found to work is to go to www.sqlbi.com and explore the site. It's been created and is being maintained by the best in the field: Marco Russo and Alberto Ferrari.

My knowledge comes exclusively from there and their epic book: The Definitive Guide to DAX.

Best
D

[Marounnsader]

i totally agree with you, DAX has its learning curve

i started working on dax since PowerPivot came up and still learning, but what I found is that it needs some mind shift in how the measures are been written and iterated

i always follow SQLBI for updates and more DAX explanation, let me see if i can book some courses online for my team donw the road, because as you said, writing DAX in a poorly way can affect the performance a lot, we used to learn it by trial and error then optimize  

[MattAllington]

I offer instructor led training into all time zones around the world if you are interested. https://exceleratorbi.com.au/learn-power-bi-from-an-industry-expert/

if you want to see what people think about my teaching style, read my book reviews on Amazon. 
https://www.amazon.com/product-reviews/B079G45FS9/ref=acr_dp_hist_5?ie=UTF8&filterByStar=five_star&r...