Introduction to MDX query basic terms – SQL vs MDX

3
2796

If you are on this page for more than 2 seconds, than I can bet you want to deal with MDX. Be rest assured, you are at the right place to begin your MDX journey. I am learning MDX these days so excited to share my knowledge on MDX. The deal from my side is to share this article with others if it helped you. I will suggest you all to ask others or google full forms which come across your way.

MDX stands for Multi-Dimensional eXpressions. The basic purpose of MDX queries is to fetch the required data from database when executed. MDX acts as a servant to you. “You command – MDX obeys”. Next question you must be thinking that I already have SQL servant to obey my database commands, than why MDX? Answer is very simple – Performance and Efficiency. As some servants are superb in Car wash, some perform cooking duties faster than others. Similarly, both MDX and SQL are expert in the areas they are designed for.

SQL vs MDX

Let’s have a football match between SQL vs MDX. Which team you will place your bet on?

Difference between MDX and SQL

SQL –

  1. Structured Query Language.
  2. Mostly we run SQL queries to fetch data from Two-Dimensional database (Relational tables).
  3. SQL have capabilities like DML (Data Manipulation Language) and DDL (Data Definition Language).
  4. SQL deals with columns and rows.

MDX –

  1. Multi-Dimensional eXpressions.
  2. MDX queries are executed to fetch data from One, Two, Three or more dimensions (Cube).
  3. MDX does not contain DML and DDL.
  4. MDX plays with rows, columns, multi-dimensions, hierarchies and levels

Result of football match between SQL and MDX = MDX beat SQL.

Analysis – MDX is a standard language designed by Microsoft Corporation to query OLAP cubes (Cube made by SQL Server, Teradata, etc). I mentioned above that we cannot say MDX is better or SQL or vice-versa. Both religiously work awesome for which they are designed. If the requirement is Multi-Dimensional (Cube), MDX cruise away in this. This is because Cube made from SQL Server Analysis Server or any other database, contains pre aggregated data stored. No need to perform operations like Sum, Count, Min, Max, etc. This is the main reason for the better efficiency and performance of MDX.

Example – Cubes are very intelligent. In Cube, if you want to know the sales for July 25, 2014. It will automatically know the parent for this date is 2014 and aggregated sales data will also be stored at parent level. This gives speed and celebrity treatment to the MDX.

MDX Terminologies

We will now learn terminologies with which we have to deal while writing MDX queries.

Cube – OLAP cube created in various technologies like Oracle, SSAS, Teradata, etc is the basic Multi-dimensional data storage unit. We can perform analysis functions on the data stored in cube.

Dimensions – These contains textual description about the Line of Business (LOB). Dimensions provide developer to perform primary functions like Grouping, Filtering, Labelling, etc. Clients can view their business by putting on various dimensions like Product categories, Date, Location dimensions, etc.

Measures – These are the numeric data stored in Fact tables. They are used to analyze performance of the business. [Measures] can be like Sales amount, Profit, Tax, etc.

Measure Groups – In simple words, it is collection of related Measures.

Level – In simple way, attributes of dimensions are called as [Level]. Example – Consider Date dimension to learn about levels in MDX. Then, Levels for Date dimension will be as Calendar year, Semester of the year, Quarter of the year and so on. This is also known as Attribute Hierarchy.

.Members and .Children – I bet you will love this keyword once you started working on MDX window. In MDX query, each level contains one or more members. Example – Consider Quarter of the year level then, it will contain members like Q1, Q2, Q3 and Q4.

Only difference between Members and children in MDX is that when you will use members with hierarchy than in result aggregated value will also come in the form of [All]. This aggregation i.e. [All] will not come if you will write children after hierarchy.

Hierarchy (Parent-Child) – In hierarchy, top node is parent and other nodes below the parent are children. You can have user defined hierarchies and default level 0 stands for [All] members. Example – Consider Geography dimension to learn about Hierarchy in MDX. Here, Parent node can be Continent and 1st child will be Country, 2nd child will be State, 3rd child can be City and so on.

[Dimension].[Hierarchy].Levels(0).Members/Children

Tuple – It is that value where column cell and row cell intersects. In simple words, when we say C5 in excel then C is column value and 5 is row value and this gives us the result. Now, I will explain this technically. Tuple in MDX is used to identify specific location in the cube with the use of dimension members. Tuples are enclosed within round brackets (). Brackets are optional, if you are using single tuple.

  • Note – Empty tuple in MDX query is not allowed. We cannot repeat same hierarchy within Tuple.

Set – Combination of zero, one or more tuple makes definition for Set. Set is enclosed within curly braces {}.

  • Note – We can have empty set in MDX. We can repeat same hierarchies within Set.

How MDX terms are related with SQL terms?

  1. Cube in MDX = Table in SQL.
  2. Level in MDX = Column in SQL.
  3. Dimension in MDX = Several related columns in SQL.
  4. Measures in MDX = Numeric data in SQL.
  5. Tuple in MDX = Cell value (where row and column intersects) in SQL.
  6. Set in MDX = Range in SQL.

Technically speaking MDX is not “faster” than T-SQL, or vice versa. Both are just languages designed for different needs. In our future posts, i will show you the syntax and how MDX query works. I hope you liked this post. Do you remember my deal mentioned above? Share this article with other friends too. Your feedback and queries are welcome as comments below.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.9/10 (22 votes cast)
VN:F [1.9.22_1171]
Rating: +17 (from 17 votes)
Introduction to MDX query basic terms - SQL vs MDX, 9.9 out of 10 based on 22 ratings
  • Vijay

    Can we expect real time scenarios in SSIS, SSRS & SSAS.
    more in MDX & SSAS Questions.

  • Gangi Reddy

    Hi Sharma,

    need more on MDX(like time functions ,sets ,touples).could you please share.

    Thanks,

  • Kiran

    I went through the article I got a basic idea regarding Cubes,Dimensions,Measures which you have described very well.
    I hope if you can write an article SSRS using MDX
    Thanks Chander Sharma