How to add column with the value of another dimension?

Question

I appologize if the title does not make sense. I am trying to do something that is probably simple, but I have not been able to figure it out, and I'm not sure how to search for the answer. I have the following MDX query:

SELECT
  event_count ON 0,
  TOPCOUNT(name.children, 10, event_count) ON 1
FROM
  events

which returns something like this:

|               | event_count |
+---------------+-------------+
| P Davis       |         123 |
| J Davis       |         123 |
| A Brown       |         120 |
| K Thompson    |         119 |
| R White       |         119 |
| M Wilson      |         118 |
| D Harris      |         118 |
| R Thompson    |         116 |
| Z Williams    |         115 |
| X Smith       |         114 |

I need to include an additional column (gender). Gender is not a metric. It's just another dimension on the data. For instance, consider this query:

SELECT
  gender.children ON 0,
  TOPCOUNT(name.children, 10, event_count) ON 1
FROM
  events

But this is not what I want! :(

|              | female | male | unknown |
+--------------+--------+------+---------+
| P Davis      |        |      |     123 |
| J Davis      |        |  123 |         |
| A Brown      |        |  120 |         |
| K Thompson   |        |  119 |         |
| R White      |    119 |      |         |
| M Wilson     |        |      |     118 |
| D Harris     |        |      |     118 |
| R Thompson   |        |      |     116 |
| Z Williams   |        |      |     115 |
| X Smith      |        |      |     114 |

Nice try, but I just want three columns: name, event_count, and gender. How hard can it be?

Obviously this reflects lack of understanding about MDX on my part. Any pointers to quality introductory material would be appreciated.

Answer 1

It's important to understand that in MDX you are building sets of members on each axis, and not specifying column names like a tabular rowset. You are describing a 2-dimensional grid of results, not a linear rowset. If you imagine each dimension as a table, the member set is the set of unique values from a single column in that table.

When you choose a Measure as the member (as in your first example), it looks as if you're selecting from a table, so it's easy to misunderstand. When you choose a Dimension, you get many members, and a cross-join between the rows and columns (which is sparse in this case because the names and genders are 1-to-1).

So, you could crossjoin these two dimensions on a single axis, and then filter out the null cells:

SELECT
  event_count ON 0,
  TOPCOUNT(
    NonEmptyCrossJoin(name.children, gender.children), 
    10, 
    event_count) ON 1
FROM
  events

Which should give you results that have a single column (event_count) and 10 rows, where each row is composed of the tuple (name, gender).

I hope that sets you on the right path, and please feel free to ask you want me to clarify.

For general introductory material, I think the book "MDX Solutions" is a good place to start: http://www.amazon.ca/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080/