What is MDX?

MDX stands for Multi-Dimensional eXpression language.

There are two ways that we work with this language.

First, we can use it to create expressions. These expressions allow us to store business logic in a cube and thereby ensure that everyone who retrieves the results of those expressions from a cube gets the same answer every time rather than relying on each user to perform their own calculation, perhaps using different formulas after retrieving the raw data. We can think of the use of MDX as an expression language as analogous to using Excel functions to create formulas in a spreadsheet. When an expression in MDX is resolved, a result is returned, such as a value that might appear in a spreadsheet cell.

Another way we use MDX is as a query language. That is, we use it to retrieve data from a cube in bulk, which is much like retrieving one or more records from a SQL Server database using Transact-SQL.

I am going to walk through a comparison of Transact-SQL as compared to MDX.

MDX1

Now first of all, when we work with SQL, we either use our drop-down list to set the current database or we can use the USE command and execute this and that will of course adjust our database accordingly.

MDX doesn’t have anything like that, so there is no USE database command so we have to physically connect to my database,

T-SQL

USE AdventureWorksDW2012

— 1 Aggregation

SELECT

Sum([Order Quantity]) AS [Order Quantity]

FROM

[vw_mdx_fundamentals]

MDX

— NO ‘USE Database’ in MDX

— 1 Aggregation

SELECT

[Order Quantity] on Columns

FROM

[Sales];

From the above T-SQL  when we select this Order Quantity, we are actually applying an Aggregate function to this so that we can get a single record and then we provide an alias here as well because otherwise this would be just an expression and it has no title, so we add in Order Quantity specifically to get that value.

The same query when we write with MDX, we have our SELECT statement, we have Order Quantity, but we have something different here. The Order Quantity does not have an aggregate function associated with it. Another thing is the MDX statement has what we call a query access on columns, and that’s something that we don’t have in our SQL. And then we tell it which cube that we are selecting this data from instead of a table or a view. So if I highlight this and execute, then I’m going to get results.

T-SQL

— 2 Group By

SELECT

[Product Name]

, Sum([Order Quantity]) AS [Order Quantity]

FROM

[vw_mdx_fundamentals]

GROUP BY

[Product Name];

MDX

— 2 Group By >> Set on Rows

SELECT

[Order Quantity] on Columns

, [Product].[Product].[Product].Members on Rows

FROM

[Sales]

The above is an example of Group By statement. In T-SQL if we neglected to add in the GROUP BY, we would get an error message that said we cannot have an aggregate function in our SELECT clause without having that GROUP BY.

In MDX  we have our Order Quantity on Columns, but we have now added in a new access. We not only have Columns, but we have Rows. So on the Rows, we define this very long reference here, Product.Product.Product.Members, but that means something very specific in MDX.

MDX2

Number one, the values are formatted.

Number two, we get an extra member here, the Unknown member.

Even though it has a null value, there are no sales associated with it, it is a member, a set of members that, and also services, recognizes as part of this group.

This member’s function says look at the Product dimension, the Product hierarchy in that dimension, and then the Product level of that dimension and give all of those members, and we can see that actually in this metadata tree.

We have Product dimension, Product attribute or hierarchy, and then the Product level, and if we expand that we can see those four members.

So one of the things about MDX is the way that it processes this query.

It actually looks at Order Quantity on Columns and says this goes here up on the Columns list and then it looks at what else have I asked for and it asks for the Product members and it lays those out, and then after the fact it goes in and calculates what the values are for each one of those separately.

MDX3

I will be comparing and adding more details in another blog.

 

Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *