Difference Between SQL and MDX
The MDX (Multi Dimensional Expressions) syntax appears very similar to the SQL syntax and also functions somewhat similar to the way SQL performs. But there are many differences, basically conceptual differences, between SQL and MDX.
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
The basic difference between MDX and SQL is the ability of MDX to reference multiple dimensions.
- Data Representation:
SQL refers to only two dimensions, rows and columns, when processing queries since SQL was designed to handle tabular data in two dimensional formats. Here rows and columns have some meaning in SQL syntax. However, in MDX Queries, it can refer to multiple dimensions distributed along different axis. Here rows and columns are different to that of SQL. They are depicted as Horizontal (rows) and vertical (column) axis.
- Difference in Select and Where clause:
In SQL, the Select clause is used to depict the column information i.e. it defines the column layout for a query, while the where clause is used to define the row layout. In MDX, the select clause is used to define several axis dimensions, while the where clause is used to restrict the data to a specific dimension or a member.
- Visualization:
In SQL, the visualization of data is in a two dimensional format consisting of rows and columns. It is more like a tabular form. In MDX, the visualization of data is multi dimensional on horizontal and vertical axis. It is more like in a cross tabular form.
- JOINS:
MDX supports all the joins that SQL supports. The join condition between the fact and the dimension will be specified while designing the cube. Following are the examples in which a join is specified,
- Inner Join:
In SQL,
SELECT table1.<column_name>,table2.<column_name>FROM table1 inner join table2
ON table1.column1 = table2.column2;
In MDX,
NON EMPTY {[Measures].[MeasureName]} ON COLUMNS
FROM [CubeName];
- Left Outer Join:
In SQL,
FROM table1 left join table2
ON table1.column1 = table2.column2;
In MDX,
NON EMPTY {[Measures].[MeasureName]} ON COLUMNS
FROM [CubeName];
- Right Outer Join:
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
In SQL,
FROM table1 right join table2
ON table1.column1 = table2.column2;
In MDX,
{[Measures].[MeasureName]} ON COLUMNS
FROM [CubeName];
- Full Outer Join:
In SQL,
FROM table1 right join table2
ON table1.column1 = table2.column2;
In MDX,
{[Measures].[MeasureName]} ON COLUMNS
FROM [CubeName];
- FUNCTIONS:
MDX supports a lot of functions which makes its execution much simpler. In SQL, generating the desired result makes query very complex. For example, generating the Sales value for current year and previous year along with the YTD value is very easy in MDX since there are some predefined functions like Parallel period and YTD which you need to define in the WITH clause of the MDX statement. In SQL, it becomes complicated since you need to create aliases for each value (Current Year, Previous Year and YTD) and then you have to join all the aliases to generate the desired result. It makes query very complex.
- Example in MDX:
SELECT CROSSJOIN({[Time].[Years].Members},{[Measures].[Sales],[Measures].[Last Period],[Measures].[Growth]}) ON COLUMNS FROM [SteelWheelsSales];
CONCLUSION:
MDX and SQL performs the same execution but the results are portrayed in different ways. SQL shows data in a tabular two dimensional form whereas MDX shows data in a multidimensional form.
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
Regards,
Nitish Kumar Mishra
Comparison of Queries Written in T-SQL and SQL Server MDX databases Difference Between SQL and MDX Good example of MDX vs SQL for analytical queries How MDX Crushes SQL in Complex Queries MDX mdx query vs sql Mondrian OLAP Analysis SQL sql to mdx what is the difference b/w mdx and sql