DAX vs SQL – When to use DAX over SQL?
Many seasoned BI Reporting professionals would have used T-SQL extensively, either in the initial stages of their career as a database developer writing numerous lines of a stored procedure or as part of the BI report authoring role to generate historical reports using SSRS or Crystal reports. When they gradually move to use Power BI to create reports and dashboards, they get introduced to Power Query and DAX. This is exactly the stage where they get into a tough situation of deciding when they need to use T-SQL and when to use DAX or Power Query.
It is a familiar situation that any Power BI expert now would vouch for as Power BI enables you to use expressions right from data source level to upmost level.
- Data source (SQL view and/or custom SQL)
- Power Query
- DAX calculated columns
- DAX measures (the highest level)
The best way to tackle DAX vs SQL confusion is to get better quipped in all the above capabilities. This way, it will be easier to use the best options based on the current task’s requirements.
The above approach is because of the varied nature of data transformation; it becomes harder to generalize the answer for the DAX vs SQL questions.
When SQL is better than DAX
SQL is a structured query language, whereas DAX is a formula language used for data analysis purposes. When our data is stored in some structured database systems like SQL server management studio, MySQL, or others, we have to use SQL to fetch the stored data. We can’t directly compare SQL in parallel with DAX because it all depends on what we are trying to achieve. Sometimes, we need to prepare data that will use later on for Power BI desktop to build interactive dashboards or to built Tabular data models. For the data modeling, we need to feed data to these models, and that’s why we have to use SQL to prepare the data for further transformations.
In data analysis and business intelligence reporting, if we are using SSRS – SQL server reporting services, we have to use SQL to fetch the data and then display using SSRS by applying business logic.
When DAX is better than SQL
DAX is a newer language than SQL, and as we know that there is no direct comparison. In reporting, we cannot get realtime interactivity when we use SQL with SSRS, but we can get realtime interactivity when we use SQL with Power BI desktop. Different visuals in Power BI can interact with each other in realtime. We can solve complex business issues that need a lot of code & complexity in SQL compared to DAX.
DAX is not a language designed to fetch the data like SQL rather than used for data analysis purposes. Preliminary DAX is used in Power BI DAX, Power Pivot, and SQL server analysis services Tabular mode. In the beginning, learning DAX is not that easy; it needs a lot of patience to learn underlying concepts. Power BI DAX entirely based on row & columns. It would help if you had a solid understanding of row context and filter context to write DAX formulas to solve complex business issues.
Power BI DAX in practice
It is always a better and recommended approach to transform the data as close to the data source itself. For example, your data source is a relational database; then, it’s better to go with T-SQL. Being a flagship product from Microsoft, SQL server has gone through many improvements in the past three decades, with performance improvements being given top priority. Hence, it is only better to leverage the T-SQL skill that you have learned and taken advantage of SQL server’s performance offers high volume data crunching.
If your data sources are flat (.csv) files or Excel documents, there should be any confusion, and Power Query can do the job for you in shaping and transforming data. The decision to choose DAX or Power Query can be tricky!
You can introduce a custom column either as DAX Calculate Columns or using Power Query, only if the performance is optimal. When you do this, the data model will treat and compress custom columns, just like regular columns.
It is now clear that you should look at calculated columns using DAX only when Power Query lacks features like ranking. Still, your requirement mandates using rankings, and when the transformations that are done using Power Query consume longer refresh times when a lookup happens between tables.
What about DAX Measures? Being unique in nature, these can only be implemented in DAX as opposed to custom columns, which can practically be implemented in any of the three layers. An excellent example of putting things in perspective would be to use an expression to reflect filters selected by end-users. This can only be a DAX Measure as they allow filter values to be evaluated and accessed at run time.
