Newbie MDX Question

Let's say I'm doing a fairly standard MDX query:

SELECT
{[Measures].[Revenue], [Measures].[Expenses], [Measures].[Profit]} ON COLUMNS,
[Products].[Name].Members ON ROWS
FROM [My Cube]

This will show the product name on the row axis, and several measures along the column axis.

What if I also want to get the product ID (numeric key) in addition to the product name in my query. How would I do that? I suspect that a cross join would work, but there is probably a much better way.

I'm using ADOMD.NET to process the results. Might this product ID already be embedded in the returned CellSet object?

Assuming that you have attribute ProductName with numeric key and text name column, you can do this:

SELECT
{[Measures].[Revenue], [Measures].[Expenses], [Measures].[Profit]} ON COLUMNS,
[Products].[Name].Members PROPERTIES MEMBER_KEY ON ROWS
FROM [My Cube]

You will find member property KEY in the CellSet and will be able to display it.

|||That almost works...

I get the key of the name attribute:

cellSet.Axes[1].Set.Tuples[1].Members[0].Caption is "Favorite Product"

cellSet.Axes[1].Set.Tuples[1].Members[0].MemberProperties has one element:
Name = "MEMBER_KEY"
UniqueName = "[Products].[Name].[MEMBER_KEY]"
Value = "Favorite Product"

It looks like I'm getting the key for the name attribute rather than the key for the Product itself. What am I doing wrong? Do I need to change the main MDX query?|||As I said: "Assuming that you have attribute ProductName with numeric key and text name column". It is not possible to answer your question without knowing which attributes are created from which columns. I assumed the best practice scenario, but it looks like your key of ProductName attribute is not from the product id field, but from the product name field (i.e. name and key share the same column), which isn't such a good practice.|||Perfect. I switched it to from
[Products].[Name].Members PROPERTIES MEMBER_KEY ON ROWS
to
[Products].[Products].Members PROPERTIES MEMBER_KEY ON ROWS

and it works perfectly. My relational table is very simple. It's named "Products" and has columns ID (integer) and Name (varchar). Did I do something wrong in my dimension setup where I need to do
[Products].[Products].Members
rather than just
[Products].Members?

I'm using Microsoft Analysis Services 2005. Is there anything in particular I should look for?

Otherwise, thank you very much. I am getting the data that I need.
|||My recommendation is instead of keeping two attributes Name and Products, have just one, and use Product ID as a key column, and Product Name as a name column.|||Will do. Thank you so much for the assistance. This MDX stuff is great! It's currently 100x as fast as our previous SQL system (and I suspect it scales better as well).