MDX with Analysis Services - Overview

Multi-Dimensional eXpressions (MDX) is a language used for querying and extending capabilities of Analysis Services cubes. MDX is typically used for two purposes:

1. Creating reporting queries. Such constructs contain SELECT, FROM and WHERE clauses (among other elements) and are referred to as MDX queries or MDX statements.
    
2. Defining cube structures such as calculated members, named sets, actions, key performance indicators and so forth. Such constructs are referred to as MDX expressions. MDX expressions do not contain SELECT, FROM or WHERE clauses; they are used to define calculations using cube dimension members and measures.

If you examine a basic MDX statement it might seem that it contains parts similar to SQL statements: the SELECT, FROM and WHERE clauses. However, that is where the similarity ends. MDX is very different from SQL because it is written for traversing dimension hierarchies and defining cube cells. MDX doesn't support variables, parameters, cursors and other common SQL structures. On the other hand, MDX is more powerful than SQL when it comes to referencing hierarchy members. Even if you have programmed SQL queries for a decade getting used to MDX might take a while.



Fortunately there are 3rd party tools on the market that create MDX queries for you. Such tools can be used for browsing the cube data and could also help you learn MDX. Unfortunately, MDX queries generated by the 3rd party tools are typically generic and therefore not as optimal as they could be. Furthermore, the tool vendors often extend the MDX functionality by writing custom functions, so queries generated by such tools might not always work for your application. 



When writing MDX queries you need to make a clear distinction between the data and metadata. Cube data is what you present to your users, for example you would show the sales amount for Ford Taurus 2005 and Toyota Camry 2004. Metadata is how the data is structured in your dimensions; for example, the product dimension could have levels of brand, make, model and year. It is important to realize if you are trying to refer to data or to metadata within MDX because different functions are used to address each need. For example, if you wanted to return all brands within your product dimension you would use the "members" function, as in [product].[brand].members. On the other hand, if you wanted to see all makes within a certain brand you would use the "children" function by providing the name of the parent, as in [product].[brand].[Toyota].children. 



This section will introduce you to basic MDX concepts and then show you how to make your expressions more powerful by adding various functions.