Difference DAX and MDX
I am often asked about the main differences between DAX and MDX or in general about the difference between tabular and multidimensional models.
From the point of view of expression or query language, one of the most important differences lies in the internal basis of both approaches.
In a cube, to address a cell in space, we have the concept of a tuple. The axis in the tuple sets the coordinates. If we have a single tuple, then the result is the content of the corresponding cell in the cube. Since the attributes of the cube have the element All, which serves as the default value (in most cases), if the attribute was not included in the tuple, then we perform the aggregation as if it were there. For example, the following tuple returns (aggregated) sales for 2013
')
Other attributes (Product, for example) are at the element level by default. As you can see, in this case, there is no need to specify an aggregate function (despite the fact that MDX has functions for aggregating values ​​across sets), since the cube “knows” how to aggregate sales. The following sketch illustrates this way of addressing a value in a cube:
For the tabular model, the filters in the pivot table work like normal filtering of the base tables. Even if one value is selected, several elements may be included in the table filter. For example, if you apply a filter in 2013, then the base date table will be filtered on all 365 days of this year. The result of the sample will be all the intersections of the other tables with the filtered measure. We need an aggregate function to calculate the result, since This operation will potentially return multiple rows of data. This is shown in the following illustration:
If you are more familiar with SQL than with MDX, then the concept of filtering and aggregation in DAX will be more understandable. In SQL, as in DAX, we usually restrict table rows (using the Where clause in SQL or the FILTER function in DAX). Next, we perform grouping (using GROUP BY in SQL or the SUMMARIZE function in DAX), and at the end we calculate the aggregates using the appropriate aggregation function (such as SUM).
However, many tasks requiring such operations in SQL or DAX can only be solved in MDX by addressing cells. Let me give you an example that I often use during my lectures on MDX: I want to create a calculated measure that shows sales on weekends. If you are a hardcore SQL box, then the MDX solution might look something like this:
This approach seems natural. Using the child function, we create a set of all dates for the selected element (for example: month, quarter, year). Next, we filter this set using the Filter function so that only Saturday and Sunday remain in the set. Finally, we aggregate sales for this set.
In fact, this solution is very similar to SQL or DAX. For example, in DAX we would perform this calculation in much the same way:
However, in spite of the fact that this DAX code is very similar to the MDX code, which we have examined a little higher, the variant presented by MDX is almost the most difficult of all possible solutions. Since “working day” is an attribute of a cube, we can simply refer to sales on weekends, but with the help of a tuple (well, well, the sum of two tuples):
So, when writing a DAX query, we rather think
That in MDX, in turn, we rather think
From the point of view of expression or query language, one of the most important differences lies in the internal basis of both approaches.
In a cube, to address a cell in space, we have the concept of a tuple. The axis in the tuple sets the coordinates. If we have a single tuple, then the result is the content of the corresponding cell in the cube. Since the attributes of the cube have the element All, which serves as the default value (in most cases), if the attribute was not included in the tuple, then we perform the aggregation as if it were there. For example, the following tuple returns (aggregated) sales for 2013
')
(Date.Calendar.[Calendar Year].&[2013], Measures.[Internet Sales Amount]) Other attributes (Product, for example) are at the element level by default. As you can see, in this case, there is no need to specify an aggregate function (despite the fact that MDX has functions for aggregating values ​​across sets), since the cube “knows” how to aggregate sales. The following sketch illustrates this way of addressing a value in a cube:
For the tabular model, the filters in the pivot table work like normal filtering of the base tables. Even if one value is selected, several elements may be included in the table filter. For example, if you apply a filter in 2013, then the base date table will be filtered on all 365 days of this year. The result of the sample will be all the intersections of the other tables with the filtered measure. We need an aggregate function to calculate the result, since This operation will potentially return multiple rows of data. This is shown in the following illustration:
If you are more familiar with SQL than with MDX, then the concept of filtering and aggregation in DAX will be more understandable. In SQL, as in DAX, we usually restrict table rows (using the Where clause in SQL or the FILTER function in DAX). Next, we perform grouping (using GROUP BY in SQL or the SUMMARIZE function in DAX), and at the end we calculate the aggregates using the appropriate aggregation function (such as SUM).
However, many tasks requiring such operations in SQL or DAX can only be solved in MDX by addressing cells. Let me give you an example that I often use during my lectures on MDX: I want to create a calculated measure that shows sales on weekends. If you are a hardcore SQL box, then the MDX solution might look something like this:
Aggregate( filter( descendants( [Date].[Calendar].currentmember, [Date].[Calendar].[Date] ) , [Date].[Day of Week].currentmember IS [Date].[Day of Week].[Sunday] or [Date].[Day of Week].currentmember IS [Date].[Day of Week].[Saturday] ) ,[Measures].[Internet Sales Amount] ) This approach seems natural. Using the child function, we create a set of all dates for the selected element (for example: month, quarter, year). Next, we filter this set using the Filter function so that only Saturday and Sunday remain in the set. Finally, we aggregate sales for this set.
In fact, this solution is very similar to SQL or DAX. For example, in DAX we would perform this calculation in much the same way:
evaluate( summarize( filter( ‚Internet Sales' , related(‚Date'[Calendar Year])=2007 ) , ‚Date'[Month] , "Umsatz" , Sum(‚Internet Sales'[Sales Amount]) , "UmsatzWE" , Calculate( Sum(‚Internet Sales'[Sales Amount]) , Or( ‚Date'[Day Name Of Week]="Sunday" , ‚Date'[Day Name Of Week]="Saturday" ) ) ) ) However, in spite of the fact that this DAX code is very similar to the MDX code, which we have examined a little higher, the variant presented by MDX is almost the most difficult of all possible solutions. Since “working day” is an attribute of a cube, we can simply refer to sales on weekends, but with the help of a tuple (well, well, the sum of two tuples):
([Measures].[Internet Sales Amount], [Date].[Day Name].[Sunday]) + ([Measures].[Internet Sales Amount], [Date].[Day Name].[Saturday]) So, when writing a DAX query, we rather think
- How to filter base tables
- What aggregation function to use
That in MDX, in turn, we rather think
- Which axis should I refer to to extract the desired value from the cube
Source: https://habr.com/ru/post/341478/
All Articles