January 11, 2010 at 4:20 am
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,LastName
----------------------------------------------------------
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.
January 11, 2010 at 9:11 am
would this work ?
SELECT ([Date].[Calendar].[Calendar Year].&[2004],{[Measures].[Internet Sales Amount]}) on columns,
non empty ({[Customer].[Customer Geography].[Customer].[Roger B. Jones],[Customer].[Customer Geography].[Customer].[Customer Key]})
on rows
FROM [Adventure Works]
I dont have the adventure works DB on my machine to test but I think you would need to select a distinct value for each 'roger b jones' in order to be able to see individual entries
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply