Intro To MDX Decathlon–Cube Space: The Final Frontier (1/10)

30 July, 2012 (13:01) | Decathlon, MDX | By: Mark V

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

image

User Hierarchy: A hierarchy composed of more than one attribute. Figure 2 shows an example.

Figure 2

image

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

image

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

image

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

image

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

image

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

image

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.

Comments

Pingback from Intro To MDX Decathlon–Introduction | Mark V SQL
Time July 30, 2012 at 1:14 pm

[…] 1. Intro To MDX Decathlon – Cube Space: The Final Frontier (1/10) […]

Pingback from Intro To MDX Decathlon–The Basic MDX Query (2/10) | Mark V SQL
Time August 6, 2012 at 1:20 pm

[…] the previous post in this series, Intro to MDX Decathlon – Cube Space: The Final Frontier (1/10), we learned the terminology we use when talking about cubes and cube space. We also gave a little […]

Pingback from Intro To MDX Decathlon–The Trouble With Tuples (3/10) | Mark V SQL
Time August 13, 2012 at 12:30 pm

[…] that we have defined a cell, let’s look at one from my first post in this series, Intro To MDX Decathlon–Cube Space: The Final Frontier (1/10). Figure 1 shows four dimensions, each named with a letter, A thru […]

Pingback from Five SQL Server Bloggers to Watch in 2013 | Doug Lane
Time March 5, 2013 at 10:56 am

[…] one post you don’t want to miss: MDX Decathlon […]

Comment from Kamon Smith
Time May 19, 2015 at 11:12 pm

This is great!!!! thank

Comment from Mark V
Time May 20, 2015 at 9:30 pm

Thanks, Kamon. I’m glad it is helpful. :)

Write a comment