Transcript

[00:00:00.00]

In this lesson I’m going to introduce MDX (Multidimensional Expressions), which is the language that we use when we’re working with Multidimensional cubes in Analysis Services.

So first, what is MDX? Let’s define it. MDX Stands for Multidimensional Expressions. These are queries or calculations that we use to analyze the data that we store in our OLAP databases.

[00:00:26.11]

This is an expression language that was invented by Microsoft engineers back in the late 90′s, and was introduced with Microsoft OLAP Services in SQL Server 7. MDX became a standard way to query Multidimensional databases and was implemented by a lot of other vendors. It’s still used in other solutions today, however Microsoft Analysis Services is probably the product most closely associated with this language because it’s so widely adopted.

[00:00:56.13]

Let’s look at the basic MDX syntax. I assume you know something about SQL. So as we look at MDX, we’ll compare it with SQL and try to draw some parallels to help you understand it.

This is a basic query. It’s analogous to a “SELECT FROM table” that we would use in SQL Server. It begins with (normally) a SELECT keyword, though it can have the “WITH” keyword that we’ll get into later on.

[00:01:24.19]

We might write “SELECT … on COLUMNS”. This is just like what we would use in SQL. In SQL we would write “SELECT A, B, C”, and that’s a lot like the “ON COLUMNS” section in MDX.

ROWS is more like a pivot table. If you think about selecting some things on columns and other things on rows, that basically maps to a Pivot Table.

[00:01:50.14]

Then we have FROM. Normally in FROM you’ll use the cube’s name (in most simple queries). Cube name here really is like a “database” in a SQL Server environment. A relational “database” collects tables, and an OLAP cube collects measures and dimensions.

[00:02:07.04]

The WHERE clause normally does the same things you expect in SQL. It limits the results that are returned by your query.

[00:02:13.05]

As far as notation in MDX, let’s talk about the things you’ll see in queries and make sure you understand them.

You’ll see a lot of brackets in MDX. Brackets are used to indicate identifiers that have spaces in them. This is the same that we would use in T-SQL in the SQL Server relational product. Braces are used to indicate sets of things. This is very much like mathematics, where we have set notation. We use braces for example in {1, 2, 3, 4} to show a set of those four integers.

[00:02:44.17]

When you see braces, it just means “within here is a set of stuff”. You’ll see this quite a lot in MDX. It will show up in places where in SQL you wouldn’t need anything to indicate a set of elements, but in MDX you always need braces to collect sets of things together.

[00:03:00.17]

The ampersand works kind of like it does in the “C” programming language, where it indicates “the address of” or “the key of”. So in the expressions to the right,

[Time]

.[Month]

.[Jan]

means “the member corresponding to January”. Underneath we have[Time]

.[Month]

.&[1]

, which means “the month that has the key of 1″. In this case, it’s “January”. So in this example the two expressions mean the same thing. 

[00:03:31.05]

Finally we have parentheses, which are used to express a tuple. There are two pronunciations, both are OK. You can think of a Tuple as an intersection. So in this expression we’re pointing to “Sales in January”, which is a specific value that occurs where these two elements intersect. It’s very much like a cell in Microsoft Excel.

[00:03:52.26]

If you think of a cell in a spreadsheet as, for example, the intersection of column A and row 4, that’s basically the same thing as a Tuple in MDX. The parentheses are indicating the intersection of whatever is between them in this case.

Category:

SSAS Multidimensional

Tags:

Leave a Reply

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

*