Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

MDX 101: Introduction to MDX

  • 1 September 2010
  • Author: DustinRyan
  • Number of views: 21974
  • 0 Comments
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.

SSAS Cube Example

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.

SSAS Hierarchy

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].&[2006], [Sales Reason].[Sales Reason].&[9])

 

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.

Basic MDX Query example

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.

 Open MDX Query window 

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]
;

 

 Results:
query results

 

If you execute the query seen above, you'll see a single number returned. While it would appear that we have not defined a measure or dimension in our query, the number being returned by the query is actually the default measure specified in the Adventure Works cube, which is the Reseller Sales Amount. Since no dimension has been specified, this query returns the sum total of the Reseller Sales Amount across all products, all time, all regions, etc. So unless you explicitly specify in your query which measure you want to see, the measure that is going to be returned is the default measure Reseller Sales Amount.

One way we could explicity request a different measure is to use the Where statement and specify a slicer.

 







SELECT
FROM [ADVENTURE WORKS]
WHERE [Measures].[Reseller Total Product Cost]
;

Results:
Basic Query with slicer

 

Because we have used the Where statement to limit the scope of the query to the Reseller Total Product Cost, this query will return the sum total of the Reseller Total Product Cost, instead of the Reseller Sales Amount, across all dimensions. 

Now lets specify some dimensions by which to slice and dice our cube with.

 

 







SELECT [Date].[Calendar].[Calendar Year] ON COLUMNS,
[Product].[Product Categories].[Category] ON ROWS
FROM [ADVENTURE WORKS]
; 

 

Results:
Basic Query with dimensions on two axes

 

Now that we have specified a dimension on both the Columns axis and the Row axis, we are starting to realize the power of the cube. Compare the above MDX query to a SQL query that would return similar results and you will see one of the reasons why MDX (and Analysis Services) is so powerful. Can you guess what measure is being displayed in our query results? If you said the Reseller Sales Amount, that would be right. Once again, because we have not explicitly specified which measure to bring back, the default measure Reseller Sales Amount will be returned.

For our last query, lets bring it all together and specify a dimension and attribute on each axis and a slicer dimension:

 







SELECT [Date].[Calendar].[Calendar Year] ON COLUMNS,
[Product].[Product Categories].[Category] ON ROWS
FROM [ADVENTURE WORKS]
WHERE [Measures].[Reseller Total Product Cost]
; 

Results:
Basic MDX query with dimensions and slicer

The above query is exactly the same as the previous query except now we have explcitly limited the scope of the query to the Reseller Total Product Cost. 

MDX Books to Check Out

 

Here are a few great MDX books I'd like to recommend if you're interesting in learning more about MDX:

MDX Solutions - http://www.amazon.com/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080/ref=sr_1_2?ie=UTF8&s=books&qid=1284385194&sr=8-2

Microsoft SQL Server 2008 MDX Step by Step - http://www.amazon.com/Microsoft-SQL-Server-2008-Step/dp/0735626189/ref=sr_1_1?s=books&ie=UTF8&qid=1284385251&sr=1-1

Practical MDX Queries - http://www.amazon.com/Practical-MDX-Queries-Microsoft-Analysis/dp/0071713360/ref=sr_1_1?s=books&ie=UTF8&qid=1284385281&sr=1-1

 

Fast Track to MDX - http://www.amazon.com/Fast-Track-MDX-Mark-Whitehorn/dp/1846281741/ref=sr_1_1?ie=UTF8&s=books&qid=1284477262&sr=8-1

 

Pracitcal MDX Queries is an especially helpful book and very good for reference when you are looking for the right MDX function. Make sure to check these books out when you start digging into MDX.

MDX Solutions is also a very helpful book. While it has several chapters aimed at the beginner, it also contains more advanced MDX topics. MDX Solutions is the first book I used when I started digging into MDX.

I hope this article has given you a good starting point so that you can begin writing your own MDX queries. Stay on the look out for my part 2 article in which we will cover more advanced MDX functions and queries. As always, feel free to shoot me a message, leave a comment, or email me at .dryan@pragmaticworks.com

Print
Categories: Query Languages
Tags:
Rate this article:
3.0
DustinRyan

DustinRyanDustinRyan

Other posts by DustinRyan

Please login or register to post comments.