Forum Discussion
SQL VS DAX mental Set
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.
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 🙂
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.
this is crystal clear sir, seems we need to build confidence in DAX for those pros
thanks alot
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
Di 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