Hello all,

The problem that I have is rather phylosophical: how can I implement
dimension with non-unique key without aggregations being screwed up?
Let's say I have a Diagnosis dimension keyed by Episode_ID.
Episode N might have more than 1 diagnosis. In this case all the
measures in episode fact table for Episode N will be multiplied by
number of diagnoses just because of the table join at cube processing
time.

How can I avoid that? Looks like non-unique key dimension will always
produce wrong aggregations?

Michael Soolkin

One approach, that I have used in similar situations, is to create a
second fact table and cube, then combine the 2 cubes in a virtual cube.
So, in this case, the second fact table would have multiple rows per
episode, one per diagnosis for that episode. The diagnosis dimension
would only be applied to the second fact table, and there would be no
aggregation issue, since each row joins to just one diagnosis member.
Don't know if this meets your needs.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Use Custom Member Formula and/or Custom Rollup Formula

I have done very similar to what you describe. Let me know if you
need more details.

Thanks Harsh,

I thought of that, but I have a Distinct Count measure (number of
clients), so I can't use custom rollup.
Maybe there is a way to formulate calculated member instead of DC
measure, but I can't yet .
As for the Brian's suggestion (Kimball's book), book is great, but he
is recommending weight factors for multi-valued dimensions, which is
not gonna fly. We can't factor let's say length of stay per diagnosis.

Thanks

Michael

creative (AT) mailcity (DOT) com (Harsh) wrote in message news:<fa671a26.0408111725.1f80e17c (AT) posting (DOT) google.com>...

Hi Harsh,

It would be great if you could provide more details on Custom Rollup
formula that you said was similar to what I might need.
I think it should be on the multi-valued diagnosis dimension, but I
don't think I understand how to write it.
Thanks in advance...

Michael



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!