Hello,


We have a problem of defining a n:n relation in a cube.
when adding the related table and the dimension the total
number of rows of the fact table is inflated (as it would
usually be with an inner join).

The problem is the following:

A user table contains a row for each user. A category
table contains categories. Each user can be a member of 1
or more categories. Within the cube I should be able to
drill down by category and show the user under each of the
categories of which the user is a member of. I appreciate
that this in itself will inflate any measures, which is ok
when breaking down by category -- but it should only
happen then ( and not when using other dimensions ).

Is this possible? And if so, how can it be accomplished?


/Martin

Martin,
a m:n relation is usually reflected by using two
different dimensions. The number of fact table rows is
depending on dimension count, dimension level depth and
dimension member count, thus defining the grain or
granularity of the cube.
I don't think there is something you can do within your
cube. Adding a dimension changes the grain of a cube and
makes the number of fact table rows grow.
It just works that way.

You could, however create separate cubes with different
dimension counts and therefore different grain.


HTH Lutz Morrien

Here is an 'out of the box' idea...

Make a Fact Table that looks like this:


Key Month Qty
1 200101 5
2 200101 10
3 200101 7
4 200101 12

For the n:n relationship you want to have in the cube -
Let's say the dimension is called: MultiDim

And make a dimension that looks like this:

Key Membername MemberType Custom Member Formula
1 name1 A
2 name2 A
3 name3 A
4 name4 A
r1 name1 B [Multidim].&[1]
r2 name2 B [Multidim].&[2]
r3 name4 B [Multidim].&[3]
r4 name5 B [Multidim].&[2]

----------

What's going to happen is...
You'll have a dimension - that has 'many to many' relationship.....
The in reality you will only have One to One in the cube. The extra
ones are handeled through the 'custom member formula'

Anyway.... that's a way I implemented it.
Thanks