Hello there,

I have a question regarding non unique values in dimension attributes. I'll illustrate the problem using an AdventureWorks example.

First of all I have updated the AdventureWorksDW OLTP database so that 4 records have the same name in the DimCustomer table. The 4 records will be:

CustomerKey,GeographyKey,FirstName,MiddleName,Last Name
----------------------------------------------------------
11963,2,Roger,B,Jones
14307,3,Roger,B,Jones
23644,2,Roger,B,Jones
11024,311,Roger,B,Jones

(i.e.
UPDATE [dbo].[DimCustomer]
SET LastName = 'Jones', FirstName = 'Roger', MiddleName = 'B'
WHERE CustomerKey IN (11963,23644, 14307, 11024) )

I then processed the AdventureWorksDW OLAP Cube based on the updated data.

Now, what I want to do is run the following query (using the name of the value, i.e. Roger B. Jones) and return all 4 records.

SELECT ([Date].[Calendar].[Calendar Year].&[2004],{[Measures].[Internet Sales Amount]}) on columns,
non empty ({[Sales Territory].[Sales Territory Country].children},
{[Customer].[Customer Geography].[Customer].[Roger B. Jones]})
on rows
FROM [Adventure Works]

However (as expected) when we run this the results are:


CY 2004
Internet Sales Amount
United States Roger B. Jones $14.23


i.e. it only returns one of the 4 results.

The only way for me to return all four results is by explicitly using the CustomerKey as follows:

SELECT ([Date].[Calendar].[Calendar Year].&[2004],{[Measures].[Internet Sales Amount]}) on columns,
non empty ({[Sales Territory].[Sales Territory Country].children},
{[Customer].[Customer Geography].[Customer].&[11963],
[Customer].[Customer Geography].[Customer].&[23644],
[Customer].[Customer Geography].[Customer].&[14307],
[Customer].[Customer Geography].[Customer].&[11024]})
on rows
FROM [Adventure Works]

My question is.....is there a way of using the name (i.e. [Customer].[Customer Geography].[Customer].[Roger B. Jones]) to return all 4 records?

Thanks,
Vaughan.