I have a problem with the design of a dimension and I'm hoping someone can
point me in the right direction.

I'm using AS2000.

It is a manufacturing problem.

I have a fact table of finished goods and components that need to be
categorised according to characteristics of the product. A finished good
will have one and only one characteristic, whereas a component is categorised
according to the characteristics of the products it can go to make. For
example,

Item A - Finished Goods - Characteristic 1
Item B - Finished Goods - Characteristic 2
Item C - Component - Characteristic 1
Item D - Component - Characteristic 1 and 2

This would represent that Item C is a component of a finished goods with
characteristic 1 and that Item D is a component that can be used in the
manufactures of finished goods C or D.

The end users would like to be able to select all Characteristics = 1 which
would included Finished Goods, characteristic 1 and Components that could be
included in finished goods with characteristic 1. In the above example, this
would return Items A,C,D.

One alternative is to create a binary dimension for each characteristic,
however, there are about 15 of these. This solution also doesn't seem very
'sophisticated'.

Another solution was to create, in SQL, a binary column for each
characteristic and a calculated member for each of the characteristics in one
dimension (this would yield double counting in this dimension but this would
be what the client is looking for, however, it wouldn't tell us how many
components could be used in a Characteristic 1 or Charactertistic 2 finished
goods.).

Any help/advice gratefully received. Let me know if you need more information