Hi All,

We are designing an application for healthcare and are faced up with the
following situations. We are using SQL Server 2000 Analysis Services.

a) Many to many relationship between fact and dimension tables : For eg. a
claim line as the fact table grain has multiple diagnosis associated with
it. The solution proposed by Ralph of having a bridge table with diagnois
grouping is workable. But the issue here is every diagnosis group related to
a claim is different and we may end up with as many rows in the dimension
table as the fact table. The question is performance.

b) We are trying to study both the enrollment and claim information and
placing the same in a single fact table. We maintain nulls for claim
information where enrollment row exists and vice-versa. Does this violate
the basic principles of DW?

c) Can we have multiple foreign keys of a single dimension table in a fact
table row. For eg a single claim line could have enrollment diagnosis and
claim diagnosis which relates the same diagnosis code table.

d) Is there a provision of saying " Do not aggregate a measure(M1) with
respect to one particular dimension(D1). We tried using source table filter
in fact and dimension tables. But that does not look like solves this issue,
since it ignores the entire row. Another option was placing null in the
dimension key. But then too, the complete row is ignored. Having '0' in the
dimension table is treated as another foreign key.

A solution of having different fact tables was considered, but other project
compulsions need a unified fact table

Thanks in advance

Jaya Prakash

Good questions Jaya. I almost hesitate to provide quickie answers. Here you
go though.

a) There is no easy solution for your many-to-many sutuation. This is
typically (?) solved by generating multiple fact table rows in place of the
single row.
b) Yes, this is contrary to common practice
c) Yes. If there is potential for numerous of these though (i.e. follow-up
diagnosis 1, follow-up 2, etc.) you should consider a "which diagnosis"
dimension. (In other words, avoid repeating groups.)
d) No such provision. What you can do though is create a calculated member
based on M and then hide M1.

tom @ the domain below
www.tomchester.net


"Jaya Prakash" <jaiprakash (AT) nous (DOT) soft.net> wrote