Hi

A modelling question - what does anyone think is the best way to model
a dimension in which each fact can have multiple values?

I've been thinking about this, and I think I've found a way to fit the
requirements in my particular situation - but wonder if anyone has
found a better way?

Here's what's going on:
i. Each fact is a completed survey form.
ii. Among the questions, there are three "most important problem"
questions. For each, the answer is restricted to selection from a
single list of problems (9 possible problems: "funding","lack of time"
etc).
iii. No particular precedence exists in this set of 3 - i.e. the
questions aren't supposed to be answered with problems in order of
importance: they're just 3 equivalent spaces for important problems to
be recorded.
iv. Data validation ensures that no problem can be recorded twice on
the same form (import routine gets rid of duplicates).

The reporting requirements are:
a) most importantly, an OR combination of the 3 answers. i.e. we don't
care whether "funding" came up in question 1, 2 or 3 - just that it was
mentioned in any one of them.
b) possibly: correlation between answers: e.g. "show cases where
Funding and Lack of Time were selected".
c) Definitely not: AND combination of the 3 answers. Since there's a
list of 9 possible problems, there would be 84 combinations of 3
distinct problems. This would give very sparse results, and thus
wouldn't be useful.

Because (c) the AND combination isn't required, I don't want to create
a single dimension consisting of all the possible combinations - this
wouldn't allow the OR question (a) to be answered, and the correlations
(b) could only be worked out by manual inspection of the description of
each combination.

On the other hand, using 3 separate dimensions seems to be an even
worse solution: because you'd have to run 3 separate MDX queries to
answer the question "how many people selected Funding as a problem?"
(one query for each dimension). Doing correlations would be even more
difficult.

The only good solution I can think of is to create a separate dimension
for each possibly problem: i.e. 9 dimensions called Funding, Lack of
Time, etc.... , each with two members: Yes and No. This will do the
job, but there's things I don't like about it:
- If the list's population is changed, this will mean a change to the
cube's basic structure, rather than just a repopulation of one or more
dimensions. I'm trying to keep the population process for my dimension
tables (and the cube rebuild SQL in general) as automatic as possible
(I'm a contractor, and there won't be any OLAP/MDX people here once I
leave).
- It would be unworkable if the list expands much beyond 9 members.

Can anyone think of a better solution?

thanks for any thoughts


Seb