MDX 101: Introduction to MDX

About MDX

Multi Dimensional eXpression language (MDX) is an extremely powerful tool that can allow you to fully realize the potential of your multidimensional database/cube. In this article, I'll be covering the basics of MDX and will hopefully provide you with a solid understanding of MDX query syntax, why MDX is so powerful, and how you can use MDX to add serious value to your B.I. solution.

According to MSDN, the purpose of MDX is to make accessing data from multiple dimensions easier. And thats exactly what MDX does, which makes it ideal to view data broken down by multiple categories and aggregated. You may be asking yourself, "Why not just write a SQL Stored Procedure and query the data warehouse? Anything I can get from the cube I can get from the data warehouse!" While this is true, imagine you have been given the requirements for a report, which should include Actual Sales, Sales Goals, and Prior Period Sales broken down by Sales Region, Stores, and Month grouped by Year. Writing a stored procedure to pull the required data from the data warehouse would be large and messy and more than likely the procedure would execute very slowly simply because a data warehouse is not optimal for reporting on aggregated data. Because a cube stores your data in an aggregated format, MDX can allow you to view that aggregated data quickly and efficiently across multiple dimensions (such as Region, Stores, and Month, just to name a few) in only a few lines of code. In short, MDX can allow you build otherwise extremely complicated queries and reports with just a fraction of the code. Not only will the queries and reports be easier to build, they will execute much faster than if you had queried the same data with a SQL statement.

With that said, lets go over some of the terms we'll need to understand before we can begin writing our first MDX query.

Basic Terms:

Cube: A cube is a collection of measures, or facts, and dimensions which are based on tables and views, usually from a data warehouse. Within the cube are every possible aggregation of measures for every combination of dimensions. For example, Sales may be a measure in a cube. At any time you may want to view Sales broken down by Product. The aggregated Sales for each product are stored within the cube. This is why MDX queries against the cube are so much faster than querying the data warehouse. In the image below, you can see a graphical representation of a cube with multiple axes featuring Time, Product, and Store dimensions. Also, be aware that an Analysis Services cube is not a cube geometrically speaking. The term cube is an accepted industry standard when referring to a multidimensional database.

Dimension: A dimension organizes data in relation to a certain interest. An example of a dimension in the Adventure Works cube is the Product dimension. A dimension can be based on a table, a view, or a select statement bringing together data from multiple tables and/or views.

Attribute: An attribute can be thought of as a qualitative way to describe a dimension. For example, some of the attributes of the Product dimension in the Adventure Works cube are Category, Model Name, Product, and Style, just to name a few.

Measures (Facts): A measure, or fact, contains quantitative data that can be aggregated. Some of the measures in the Reseller Sales measure group in the Adventure Works cube are Reseller Tax Amount, Reseller Freight Amount, and Reseller Order Quantity. These measures can be aggregated across dimensions in our cube. For instance, it may be valuable to the end user to see Reseller Order Quantities broken down by product, region, time period, or all of the above. Measures can be viewed this way.

Hierarchy: A hierarchy is a hierarchical structure of dimension attributes. Within a hierarchy are levels and within a level are members, which you'll learn about in a moment. In the image below, we have an illustration of a hierarchy. You can clearly see how the levels make up a hierarchy and the members that are part of those levels.

Level: As mentioned above, in a hierarchy are levels. In the Adventure Works cube, the Fiscal Year hierarchy is made up of four levels: The top level is the Year, the second level is Quarter, the third level is Semester, the fourth level is Month, and the final level is the Date level. When viewing aggregated measures across a hierarchy, we would be able to see that measure aggregated up to each member in that level. 

Member: A member is a value in a level. An example of a member of the Month level of the Fiscal Year hierarchy in the Adventure Works cube would be March.

Axis: An axis can be thought of as a line on which a dimension rests. This line and the dimension intersect our cube and measures.
An MDX query can have up to 128 axes. Only 5 are aliased.

• Rows
• Columns
• Pages
• Chapters
• Sections

Most MDX queries will only contain 2 axes since most reporting is done in a two dimensional tabular format. There aren't many vendors of multidimensional database software that support views of more than two dimensions  at a time.

Tuple: A tuple is an ordered collection of dimension attributes that are not from the same dimension that uniquely identify a cell in a cube. In the example below, I have a tuple that features the Fiscal Year attribute of the Date dimension along with the Sales Reason attribute of the Sales Reason dimension. Notice that the tuple is contained in parentheses.

--A tuple 
([Date].[Fiscal Year].members, [Sales Territory].[Sales Territory Country].members)

Set: A set is a collection of tuples. In the example of a set (seen below), I have two tuples within the set. The dimensionality of tuples within a set must be the same or your query will throw an error. In other words, the dimensions used in the tuples of a set must be the same and in the same order. A set must be wrapped in curly brackets and each tuple in the set should have parentheses around it.

--A collection of tuples
{
     ([Date].[Fiscal Year].&[2006], [Sales Reason].[Sales Reason].&[9]),
     ([Date].[Fiscal Year].&[2006], [Sales Reason].[Sales Reason].&[5])
}

MDX Query Syntax

Here we have a basic MDX query. Let's take a look at each piece of this query so we can understand the necessary parts to a basic MDX statement.

When you first look at this query, you may think that it appears awefully similar to T-SQL, but that would be where the similarities end. The MDX query statements function very differently compared to T-SQL and it is important to remember that when you are learning to write MDX.

The first thing your MDX query will need is a SELECT statement. The Select statement in an MDX query is very different from a SELECT statement in a SQL query. In T-SQL, the SELECT statement can only define the columns layout of your query results. In an MDX query, the SELECT statement defines the lay out of multiple dimensions along up to 128 different axes. In our example query seen above, you can see that I have specified the Date dimension and Fiscal Year attribute be displayed along the Row axis and the Internet Order Quanity measure be displayed along the Column axis.

The second thing your MDX query will need is a FROM statement. The FROM statement specifies the context of your query, or which cube you wish to query. In T-SQL, you can use the FROM statement to join multiple tables from multiple databases, but in MDX there is no joining of cubes together so don't even think about it.

The last piece of this MDX query is the WHERE statement. The WHERE statement limits the results of our MDX query by specifying what is known as a slicer dimension. You can see in in this example that I am restricting the results of the MDX query to only the 1 member (which is actually the Bikes category) of the Category attribute of the Product dimension.

Basic Queries To Get Us Started

So now that we've learned a little bit about MDX, have covered some of the necessary terminology, and understand the MDX query syntax, lets start writing some MDX.

Important Note: All of the examples in this article are for use with the Adventure Works 2008 Analysis Services database, which can be downloaded from here. Once the database is downloaded and installed, open the Analysis Services project (located by default in c:\Program Files\Microsoft SQL Server\100\Tools\Samples) in BIDS. Make sure you change the data source connection to the instance of SQL Server where you installed the sample database. Then deploy the project.

Open SQL Server Management Studio. When the Connect to Server dialogue window pops up, make sure to select Analysis Services in the drop down list next to Server type. Specify the correct server where the Adventure Works AS database is at and click Connect.

Navigate to the Analysis Services database called AdventureWorksCube. Right-click, select New Query, and select MDX.

Now that the MDX query editor window is open, we can start writing some MDX. The first query is very simple and its purpose is to show us the default measure of the Adventure Works cube.

SELECT
FROM [ADVENTURE WORKS]
;