Hello, I Need some help with my cube and how I should design the fact table
and dimensions. I'm a beginner on OLAP/MDX so maybe it's a simple task I'm
trying to do .... I've done some cubes that I access with MDX and return the
result in an ASP application. The problem I have is how to handle records in
the fact table that have a relation, a one to many relation, to a dimension.
I'll try to explain with a simple example:

I have three tables in the database:

Customer:
CustomerID
SaleAmount
Active

CustomerGroups:
GroupID
Name

CustomersCustomerGroups:
CustomerID
CustomerGroupID


Table Customers consist of customer records with "measure" columns, this
table is my "fact table". Table CustomerGroups consist of customer groups,
CustomerGroup is a dimension. Table CustomersCustomerGroups consist of the
relation between Customers and CustomerGroups, ie in this table a customer
is connected to one or more groups. No dimension on this (maybe it should be
that?).

When I do a cube of this, Customers will be the fact table, I have
dimensions on CustomerGroups and Customers. I have included the table
CustomersCustomerGroups in the cube and it's connected to table Customers
(connection with CustomerID) and then I connect dimension CustomerGroups to
the CustomersCustomerGroups table. My problem now is that this affects my
result. If Customer 1 has a record in table Customers with SaleAmount 1000
AND belongs to two groups, ie two records in CustomersCustomerGroups,
SaleAmount for Customer 1 will be 2000 if I browse my cube and that is not
what I want, it should still be 1000. Just becasue that customer belongs to
two groups he still has a sale amount of 1000.

The groups should only be used to set conditions on, maybe I only wants to
see customers that belongs to group 1 and 4, then I should specify that in
my MDX query. The result should be summerized on SaleAmount in Customer and
should not be multiplied with the amount of times that the customer exists
in table CustomersCustomerGroups.

Should I change my design or could a MDX statement solve this?

Many thanks
C-J

A fact table without Time is unusual.

tom @ the domain below
www.tomchester.net

Many-to-many joins are difficult to deal with in Data
Marts if you are trying to sum across them.

One solution would be to break the groups into seperate
data marts (ie a seperate Fact table for each group). This
will only work if the maintenance overhead is low - ie new
groups are added rarely.

Another solution I've seen suggested by Kimball (from
the 'Data Warehouse Life Cycle Toolkit') is to add
a 'weighting' to each customer related to the number of
groups they are in, and multiply it to the sale value.
Therefore in your example, Customer 1 will be in 2 groups
and therefore will have a weighting of 0.5. Multiplying
this to your sale total of 2000 gives you the desired
result of 1000. This obviously requires a lot of extra
processing.

Either way there is no simple solution that I've come
across!