Saturday, 3 November 2012

MDX Introduction

MDX: Well you must have heard of it. So like you need T SQL or SQL to communicate with a RDBS, same way you need MDX to communicate with Multi-Dimensional OLAP (MOLAP).

MDX is Multi-Dimensional Expression. .  It is part of the OLEDB for OLAP specification which was developed by Microsoft but is now supported by multiple vendors.

Main purpose of MDX is to create query statements for data retrieval. We can also perform some of the management functions, such as the UPDATE CUBE statement etc.

It is good if you have knowledge of SQL as that will help in learning MDX.
MDX seems to be tough, but believe me once you have an understanding of it, it will not be complex and you will enjoy writing it.

Let see how MDX statement looks like –

SELECT, FROM and WHERE clause you have seen in SQL as well, so they are self-explanatory.
 SELECT is to select , FROM is to from where do you want to select. In MDX it will a cube or a sub query. And WHERE to filter your selection that we call it SLICING.

In addition to it, here you define Axis. Well its Multi-Dimensional, isn’t it??  So need different dimensional Axis here.
Let see an example here -

 I consider you have basic knowledge of Facts and Dimensions and you have Adventure Works DB & Cube installed.

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,
[Date].[Calendar Year].&[2009] on 1
FROM [Adventure Works]

Well its very simple SELECT statement, where we have selected measure Sales Amount with respect to Calder Year 2007 of date dimension.
Result will be as follows –

So writing first statement was not that tough??
You can put different member of different dimension and can try by your Self.
In the Next blog I will go more detail about MDX and will discuss some of the function of MDX.

No comments:

Post a Comment