DAX vs SQL Modelling
DAX vs SQL Modelling – When to use SQL over DAX in Power BI
Advancing into the Power BI experience, one may find themselves moving towards importing more data from a SQL Server, either via the import tool or through a direct query, Of course this is not an article about moving away from other import methods such as from Excel, CSV’s and other data sources. This is rather a view on an efficient querying language to model data from a SQL Server data source.
The fact that you are reading this article already means you understand T-SQL and DAX. The question is, at what point is it preferable to use one over the other.
The short answer
If you are able to model your data in SQL the same way you would do it in DAX, rather use SQL, load the script into a view and call the SQL view in your Power BI datasource.
Why?
Modelling your data prior to it being imported into Power BI, will result in your report being less resourceful. This is due to the calculations and syntax’s being done in the query within the view when its called into the Power BI report and thus, Power BI no longer has to use its internal resources to remodel that data.
Whereas when using DAX to model your data, your Power BI report will first import the SQL data and then only model the data internally resulting in a slow report (if you modelling masive amounts of data).
Think about it
Yes, the above scenario will work well when all your data is coming from a single SQL datasource. But what about when you have multiple datasources within your report?
Do both! DAX is an amazing query language that gives you tremendous amounts of potential to design your Power BI report. At the same time though, modelling as much as you can within your SQL datasource will assist in your reports efficiency.
When to use SQL over DAX?
SQL is a structured query language whereas DAX is more of a language that consists of formulas and measures to dive deeper into the data.
When it comes to certain calculations and simplifying of data, SQL is preferable as your data will be structured properly before it is queried.
There are other benefits to modelling as much as possible in SQL, but we will touch on this at the end.
[code language=”sql”]
create view vw_Sales_Demo AS
select
P.TxDate AS Date
, DATENAME(month, P.TxDate) AS Month
, CAST(YEAR(P.TxDate) AS VARCHAR(4)) AS Year
, CASE WHEN datename(month, P.txdate) = ‘December’ THEN ‘1’
WHEN datename(month, P.txdate) = ‘January’ THEN ‘2’
WHEN datename(month, P.txdate) = ‘February’ THEN ‘3’
WHEN datename(month, P.txdate) = ‘March’ THEN ‘4’
WHEN datename(month, P.txdate) = ‘April’ THEN ‘5’
WHEN datename(month, P.txdate) = ‘May’ THEN ‘6’
WHEN datename(month, P.txdate) = ‘June’ THEN ‘7’
WHEN datename(month, P.txdate) = ‘July’ THEN ‘8’
WHEN datename(month, P.txdate) = ‘August’ THEN ‘9’
WHEN datename(month, P.txdate) = ‘September’ THEN ’10’
WHEN datename(month, P.txdate) = ‘October’ THEN ’11’
WHEN datename(month, P.txdate) = ‘November’ THEN ’12’ END AS MonthNo
, SUM(P.ActualQuantityInvoiced) AS Quantity
, SUM(P.Cost * P.ActualQuantityInvoiced) AS LineCost
, SUM(S.LastGRVCost * P.ActualQuantityInvoiced) AS LastGRVCost
, SUM(P.ActualValue) AS LineSales
, SUM(P.ActualValue) – SUM(P.Cost * P.ActualQuantityInvoiced) AS Profit
, SUM(P.ActualValue) – SUM(S.LastGRVCost * P.ActualQuantityInvoiced) AS GRVProfit
FROM Sales.dbo._bvSTTransactionsFull AS P
[/code]
When to use DAX over SQL?
When you have reached a point where you need some internal measures or parameters, its time to use DAX.
For methods on visuals, parameters or measures linking multiple datasrouces, DAX is the route to take. It will allow you to dive deeper into your data analytics and business intelligence, which is not a primary feature of SQL.
[code language=”sql”]
ObjectiveText = IF(
ISFILTERED(‘Key Skills'[Skill])
, SELECTEDVALUE(‘Key Skills'[Objective])
,”Select a Skill”)
[/code]
How to plan?
Every environment and project will differ. Before throwing out out or specifically choosing a preferred method, try identify some answers to the below questions. You may be surprised that you’ll be using both SQL and DAX 🙂
- What are your datasources?
- What is the purpose of your report?
- How big is your report?
- What DAX modelling will you need?
Other benefits to modelling in SQL
If you are a Data Analyst using software like Power BI, Tableau, SSRS etc, you may need to access the same datasrouce on multiple platforms. By modelling your data in SQL and storing the model as a dataset, view, table or any other means of data storage, you have the potential to utilise the same modeled data over multiple platforms without recreating the entire process.
Thinking long term. Is it no more ideal to make one change in the dataset if needed and it effects all connected services, rather than maintaining each services dataset on its own?
Sharing the dataset to others that require similar information makes it easier to collaborate if the datasource is modeled first.
In conclusion
There are many pros and cos to each scenario and we would love to here your thoughts or experiences on the matter. You can share these in the comments section below.
Until next time 🙂
