Returning multiple attributes from the same dimension at the same level

This seems like it should be basic, but I can't find any examples that explain if this should be possible, or if I'm just approach the problem incorrectly. I've got several books on MDX and it seems every example uses only one column from a dimension to perform any reports.

Let's say I have a customer dimension with the attributes:

Customer Id, FirstName, LastName, MiddleName, State, City

A standard date dimension and a fact table that tracks Orders.

In my Customer Dimension I define a hiearchy that has State, City and Customer Id.

With this simple setup, I am able to show Order totals by Customer Id, City or State with no problem. The issue I am having is if I want to write a query that shows order totals by the person, with their first, middle and last name. I can write a cross join with all of these dimensions, but as soon as I add in the Last Name or any other attribute, the query never returns. Is there an architectural thing I am missing that this isn' t how I should structure my cube or is there a way in MDX to have it return other attributes of the dimension along with the attribute that is part of the Hierarchy. I.e, I want results like:

First Middle last Customer Id Month Order Total

Joe M. Smith 123 July $1000

Sally T. Smith 456 July $2000

Is this something I need to structure differently in my Cube (2005) or is there another construct in MDX other than a crossjoin that allows me to get the Rows to contain First, Middle and last name along with the attribute from the hierarchy (Customer Id).

Thanks!

Luke

Luke,

When you crossjoin your attributes you are creating a lot of combinations that do not have any data associated with them and depending on the number of members in each of your attributes this could be a very large result set. Try using NON EMPTY to filter out empty cells:

SELECT

NON EMPTY {Customer.First.DefaultMember.Children * Customer.MI.DefaultMember.Children * Customer.Last.DefaultMember.Children * Customer.ID.DefaultMember.Children} ON ROWS...

HTH,

Steve

|||

You can add a named calculation to your customer table in the data source view(like FullName). Use this attribute in the the dimension editor as the name column for your CustomerId attribute.

Regards

Thomas Ivarsson

|||

I ended up doing three things:

Creating a user hierarchy (on the columns that really are not in a hierarchy)

Creating a query using the hierarchy that referenced the lowest level member

Used a different tool for seeing the results. There is a lot of meta-data returned that all viewers do not show. When accessing through ADO, I received the Unique Name that had all the levels in the hierarchy. This was not show in the Management Studio query interface.

The calculation idea is probably the best way, but it seems odd there isn't another way to do this. I'm still not satisfied with this solution. It seems like this should be a common problem, but I can't find any real reference or example to it.