If you are working with BI (Business Intelligence) and you have cube then as a developer you have tasks to produce data set for
the business from the cube or cubes. Then your preferable way of populating
data set will be using MDX query.
MDX is stands for
Multidimensional Expressions. MDX is not specific to a database rather it's
standard expressions to make query from multi-dimensional databases and
solutions e.g. MDX uses by MS SQL, Oracle, SAP, Teradata and many more
vendors.
When we make SQL query
it's for two dimension data e.g. rows and columns and that's how SQL is
designed. In contrast, MDX can process one, two, three or more dimensions in
the query.
If you have experience
working with SQL, you will try to compare MDX expression with SQL syntax. While
I was reading through different blogs, I found that some suggested 'Never
compare with SQL Syntax that will only hurt you'. On the other hand, some wrote
blog explaining how SQL syntax can be converted into MDX expression. So, right
now I can't decide what to follow??!!!
So without comparing two
things, I will only explain about basic of MDX expressions.
MDX expression has three
different parts:
SELECT
FROM
WHERE
Part 1: SELECT
You will put both measures (fact) and dimension table's
column name at the SELECT part. However, when you put columns from fact
table then you need to add 'ON COLUMN' at the end of the column selection
e.g.
SELECT
{
[Measures].[Internet Order Quantity],
[Measures].[Internet Sales Amount]
}
ON COLUMN
And when you SELECT from
dimension then you need to add 'ON ROWS'
[Date].[Calendar
Year].MEMBERS ON ROWS
Part 2: FROM
As you know in SQL you
generally use table or view to select FROM, however, in this case it's a cube
name e.g. FROM [Adventure Works] and
a cube can hold numbers of fact and dimension tables.
So, complete syntax
will look like below where SELECT and FROM part is included:
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
[Date].[Calendar
Year].MEMBERS ON ROWS
FROM
[Adventure Works]
Part 3: WHERE (Optional)
The ‘WHERE’ part is not
mandatory in MDX expression. This part is called slicer axis, it's similar as
'Where' clause in SQL which filters the data. And the syntax looks like:
WHERE ([Customer].[Customer
Geography].[Country].&[Denmark])
You can filter by using
completely different dimension than the one used in select statement. As like
above we are filtering the data based on Customer dimension and only interested
about Denmark customer to produce the result set. Let’s make full MDX
expression:
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
[Date].[Calendar
Year].MEMBERS ON ROWS
FROM
[Adventure Works]
WHERE
([Customer].[Customer Geography].[Country].&[Denmark])
