For an "impure" star-schema where a single fact can correspond to
multiple values from a dimension (for example, a single patient visit
to a hospital with multiple diagnosis), can anyone identify how to
"crack this nut" in the wonderful world of Microsoft OLAP services? I
found a White Paper on the Microsoft site which describes the use of
COUNT DISTINCT, but it sure sounds like an *UGLY* solution.
I can build cubes when an underlying star-schema has "proper" joins
with many on the fact side, and 1 on the dimension side, like this:
FACT >------ DIMENSION
but the special case of many on the DIMENSION side (just this one
dimension), like this:
FACT ------< DIMENSION
is really giving me hearburn!
Yes, I know what I describe is not, strictly speaking, a "star-schema"
- but contemporary texts, such as Ralph Kimbel's "Data Warehouse
Lifecycle Toolkit" describe this special case (and many others) as
essential in dimensional modelling.