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.
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.
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.
([Date].[Fiscal Year].&, [Sales Reason].[Sales Reason].&)