MDX stands for Multi-Dimensional eXpressions. why MDX? Answer is very simple – Performance and Efficiency.
SQL – Structured Query Language
Mostly we run SQL queries to fetch data from Two-Dimensional database (Relational tables).
SQL have capabilities like DML (Data Manipulation Language) and DDL (Data Definition Language).
SQL deals with columns and rows.
MDX – Multi-Dimensional eXpressions.
MDX queries are executed to fetch data from One, Two, Three or more dimensions (Cube).
MDX does not contain DML and DDL.
MDX plays with rows, columns, multi-dimensions, hierarchies and levels
Analysis – MDX is a standard language designed by Microsoft Corporation to query OLAP cubes(Cube made by SQL Server, Teradata, etc).
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.
Mostly we run SQL queries to fetch data from Two-Dimensional database (Relational tables).
SQL have capabilities like DML (Data Manipulation Language) and DDL (Data Definition Language).
SQL deals with columns and rows.
MDX – Multi-Dimensional eXpressions.
MDX queries are executed to fetch data from One, Two, Three or more dimensions (Cube).
MDX does not contain DML and DDL.
MDX plays with rows, columns, multi-dimensions, hierarchies and levels
Analysis – MDX is a standard language designed by Microsoft Corporation to query OLAP cubes(Cube made by SQL Server, Teradata, etc).
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
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 arechildren. 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?
Cube in MDX = Table in SQL.
Level in MDX = Column in SQL.
Dimension in MDX = Several related columns in SQL.
Measures in MDX = Numeric data in SQL.
Tuple in MDX = Cell value (where row and column intersects) in SQL.
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.
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 arechildren. 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?
Cube in MDX = Table in SQL.
Level in MDX = Column in SQL.
Dimension in MDX = Several related columns in SQL.
Measures in MDX = Numeric data in SQL.
Tuple in MDX = Cell value (where row and column intersects) in SQL.
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.
