Since this series is an introduction to MDX (Multidimensional Expressions), let’s start off with a few definitions. The following terms form the foundation of our discussion of MDX and Cube Space.
Measure: A value used to asses a business process or event. Measures are typically numeric and often map to fields in your fact table. These values are frequently aggregated in your queries. Examples are Sales Amount and Order Count.
Measure Group: A container into which related Measures are organized. These typically map to fact tables. Examples are Internet Sales or Reseller Sales.
Dimension: A collection of information used to categorize Measure values. These form the BY or the FOR EACH of the questions your queries are meant to answer. An example would be a Publisher dimension. Dimensions usually map to a Dim table.
Attribute: A row or field in a Dimension. Attributes are organized into hierarchies. Examples include a Publisher Name in a Publisher dimension.
Member: A distinct value within an attribute’s domain. An example would be the [Random House] in a Publisher Name attribute.
Hierarchy: A structure in which dimension attributes/members are organized at different levels relating to each other. An example would be a Date hierarchy in which Dates roll up to Months, Months to Quarters, and Quarters to Years.
Attribute Hierarchy: A hierarchy composed of a single attribute. Figure 1 shows an example.
Figure 1
User Hierarchy: A hierarchy composed of more than one attribute. Figure 2 shows an example.
Figure 2
Cube Space
The Cube Space is a very different place from your relational database environment. It requires a different mindset to navigate. Rather than Boolean logic of whether field values meet the criteria within various filters, the Cube Space is all about intersections of dimensions in the N-Dimensional space. The “N” in this term just stands in for the number of dimensions you have, including the Measures dimension itself. I find keeping this in mind to be hugely helpful when I am dealing with MDX queries and scripts.
Figure 3 shows a one-dimensional space; essentially, a number line. This is Dimension A.
Figure 3
If we want to refer to a particular location within Dimension A, we can just use the name of that location. For example, in Figure 3, location (A3) is highlighted.
We can add another dimension to our space, creating the 2-dimensional space shown in Figure 4.
Figure 4
In this space, we can reference a particular location using the coordinates (A3,B5). This names a particular intersection of Dimension A and Dimension B.
Figure 5 shows the effect of adding a third dimension, Dimension C.
Figure 5
In this space, we reference locations using a 3-part coordinate, (A3,B5,C7). So far so good.
I have seen explanations of the cube space using the familiar X,Y and Z dimensions you may recall from Geometry class. You may recall that the X Axis is the Horizontal Axis. Y is the Vertical. Z then comes into the picture referring to Depth, adding that third dimension. All 3 are necessary for picturing a physical Cube. Going down this path can help you picture an Analysis Services cube with 3 dimensions as a physical object you can hold in your hand. The problem I have with this model is that SSAS Cubes will typically have more than 3 dimensions. Now, I challenge you to picture a physical object that has 4 dimensions that you can hold in your hand. How about 5? You can see pretty quickly that this model of picturing a physical object breaks down REALLY quickly. Thus, to me, it is not a good way to go.
I prefer to leave physics out of the whole thing. I don’t picture an object I can hold or than can exist within the bounds of Physics. Rather, I picture an SSAS Cube as just a collection of intersecting number lines, like in the figures I have shown so far, with each number line being a Dimension. By using this more linear style way of thinking, picturing the cube space with more than 3 dimensions is easy. Figure 6 demonstrates this pretty well.
Figure 6
You can see in Figure 6 that adding the fourth dimension, Dimension D, is no big deal at all. It works just fine. Figure 7 takes it even further.
Figure 7
Here we have six dimensions and it still works.
So, in this first post of my MDX Decathlon series, my main point is that by picturing the Cube Space as just a collection of intersecting number lines, rather than the physical cube that is often suggested, it helps keep things from getting too complicated too quickly. It was when I figured this out that MDX started to click for me. I got it. Believe me, I still have a lot to learn about MDX, but this foundation is really important.
Mark Vaillancourt 