Here is a cube design problem.
In the data file we have a field named SERVICE which is a colon
separated list of transport modes. The value in this column looks like
"Bus:Road:Rail", "Road:Rail", etc.
We intend to use a dimension table named DIM_SERVICE[ ID, SERVICE], so
that we can obtain measures such as number of times a service has been
used, etc. Hence single record from the file can contribute to a
measure for more than one members of a dimension.
To build a cube, we would like to enrich the data file and add foreign
key columns before loading the enriched file in a fact table.
The number of transport modes can be a lot. Also there will be more
dimensions like these. Hence we want to avoid creating a column for
every distinct transport mode.
We cannot do anything to change the format of the incoming data files.

Can someone suggest how we should achieve this? We use MS OLAP 2000.
Thanks