Hello all,

I am using SQL server 2000 Analysis services. I have built a cube for
the purposes of counting products with identifying attributes (or
dimensions).

As an exmaple here are my dimensions:

ArtistOnly:
[Key Letter] (for groupoing purposes 1st letter of Artist)
[Artist]
[PrdId]

TitleOnly:
[Title Letter] (for groupoing purposes 1st letter of Title)
[Title]
[PrdId]


As an example a product could a key letter of "S", an artist of
"Seal", and a title of "Beginning".

Here is my query to return the list of titles of an artist:

select {[Measures].[DistinctCount]} on
columns,Filter([TitleOnly].[Title].members,[Measures].[DistinctCount]=1)
on rows from [Music] where [ArtistOnly].[Artist].[Seal]

Here is my query to return a list of artists based on the title:


select {[Measures].[DistinctCount]} on
columns,Filter([ArtistOnly].[Artist].members,[Measures].[DistinctCount]=1)
on rows from [Music] where [TitleOnly].[Title].[Beginning]


Now I need to return the PrdId(the lowest level in the dimension) in
addition to the artist or title. I have tried using a crossjoin but
this seriously affects the performance of the cube. How can I simply
add the field to the results? Am I missing an obvious solution to this
problem?

Any help or suggestions would be greatly appreciated,

Benny

One approach would be to create a main Product dimension
with just [All] and [PrdID] levels, and with [Artist] and
[Title] Member Properties. Then create [ArtistOnly] and
[TitleOnly] Virtual Dimensions from the Member Properties.
Use a NonEmptyCrossJoin MDX query to add [PrdId] info:

NonEmptyCrossJoin(Filter([TitleOnly].[Title].members,
[Measures].[DistinctCount]=1), [Product].[PrdId].Members)
on rows
from [Music]
where [ArtistOnly].[Artist].[Seal]

Thanks Deepak. That worked perfectly.


"Deepak" <anonymous (AT) discussions (DOT) microsoft.com> wrote